Db2での監査ログ設定まわりの覚書。

前提環境

cat /etc/redhat-release
uname -a
db2licm -l

Red Hat Enterprise Linux Server release 7.3 (Maipo)

Linux db2 3.10.0-514.el7.x86_64 #1 SMP Wed Oct 19 11:24:13 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

Product name:                     "IBM DB2 Developer-C Edition"
License type:                     "Community"
Expiry date:                      "Permanent"
Product identifier:               "db2dec"
Version information:              "11.1"
Max amount of memory (GB):        "16"
Max number of cores:              "4"
Max amount of table space (GB):   "100"

監査対象となるDB名は、「DBAUD」

監査設定

監査はインスタンスレベルまたはDBレベル（DB全体、特定の表など）で設定が可能。
いずれにせよ、設定は以下の要素からなる。

監査ポリシー：どんなイベントを出力対象とするか
アクティブ監査ログ：イベントがロギングされたファイル
アーカイブ監査ログ：アクティブログの退避コピーファイル
レポーティング：レポート作成ユーティリティ（db2auditコマンド）
（オプション）監査イベント表用のスキーマ
（オプション）監査イベント表

オプションとしたものは、レポーティング向けに必要になるかもしれない要素で、製品提供の監査イベント表DDLファイルがある（スキーマも用途を考えると分けるべき）。

監査ポリシーの作成と適用

前述の通り、監査ポリシーはどんなイベントを出力するか、という情報を保持する。
ここでは例として、対象DBであるDBAUDに対し、SQLの実行（CATEGORIES EXECUTE）を成功・失敗問わず（STATUS BOTH）出力する。
その他、設定できるカテゴリ（どのような操作をロギング対象とするか）は以下の通り（マニュアルからの要約抜粋）。

AUDIT: 監査設定自体の変更、また監査ログアクセス時
CHECKING: DBオブジェクトや関数に対するアクセスの許可検査時
OBJMAINT: DDL発行時
SECMAINT: 権限変更時
SYSADMIN: SYSADM,SYSMAINT,SYSCTL権限を必要とする操作実行時
VALIDATE: ユーザ認証、セキュリティ情報検索時
CONTEXT: DB操作時（イベントの相関関係を識別できるレコードを生成する）
EXECUTE: SQL実行時

監査ポリシーはDBオブジェクトに関連付けることで初めて意義をなす。オブジェクトに関連付けられるポリシーは一つだけで、シンプルなポリシーをいくつも作っておいて必要なものを複数アタッチしていくというわけにはいかない。

# 監査ログ・アーカイブ監査ログ出力先設定
db2audit configure datapath /db/dbaud/audit archivepath /db/dbaud/auditarch
# 確認
db2audit describe

# ポリシー作成
db2 "CREATE AUDIT POLICY ACCESSTOSECRETPOLICY CATEGORIES EXECUTE STATUS BOTH ERROR TYPE AUDIT"
# ポリシーのDBへのアタッチ
db2 "AUDIT DATABASE USING POLICY ACCESSTOSECRETPOLICY"

"ERROR TYPE AUDIT"は監査ログ出力不可時に実行操作もエラーとする設定で、つまり監査不能状態ではDBの操作を許可しない、ということ。重要度の高い（機密性の高い）システムではこの設定になるのが通常だが、開発環境などで監査ログ出力できなくてもエラーにしないなら、"ERROR TYPE NORMAL"とする。
なお、db2auditコマンドではdb2（CLP）で使用可能な短縮形は使えない（configureをCFGとか）。同様にAUDITのDATABASEも短縮形DBでは不可。
ポリシー自体やDBへのアタッチ状況を確認する専用のコマンドは少し調べた限りでは見つけられなかった（db2auditコマンドでできてもよさそうなものだが、、、）。SYSCAT.AUDITPOLICIES、SYSCAT.AUDITUSEあたりをSELECTして確認するしかなさそう。かわりにdb2lookに"-ap"オプションを付与することで、アタッチするSQL文（DDL）を生成することで一応見えるようにはなる。

db2 "SELECT VARCHAR(AUDITPOLICYNAME,20) AS AUDITPOLICYNAME,AUDITSTATUS,CONTEXTSTATUS,VALIDATESTATUS,CHECKINGSTATUS,SECMAINTSTATUS,OBJMAINTSTATUS,SYSADMINSTATUS,EXECUTESTATUS,EXECUTEWITHDATA,ERRORTYPE FROM SYSCAT.AUDITPOLICIES"
db2 "SELECT VARCHAR(AUDITPOLICYNAME,16) AS AUDITPOLICYNAME,OBJECTNAME,OBJECTSCHEMA,OBJECTTYPE FROM SYSCAT.AUDITUSE"

db2look -ap -d DBAUD

db2look結果抜粋

