Are you aware of an existing DBA opening or consulting requirement in your organization? Enter your email for a chance to win one year's access to Safari Books.
Oracle 11g — Audit Enabled by Default, But What About Purging?
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 SYSTEM (thanks Eduardo Legatti) tablespace that hosts the audit trail table SYSAUXSYS.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 SYSTEM (thanks Eduardo Legatti) tablespace.SYSAUX
I guess this (and Audit Vault) is what was behind the introduction of the DBMS_AUDIT_MGMT package …
http://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm
Actually, this is probably a better link
http://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_mng_admin_tasks.htm#insertedID4
I know it’s AV documentation, but I think patches are available for various RDBMS versions.
Should “trunc(sysdate-1)” read “trunc(sysdate-days)”?
I’m liking that writing to the alert log idea, very neat.
@Doug:
Interesting package. Thanks for mentioning it Doug. Let me know if you find more details on whether it’s available without DV. Well, I might as well check it one day if I have time.
@Neil:
Good catch mate! Thanks for the correction. It’s fixed now.
It’s Patch number 6989148 for RDBMS 10.2.0.3. I knew I’d seen it somewhere - it was in the AV release notes.
There are a few new scripts in $ORACLE_HOME/rdbms/admin :-
catamgt.sql
dbmsamgt.sql
prvtamgt.plb
It also mentions that they will be included in 11.1.0.7 but I don’t know what the situation is with 10.2.0.4 (they don’t exist on my laptop 10.2.0.4 db and the patch doesn’t mention 10.2.0.4). I saw a reference to a new solution to moving AUD$ being supplied for 9.2 and up, so I think it will eventually be just a standard db package.
Hi:
I am not a DBA person but I have read somewhere about auto purge mechanism for audit data in Oracle 11g and its called Flashback data Archive, based on retention policy set, is that right?
-Rohit
Hi Alex,
Just a little correction … If I’m not wrong, the SYS.AUD$ table is hosted under the SYSTEM tablespace (not SYSAUX).
Cheers
@Eduardo: Hm… yes — don’t know why I wrote SYSAUX that time. We often move AUD$ to SYSAUX as part of database creation so that might what it has been on the database I happened to check. Fixed the post — thanks for correction.
@Rohit: Missed your question but in general terms — yes. But I don’t think I clarified anything for you with this — I’d rather hit the docs.
[...] implement something to purge the audit data after 90 days from some Oracle databases I found a nice blog post from Alex Gorbachev. The post provides a procedure for keeping X days of records in SYS.AUD$ and [...]
Hi,what about on older version 9i database.
I have delete previlege on AUD$, but I can’t delete using where timestamp#<xxxx. It gives me ORA-01031: However when I delete all records or using rownum < xx, it works fine. here is my statement
delete from SYS.AUD$ where timestamp# < trunc(sysdate,’MONTH’)
Error at line 1
ORA-01031: insufficient privileges
I tried using userid = ‘xxx’ in the where clause instead of timestamp#, they don’t work either.
any ideas?
@Praveen: from the top of my head, I couldn’t see why this would be an issue in 9i. I don’t have a sand-box with 9i database at the moment but I would think it should work just fine.
Can you select from AUD$?