I had a requirement to track the activity on a list of tables and decided to try of Fine Grained Auditing (FGA). This was done in a 12.1.0.2.0 – 64bit Unix environment.
Note that the SYS user is not audited by FGA.
Setting up FGA
Check the location of the audit trail
show parameter audit;
If not set to DB, change as follows (11g):
alter system set audit_trail='DB_EXTENDED' scope=SPFILE sid='*';
In 12c the command seems to have changed to:
alter system set audit_trail=DB,EXTENDED scope=SPFILE sid='*';
Bounce the database
The tables to contain the auditing data are created in SYSAUX. It is good practice to move them to their own table space.
SELECT table_name ,tablespace_name FROM dba_tables WHERE owner='SYS' AND table_name IN ('AUD$','FGA_LOG$') ;
The standard auditing data is written into SYS.AUD$ but a better-formatted version is in table DBA_AUDIT_TRAIL. Similarily, SYS.FGA_LOG$ is best viewed from DBA_FGA_AUDIT_TRAIL.
Create a tablespace
CREATE TABLESPACE "AUDAUX" DATAFILE '+DG_ORA1/datafile/audaux_1.dbf' SIZE 2G LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ;
Turn off auto extend as this can fill up fast:
alter database datafile '+DG_ORA1/datafile/audaux_1.dbf' autoextend off;
Move SYS.AUD$ to the new tablespace with
BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDAUX'); END; /
Move SYS.FGA_LOG$ to the new tablespace with:
BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'AUDAUX'); END; /
Create an audit policy as follows. In this case, I am auditing inserts, updates, and deletes on this table.
EXEC DBMS_FGA.ADD_POLICY ( object_schema=>'BILLING', object_name=>'INVENTORY_MASTER', policy_name=>'AUDIT_BILLING_INVENTORY_MASTER', statement_types=>'insert, update, delete' ) ;
If needed, the policy can be dropped with
EXEC DBMS_FGA.DROP_POLICY
(
object_schema=>’BILLING’,
object_name=>’INVENTORY_MASTER’,
policy_name=>’AUDIT_BILLING_INVENTORY_MASTER’
)
;
Check the current policies and their status
SELECT * --object_schema,object_name,policy_name,policy_column,enabled,sel,ins,upd,del FROM dba_audit_policies ;
The above example turned on FGA for all columns in the table. FGA can also be used for a specific column as shown below:
EXEC DBMS_FGA.ADD_POLICY ( object_schema=>'BILLING', object_name=>'INVENTORY_MASTER', policy_name=>'AUDIT_BILLING_INVENTORY_MASTER', audit_column=>'bonus' statement_types=>'insert, update, delete' ) ;
The columns that are part of an FGA policy can be checked with:
SELECT * FROM dba_audit_policy_columns;
The information being collected by the FGA can be viewed with:
ALTER SESSION SET nls_date_format='dd-Mon-yyyy hh24:mi:ss'; select * FROM DBA_FGA_AUDIT_TRAIL order by timestamp ;
Cleaning up FGA
Please read until the end before running these commands:
Check if the DBMS_AUDIT_MGMT.INIT_CLEANUP has already been run on the database:
SET serveroutput ON SIZE 999999 BEGIN IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD) THEN DBMS_output.put_line('True'); ELSE DBMS_output.put_line('False'); END IF; END; /
If the response is “FALSE”, run the DBMS_AUDIT_MGMT.INIT_CLEANUP as follows:
In this example, the cleanup is being set to run every 1 hour
BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, default_cleanup_interval => 1 /* hours */); END; /
If you get an error such as:
Error starting at line : 7 in command - BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, default_cleanup_interval => 24 /* hours */); END; Error report - ORA-46263: The audit trail is already initialized for cleanup ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61 ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 757 ORA-06512: at line 2
Uninitialize the cleanup with:
BEGIN DBMS_AUDIT_MGMT.DEINIT_CLEANUP( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD); END; /
Followed by the above command to initialize the cleanup.
I spent some time looking into DBMS_SCHEDULER, DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP and DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD etc. but it seemed overly complicated for what the goal was. The same can be accomplished with a cron script:
DELETE FROM SYS.fga_log$ WHERE NTIMESTAMP# < to_date('21-SEP-20 18.42.00','dd-MON-yy hh24:mi:ss') ;
Remember to gather statistics and/or add indices to improve performance
exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5); exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'FGA_LOG$' , estimate_percent=>10, cascade=>TRUE, degree=>5);