IIASでDb2 AUDIT機能を設定してみた

みなさん、こんにちは
通常DBを利用するときには、昨今のセキュリティ対策や監査対策のために、いつ誰がどういうアクセスをしたかのログを取っておくことが必要です。
それにはGuardiumのような別製品で監査する方法や、データベースネイティブで取得する方法があります。

そこで、弊社一押しソリューションのDWHであるIIASで、監査ログを取得する方法を設定してみました。

IIASにもDb2 AUDIT機能が存在するようです。
（参考資料）Audit policy guidelines
www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.security.doc/doc/audit_policy_guidelines.html

Db2 AUDIT機能では以下の作業が必要になります。
【設定作業】最初に1度だけ行う作業です。
　　1. 監査ログ格納用のスキーマおよびテーブルの作成
　　2. 監査ログを取得する対象のユーザー及びロールグループの確定
　　3. 監査ログポリシーの作成
　　4. 監査ログポリシーへの監査対象オブジェクトのマップ
　　5. アーカイブ関連ディレクトリの作成
【運用作業】運用で繰り返し行う必要のある作業です。
　　6. 監査ログのアーカイブおよびCSVの抽出
　　7. CSVファイルのテーブルへのLOAD
【検索】必要な時に随時行います。
　　8. LOADしたデータの検索

今回は一般ユーザーが実行したSQLの監査ログを取得してみます。
まずは設定作業をおこないます。

1. 監査ログ格納用のスキーマおよびテーブルの作成
監査ログを格納し、検索するためのテーブルを作成します。
スキーマ名は任意の名前です。今回は”AUDIT”というスキーマを作成し、そこににテーブルを作成します。
テーブル作成用のDDLは”/head/home/db2inst1/sqllib/misc/db2audit.ddl”としてIIASのコンテナ内に存在します。
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ db2 “create schema AUDIT”
DB20000I The SQL command completed successfully.
[bluadmin@node0101-fab – Db2wh ~]$
[bluadmin@node0101-fab – Db2wh ~]$ db2 “set current schema AUDIT”
DB20000I The SQL command completed successfully.
[bluadmin@node0101-fab – Db2wh ~]$
[bluadmin@node0101-fab – Db2wh ~]$ db2 -tvf /head/home/db2inst1/sqllib/misc/db2audit.ddl

******* IMPORTANT **********

USAGE: db2 -tf db2audit.ddl

******* IMPORTANT **********

CREATE TABLE AUDIT ( TIMESTAMP CHAR(26 OCTETS), CATEGORY CHAR(8 OCTETS), EVENT VARCHAR(32 OCTETS), CORRELATOR INTEGER, STATUS INTEGER, USERID VARCHAR(1024 OCTETS), AUTHID VARCHAR(128 OCTETS), DATABASE CHAR(8 OCTETS), NODENUM SMALLINT, COORDNUM SMALLINT, APPID VARCHAR(255 OCTETS), APPNAME VARCHAR(1024 OCTETS), PKGSCHEMA VARCHAR(128 OCTETS), PKGNAME VARCHAR(128 OCTETS), PKGSECNUM SMALLINT, PKGVER VARCHAR(64 OCTETS), LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA, GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA, CLNTUSERID VARCHAR(255 OCTETS), CLNTWRKSTNAME VARCHAR(255 OCTETS), CLNTAPPNAME VARCHAR(255 OCTETS), CLNTACCSTRING VARCHAR(255 OCTETS), TRSTCTXNAME VARCHAR(255 OCTETS), CONTRSTTYPE CHAR(1 OCTETS), ROLEINHERITED VARCHAR(128 OCTETS), POLNAME VARCHAR(128 OCTETS), POLASSOCOBJTYPE CHAR(1 OCTETS), POLASSOCSUBOBJTYPE CHAR(1 OCTETS), POLASSOCNAME VARCHAR(128 OCTETS), POLASSOCOBJSCHEMA VARCHAR(128 OCTETS), AUDITSTATUS CHAR(1 OCTETS), CHECKINGSTATUS CHAR(1 OCTETS), CONTEXTSTATUS CHAR(1 OCTETS), EXECUTESTATUS CHAR(1 OCTETS), EXECUTEDATA CHAR(1 OCTETS), OBJMAINTSTATUS CHAR(1 OCTETS), SECMAINTSTATUS CHAR(1 OCTETS), SYSADMINSTATUS CHAR(1 OCTETS), VALIDATESTATUS CHAR(1 OCTETS), ERRORTYPE CHAR(8 OCTETS), DATAPATH VARCHAR(1024 OCTETS), ARCHIVEPATH VARCHAR(1024 OCTETS), ORIGUSERID VARCHAR(1024 OCTETS), INSTNAME VARCHAR(128 OCTETS), HOSTNAME VARCHAR(255 OCTETS)) ORGANIZE BY ROW
DB20000I The SQL command completed successfully.