AUDIT DATABASE USING POLICY "ACCESSTOSECRETPOLICY";

ログ出力確認

SQLを発行して監査ログの出力を確認する。

# SQL発行
db2 "CREATE TABLE Hogetbl ( id INTEGER PRIMARY KEY NOT NULL, description VARCHAR(128) )"

# 監査ログ確認
ls /db/dbaud/audit

db2audit.db.DBAUD.log.0

ファイル名からDBに対する監査（".db."の部分）であり、対象DB名がDBAUDであることがわかるようになっている。
アクティブログはcatやstringsで読める部分は読めるが、基本的にバイナリ形式のため、これをCSV形式に変換したり、変換したファイルをDBにロードして分析したりする。

監査運用

監査ログはおいておくだけでは意味がないので、定期的にアーカイブしたり分析用に集計したりしなければならない。
監査データは同期モード（DBM CFGのaudit_buf_szパラメタが0なら同期書き込み）で書き込んでいない限り、バッファリングしている可能性があるため、アーカイブ前にフラッシュしておくのが吉。

アーカイブ

db2audit flush                    # ログのフラッシュ
db2audit archive database DBAUD   # 監査ログのアーカイブ
# アーカイブ先は設定済みなので出力先指定不要だが、
# 別の場所へ出力したいときは"to /path/to/archive/directory"を付与して指定可能

# 結果確認
ls /db/dbaud/audit*

/db/dbaud/audit:
db2audit.db.DBAUD.log.0

/db/dbaud/auditarch:
db2audit.db.DBAUD.log.0.20181104160110

このように、アーカイブ監査ログには末尾にタイムスタンプが付与される。アーカイブは単なるコピーなので、これもバイナリ形式となっている。トランザクションログ同様、アーカイブしたらアクティブログは切替わる（新規ログになる）ので、レポート作成時にイベントの重複を気にする必要はない。

レポーティング

テキスト出力

db2audit extractでテキストファイルに出力できる。
アーカイブ監査ログすべてを使ってカレントディレクトリに"dbaudit.txt"というファイルを出力するには以下のようにする。

db2audit extract file dbaudit.txt from path /db/dbaud/auditarch files "*"
# pathはアーカイブ監査ログのパスでパラメタ設定済みなので省略可能
db2audit extract file dbaudit.txt from files "*"
# 既存ファイルを指定すると追加書きになることに注意

cat dbaudit.txt

抜粋

timestamp=2018-11-04-16.00.48.501743;
  category=EXECUTE;
  audit event=CONNECT RESET;
  event correlator=4;
  event status=0;
  database=DBAUD;
  userid=dbinst;
  authid=DBINST;
  session authid=DBINST;
  application id=*LOCAL.dbinst.181104070053;
  application name=DB2HMON;
  client workstation name=db2;
  activity type=OTHER;
  instance name=dbinst;
  hostname=db2.mognet.net.local;

"timestamp=yyyy-MM-dd-HH.mm.ss.SSSXXX;"で始まる行からが１つの監査イベントになる。カテゴリが一緒でもイベントによって内容が異なるため、行数も可変。これを自力でフラット化するのは骨の折れる作業になる（完全なリストはマニュアル参照）。

CSV出力

フラットファイルだけでなくCSV出力するというオプションがある。

db2audit extract delasc to /db/dbaud/work from files "*" 
# to /path/to/extractを省略するとアーカイブ監査ログと同じディレクトリに出力する

ls /db/dbaud/work

audit.del  auditlobs  checking.del  context.del  execute.del  objmaint.del  secmaint.del  sysadmin.del  validate.del

このようにカテゴリごとにファイルが出力される。今回はEXECUTEカテゴリしかポリシーに入れていないので、０バイトファイルとして出力される。邪魔であればdb2audit extract delasc to /db/dbaud/work category execute from "*"というように、"category"オプションで絞り込めばexecute.delとauditlobs以外のファイルは出力しない。
auditlobsにはその名の通りLOBデータが格納されており、execute.delから参照されるような格好になっている。

create_tableの監査ログ抜粋

"2018-11-04-15.46.49.272508","EXECUTE","STATEMENT",..."dbinst",...,"DDL","auditlobs.0.82/","CS","auditlobs.82.808/"...

このようにファイル名とオフセットが表示されている。ちなみにCSVファイルにヘッダ行は出力されない。DBにロードすることを前提にしているようだが、せめてオプションでヘッダ出力させてほしいところだ（たいていのRDBMSにはロード時にヘッダ行を無視するオプションがある。もちろんDb2にも！！！）。
実行したSQL自体もauditlobsに出るので、どうしてもスクリプトで取り出したければ以下のようにodコマンドあたりを使うしかない。

od -tc -j 0 -N 82 auditlobs

