If you have created a new Oracle 11g database using DBCA and opted to use by default 11g’s enhanced security settings or, at least, the audit setting, then you risk the unlimited growth of the SYSAUS tablespace that hosts the audit trail table SYS.AUD$. I realized that while reviewing the slides of my presentation on 11g’s new features, a few of which covered security enhancements.

During my presentation at the TOUG meeting later that day, I mentioned that concern, and Mohamed El-Shafie from Oracle quickly noticed that there is no auto-purge. I promised to have another look at the maintenance tasks in 11g to confirm that, and indeed, the audit trail is not purged automatically when auditing is enabled by default.

Here is a quick remedy — scheduling an audit trail maintenance job.

First create a PL/SQL procedure that will accept a number of days to keep. It rounds down to the beginning of the day. I like to dump a few diagnostic messages to alert.log when my maintenance procedures are running, so I included that here as well. (There was a typo in purge data calculation — thanks to Nial for catching it.

create or replace procedure purge_audit_trail (days in number) as
  purge_date date;
begin
  purge_date := trunc(sysdate-days);
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' started');
  delete from aud$ where ntimestamp# < purge_date;
  commit;
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' has completed');
end;
/

Then let’s schedule a new maintenance job using Job Scheduler, which appeared first in 10g. Oracle 11g includes the default maintenance windows group, MAINTENANCE_WINDOW_GROUP, and we will use that to run the purge.

BEGIN
  sys.dbms_scheduler.create_job(
    job_name => 'AUDIT_PURGE',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin purge_audit_trail(31); end;',
    schedule_name => 'MAINTENANCE_WINDOW_GROUP',
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Audit Trail Purge',
    auto_drop => FALSE,
    enabled => TRUE);
END;
/

During the job run you will see the following in the alert.log:

Tue Jul 01 15:18:02 2008
AUDIT: Purging Audit Trail until 30-JUN-08 started
AUDIT: Purging Audit Trail until 30-JUN-08 has completed

The good news, in a nutshell — Oracle 11g includes default audit settings that should be enabled if you have even a tiny thought about security in your environment. But don’t stop there. Make sure that you implement some purging strategy to avoid unlimited growth of the audit trail.

If you want to go further, and/or if you require the retention of years of audit information, consider exporting the data or moving it to your enterprise auditing repository. This will avoid a significant burden on the SYSAUX tablespace.

You can follow any responses to this post through the feed. You can leave a response, or trackback from your own site.