Oracle のlogon triggerによるシノニム機能実現

シノニム機能

Oracleはシノニム利用して、他スキマーのテーブルにOwner名省略してアクセス可能です。

例：

• 二つスキマーがあります、APPOとAPP1
• APPOにT1というテーブルがあります
• APP1からAPPO.T1にSELECTします

--Schema: APPO
SQL> CREATE TABLE T1(NAME1 VARCHAR(50));
Table created.
 
SQL> INSERT INTO T1 VALUES('YEAR2024');
1 row created.
 
SQL> COMMIT;
Commit complete.
 
SQL> SELECT * FROM T1;
NAME1
--------------------------------------------------
YEAR2024
 
SQL> GRANT SELECT ON T1 TO APP1;
Grant succeeded.
 
--Schema: APP1
--直接アクセス
SQL> SELECT * FROM APPO.T1;
NAME1
--------------------------------------------------
YEAR2024
 
--SYNONYM作成してアクセス
SQL> CREATE SYNONYM T1 FOR APPO.T1;
Synonym created.
 
SQL> SELECT * FROM T1;                                 
NAME1
--------------------------------------------------
YEAR2024

logon triggerによるシノニム機能実現

• シノニム利用のアクセスはシノニム作成する手間がかかりますから、logon triggerでシノニム作成せず、シノニムのようにアクセス方法を紹介します。
• sysユーザー下記のlogon trigger作成

--sysユーザーで下記のtrigger作成
create or replace trigger TR_LOGON
after logon on database
declare
        v_username      varchar2(20);
begin
        SELECT sys_context('userenv','SESSION_USER') INTO v_username FROM DUAL;
        if sys_context('userenv','isdba') = 'FALSE' then
             if v_username != 'DBSNMP' and v_username != 'SYS'
                and v_username != 'SYSTEM'
            then
                if (v_username = 'APP1')
                then
                    execute immediate 'alter session set current_schema=APPO';
                end if;
           end if;
       end if;
end;
/

• 対象テーブル(T1)の権限付与のみ、シノニムのようにアクセス可能になります
• 上記の例

--Schema: APPO
SQL> CREATE TABLE T1(NAME1 VARCHAR(50));
Table created.
 
SQL> INSERT INTO T1 VALUES('YEAR2024');
1 row created.
 
SQL> COMMIT;
Commit complete.
 
SQL> SELECT * FROM T1;
NAME1
--------------------------------------------------
YEAR2024
 
SQL> GRANT SELECT ON T1 TO APP1;
Grant succeeded.
 
--Schema: APP1
--直接アクセス
SQL> SELECT * FROM APPO.T1;
NAME1
--------------------------------------------------
YEAR2024
 
#SYNONYM作成せずアクセス
SQL> SELECT * FROM T1;                                 
NAME1
--------------------------------------------------
YEAR2024
 
--sys/system　からシノニム作成されてないことを確認
SQL> select count(*) from dba_synonyms where synonym_name='T1';
  COUNT(*)
----------
         0

• 上記のlogon trigger方式は権限的にシノニム作成と一緒で、object ownerから権限付与されてないobjectにアクセスできないです。

ユースケース

• よく利用されているユースケースを一つ紹介します。
• アプリ用アカウントのパスワードは定期的に変更必要なケース
  • 三つアカウント作成
    • APPO：全ObjectのOwner
    • APP1：アプリ用
    • APP2：アプリ用
  • APP1とAPP2は交代方式でアプリ側に利用していただく
  • パスワード変更シナリオ
    • APPOのパスワード変更はアプリに影響がないため、いつでも変更可能です。
    • APP1とAPP2のパスワード変更はrolling方式で変更
      • 仮に現在アプリがAPP1を利用してDBに接続している
      • APP2はアプリに未使用のため、APP2のパスワードを変更します
      • アプリ側はAPP1からAPP2に利用切替していただく
      • APP1のパスワードを変更します

最後

• logon triggerを活用すれば、他にも色々実現できます。
  • 例：Oracle DB ACLの実装やログイン履歴ロギング等々。