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

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

第57回 「自律型トランザクションとデータベーストリガー」

2013.05.13

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

前回は『自律型トランザクション』がどういうものかを紹介しました。
簡単に振り返っておきましょう。
この機能は、メイントランザクションから、自律型トランザクションのサブプログラムを呼び出して、そのサブプログラム内の処理だけを確定(COMMIT)できるものです。
それにより、メイントランザクションが取り消し(ROLLBACK)されたとしても、自律型トランザクション内の処理だけは、確定済みなので、取り消されないわけでしたね。

この機能は、例えば、監査の記録を確実に監査表に残したい時になどに便利です。

今回はその応用例の紹介です。

自律型トランザクション機能とデータベーストリガーを併用して、より確実にセキュリティ目的の監査記録を残せることをデモを交えて解説します。

ストーリーとしては、テスト用の表に UPDATE操作において「いつ、誰が、どの列を、どのように更新したか」ということを監査表にINSERTするという内容です。
(トリガーを利用した監査についてはバックナンバー第39回にも事例があります。ただし、第39回の内容は自律型トランザクションは使っていません)

では、早速、やってみます。

まず、テスト用の表(TEST01表)と、その表に対する更新を記録する監査表(AUDIT01表)を作成します。
TEST01表には、チェック制約で ( A列 > 10 )の制約を設けておきます。
監査表へのINSERT処理のため、順序オブジェクト SEQ_AUDITも作成しておきます。

/********************************/
--  テスト用の表作成と制約の設定
/********************************/
SQL> CREATE TABLE TEST01 ( A NUMBER, B VARCHAR2(10));

表が作成されました。

SQL> ALTER TABLE TEST01
  2  ADD CONSTRAINT CHECK_TEST01 CHECK ( A > 10);

表が変更されました。

/********************************/
--  監査表と順序オブジェクトの作成
/********************************/
SQL> CREATE TABLE AUDIT01 ( ID NUMBER, MSG  VARCHAR2(200));

表が作成されました。

SQL> CREATE SEQUENCE SEQ_AUDIT;

順序が作成されました。

早速、TEST01に対して、トリガーを作成します。
単純化するために、今回は、TEST01表に対する UPDATEに限定したトリガーにします。

/********************************/
--  トリガーの作成
/********************************/
SQL> CREATE OR REPLACE  TRIGGER TRIG01
  2  BEFORE UPDATE
  3  ON TEST01
  4  FOR EACH ROW
  5  /******************/
  6  -- トリガーの宣言部
  7  /******************/
  8  DECLARE
  9    -- 監査のメッセージ
 10    MSG_STRING  VARCHAR2(200);
 11    -- 自律型トランザクションのローカルプロシージャ
 12    PROCEDURE PROC_AUDIT_INSERT(P_MSG IN VARCHAR2)
 13    IS
 14       PRAGMA AUTONOMOUS_TRANSACTION;
 15    BEGIN
 16      INSERT INTO AUDIT01 VALUES (SEQ_AUDIT.NEXTVAL, P_MSG);
 17      COMMIT;
 18    END PROC_AUDIT_INSERT;
 19  /******************/
 20  -- トリガーの実行部
 21  /******************/
 22  BEGIN
 23    -- 監査記録のメッセージを用意して
 24    MSG_STRING := TO_CHAR(SYSDATE,'YY/MM/DD HH24:MI:SS') ||
 25         'に ユーザ' || USER|| 'が、' || 'A列の値を ' ||
 26         :OLD.A || '→' || :NEW.A || ', B列の値を ' ||
 27         :OLD.B || '→' || :NEW.B || ' に更新';
 28    -- 自律型トランザクションで監査表にINSERT
 29    PROC_AUDIT_INSERT(MSG_STRING);
 30  END  TRIG_EMP;
 31  /

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

このトリガーについて解説します。

まず、このトリガーは、行トリガーです(4行目のFOR EACH ROW)。
表のDMLトリガーには、『文トリガー』と『行トリガー』がありますが、行トリガーは、文トリガーと違って、更新される1行1行について順番にその行の上で起動しますので、その行の列値が参照できます。
このトリガー内の処理は、監査の記録なので、1行、1行の列値を参照しますから、行トリガーでなければならないわけです。
(行トリガー、文トリガーについては、バックナンバー 第38回を参照)

次のポイントは、このトリガーは、BEFOREのトリガー(2行目のBEFORE)だということです。
これにも理由があります。もしも、AFTERのトリガーであれば、制約違反でエラーとなるようなDMLの場合、トリガーが起動しないので監査表に監査の記録をINSERTできません。

一般的に行トリガーの起動と行の更新の順番を復習すると以下の通りです。

1.まず更新対象の1行目にカーソルが移動します。

2.その行の上で、BEFORE行トリガーが起動します。

3.次にその行を更新します。制約違反があればエラーとなり終了します。
   (エラーであればそれまでのトリガー処理も取消されます。ただし自律型トランザクションは除く)

4.上記の更新がエラーでなければ、その行の上でAFTERの行トリガーが起動します。