（省略）

CREATE TABLE EXECUTE ( TIMESTAMP CHAR(26 OCTETS), CATEGORY CHAR(8 OCTETS), EVENT VARCHAR(32 OCTETS), CORRELATOR INTEGER, STATUS INTEGER, DATABASE CHAR(8 OCTETS), USERID VARCHAR(1024 OCTETS), AUTHID VARCHAR(128 OCTETS), SESSNAUTHID VARCHAR(128 OCTETS), NODENUM SMALLINT, COORDNUM SMALLINT, APPID VARCHAR(255 OCTETS), APPNAME VARCHAR(1024 OCTETS), CLNTUSERID VARCHAR(255 OCTETS), CLNTWRKSTNAME VARCHAR(255 OCTETS), CLNTAPPNAME VARCHAR(255 OCTETS), CLNTACCSTRING VARCHAR(255 OCTETS), TRSTCTXNAME VARCHAR(255 OCTETS), CONTRSTTYPE CHAR(1 OCTETS), ROLEINHERITED VARCHAR(128 OCTETS), PKGSCHEMA VARCHAR(128 OCTETS), PKGNAME VARCHAR(128 OCTETS), PKGSECNUM SMALLINT, PKGVER VARCHAR(64 OCTETS), LCLTRANSID VARCHAR(10 OCTETS) FOR BIT DATA, GLBLTRANSID VARCHAR(30 OCTETS) FOR BIT DATA, UOWID BIGINT, ACTIVITYID BIGINT, STMTINVOCID BIGINT, STMTNESTLVL BIGINT, ACTIVITYTYPE VARCHAR(32 OCTETS), STMTTEXT CLOB(8M OCTETS), STMTISOLATIONLVL CHAR(8 OCTETS), COMPENVDESC BLOB(8K), ROWSMODIFIED INTEGER, ROWSRETURNED BIGINT, SAVEPOINTID BIGINT, STMTVALINDEX INTEGER, STMTVALTYPE CHAR(16 OCTETS), STMTVALDATA CLOB(128K OCTETS), STMTVALEXTENDEDINDICATOR INTEGER, LOCAL_START_TIME CHAR(26 OCTETS), ORIGUSERID VARCHAR(1024 OCTETS), INSTNAME VARCHAR(128 OCTETS), HOSTNAME VARCHAR(255 OCTETS)) ORGANIZE BY ROW
DB20000I The SQL command completed successfully.

[bluadmin@node0101-fab – Db2wh ~]$

2. 監査ログを取得する対象のユーザー及びロールグループの確定
AUDIT機能用のロールを作成し、監査ログを取得する対象のユーザー・グループを紐づけます。
ロール名は任意の名前です。今回は”AUDIT_USERS”というロールを作成し、一般ユーザーを紐づけます。
“BLUUSERS”はIIASで一般ユーザーを作成すると自動で所属するグループです。
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ db2 “create role AUDIT_USERS”
DB20000I The SQL command completed successfully.
[bluadmin@node0101-fab – Db2wh ~]$
[bluadmin@node0101-fab – Db2wh ~]$ db2 “grant role AUDIT_USERS to BLUUSERS”
DB20000I The SQL command completed successfully.
[bluadmin@node0101-fab – Db2wh ~]$

3. 監査ログポリシーの作成
SQL監査ログ用のポリシーを作成します。
ポリシー名は任意の名前です。今回は”POLICY_EXECUTE”というポリシーを作成します。
SQL監査ログ用のカテゴリーは”EXECUTE”です。
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ db2 “create audit policy POLICY_EXECUTE categories EXECUTE with data status both error type audit”
DB20000I The SQL command completed successfully.
[bluadmin@node0101-fab – Db2wh ~]$