# 記号として出力された"sp"をスペースにしつつ一行にする
od -ta -j 0 -N 82 auditlobs  | cut -d' ' -f2- -s | sed -e 's/ *//g' -e 's/sp/ /g' | perl -pe 's/\n//'

DBへロード

こんなことをするくらいならDBにロードするほうがマシなので、マニュアル（文末の参考資料参照）に従ってスキーマとテーブルを作成し、そこに監査データをロードする。

db2 "CREATE SCHEMA AUDIT"
db2 "SET CURRENT SCHEMA = 'AUDIT'"
db2 +o -tf ~/sqllib/misc/db2audit.ddl

db2 "LOAD FROM /db/dbaud/work/execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO AUDIT.EXECUTE"

あとはDML文で必要な情報をとってくればよいし、とりあえずCSVにして表計算ソフトでレポートを作成するなら、EXPORTすればよい（CSVからロードしたものをCSVにEXPORTしなおすというのもなんだかなぁだが、手っ取り早く見やすい情報を得るにはこれが良い手段でもある）。

EXPORTにヘッダをつける

本筋からはずれるが、例によってEXPORTにはヘッダを出力するというオプションがないので、動的に取ってくるには以下のようにする。

set -f   # シェルワイルドカード展開を無効化
TMP_FILE=/db/dbaud/work/tmp.csv       # EXPORTする一時ファイル
REPORT_FILE=/db/dbaud/work/report.csv # ヘッダ付きのレポートファイル
SQL='SELECT * FROM EXECUTE'           # 実行するSQL文

# ヘッダ（カラム名）を取得
db2 "DESCRIBE ${SQL}" | awk '/^ [0-9].*$/ { printf("%s,", $4) }' | sed -e "s/,$//" >${REPORT_FILE}
# こっちでも可　db2 "DESCRIBE ${SQL}" | grep '^ [0-9].*$' | awk '{printf("%s,",$4)}' | sed -e "s/,$//"

# データを一時ファイルに取得
db2 "EXPORT TO ${TMP_FILE} OF DEL ${SQL}"

# データをレポートファイル（ヘッダのみ）にマージ（追加書き）
cat ${TMP_FILE} >>${REPORT_FILE}

set +f   # シェルワイルドカード展開を有効化

いろいろと応用が効く（普通の照会にも使える）ので、関数化するなりシェルにしておくなりするのがよいかもしれない。
なお、対話型シェル上で実行したので、"*"をワイルドカード扱いしないよう前後で"set -f"/"set +f"しているが、スクリプトにしておけば、適切にエスケープしておけば大丈夫なはず。

手元の表計算ソフトで開くと下図のように実行されたSQL文も取得できた。

ヘッダ出力部分（awk）の補足

awk '/^ [0-9].*$/
{ 
    printf("%s,", $4) 
}' | sed -e "s/,$//"

SQL文をDESCRIBEすると以下のような出力が得られる。

横線区切りの次の行、8行目から実際に欲しい情報が出力され、カラム名は４列目にある。出力のヘッダ行数が固定ならif(NR > 7) {printf("%s,",$4)}でもいける。
区切り線を目印にするか（そうすると区切り線行自体の処理が必要になる）、SQL列の数字を目印にするほうがより確実そうなので、awkの処理開始条件を/^ [0-9].*$/（行頭スペースに続いて数字一文字、あとは行末までなんでも）としている（grepと組み合わせでもよい）。
1カラム1行で出力されるので、これを","で区切りつつ平らに（一行に）してやり、最後にsedで必ず余る最後の余計な","を削除している。
いろいろやり方はあろうかとも思われるが、ぱっと見のみやすさ等々で一旦これに落ち着いた。

SQLでいつ・どこで・だれが・なにを・どうした・どうなっただけ取ってくる

せっかくCSVからDBへロードしたものを、再度全量CSVに出力するだけというのもなんだか切ない。監査レポートに最低限必要な情報のみ取得するには、EXECUTEの場合以下のようなSELECT文になる（画面上見やすいようにVARCHAR()で切捨てているが、前述のEXPORTでCSVにするなら不要）。

SELECT timestamp,VARCHAR(hostname,20) AS hostname,VARCHAR(userid,10) AS username,activitytype,VARCHAR(stmttext,30) AS sql,rowsreturned,rowsmodified FROM Execute;

以下蛇足だが、バージョンによって監査テーブルのデータ構造は変更される可能性がある。つまり列名や列数が変わる可能性がある。さすがに表名は変えないと思いたいが、列については「いつ・どこで・だれが・なにを・どうした・どうなった」あたりを列の別名で定義しておくとよいかもしれない（バージョンアップのときに考えるでもよさそうだが）。
いずれにせよ、長い目で見ればプログラムのどこかの部分（DBだったりSQLだったりEntityクラスだったり）で差異を吸収できるようにしておかないと、将来のバージョンアップ時に面倒になる。

Db2での監査