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

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

第70回 「DDLトリガー ~DDLの履歴をログ表に記録するなど~」

2013.08.26

こんにちは。インストラクターの蓑島です。

前回は、DDLトリガーについて解説しました。
DDLトリガーはDDL文(CREATE , ALTER , DROP)により起動するトリガーであり、トリガーの中で、イベント属性関数(ora_xxxx)を使ってDDLのイベントやDDL操作の対象となるオブジェクト情報の詳細を参照できるわけでしたね。
前回はそれを利用して、SCOTTスキーマのEMP表のDROP操作の時を判定してエラーを発生させるDDLトリガーの例をご紹介しました。
そのトリガーがあれば、SCOTTスキーマのEMP表はDROP不可能となります。

では今回も、DDLトリガーの応用例をご紹介します。
今回はDDLトリガーの中で、他の表にDML操作をしてみます。
具体的には、DDL操作の履歴をログ表にINSERTする処理です。
例えば、誰が、いつ、どのスキーマの、どういいった種類のオブジェクトの、どういう名前のオブジェクトを、CREATEしたのか、あるいはALTERしたのかDROPしたのか、といった記録をログ表に残すわけです。
これにより、誤ってオブジェクトが削除された場合でも、それがいつ誰による操作だったのかなどを確認できます。

では早速、ログ表を作ります。

SQL> SHOW USER
ユーザーは"SYSTEM"です。

現在、データベース管理者であるSYSTEMユーザのセッションです。

以下のようなログ表を作成します。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE LOG_DDL
( COL_SYSDATE   DATE ,          -- 操作日時
   COL_EVENT     VARCHAR2(20),  -- 操作(CREATE, ALTER, DROP)
   COL_USER      VARCHAR2(30),  -- 操作者
   COL_OWNER     VARCHAR2(30),  -- オブジェクト所有者
   COL_OBJ_TYPE  VARCHAR2(20),  -- オブジェクトタイプ
   COL_OBJ_NAME  VARCHAR2(30)   -- オブジェクト名
)
/
 
表が作成されました。

次に以下のようなDDLトリガーを作成します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE OR REPLACE TRIGGER TEST_DDL_TRIG2
BEFORE   CREATE OR ALTER OR DROP
ON  DATABASE
DECLARE
    V_SYSEVENT  VARCHAR2(200);
    V_DICT_OBJ_OWNER   VARCHAR2(30);
    V_DICT_OBJ_NAME    VARCHAR2(30);
    V_DICT_OBJ_TYPE    VARCHAR2(20);
    V_MESSAGE          VARCHAR2(200);
BEGIN
    /** イベントの取得 **/
    V_SYSEVENT        := ora_sysevent;
    /** オブジェクトの所有者の取得   **/
    V_DICT_OBJ_OWNER  := ora_dict_obj_owner;
    /**  オブジェクト名の取得  **/
    V_DICT_OBJ_NAME   := ora_dict_obj_name;
    /** オブジェクトのタイプの取得   **/
    V_DICT_OBJ_TYPE   := ora_dict_obj_type;
    /** LOG表に INSERT する **/
    INSERT INTO LOG_DDL
    ( COL_SYSDATE,
      COL_EVENT,
      COL_USER,
      COL_OWNER,
      COL_OBJ_TYPE,
      COL_OBJ_NAME)
     VALUES
     ( SYSDATE,
       V_SYSEVENT,
       USER ,
       V_DICT_OBJ_OWNER,
       V_DICT_OBJ_TYPE,
       V_DICT_OBJ_NAME);
END ;
/
 
トリガーが作成されました。

このトリガーは前回、紹介したトリガーを少し修正したものです。

●2行目(BEFORE CREATE OR ALTER OR DROP)は、このトリガーがCREATE、ALTER、DROPのいずれでも起動するBEFOREトリガーであることを示します。
●3行目(ON DATABASE)は、「ON SCHEMA」ではなく「ON DATABASE」なので、このトリガーがどのスキーマオブジェクトに対しても起動することを示します。
●5行目から9行目で変数を宣言し、
●11行目から18行目でイベント属性の値をそれらの変数に代入し、
●20行目から33行目までのINSERT文でそれらの変数をもとに、ログ表にDDL操作の詳細をINSERTするわけです。

では早速検証してみましょう。

まず現在のセッションはSYSTEMユーザですが、SCOTTスキーマに適当に表を作成します。

1
2
3
CREATE TABLE SCOTT.TEST99 ( DUMMY NUMBER);
 
表が作成されました。

ではその表を削除します。

1
2
3
DROP TABLE SCOTT.TEST99  PURGE;
 
表が削除されました。

これで上記のCREATEとDROPの記録がログ表にINSERTされているはずなので、ログ表を問い合わせてみましょう。

時間までわかるようにDATE型の書式を変更します。

1
2
3
ALTER SESSION SET NLS_DATE_FORMAT = 'YY/MM/DD HH24:MI:SS' ;
 
セッションが変更されました。

そして、ログ表を問い合わせます。
各列のカラム属性(長さなど)を事前に調整していれば、見やすい結果が表示されます。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT COL_SYSDATE,
        COL_EVENT AS EVENT,
        COL_USER  AS OPER,
        COL_OWNER AS OWNER,
        COL_OBJ_TYPE AS TYPE,
        COL_OBJ_NAME AS NAME
FROM   LOG_DDL
/
 
COL_SYSDATE          EVENT   OPER    OWNER   TYPE    NAME
-------------------- ------- ------- ------- ------- --------
13/08/22 22:46:41    CREATE  SYSTEM  SCOTT   TABLE   TEST99
13/08/22 22:48:16    DROP    SYSTEM  SCOTT   TABLE   TEST99

ログ表の記録により、13/08/22 22:46:41の時点で、SYSTEMユーザが、SCOTT.TEST99表をCREATEし、その後、13/08/22 22:48:16の時点でDROPしたことがわかりますね。
監査的な意味でもたいへん役に立つ記録です。

単にDDLの監査だけが目的であれば、データベースの標準監査機能(AUDITコマンドによる監査オプション設定)の方が簡単なのですが、この方法であれば自由に他の表に対してDDL操作の詳細を記録できますので、いろいろと応用できると思います。

いかがでしたか? イメージも湧きやすかったのではないでしょうか。
次回はまた違ったタイプのトリガーをご紹介しますので、引き続きご期待ください。

先頭へ戻る