第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)に１行登録され、その行の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アプリに組み込んでみたいと思います。
ご期待ください。
