ファイングレイン監査の機能を使いあるテーブルへのアクセス状況を調べる事ができます。DMLに関する調査であればトリガーを使って調べている方けっこうあると思いますが、ファイングレイン監査ではDMLはもちろんSELECT文もキャッチできるので便利です。通常監査機能っていうのは、管理者の権限乱用、不正アクセスなどの抑止効果を期待し用いられている事が一般的ですが、私の場合「アプリケーションの運用管理」に使うことがあります。「このテーブル使っていないはずなんだけど、削除するの怖いなぁ、、、」なんてシーンよくありますがこんな時、本当にアクセスがないか調べる際に利用します。注意点として、SYSユーザの監査証跡は残りません（なぜだか取得できません。マニュアルにも記載ありました）
使用上の注意
表またはビューには、最大256個のファイングレイン監査ポリシーを適用できます。
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_fga.htm
object_schemaが指定されていない場合は、現行のログオン・ユーザーのスキーマと想定されます。
FGAポリシーは、LOB列などの行外の列には適用しないでください。
監査方針の作成
DBMS_FGAパッケージに対する実行権限が必要です。（DBAロールを持っていれば大丈夫）
以下でファイングレイ監査ポリシー追加ができます。
SOONIスキーマのEXP01テーブルに対し POLICY_01という名のポリシーを作成する。
これによって、各種DML（INSERT,UPDATE,DELETE,SELECT）が発行されると SYS.FGA_LOG$へ監査データが蓄積していきます。 アクセス状況の確認は、SYS.FGA_LOG$を直接参照するのではなく、DBA_FGA_AUDIT_TRAILビューを通して確認するのが一般的です。
監査方針を作成するSQL
以下はSOONI.EXP01 テーブルに対してのアクセスを調べるものですが、シノニム経由でアクセスした場合も監査ログは取得できます。（OBJECT_NAMEへシノニムを指定する事はできません。）
BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'SOONI',
OBJECT_NAME => 'EXP01',
POLICY_NAME => 'POLICY_01',
STATEMENT_TYPES => 'INSERT,UPDATE,DELETE,SELECT'
);
END;
/
作成した監査方針の確認
select * from ALL_AUDIT_POLICIES t where t.POLICY_NAME='POLICY_01' ;
監査方針を削除するSQL
BEGIN DBMS_FGA.DROP_POLICY( object_schema => 'SOONI', object_name => 'EXP01', policy_name => 'POLICY_01' ); END; /
作成した監査方針の確認例
-- 確認例
--
SQL> select t.OBJECT_SCHEMA,t.OBJECT_NAME,t.POLICY_OWNER,t.POLICY_NAME,t.ENABLED,t.SEL,t.INS,t.UPD,t.DEL,t.POLICY_COLUMN_OPTIONS
2 from ALL_AUDIT_POLICIES t where t.POLICY_NAME='POLICY_01'
3 ;
OBJECT_SCH OBJECT_NAM POLICY_OWN POLICY_NAM ENA SEL INS UPD DEL POLICY_COLUM
---------- ---------- ---------- ---------- --- ------ ------ ------ ------ ------------
SOONI EXP01 SOONI POLICY_01 YES YES YES YES YES ANY_COLUMNS
SQL>
監査データが取得されることを確認する
-- 試しに上記定義したexp01に対しSELECTをかけてみます
--
SQL> select * from exp01
2 ;
C1 C2
---------- ----------------------------------------
1 みかん
2 りんご
3 いちご
4
SQL>
-- 監査情報を確認
select * from DBA_FGA_AUDIT_TRAIL t
;
SQL> @pr
==============================
SESSION_ID : 220634
TIMESTAMP : 2022-12-21 22:32:21
DB_USER : SOONI
OS_USER : sooni
USERHOST : WORKGROUP\DESKTOP-HJ85QJ6
CLIENT_ID :
ECONTEXT_ID :
EXT_NAME :
OBJECT_SCHEMA : SOONI
OBJECT_NAME : EXP01
POLICY_NAME : POLICY_01
SCN : 2418242
SQL_TEXT : select * from exp01
SQL_BIND :
COMMENT$TEXT :
STATEMENT_TYPE : SELECT
EXTENDED_TIMESTAMP : 22-12-21 22:32:21.847517 +09:00
PROXY_SESSIONID :
GLOBAL_UID :
INSTANCE_NUMBER : 0
OS_PROCESS : 2815
TRANSACTIONID :
STATEMENTID : 224
ENTRYID : 10
OBJ_EDITION_NAME :
DBID : 607025343
RLS_INFO :
CURRENT_USER : SOONI
PL/SQLプロシージャが正常に完了しました。
--
-- 次はdeleteしてみます
--
SQL> delete from exp01 t where t.C1=4
2 ;
1行が削除されました。
--
--commitを入れないまま確認
--
SQL> select * from DBA_FGA_AUDIT_TRAIL t
2 ;
==============================
SESSION_ID : 210636
TIMESTAMP : 2022-12-21 22:37:05
DB_USER : SOONI
OS_USER : sooni
USERHOST : WORKGROUP\DESKTOP-HJ85QJ6
CLIENT_ID :
ECONTEXT_ID :
EXT_NAME :
OBJECT_SCHEMA : SOONI
OBJECT_NAME : EXP01
POLICY_NAME : POLICY_01
SCN : 2418577
SQL_TEXT : delete from exp01 t where t.C1=4
SQL_BIND :
COMMENT$TEXT :
STATEMENT_TYPE : DELETE
EXTENDED_TIMESTAMP : 22-12-21 22:37:05.737393 +09:00
PROXY_SESSIONID :
GLOBAL_UID :
INSTANCE_NUMBER : 0
OS_PROCESS : 2543
TRANSACTIONID : 030013009D040000
STATEMENTID : 65
ENTRYID : 3
OBJ_EDITION_NAME :
DBID : 607025343
RLS_INFO :
CURRENT_USER : SOONI
PL/SQLプロシージャが正常に完了しました。
SQL>
--
-- コミット入れてなくても監査データとして取得できていますね
--作成した監査方針の無効化／有効化
--
-- 無効化する事もできます。
--
SQL> BEGIN
2 DBMS_FGA.DISABLE_POLICY(
3 object_schema => 'SOONI',
4 object_name => 'EXP01',
5 policy_name => 'POLICY_01'
6 );
7 END;
8 /
PL/SQLプロシージャが正常に完了しました。
SQL> select t.OBJECT_SCHEMA,t.OBJECT_NAME,t.POLICY_OWNER,t.POLICY_NAME,t.ENABLED,t.SEL,t.INS,t.UPD,t.DEL,t.POLICY_COLUMN_OPTIONS
2 from ALL_AUDIT_POLICIES t where t.POLICY_NAME='POLICY_01'
3 ;
OBJECT_SCH OBJECT_NAM POLICY_OWN POLICY_NAM ENA SEL INS UPD DEL POLICY_COLUM
---------- ---------- ---------- ---------- --- ------ ------ ------ ------ ------------
SOONI EXP01 SOONI POLICY_01 NO YES YES YES YES ANY_COLUMNS
SQL>
-- そして再度有効化します
--
SQL> BEGIN
2 DBMS_FGA.ENABLE_POLICY (
3 object_schema => 'SOONI',
4 object_name => 'EXP01',
5 policy_name => 'POLICY_01',
6 enable => TRUE
7 );
8 END;
9 /
PL/SQLプロシージャが正常に完了しました。
SQL> select t.OBJECT_SCHEMA,t.OBJECT_NAME,t.POLICY_OWNER,t.POLICY_NAME,t.ENABLED,t.SEL,t.INS,t.UPD,t.DEL,t.POLICY_COLUMN_OPTIONS
2 from ALL_AUDIT_POLICIES t where t.POLICY_NAME='POLICY_01'
3 ;
OBJECT_SCH OBJECT_NAM POLICY_OWN POLICY_NAM ENA SEL INS UPD DEL POLICY_COLUM
---------- ---------- ---------- ---------- --- ------ ------ ------ ------ ------------
SOONI EXP01 SOONI POLICY_01 YES YES YES YES YES ANY_COLUMNS
SQL>
--
-- 監査方針自体をDROPします
-- ※監査方針を削除しても、監査ログデータは消えません
--
SQL> BEGIN
2 DBMS_FGA.DROP_POLICY(
3 object_schema => 'SOONI',
4 object_name => 'EXP01',
5 policy_name => 'POLICY_01'
6 );
7 END;
8 /
PL/SQLプロシージャが正常に完了しました。
SQL> select t.OBJECT_SCHEMA,t.OBJECT_NAME,t.POLICY_OWNER,t.POLICY_NAME,t.ENABLED,t.SEL,t.INS,t.UPD,t.DEL,t.POLICY_COLUMN_OPTIONS
2 from ALL_AUDIT_POLICIES t where t.POLICY_NAME='POLICY_01'
3 ;
レコードが選択されませんでした。
SQL>監査データの削除
監査データの削除を行うにはまずは、「監査証跡(データ)クリーンアップ前の初期化」が必要です。
以下は、クリーンアップ・プロシージャがコールされるデフォルトの間隔(時間単位)を10日間（240H)にて初期化しています。最小値は1、最大値は999を設定できます。
まずは初期化
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
default_cleanup_interval => 240) ;
END;
/
999に変更する場合SET_AUDIT_TRAIL_PROPERTYプロシージャを使います
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_property => DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL,
audit_trail_property_value => 999
);
END;
/
監査データの削除実施
参考までにですが、上記初期化を行わず削除を試みると以下のようなエラーがでます。
ORA-46258: 監査証跡のためのクリーンアップが初期化されていません
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
use_last_arch_timestamp => false
);
END;
/
SQL*Plus用
set lin 200 COLUMN OBJECT_SCHEMA FORMAT A10 COLUMN OBJECT_NAME FORMAT A10 COLUMN POLICY_OWNER FORMAT A10 COLUMN POLICY_NAME FORMAT A10 COLUMN ENABLED FORMAT A3 COLUMN ENABLED FORMAT A3 COLUMN POLICY_COLUMN_OPTIONS FORMAT A12
初期化パラメータ確認
以下が監査にかかわる初期化パラメータですが、今回は何れもデフォルトのまま利用できました。
audit_sys_operations＝false の場合、SYSDBA、SYSOPR権限で接続したユーザについても全ての操作を監査してOSファイルは書き込む（DBA監査）。SYSユーザによる操作は標準監査や、ファイングレイン監査の監査証跡に残らないのでSYSユーザの監査をする場合はTRUEにしておく必要がある。
select name,type,value from
v$parameter2 where upper(name) like upper('%AUD%');
SQL> select name,type,value from
2 v$parameter2 where upper(name) like upper('%AUD%');
NAME TYPE VALUE
------------------------------ ---------- ----------------------------------------
audit_sys_operations 1 TRUE
unified_audit_sga_queue_size 3 1048576
audit_file_dest 2 /usr/oracle/app/admin/orau8/adump
audit_syslog_level 2
audit_trail 2 DB
SQL>set lin 200 COLUMN name FORMAT A30 COLUMN value FORMAT A40
,AUDIT_CONDITION => 'USER IN (''JOHNDOE'', ''SCOTT'')',AUDIT_CONDITION => 'USER = ''SCOTT'''