5.AFTERの行トリガーが正常終了したあと、カーソルは次の更新対象行に移動します。

以上のような処理が行われています。
したがってユーザーが制約違反エラーとなる更新を行う場合、AFTERの行トリガーは起動しません。
したがって、自律型トランザクションを用いてトリガーで監査を行うのであれば、AFTERよりもBEFOREの方が良いということが言えます。

最後に、一番大事なポイントとして、監査表へのINSERT処理が自律型トランザクションのサブプログラムとして定義されていることが重要です(12~18行目)。
自律型トランザクションは必ずサブプログラムとして定義する必要があります。
たとえローカルな宣言部でもよいので、サブプログラムとして定義します。

解説はここまでにして、実際に、上記のトリガーで監査の記録が残せることを確認します。

まず、最初にテスト用の表に2行INSERTしてコミットしておきます。
上記のトリガーはINSERTでは起動しません。UPDATEでのみ起動します。A列の値は 10より大きい必要があります。

SQL> INSERT INTO TEST01(A,B) VALUES (11,'ABC');

1行が作成されました。

SQL> INSERT INTO TEST01(A,B) VALUES (12,'DEF');

1行が作成されました。

SQL> COMMIT;

コミットが完了しました。

SQL> SELECT * FROM TEST01;

         A B
---------- ----------
        11 ABC
        12 DEF

テスト表に2行存在してますね。
では制約 ( A > 10 ) に違反しない形で1行UPDATEします。

SQL> UPDATE TEST01 SET A = 21 WHERE A = 11;

1行が更新されました。

SQL> SELECT * FROM TEST01;

         A B
---------- ----------
        21 ABC          ←更新された行
        12 DEF

確かに、1行更新されました。

上記のUPDATEによりトリガーが起動して、監査表に記録されているはずです。

SQL> SELECT MSG FROM AUDIT01 ORDER BY ID;

MSG
--------------------------------------------------------------------------------
13/05/08 15:30:37に ユーザSCOTTが、A列の値を 11→21, B列の値を ABC→ABC に更新

ご覧のように確かに監査が記録されています。

ではロールバックします。

SQL> ROLLBACK;

ロールバックが完了しました。

SQL> SELECT * FROM  TEST01;

         A B
---------- ----------
        11 ABC          ←ロールバックで元に戻った
        12 DEF

テスト表のデータはロールバックで取り消され、もとに戻りました。
しかし、監査表の記録は自律型トランザクションで確定済みなので、取り消されていないはずです。
実際に問い合わせて確認してみます。

SQL> SELECT MSG FROM AUDIT01 ORDER BY ID;

MSG
--------------------------------------------------------------------------------
13/05/08 15:30:37に ユーザSCOTTが、A列の値を 11→21, B列の値を ABC→ABC に更新

ご覧のように監査の記録が取り消されていません。

このように、トランザクションが取り消されても、自律型トランザクションで記録した監査データは取り消されません。

では、次に制約 ( A > 10 ) に違反する UDPATEをしてみましょう。
当然文は制約に違反するためエラーとなりますが、監査は記録されるでしょうか?
結論を言えば記録されます。実際にやってみます。

まず制約違反のエラーとなるUPDATEを行います。

SQL> UPDATE TEST01 SET A = 0 WHERE A = 12;
UPDATE TEST01 SET A = 0 WHERE A = 12
*
行1でエラーが発生しました。:
ORA-02290: チェック制約(SCOTT.CHECK_TEST01)に違反しました

TEST01表へのUPDATEがエラーなので、TEST01表のデータは変更されていません。

SQL> SELECT * FROM TEST01;

         A B
---------- ----------
        11 ABC
        12 DEF

しかし、監査の記録は作成されています。

SQL> SELECT MSG FROM AUDIT01 ORDER BY ID;

MSG
--------------------------------------------------------------------------------
13/05/08 15:30:37に ユーザSCOTTが、A列の値を 11→21, B列の値を ABC→ABC に更新
13/05/08 15:40:42に ユーザSCOTTが、A列の値を 12→0, B列の値を DEF→DEF に更新

ご覧のように実際の更新はエラーとなったのですが、監査の記録はされているわけです。

なぜ、SQL文がエラーでも監査の記録がされかと言えば、もうお分かりのように、トリガーがBEFOREの行トリガーでかつ、自律型トランザクションで記録しているからですね。

いかがですか? BEFOREの行トリガーで自律型トランザクションを使えば、たとえトリガー起動のもとになったSQL文がエラーで終わっても、少なくとも更新対象の最初の行に対しては監査の記録が残せることがおわかりいただけたと思います。
AFTERのトリガーだったり、あるいはBEFOREでも自律型トランザクションを使っていない場合は、SQL文がエラーで終わった場合、監査の記録は残せません。

では、今回はここまでにいたします。
次回はこの内容に一つ補足しておきたいことがある(大変細かいことですが・・)ので、そのことについて触れたいと思います。

ご期待ください。

先頭へ戻る