4. 監査ログポリシーへの監査対象オブジェクトのマップ
手順2で作成したロールと手順3で作成したポリシーを紐づけます。
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ db2 “audit role AUDIT_USERS using policy POLICY_EXECUTE”
DB20000I The SQL command completed successfully.
[bluadmin@node0101-fab – Db2wh ~]$

5. アーカイブ関連ディレクトリの作成
監査ログのアーカイブ用ディレクトリ、およびアーカイブからのCSV抽出用のディレクトリを作成します。
今回はbluadminユーザーのホームディレクトリ配下にそれぞれ”audit_log_archive”および”audit_log_extract”という名前でディレクトリを作成します。
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ mkdir /scratch/home/bluadmin/audit_log_archive
[bluadmin@node0101-fab – Db2wh ~]$ mkdir /scratch/home/bluadmin/audit_log_extract
[bluadmin@node0101-fab – Db2wh ~]$

ここまでで設定作業は完了です。
設定ができたら、一般ユーザーでログインしてSQLを実行してみます。
【実行例】
[testuser1@node0101-fab – Db2wh ~]$ db2 “create table audit_test (col1 smallint, col2 char(3))”
DB20000I The SQL command completed successfully.
[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “insert into audit_test values (1, ‘AAA’)”
DB20000I The SQL command completed successfully.
[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “insert into audit_test values (2, ‘BBB’)”
DB20000I The SQL command completed successfully.
[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “insert into audit_test values (3, ‘CCC’)”
DB20000I The SQL command completed successfully.
[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “select * from audit_test”

COL1 COL2
—— —-
2 BBB
3 CCC
1 AAA

3 record(s) selected.

[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “update audit_test set col2 = ‘DDD’ where col1 = 2”
DB20000I The SQL command completed successfully.
[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “select * from audit_test”

COL1 COL2
—— —-
3 CCC
1 AAA
2 DDD

3 record(s) selected.

[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “delete from audit_test”
DB20000I The SQL command completed successfully.
[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “select * from audit_test”

COL1 COL2
—— —-

0 record(s) selected.

[testuser1@node0101-fab – Db2wh ~]$
[testuser1@node0101-fab – Db2wh ~]$ db2 “drop table audit_test”
DB20000I The SQL command completed successfully.
[testuser1@node0101-fab – Db2wh ~]$

ここからは運用作業です。
6. 監査ログのアーカイブおよびCSVの抽出
監査ログをアーカイブし、アーカイブしたログからLOAD用のCSVファイルを抽出します。
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ db2 “call SYSPROC.AUDIT_ARCHIVE(‘/scratch/home/bluadmin/audit_log_archive’, NULL)”

Result set 1
————–

DBPARTITIONNUM PATH FILE SQLCODE SQLSTATE SQLERRMC MEMBER
————– —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————- ———– ——– ———————————————————————————————————————————————– ——
0 /scratch/home/bluadmin/audit_log_archive db2audit.db.BLUDB.log.0.20190625171600 0 – – 0
（省略）
10 /scratch/home/bluadmin/audit_log_archive 0 – – 10

11 record(s) selected.

Return Status = 0
[bluadmin@node0101-fab – Db2wh ~]$
[bluadmin@node0101-fab – Db2wh ~]$ db2 “call SYSPROC.AUDIT_DELIM_EXTRACT(NULL, ‘/scratch/home/bluadmin/audit_log_extract’, ‘/scratch/home/bluadmin/audit_log_archive’, NULL, ‘CATEGORY EXECUTE’)”

Return Status = 0
[bluadmin@node0101-fab – Db2wh ~]$

7. CSVファイルのテーブルへのLOAD
手順6で抽出したCSVファイルを、手順1で作成したテーブルにLOADします。
監査カテゴリがEXECUTEのの場合はEXECUTEテーブルにLOADします。
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ db2 “load from /scratch/home/bluadmin/audit_log_extract/execute.del of del modified by delprioritychar lobsinfile insert into AUDIT.EXECUTE”

Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 001 +00000000 Success.
______________________________________________________________________________
LOAD 002 +00000000 Success.
______________________________________________________________________________
LOAD 003 +00000000 Success.
______________________________________________________________________________
LOAD 004 +00000000 Success.
______________________________________________________________________________
LOAD 005 +00000000 Success.
______________________________________________________________________________
LOAD 006 +00000000 Success.
______________________________________________________________________________
LOAD 007 +00000000 Success.
______________________________________________________________________________
LOAD 008 +00000000 Success.
______________________________________________________________________________
LOAD 009 +00000000 Success.
______________________________________________________________________________
LOAD 010 +00000000 Success.
______________________________________________________________________________
PARTITION 000 +00000000 Success.
______________________________________________________________________________
PRE_PARTITION 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 10 of 10 LOADs completed successfully.
______________________________________________________________________________

Summary of Partitioning Agents:
Rows Read = 24
Rows Rejected = 0
Rows Partitioned = 24

Summary of LOAD Agents:
Number of rows read = 24
Number of rows skipped = 0
Number of rows loaded = 24
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 24

[bluadmin@node0101-fab – Db2wh ~]$

ここまでで運用作業は完了です。

SQL監査ログをテーブルにLOADしたので、一般ユーザーで実行したSQLが取得できているか確認してみます。

8. LOADしたデータの検索
【実行例】
[bluadmin@node0101-fab – Db2wh ~]$ db2 “select TIMESTAMP,CATEGORY,substr(EVENT,1,10) as EVENT,DATABASE,substr(AUTHID,1,10) as AUTHID,substr(APPID,1,30) as APPID,ACTIVITYTYPE,substr(STMTTEXT,1,100) as STMTTEXT,ROWSRETURNED,ROWSMODIFIED,HOSTNAME from AUDIT.EXECUTE WHERE EVENT =’STATEMENT’ order by TIMESTAMP”

TIMESTAMP CATEGORY EVENT DATABASE AUTHID APPID ACTIVITYTYPE STMTTEXT ROWSRETURNED ROWSMODIFIED HOSTNAME
————————– ——– ———- ——– ———- —————————— ——————————– —————————————————————————————————- ——————– ———— —————————————————————————————————————————————————————————————————————————————————————
2019-06-25-16.55.55.405599 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 DDL create table audit_test (col1 smallint, col2 char(3)) 0 0 node0101-fab.apdomain.ibm.com
2019-06-25-16.56.06.157090 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 WRITE_DML insert into audit_test values (1, ‘AAA’) 0 1 node0101-fab.apdomain.ibm.com
2019-06-25-16.56.17.230169 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 WRITE_DML insert into audit_test values (2, ‘BBB’) 0 1 node0101-fab.apdomain.ibm.com
2019-06-25-16.56.29.156010 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 WRITE_DML insert into audit_test values (3, ‘CCC’) 0 1 node0101-fab.apdomain.ibm.com
2019-06-25-16.57.33.774239 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 READ_DML select * from audit_test 3 0 node0101-fab.apdomain.ibm.com
2019-06-25-16.57.44.889890 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 WRITE_DML update audit_test set col2 = ‘DDD’ where col1 = 2 0 1 node0101-fab.apdomain.ibm.com
2019-06-25-16.57.55.971885 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 READ_DML select * from audit_test 3 0 node0101-fab.apdomain.ibm.com
2019-06-25-16.58.06.097039 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 WRITE_DML delete from audit_test 0 3 node0101-fab.apdomain.ibm.com
2019-06-25-16.58.40.389263 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 READ_DML select * from audit_test 0 0 node0101-fab.apdomain.ibm.com
2019-06-25-16.58.51.062994 EXECUTE STATEMENT BLUDB TESTUSER1 *N0.db2inst1.190625095234 DDL drop table audit_test 0 0 node0101-fab.apdomain.ibm.com

10 record(s) selected.

[bluadmin@node0101-fab – Db2wh ~]$

一般ユーザーが実行したSQLが取得できました。
（参考資料）Audit record layout for EXECUTE events
www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.security.doc/doc/r0051526.html

まとめ
・IIASにも監査ログを取得する方法があります。
・Webコンソールのテーブルから監査ログを取得する方法もありますが、一度に取得できるレコード数に制限があります。
・Db2 AUDIT機能にはこの制限がないため便利です。