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

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

こんにちは!!

暖房を使いはじめた ぽっちゃりメタボンです。
いやぁ 茅ヶ崎は美味しいものがたくさんあって困った、困った。。

それはさておき、今週もはりきってはじめましょう。

▼ 前回のおさらい

前回はログマイナーの価値である「レコード単位で更新前後の値を参照するこ
と」について説明をしました。

ログマイナーを使うシチュエーションとはどんな場面が想定されるのか、
また、それをどのように解決することができるのかを、今回は探っていきたい
と思います。

▼ 環境のおさらい

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

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

★ テストデータ

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

SQL> select * from 会計;

         A          B
---------- ----------
         1     200199
         2     203780
         3    2150587
         4     200310
         5     333738
         6     214150
         7    2296765
         8    2546115
         9     200054
        10     235499

このデータを以下の10個のSQL文で更新しておきます。

update "会計" set b=b*1 where a=1;
update "会計" set b=b*2 where a=2;
update "会計" set b=b*3 where a=3;
update "会計" set b=b*4 where a=4;
update "会計" set b=b*5 where a=5;
update "会計" set b=b*6 where a=6;
update "会計" set b=b*7 where a=7;
update "会計" set b=b*8 where a=8;
update "会計" set b=b*9 where a=9;
update "会計" set b=b*10 where a=10;
commit;

この状態からログマイナーを起動し、分析を行っていきます。

★ 要件その1
「本日の会計テーブルに対する全ての変更履歴を確認したい」

● ポイント

v$logmnr_contentsの条件として以下を指定することにより要件を満たす事が
可能になります。

・seg_name	    ・・・表を特定するために使用します
・operation='UPDATE'・・・「変更」が行われたことを特定します
・timestamp         ・・・更新日を特定します

# 条件「operation != ‘INTERNAL’」は Oracleが内部的に実行している処理
# を除外するために追加しています。

timestamp カラム を持っているため、業務時間外に不正に更新されたデータ
があるかどうか等のチェックも可能ですね。

では、はじめに本日の更新レコード件数から確認してみましょう。

SQL> select count(*) from v$logmnr_contents
    where 
        operation != 'INTERNAL'
    and seg_name='会計'
    and operation='UPDATE'
    and to_char(timestamp,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD')
    ;

  COUNT(*)
----------
        10

ばっちり、本日の更新レコード件数が10件であることがわかりますね。

では、次に更新前後の値についても確認したいと思います。
更新前後の値を取得するためにDBMS_LOGMNR.MINE_VALUEを使用します。

■ DBMS_LOGMNR.MINE_VALUE ================================

REDO、UNDOの値を取得するためにはDBMS_LOGMNRパッケージのMINE_VALUEファ
ンクションをコールする必要があります。値をダイレクトに取得が可能という
ことは、「通常認められていない、閾値を超えて更新されたデータ」等も簡単
に見つけ出すことが可能になります。

[構文]
DBMS_LOGMNR.MINE_VALUE (sql_redo_undo,column_name)

sql_redo_undo:
V$LOGMNR_CONTENTSビューのREDO_VALUE列またはUNDO_VALUE列を指定

column_name:
列の完全修飾名(schema.table.column)を指定

戻り値:
VARCHAR2

===========================================================

SQL> select 
        decode(instr(SQL_REDO,'where'),0,'NO WHERE CLAUSE', 
        substr(SQL_REDO,instr(SQL_REDO,'where'))) "WHERE CLAUSE",
        DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,'METABON.会計.B') REDO,
        DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE,'METABON.会計.B') UNDO
     from V_$LOGMNR_CONTENTS
     where 
         operation != 'INTERNAL'
     and seg_name='会計'
     and operation='UPDATE'
     and to_char(timestamp,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD')

WHERE CLAUSE                             REDO       UNDO
---------------------------------------- ---------- ----------
where "A" = '1' and "B" = '200199';      200199     200199
where "A" = '2' and "B" = '203780';      407560     203780
where "A" = '3' and "B" = '2150587';     6451761    2150587
where "A" = '4' and "B" = '200310';      801240     200310
where "A" = '5' and "B" = '333738';      1668690    333738
where "A" = '6' and "B" = '214150';      1284900    214150
where "A" = '7' and "B" = '2296765';     16077355   2296765
where "A" = '8' and "B" = '2546115';     20368920   2546115
where "A" = '9' and "B" = '200054';      1800486    200054
where "A" = '10' and "B" = '235499';     2354990    235499

しっかりと、更新前後の値が確認できますね。

★ 要件その2
「通常、実行されないプログラムからの変更データがあるかどうか確認し
たい」

● ポイント

DMLを実行したプログラムの情報は ずばり、「session_info」カラムに格納
されています。

session_info カラムには以下の情報が格納されています。

 
・login_username  = v$session.username
 ・client_info     = v$session.client_info
 ・OS_username     = v$session.osuser
 ・Machine_name    = v$session.machine
 ・OS_terminal     = v$session.terminal
 ・OS_process_id   = v$session.process
 ・OS_program_name = v$session.program
 
 例)
 SESSION_INFO
---------------------------------------------------------------------
 login_username=METABON client_info= OS_username=ora102 
 Machine_name=VMRH30 OS_terminal=pts/1 OS_process_id=1815 
 OS_program_name=sqlplus@VMRH30 (TNS V1-V3)
 

よって、要件を満たすためには “OS_program_name=[正規アプリケーション名]”
のみしか session_info カラムには存在しないと、仮定して以下のように確認
を行うことが可能です。

SQL> select count(*) from v$logmnr_contents 
     where 
         operation != 'INTERNAL'
     and seg_name='会計'
     and operation='UPDATE'
     and session_info not like '%[正規アプリケーション]%'
 
  COUNT(*)
----------
         0

上記のように、結果が0件であれば、正規アプリケーションからの更新のみし
か行われておらず、不正な経路からのデータ改ざんは無かったと証明するこ
とが可能になりますよね。

▼ まとめ

いかがでしたでしょうか?
ちょっと考えてだけでもログマイナーを使って事後監査として十分に使える情
報を引き出すことが可能であることを実感していただけたのではないでしょう
か。

次回は引き続き、ログマイナーを使用した事後監査の、ポイントを探りたいと
思います。

今回はここまでです!!

ユカイソウカイ 茅ヶ崎より