e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第39回 「行トリガーで『:OLD.列名』と『:NEW.列名』を用いた値ベース監査」

2012.10.29

こんにちは。インストラクターの蓑島です。
前回、行トリガーはDML操作の対象行の上で起動しており、『その行の列値が参照できる』ことを解説しました。

具体的には、

   :OLD.列名      ←DML操作前の列値
   :NEW.列名      ←DML操作後の列値

という記述でその行の列値が参照できるということでした。

そのトリガーがUPDATEで起動している場合は、
『:OLD.列名』はUPDATE前の値、『:NEW.列名』はUPDATE後の値であるためわかりやすいと思います。

では、DELETEで起動している場合は、
『:OLD.列名』は行をDELETEする前のその列の値ですが、
『:NEW.列名』は行をDELETEした後のその列の値ということになります。
ここで、DELETEすればその行は存在しないのだから、その列値を参照するということ自体、矛盾しているのではないか?と思われるかも知れません。
その通りです。
したがってDELETEで起動している行トリガーの中で参照する『:NEW.列名』の値は常にNULLになります。

同じように、INSERTで起動している行トリガーの中で、『:OLD.列名』を参照すると、それはINSERT前の行の列値という意味であり、INSERT前には行は存在しないのだから、その値はNULLということになります。
『:NEW.列名』は、INSERT後の行の列値だからこれはきちんと参照できます。

このように、DELETEの場合は『:NEW.列名』は必ずNULL値であり、INSERTの場合は『:OLD.列名』が必ずNULL値です。

では、このことを利用した行トリガーの事例を確認しましょう。

表のデータを誰が、いつ、どんな風にDML操作したかを他の表に記録する内容のトリガーです。
このようなトリガーの処理を値ベース監査といいます。

まず、SOCTTユーザで、TEST01という2列(ID列、NAME列)から成る表を作ります。

SQL> SHOW USER
ユーザーは"SCOTT"です。
SQL> CREATE TABLE TEST01
  2  (ID  NUMBER,
  3   NAME  VARCHAR2(10));

表が作成されました。

このTEST01表に対して、誰が、いつ、どのようなDML操作をしたのかを記録するAUDIT_TEST01という表を作成します。

SQL> CREATE TABLE AUDIT_TEST01
  2  (  USERNAME    VARCHAR2(10),
  3     OP_TIME     VARCHAR2(30),
  4     OLD_ID      NUMBER,
  5     NEW_ID      NUMBER,
  6     OLD_NAME    VARCHAR2(10),
  7     NEW_NAME    VARCHAR2(10))
  8  /

表が作成されました。

この表には、誰が(USERNAME)、いつ(OP_TIME)操作したかということと、その行の各列のOLDとNEWの値を記録する列(OLD_ID, NEW_ID, OLD_NAME, NEW_NAME)があります。

では実際にTEST01表にDML操作した時に操作したユーザと、タイムスタンプと、対象行の『:OLD.列値』と『:NEW列値』をAUDIT_TEST01表に格納(INSERT)するトリガーを実装します。

以下のようなトリガーです。

SQL> CREATE OR REPLACE TRIGGER AUDIT_TEST01_TRIG
  2  AFTER
  3  INSERT OR UPDATE OR DELETE ON TEST01
  4  FOR EACH ROW
  5  BEGIN
  6     INSERT INTO AUDIT_TEST01
  7     VALUES(USER, TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS'),
  8            :OLD.ID, :NEW.ID, :OLD.NAME, :NEW.NAME);
  9 END;
  10 /

トリガーが作成されました。

●2行目・・・タイミングとしてAFTERの指定をしてますが、BEFOREでも同じ結果になります。
AFTERでもBEFOREでも:OLDや:NEWの列値は同じだからです。
しかし、このトリガーの処理は事前チェックではなく、事後的な記録を残す内容なので、タイミングはAFTERにすることが自然といえます。

●3行目・・・TEST01表に対するイベントとしてINSERT,UPDATE,DELETEを指定します。

