ログマイナー再び!! その8

<ログマイナー再び!! その8>
ペンネーム: ぽっちゃりメタボン

読者の皆様こんにちは!!

あまりの寒さのため、震えが止まらないぽっちゃりメタボンです。
筆者は雪国出身ですが寒いのは大の苦手です。

サムサニモ、ネムケニモマケズ今週もはりきってはじめましょう。

▼ 前回のおさらい

ログマイナーをどのようなシチュエーションで使う事が効果的であるかを
2つの例をご紹介しながら実践的に考えてみました。

今回も、前回からの続きとなりますが、ログマイナーを使用した事後監査の
ポイントを探っていきます。

▼ 環境のおさらい

OS:Red Hat Enterprise Linux ES release 3
DB:10gR2(10.2.0.1)

▼ ログマイナーで解決しちゃう。パート2

★ テストデータ

以下のような表があるとします。

SQL> select * from 会計;

         A          B
---------- ----------
         1     200199
         2     407560
         3   19355283
         4     801240
         5    1668690
         6    1284900
         7   16077355
         8   20368920
         9    1800486
        10    2354990

★ 要件その3
「Audit Trailで取得している監査証跡とログマイナーの取得情報と結び
つけたい」

● ポイント

まずは監査証跡とお馴染みのv$logmnr_contentsで結合可能なキーがあるか
確認してみることにします。

SQL> desc v$logmnr_contents

 名前                                      NULL?    型
 ----------------------------------------- -------- ------------------
 SCN                                                NUMBER
 CSCN                                               NUMBER
 TIMESTAMP                                          DATE
 COMMIT_TIMESTAMP                                   DATE
 THREAD#                                            NUMBER
 LOG_ID                                             NUMBER
 XIDUSN                                             NUMBER
 XIDSLT                                             NUMBER
 XIDSQN                                             NUMBER
 PXIDUSN                                            NUMBER
 PXIDSLT                                            NUMBER
 PXIDSQN                                            NUMBER
 RBASQN                                             NUMBER
 RBABLK                                             NUMBER
 RBABYTE                                            NUMBER
 UBAFIL                                             NUMBER
 UBABLK                                             NUMBER
 UBAREC                                             NUMBER
 UBASQN                                             NUMBER
 ABS_FILE#                                          NUMBER
 REL_FILE#                                          NUMBER
 DATA_BLK#                                          NUMBER
 DATA_OBJ#                                          NUMBER
 DATA_OBJD#                                         NUMBER
 SEG_OWNER                                          VARCHAR2(32)
 SEG_NAME                                           VARCHAR2(256)
 TABLE_NAME                                         VARCHAR2(32)
 SEG_TYPE                                           NUMBER
 SEG_TYPE_NAME                                      VARCHAR2(32)
 TABLE_SPACE                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(18)
 SESSION#                                           NUMBER
 SERIAL#                                            NUMBER
 USERNAME                                           VARCHAR2(30)
 SESSION_INFO                                       VARCHAR2(4000)
 TX_NAME                                            VARCHAR2(256)
 ROLLBACK                                           NUMBER
 OPERATION                                          VARCHAR2(32)
 OPERATION_CODE                                     NUMBER
 SQL_REDO                                           VARCHAR2(4000)
 SQL_UNDO                                           VARCHAR2(4000)
 RS_ID                                              VARCHAR2(32)
 SEQUENCE#                                          NUMBER
 SSN                                                NUMBER
 CSF                                                NUMBER
 INFO                                               VARCHAR2(32)
 STATUS                                             NUMBER
 REDO_VALUE                                         NUMBER
 UNDO_VALUE                                         NUMBER
 SQL_COLUMN_TYPE                                    VARCHAR2(30)
 SQL_COLUMN_NAME                                    VARCHAR2(30)
 REDO_LENGTH                                        NUMBER
 REDO_OFFSET                                        NUMBER
 UNDO_LENGTH                                        NUMBER
 UNDO_OFFSET                                        NUMBER
 DATA_OBJV#                                         NUMBER
 SAFE_RESUME_SCN                                    NUMBER
 XID                                                RAW(8) (★)
 PXID                                               RAW(8)
 AUDIT_SESSIONID                                    NUMBER


