システム・トリガーを試す（２）（Oracle Database 19c)

　ユーザーの接続や切断と同時にPL/SQLプログラムを実行したい場合にはログオン・トリガー／ログオフ・トリガーを作成します。利用用途としては、例えばサードパーティ製のパッケージを使っていてALTER SESSION文が実行できない場合でもログオン・トリガーを設定することでクライアントの接続と同時にSQL文を自動実行することができるようになります。

ログオン・トリガー

　ログオン・トリガーを使うためにはAFTER LOGON ON DATABASEトリガーを定義します。認証が完了した後にトリガーが実行されます。このため認証に失敗した場合は実行されません。以下はログオン・トリガーlogon_trigger1の実装例です。実行ユーザーを確認するために、SYS_CONTEXT関数とUSER関数を実行してアラートログに書きこんでいます。特定のユーザーが接続した場合のみトリガーを発行する場合は「WHEN (USER='SCOTT')」のようにWHEN句を指定するか、PL/SQLブロック内で接続ユーザーをチェックします。

SQL> CREATE OR REPLACE TRIGGER logon_trigger1 AFTER LOGON ON DATABASE
DECLARE
    u VARCHAR2(128);
    c VARCHAR2(128);
    s VARCHAR2(128);
BEGIN
    u := USER;
    c := SYS_CONTEXT('USERENV', 'CURRENT_USER');
    s := SYS_CONTEXT('USERENV', 'SESSION_USER');
    DBMS_SYSTEM.KSDWRT(2, TO_CHAR(SYSTIMESTAMP) || ': AFTER LOGON TRIGGER: ' || u || ' : ' || c || ' : ' || s);
END;
/
トリガーが作成されました

USER関数とSYS_CONTEXT関数の実行結果は以下の通りです。SCOTT列はユーザーSCOTTを使って認証を行った場合の出力です。SYS列は「$ sqlplus / as sysdba」接続を行った場合の出力です。基本的には接続したユーザーの権限でトリガーが実行されていることがわかります。

関数	SYS	SCOTT
USER	SYS	SCOTT
SYS_CONTEXT('USERENV', 'CURRENT_USER')	SYS	SYS
SYS_CONTEXT('USERENV', 'SESSION_USER')	SYS	SCOTT

ログオフ・トリガー

　ログオフ・トリガーを使うためにはBEFORE LOGON ON DATABASEトリガーを定義します。以下はログオフ・トリガーlogoff_trigger1の実装例です。実行ユーザーを確認するために、SYS_CONTEXT関数とUSER関数を実行してアラートログに書きこんでいます。

CREATE OR REPLACE TRIGGER logoff_trigger1 BEFORE LOGOFF ON DATABASE
DECLARE
    u VARCHAR2(128);
    c VARCHAR2(128);
    s VARCHAR2(128);
BEGIN
    u := USER;
    c := SYS_CONTEXT('USERENV', 'CURRENT_USER');
    s := SYS_CONTEXT('USERENV', 'SESSION_USER');
    DBMS_SYSTEM.KSDWRT(2, TO_CHAR(SYSTIMESTAMP) || ': BEFORE LOGOFF TRIGGER: ' || u || ' : ' || c || ' : ' || s);
END;
/
トリガーが作成されました

USER 関数と SYS_CONTEXT 関数の実行結果は以下の通りです。SCOTT列はユーザー SCOTT を使った接続を切った場合の出力です。SYS 列は SYS ユーザーの接続を切った場合の出力です。基本的には接続中のユーザーの権限でトリガーが実行されていることがわかります。

関数	SYS	SCOTT
USER	SYS	SCOTT
SYS_CONTEXT('USERENV', 'CURRENT_USER')	SYS	SYS
SYS_CONTEXT('USERENV', 'SESSION_USER')	SYS	SCOTT

ALTER SYSTEM KILL SESSIONで強制切断された場合はログオフ・トリガーは動作しないようです。

SQL> SELECT sid, serial# FROM V$SESSION WHERE USERNAME='SCOTT';

       SID    SERIAL#
---------- ----------
       139      35714

SQL> ALTER SYSTEM KILL SESSION '139,35714';
システムが変更されました。
``