There are several reason to enable database audit and at least as much reason to not enable database audit. For today I will focus on what can be done after the audit has enabled and how to get rid of the audit data quite easily. It is nothing new nor is it rocket engineering. 
Ok, removing data is mostly a quite easy task, sometimes to easy…
Rather than deleting or truncating the data, I would like to user the procedures provided by DBMS_AUDIT_MGMT. This post is kind of a sequel to my post earlier this february about Manage Audit Trails with DBMS_AUDIT_MGMT
Enable Audit
Ok, first of all we have to set the corresponding init.ora parameter and enable the database audit. In my case I’ll also enable audit of all sys operation. This is not necessary when you plan to use standard audit. I just did it because I restarted the database anyway. ok lame excuse, I will come back again later on.
Set the init.ora parameter:
ALTER system SET audit_sys_operations=TRUE scope=spfile;
ALTER system SET audit_trail=db_extended scope=spfile;
startup force;
SHOW parameter audit
Initialize the audit infrastructure. In this example I’ve initialized the standard audit and set a default interval of 10 days.
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
DEFAULT_CLEANUP_INTERVAL => 240 /*hours*/
);
END;
/
commit;
Create a dedicated tablespace for the audit data and change the location of the audit trail. If this is not done AUD$ will remain in the SYSAUX tablespace.
CREATE tablespace audit_data datafile '/u01/oradata/TDB03/audit01TDB03.dbf'
SIZE 100M autoextend ON NEXT 50M maxsize 5G;
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA'
);
END;
/
commit;
Review the audit settings
col PARAMETER_NAME FOR a30
col PARAMETER_VALUE FOR a15
col AUDIT_TRAIL FOR a20
SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE audit_trail = 'STANDARD AUDIT TRAIL';
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- --------------------
DEFAULT CLEAN UP INTERVAL 12 STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE AUDIT_DATA STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
col segment_name FOR a10
SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$';
OWNER SEGMENT_NA TABLESPACE_NAME
------------------------------ ---------- ------------------------------
SYS AUD$ AUDIT_DATA
Ok, the audit infrastructure has been setup and configured. Now lets enable audit for certain system privilege and objects. The first part of the following audit statements have been taken from $ORACLE_HOME/rdbms/admin/secconf.sql. This script is used in 11g to enable audit during database creation.
audit ALTER any TABLE BY access;
audit CREATE any TABLE BY access;
audit DROP any TABLE BY access;
audit CREATE any PROCEDURE BY access;
audit DROP any PROCEDURE BY access;
audit ALTER any PROCEDURE BY access;
audit GRANT any privilege BY access;
audit GRANT any object privilege BY access;
audit GRANT any ROLE BY access;
audit audit system BY access;
audit CREATE external job BY access;
audit CREATE any job BY access;
audit CREATE any library BY access;
audit CREATE public DATABASE link BY access;
audit exempt access policy BY access;
audit ALTER USER BY access;
audit CREATE USER BY access;
audit ROLE BY access;
audit CREATE SESSION BY access;
audit DROP USER BY access;
audit ALTER DATABASE BY access;
audit ALTER system BY access;
audit ALTER profile BY access;
audit DROP profile BY access;
audit DATABASE link BY access;
audit system audit BY access;
audit profile BY access;
audit public synonym BY access;
audit system GRANT BY access;
audit DELETE ON sys.aud$;
audit ALTER ON DEFAULT;
audit GRANT ON DEFAULT;
There are a few data dictionary views to review which object and system privilege has audit enabled.
SELECT * FROM DBA_STMT_AUDIT_OPTS;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
---------- ---------- ---------------------------- ---------- ----------
ALTER SYSTEM BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
PUBLIC SYNONYM BY ACCESS BY ACCESS
DATABASE LINK BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
PROFILE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
GRANT TABLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
SYSTEM GRANT BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
SELECT * FROM DBA_PRIV_AUDIT_OPTS;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- ---------- ---------------------------- ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
SELECT * FROM DBA_OBJ_AUDIT_OPTS;
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
----- -------------------- ----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
SYS AUD$ TABLE -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
SYS VERIFY_FUNCTION PROCEDURE -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
SYS VERIFY_FUNCTION_11G PROCEDURE -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
SELECT * FROM ALL_DEF_AUDIT_OPTS;
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
Audit trail housekeeping
So far we’ve spend quite some time on setting up the audit. Let’s come to the actual part of the post, the housekeeping. The plan is to have regular database job, which purge the audit data as required. For this I defined the following steps:
- Create a report of your audit trail (optional)
- Export your audit trail information for longterm archiving (optional)
- Set the archive timestamp as soon as audit data has been archived
- Purge archived audit data
I the current example I’ll skip the first to steps.
Create daily archive timestamp job. This job will be a regular dbms_scheduler job.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-10); END;',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
enabled => TRUE,
comments => 'Create an archive timestamp'
);
END;
/
Create daily purge job
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
commit;
The defined AUDIT JOB’s are then visible in DBA_AUDIT_MGMT_CLEANUP_JOBS.
col JOB_NAME FOR a30
col JOB_FREQUENCY FOR a40
SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
JOB_NAME JOB_STAT AUDIT_TRAIL JOB_FREQUENCY
---------------------- -------- -------------------- ------------------------
DAILY_AUDIT_PURGE_JOB ENABLED STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24
As we can see in dba_scheduler_jobs, DBMS_SCHEDULER.CREATE_JOB is just creating a other scheduler job.
col next_run_date FOR a50
SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';
JOB_NAME NEXT_RUN_DATE STATE ENABL
------------------------------ -------------------------------------------------- --------------- -----
DAILY_AUDIT_ARCHIVE_TIMESTAMP 31-MAY-11 01.46.58.000000 PM +02:00 SCHEDULED TRUE
DAILY_AUDIT_PURGE_JOB 31-MAY-11 01.49.34.900000 PM EUROPE/VIENNA SCHEDULED TRUE
Conclusion
For new Oracle 11g database auditing is enabled by default. Unfortunately Oracle does not setup a housekeeping. To make sure, that you do not end up with a full AUDIT_TRAIL you have to either switch off auditing or define some housekeeping jobs. With DBMS_AUDIT_MGMT this is a pretty easy job. It is that easy, that it is worth to think about having some kind of rolling audit window. Define a short retention time to save disk space, but long enough to have access to the audit information of the last hours or days. You may then be able to answer questions like “Did anybody change something?”, “I can not access XY but I could yesterday…”. When enabling audit for sys operation you will also be able to review what user SYS did all day long. Depending on the amount of audit data it will be quite a task to create nice audit reports.
