はじめに
Autonomous AI Databaseで統合監査をご利用予定のお客様から、「あるスキーマ内の既存の表を一括で監査ポリシーに追加したい。ポリシーの作成後に作成された表も監査対象として同じポリシーに追加したい。」というお話があったので、その実現が可能かを検証してみました。
ここでは、データベーストリガーを使用した方法を検証してみました。
ただし、直接トリガー内で監査ポリシーへの監査対象の追加（ALTER AUDIT POLICY）を行うことができないので、監査対象の追加（ALTER AUDIT POLICY）を実行するDBMS_SCHEDULERジョブを実行するPL/SQLプロシージャを作成し、表が追加された時に実行されるトリガーからそのPL/SQLプロシージャを実行するような構成としました。
注意
こちらの記事の内容はあくまで個人のメモ的な内容のため、こちらの内容を利用した場合のトラブルには一切責任を負いません。
また、こちらの記事の内容を元にしたOracleサポートへの問い合わせはご遠慮ください。
0.既存表の確認
user_tablesビューで、スキーマ内の既存の表を確認します。
SQL> SELECT table_name FROM user_tables
2 ORDER BY table_name;
TABLE_NAME
--------------------------------------------------------------------------------
TAB1
TAB2
TAB3
TAB4
TAB5
TAB6
TAB7
TAB8
TAB9
9 rows selected.
SQL>
1. 監査ポリシーの作成
スキーマ内の1つの表を対象にした監査ポリシーschema_all_policyを作成します。
SQL> CREATE AUDIT POLICY schema_all_policy ACTIONS ALL ON admin.tab1;
Audit policy created.
SQL>
audit_unified_policiesビューで、作成した監査ポリシーschema_all_policyの監査対象を確認します。
SQL> SELECT * FROM audit_unified_policies
2 WHERE policy_name = 'SCHEMA_ALL_POLICY';
POLICY_NAME
--------------------------------------------------------------------------------
AUDIT_CONDITION
--------------------------------------------------------------------------------
CONDITION
---------
AUDIT_OPTION
--------------------------------------------------------------------------------
AUDIT_OPTION_TYPE
------------------
OBJECT_SCHEMA
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE COM INH AUD ORA PRO
----------------------- --- --- --- --- ---
COLUMN_NAME
--------------------------------------------------------------------------------
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB1
TABLE NO NO NO NO NO
SQL>
表TAB1に対する全てのアクションが監査対象になっていることが確認できました。
2. スキーマ内の全ての表を監査ポリシーの監査対象に一括追加
1.の監査ポリシー作成に使用した表（TAB1）以外の表を、監査ポリシーschema_all_policyの監査対象に一括で追加します。
SQL> DECLARE
2 sql_stmt VARCHAR2(1000);
3 CURSOR c1 IS
4 SELECT table_name FROM user_tables WHERE table_name != 'TAB1';
5 BEGIN
6 FOR item IN c1
7 LOOP
8 sql_stmt := 'ALTER AUDIT POLICY schema_all_policy ADD ACTIONS ALL ON admin.'||item.table_name;
9 EXECUTE IMMEDIATE sql_stmt;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
audit_unified_policiesビューで、作成した監査ポリシーschema_all_policyの監査対象を確認します。
SQL> SELECT * FROM audit_unified_policies
2 WHERE policy_name = 'SCHEMA_ALL_POLICY'
3 ORDER BY object_name;
POLICY_NAME
--------------------------------------------------------------------------------
AUDIT_CONDITION
--------------------------------------------------------------------------------
CONDITION
---------
AUDIT_OPTION
--------------------------------------------------------------------------------
AUDIT_OPTION_TYPE
------------------
OBJECT_SCHEMA
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE COM INH AUD ORA PRO
----------------------- --- --- --- --- ---
COLUMN_NAME
--------------------------------------------------------------------------------
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB1
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB2
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB3
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB4
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB5
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB6
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB7
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB8
TABLE NO NO NO NO NO
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB9
TABLE NO NO NO NO NO
9 rows selected.
SQL>
スキーマ内の全ての表が、監査ポリシーschema_all_policyに監査対象として追加されたことが確認できました。
3. トリガーからコールされるPL/SQLプロシージャの作成
スキーマ内に表が作成された時に実行されるトリガーからコールされるPL/SQLプロシージャalter_audit_policyを作成します。
PL/SQLプロシージャalter_audit_policyは、パラメータとしてDBMS_SCHEDULERジョブで実行するPL/SQLブロックを受け取り、そのPL/SQLブロックを実行するDBMS_SCHEDULERジョブを作成/実行し、実行後にジョブを削除します。
SQL> CREATE OR REPLACE PROCEDURE alter_audit_policy(sql_stmt IN VARCHAR2)
2 IS
3 BEGIN
4 DBMS_SCHEDULER.CREATE_JOB (
5 job_name => DBMS_SCHEDULER.GENERATE_JOB_NAME('AUD_JOB'),
6 job_type => 'PLSQL_BLOCK',
7 job_action => sql_stmt,
8 start_date => SYSTIMESTAMP,
9 enabled => TRUE,
10 auto_drop => TRUE
11 );
12 END;
13 /
Procedure created.
SQL>
4. スキーマ内に新規表が作成された時に実行されるトリガーを作成
スキーマ内に表が作成された時に実行されるトリガーafter_create_tableを作成します。
トリガーafter_create_tableは、スキーマ内に表が作成されると、作成された表を監査ポリシーschema_all_policyの監査対象として追加するPL/SQLブロックを生成し、そのPL/SQLブロックを3.で作成したPL/SQLプロシージャalter_audit_policyに渡して実行します。
SQL> CREATE OR REPLACE TRIGGER after_create_table
2 AFTER CREATE ON SCHEMA
3 BEGIN
4 IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
5 alter_audit_policy('BEGIN EXECUTE IMMEDIATE ''ALTER AUDIT POLICY schema_all_policy ADD ACTIONS ALL ON ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || '''; END;');
6 END IF;
7 EXCEPTION
8 WHEN OTHERS THEN
9 -- Log errors here to prevent blocking user table creation
10 NULL;
11 END;
12 /
Trigger created.
SQL>
5. 動作確認
スキーマ内に表TAB10を作成します。
SQL> CREATE TABLE tab10 (
2 col1 NUMBER,
3 col2 VARCHAR2(100)
4 );
Table created.
SQL>
audit_unified_policiesビューで、スキーマ内に作成した表TAB10が監査ポリシーschema_all_policyの監査対象に追加されているかを確認します。
SQL> SELECT * FROM audit_unified_policies
2 WHERE policy_name = 'SCHEMA_ALL_POLICY'
3 AND object_name = 'TAB10';
POLICY_NAME
--------------------------------------------------------------------------------
AUDIT_CONDITION
--------------------------------------------------------------------------------
CONDITION
---------
AUDIT_OPTION
--------------------------------------------------------------------------------
AUDIT_OPTION_TYPE
------------------
OBJECT_SCHEMA
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE COM INH AUD ORA PRO
----------------------- --- --- --- --- ---
COLUMN_NAME
--------------------------------------------------------------------------------
SCHEMA_ALL_POLICY
NONE
NONE
ALL
OBJECT ACTION
ADMIN
TAB10
TABLE NO NO NO NO NO
SQL>
新たに作成した表TAB10が、監査ポリシーschema_all_policyの監査対象に追加されていることが確認できました。
上記の方法で、スキーマ内に追加された表を自動的に監査ポリシーの監査対象に追加できることがわかりました。