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);