SQL> desc desc dba_audit_trail

 名前                                      NULL?    型
 ----------------------------------------- -------- ------------------
 OS_USERNAME                                        VARCHAR2(255)
 USERNAME                                           VARCHAR2(30)
 USERHOST                                           VARCHAR2(128)
 TERMINAL                                           VARCHAR2(255)
 TIMESTAMP                                          DATE
 OWNER                                              VARCHAR2(30)
 OBJ_NAME                                           VARCHAR2(128)
 ACTION                                    NOT NULL NUMBER
 ACTION_NAME                                        VARCHAR2(28)
 NEW_OWNER                                          VARCHAR2(30)
 NEW_NAME                                           VARCHAR2(128)
 OBJ_PRIVILEGE                                      VARCHAR2(16)
 SYS_PRIVILEGE                                      VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)
 GRANTEE                                            VARCHAR2(30)
 AUDIT_OPTION                                       VARCHAR2(40)
 SES_ACTIONS                                        VARCHAR2(19)
 LOGOFF_TIME                                        DATE
 LOGOFF_LREAD                                       NUMBER
 LOGOFF_PREAD                                       NUMBER
 LOGOFF_LWRITE                                      NUMBER
 LOGOFF_DLOCK                                       VARCHAR2(40)
 COMMENT_TEXT                                       VARCHAR2(4000)
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENTID                               NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER
 PRIV_USED                                          VARCHAR2(40)
 CLIENT_ID                                          VARCHAR2(64)
 ECONTEXT_ID                                        VARCHAR2(64)
 SESSION_CPU                                        NUMBER
 EXTENDED_TIMESTAMP                        TIMESTAMP(6) WITH TIME ZONE
 PROXY_SESSIONID                                    NUMBER
 GLOBAL_UID                                         VARCHAR2(32)
 INSTANCE_NUMBER                                    NUMBER
 OS_PROCESS                                         VARCHAR2(16)
 TRANSACTIONID                                      RAW(8) (★)
 SCN                                                NUMBER
 SQL_BIND                                           NVARCHAR2(2000)
 SQL_TEXT                                           NVARCHAR2(2000)

ふむ、どちらにも、トランザクション識別子
(dba_audit_trail.transactionid,v$logmnr_contents.xid)があることが確認
できます。
この値はインスタンス内では一意になりますので使えそうですね。
これをキーにdba_audit_trailとv$logmnr_contentsを結合できそうです。

期待通りの結果を得ることができるのか、実際にやってみましょう。

▼ 検証前の下ごしらえ

検証環境の audit_trail の設定は「DB_EXTENDED」です。
通常の監査証跡に加えて、SQL文やバインド変数も監査証跡へ書き込みます。

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
audit_trail                          string      DB_EXTENDED

また、現在の監査設定は以下のようになっており、会計テーブルに対して、DML
が発行された場合には監査対象となります。

SQL> select OWNER,OBJECT_NAME,DEL,INS,UPD from DBA_OBJ_AUDIT_OPTS;

OWNER      OBJECT_NAME  DEL     INS     UPD
---------- ------------ ------- ------- -------
METABON    会計         A/A     A/A     A/A

▼ 検証開始

この状態から、会計.A = 1 のレコードに対して更新を行います。

SQL> update 会計 set b=b*2 where a=1;

いつものとおりログマイナーを起動し、v$logmnr_contentsと監査証跡を確認
可能なビュー dba_audit_trailをトランザクション識別子をキーに結合します。

SQL> select a.OS_USERNAME,a.USERNAME,b.TIMESTAMP,
            a.OBJ_NAME,a.USERHOST,a.SQL_TEXT,
     DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,'METABON.会計.B') REDO_VALUE,
     DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE,'METABON.会計.B') UNDO_VALUE
     from v$logmnr_contents b ,dba_audit_trail a
     where 
         b.operation != 'INTERNAL'
     and b.seg_name = '会計'
     and b.xid = a.transactionid


OS_USERNAME  USERNAME   TIMESTAMP         OBJ_NAME   USERHOST
------------ ---------- ----------------- ---------- ----------
ora102       METABON    07/11/12 06:27:00 会計       VMRH30

SQL_TEXT                        REDO_VALUE   UNDO_VALUE
------------------------------- ------------ ------------
update 会計 set b=b*2 where a=1 400398       200199

ふむふむ。 ばっちり、結合ができました。

上記の結果を見ればお分かりいただけることと思いますが、「誰が」「いつ」
「どのオブジェクトに対して」「どこから」プラス(+)監査証跡のみでは取得
できない「更新前後の値」、ログマイナーデータのみでは取得できない「実行
SQL文」を確認することができました。監査または悪意のあるユーザによる
データ改ざんの追跡においても十分な力を発揮してくれそうです。

▼ まとめ

2回に渡って、より実践的な内容をお送りしてきましたがいかがでしたでし
ょうか。これによりログマイナーの活用シーンなど、新たに浮かんだ読者の方
がいらっしゃいましたら幸いです。
実装に当たっては負荷的な考慮など含め、充分な検証を行った上で使用される
ことをお勧め致します。

本シリーズも残り回数が少なくなってきました。
次回は本シリーズの総括の予定です。

今回はここまで!!

「オキヅケ」がなくなってしまった 茅ヶ崎より