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

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

第118回「WEBアプリ作成(16)(アクセスログを記録する(データベーストリガー))」

2015.02.05

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

前回はWEBアプリを複数ユーザで共有する手順を解説しました。
複数のユーザーで共有・更新する場合、セキュリティ観点で「誰が、いつ、どのデータをどのように更新したのか」という記録を残したいニーズがあり得ます。
今回はそのような、いわゆるアクセスログをPL/SQLのデータベース・トリガーで記録することを解説したいと思います。データベーストリガーは、バックナンバー第36回~第42回で基礎から詳細までを解説しています。特に今回のテーマに該当するのは、バックナンバー第39回 「行トリガーで『:OLD.列名』と『:NEW.列名』を用いた値ベース監査」です。ぜひ、この第39回にも目を通してください。

バックナンバー第67回~第74回にもトリガーの説明がありますが、こちらは特殊なトリガーなので、今回のテーマには関係ありません。

※バックナンバーは本メルマガのINDEXページからもたどれますので、ご参照ください。

さて、一般的なデータベース・トリガーは特定の表に対して定義するプログラムであり、その表が更新(INSERT, UDATE, DELETE)されるときに自動起動します。今回はその表の更新時に、「誰が、いつ、どのデータをどのように更新したのか」という記録を監査表(ログ表)に記録(INSERT)するものです。

監査表はフォーマット(列の定義)が決まっているわけではなく、設計者が目的に合うように設計します。今回は以下のような監査表を作成します。この表にアクセスログを記録する予定です。
SQL> SHOW USER ユーザーは"SCOTT"です。

1
2
3
4
5
6
7
8
9
CREATE TABLE  AUDIT_TABLE
( OPER_DATE    DATE ,           -- いつ
   OPERATOR     VARCHAR2(100),  -- 誰が、
   TABLE_NAME   VARCHAR2(100),  -- どの表の
   KEY_VALUE    VARCHAR2(100),  -- どの行に対して
   DETAIL       CLOB)           -- どのような操作をおこなったか
/
 
表が作成されました。

これで監査表(ログ表)を作成しました。
続いて、前回も使ったEMP2表(社員表)に対して、データベーストリガーを作成し、EMP2表に対するアクセスログが上記の監査表(AUDIT_TABLE表)に自動的に記録されるようにします。なお、EMP2表のフォーマットは以下の通りです。
※EMP2表は前回も使っていますが、もともとバックナンバー第107回「WEBアプリ作成(5)(データ登録 1/2)」で、EMP表をもとに簡単なフォーマットを目的に作成したものです。

1
2
3
4
5
6
7
8
SQL> DESC EMP2
  名前                                      NULL ?    型
  ----------------------------------------- -------- ----------------------------
  EMPNO                                     NOT NULL NUMBER(4)        -- 社員番号
  ENAME                                              VARCHAR2(20)     -- 社員名
  HIREDATE                                           DATE             -- 入社日
  DEPTNO                                             NUMBER(2)        -- 部門番号
  NOTE                                               CLOB             -- 備考

EMP2表に対して、データベース・トリガーを作成します。名前をTRIG_AUDIT_EMP2とします。 SQL> SHOW USER ユーザーは"SCOTT"です。

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
CREATE OR REPLACE TRIGGER TRIG_AUDIT_EMP2
/*******************************************************************************/
-- EMP2表に対して、 INSERT, UPDATE, DELETE の後に起動するトリガー
/*******************************************************************************/
AFTER INSERT OR DELETE OR UPDATE ON EMP2
/****************************************************/
-- 行トリガー(FOR EACH ROW  行ごとに起動する)
/****************************************************/
FOR EACH ROW
/********************************/
-- 以下は無名ブロック
/********************************/
DECLARE  -- 宣言部  監査表(AUDIT_TABLE)にINSERTする値を格納する変数
    V_OPER_DATE   DATE := SYSDATE;            -- いつ(SYSDATE)
    V_OPERATOR    VARCHAR2(100) := USER ;      -- 誰が(USER)
    TABLE_NAME    VARCHAR2(10) := 'EMP2' ;    -- 表名前
    V_KEY_VALUE   VARCHAR2(100);
    V_DETAIL      CLOB;                      -- 操作の詳細