●4行目・・・FOR EACH ROW があることで行トリガーとなります。

●6-8行目・・・INSERT文で TEST01表の対象行のOLDとNEWの列値をAUDIT_TEST01表にINSERTしてます。

●7行目でINSERTする値としてUSER関数を使うとその操作をしているデータベースユーザの名前を返します。
タイムスタンプは問い合わせしやすいように、TO_CHAR関数でSYSDATE関数を書式指定して文字列変換してます。

これにより、TEST01表に対してDML操作(INSERT,UPDATE,DELETE)をすれば AUDIT_TEST01表に詳細な記録が残ります。
早速、試してみましょう。

まず、SCOTTユーザで、TEST01表に対して、2行INSERTします。

SQL> SHOW USER
ユーザーは"SCOTT"です。
SQL> INSERT INTO TEST01 VALUES (10,'ABC');

1行が作成されました。

SQL> INSERT INTO TEST01 VALUES (20,'XYZ');

1行が作成されました。

次にSYSTEMユーザで、TEST01表の10番の行のNAME列を'abc'にUPDATEします。

SQL> CONNECT SYSTEM/oracle
接続されました。
SQL> UPDATE SCOTT.TEST01 SET NAME = 'abc' WHERE ID = 10;

1行が更新されました。

さらにSYSTEMユーザで、TEST01表の20番の行のID列の値を21にUPDATEします。

SQL> UPDATE SCOTT.TEST01 SET ID = 21 WHERE ID = 20;

1行が更新されました。

さらにSYSTEMユーザで、TEST01表の10番の行をDELETE します。
SQL> DELETE FROM SCOTT.TEST01 WHERE ID = 10;

1行が削除されました。

参考までに現在のTEST01表は以下のデータになっています。

SQL> SELECT * FROM SCOTT.TEST01;

        ID NAME
--------------------
        21 XYZ

以上のように、TEST01表に対して、SOCTTユーザで2行インサートしたり、SYSTEMユーザで2行UPDATEしたり1行DELETEしたわけですが、そのたびに行トリガーが起動してますので、結果として、AUDIT_TEST01表には、全部で5行がINSERTされています。

SQL> SELECT * FROM SCOTT.AUDIT_TEST01 ORDER BY OP_TIME;

USERNAME   OP_TIME                            OLD_ID    NEW_ID   OLD_NAME   NEW_NAME
-------------------------------------------------------------------------------------------------------------------------
SCOTT       12-10-25 18:23:26                  10                                                               ABC
SCOTT       12-10-25 18:23:41                  20                                                               XYZ
SYSTEM     12-10-25 18:24:31                  10                10                  ABC                abc
SYSTEM     12-10-25 18:25:03                  20                21                  XYZ                 XYZ
SYSTEM     12-10-25 18:25:24                  10                                       abc

この結果を見れば、誰が(USERNAME)、いつ(OP_TIME)、DML操作したかわかりますし、その操作の内容もOLD_ID, NEW_ID, OLD_NAME, NEW_NAME列を見ると推測できます。
すなわち、最初の2行はNEWの列に値があり、OLDの列に値がないのでINSERTの記録であることがわかります。
INSERTした値が、NEW_ID列とNEW_NAME列です。
3行目と4行目はOLDとNEWの両方に値があるので、この2行はUPDATEの記録であることがわかります。
OLDからNEWへ値をUPDATEした記録です。
また最後の行はNEWの列に値がないので、DELETEの記録であることがわかります。
すなわち、ID列が10、NAME列が'abc'の行のDELETEの記録であることがわかります。

このように、誰が、いつ、どの行に対して、どのようなDML操作をしたのかという記録を行トリガーで、他の表に残すことができます。つまり非常に詳細な監査記録というわけです。

しかし、表のデータをDML操作する場合のパフォーマンスに大きく影響することは言うまでもないので、実際にこのようなトリガーはよく検討してから設けるべきです。

それでは今回はここまでにいたします。また次回、ご期待ください。

先頭へ戻る