BEGIN  -- 実行部
/****************************************************************/
-- INSERT時の操作の詳細をV_DETAIL変数に格納する 各列の値 列名=値
/****************************************************************/
   IF INSERTING THEN
      V_KEY_VALUE := TO_CHAR(:NEW.EMPNO);   -- 行の識別のため主キー情報取得
      V_DETAIL := 'INSERT:' ;
      V_DETAIL := V_DETAIL || ' EMPNO=' || TO_CHAR(:NEW.EMPNO);
      V_DETAIL := V_DETAIL || ' ENAME=' || :NEW.ENAME;
      V_DETAIL := V_DETAIL || ' HIREDATE=' || TO_CHAR(:NEW.HIREDATE, 'YY/MM/DD' );
      V_DETAIL := V_DETAIL || ' DEPNOT='   || TO_CHAR(:NEW.DEPTNO);
      V_DETAIL := V_DETAIL || ' NOTE(文字数)=' || TO_CHAR(LENGTH(:NEW.NOTE));
/***********************************************************************************/
-- UPDATE時の操作の詳細をV_DETAIL変数に格納する 変更された列について列名[旧値⇒新値]
/***********************************************************************************/
   ELSIF  UPDATING THEN
      V_KEY_VALUE := TO_CHAR(:NEW.EMPNO);   -- 行の識別のため主キー情報取得
      V_DETAIL := 'UPDATE:' ;
      IF :NEW.EMPNO <> :OLD.EMPNO  THEN   -- EMPNO列(社員番号)が変更されている場合
         V_DETAIL := V_DETAIL || ' EMPNO[' || TO_CHAR(:OLD.EMPNO) || '⇒'
                                           || TO_CHAR(:NEW.EMPNO) || ']' ;
      END IF;
      IF :NEW.ENAME <> :OLD.ENAME THEN  --  ENAME列(社員名)が変更されている場合
         V_DETAIL := V_DETAIL || ' ENAME[' || :OLD.ENAME || '⇒'
                                          ||  :NEW.ENAME || ']' ;
      END IF;
      IF :NEW.HIREDATE <> :OLD.HIREDATE THEN   -- HIREDATE列(入社日)が変更されている場合
         V_DETAIL := V_DETAIL || ' HIREDATE[' || TO_CHAR(:OLD.HIREDATE, 'YY/MM/DD' ) || '⇒'
                                              || TO_CHAR(:NEW.HIREDATE, 'YY/MM/DD' ) || ']' ;
      END IF;
      IF :NEW.DEPTNO <> :OLD.DEPTNO THEN  -- DEPTNO列(部門番号列)が変更されている場合
         V_DETAIL := V_DETAIL || ' DEPTNO[' || TO_CHAR(:OLD.DEPTNO) || '⇒'
                                            || TO_CHAR(:NEW.DEPTNO) || ']' ;
      END IF;
      IF NVL(:NEW.NOTE, 'X' )  <> NVL(:OLD.NOTE, 'X' THEN  -- NOTE列(備考)が変更された場合
        V_DETAIL := V_DETAIL || ' NOTE(文字数)[' || TO_CHAR((LENGTH(:OLD.NOTE))) || '⇒'
                                                 || TO_CHAR((LENGTH(:NEW.NOTE))) || ']' ;
      END IF;
/**********************************************************/
-- DELETE時の操作の詳細をV_DETAIL変数に格納する 列名=値
/**********************************************************/
   ELSE
      V_KEY_VALUE := TO_CHAR(:OLD.EMPNO);   -- 行の識別のため主キー情報取得
      V_DETAIL := 'DELETE:' ;
      V_DETAIL := V_DETAIL || ' EMPNO=' || TO_CHAR(:OLD.EMPNO);
      V_DETAIL := V_DETAIL || ' ENAME=' || :OLD.ENAME;
      V_DETAIL := V_DETAIL || ' HIREDATE=' || TO_CHAR(:OLD.HIREDATE, 'YY/MM/DD' );
      V_DETAIL := V_DETAIL || ' DEPNOT='   || TO_CHAR(:OLD.DEPTNO);
      V_DETAIL := V_DETAIL || ' NOTE(文字数)=' || TO_CHAR(LENGTH(:OLD.NOTE));
   END IF;
/*************************************************************/
-- 最終的に上記の更新記録の詳細を監査表に記録(INSERT)する
/*************************************************************/
   INSERT INTO AUDIT_TABLE(OPER_DATE, OPERATOR, TABLE_NAME, KEY_VALUE, DETAIL)
   VALUES (V_OPER_DATE, V_OPERATOR, TABLE_NAME, V_KEY_VALUE, V_DETAIL);
END ;
/
トリガーが作成されました。

このトリガーはAFTERの行トリガーです。(5行目と9行目)EMP2表に対する更新(INSERT,UPDATE,DELETE)の直後に起動します。行トリガーなので、更新対象の1行1行について起動し、トリガーの中でその行の列値を参照できます。更新前の列値が:OLD.列名であり、更新後の列値が:NEW.列名です。それらの情報を使って詳細な記録をするわけです。

ソースコードにコメントを書いていますので、参考にしてください。
例えば、INSERT時の処理では、:NEW.列名のみを使っています。INSERTで:OLD.列名は常にNULL値なので、:NEW.列名を使います。逆にDELETE時の処理では、:OLD.列名のみを使っています。DELETEでは、:NEW.列名は常にNULL値です。

UPDATE時の処理では、それぞれの列値が更新されている場合(:NEW列名と:OLD.列名が違う場合)に詳細を変数に追記していることがわかります。

なお、53行目のNOTE列(備考)の比較条件で、NVL関数を使っていますが、これは値がNULLである場合に備えています。値がNULLの場合、「IS NULL」や「IS NOT NULL」以外の比較演算子 は正しい判定ができないので、NULLのときは、'X'という文字に置き換えてNULLでない状態で比較しています。比較にNULL値が対象となる可能性がある場合はこのように、NVL関数でNULL値を他のデータに置き換えて比較すると簡単です。
なお、NOTE列(備考)は、大量の文字データなので、文字数の変化だけを記録するようにしました。(54~55行目)

このようなトリガーを設けることにより、たとえば、EMP2表のEMPNO列(社員番号)=1234 の社員が存在するとして、そのENAME列(社員名)をAAAからBBBへ、そしてDEPTNO列(部門番号)を10から20へ更新したとすると、監査表(AUDIT_TABLE)に1行登録され、その行のDETAIL列の値は以下のようになります。

----------------------------------------------------------------
UPDATE: ENAME[AAA⇒BBB] DEPTNO[10⇒20]
-----------------------------------------------------------------

もちろんその行のKEY_VALUE列には、EMP2表の主キーである、EMPNO列の値「1234」がセットされ、OPER_DATE列には、更新を行った日時がセットされ、OPERATOR列には、更新を行ったユーザ名がセットされ、TABLE_NAME列には、更新対象の表名である、「EMP2」という文字がセットされています。

データベース・トリガーはそれが有効である限り、該当するアクションがあれば必ず起動しますので、誰がどのような手段でEMP2表を更新しても、監査表への記録が行われます。更新によっては一つのUPDATE文でEMP2表の複数の行を更新するかもしれませんが、行トリガーなので、各行ごとの更新ログがきちんと監査表に記録されます。

通常、このようなアクセスログ(監査)の記録は、一つの表だけでなく、セキュリティが重要な複数の表が対象になります。
そのとき対象となる表毎に監査表を用意することもできますが、共通の監査表を使うこともできます。共通の監査表にしておけば、それを表示するロジックも共通になるので、プロシージャ化しておけば便利です。
今回の監査表は、複数の表で共有できる共通のフォーマットを意識しました。監査表にTABLE_NAME列 (表名)がありますので、複数の表でも利用可能です。

次回はこの監査表(ログ表)の内容をWEBアプリに表示するプロシージャを作成し、それをWEBアプリに組み込んでみたいと思います。

ご期待ください。

先頭へ戻る