Oracle 11g — Audit Enabled by Default, But What About Purging?

Posted in: Technical Track

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 SYSAUX (thanks Eduardo Legatti) 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 SYSTEM SYSAUX (thanks Eduardo Legatti) tablespace.

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

23 Comments. Leave new

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

Reply

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.

Reply

Should “trunc(sysdate-1)” read “trunc(sysdate-days)”?

I’m liking that writing to the alert log idea, very neat.

Reply
Alex Gorbachev
July 4, 2008 11:38 am

@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.

Reply

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.

Reply

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

Reply
Eduardo Legatti
May 25, 2009 1:24 pm

Hi Alex,

Just a little correction … If I’m not wrong, the SYS.AUD$ table is hosted under the SYSTEM tablespace (not SYSAUX).

Cheers

Reply
Alex Gorbachev
May 26, 2009 1:28 am

@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.

Reply
Purging Audit Data « ORAganism
May 30, 2009 7:22 am

[…] 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 […]

Reply

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?

Reply
Alex Gorbachev
June 9, 2009 10:48 pm

@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$?

Reply

@Alex , you wrote a while back about purging AUD$ data using slick pls/sql script(s) anyway did you find out if the dbms_audit_mgmt package is available without licensing the Vault ? otherwise by purging the AUD$ via homemade scripts, will this cleanup the OS xml files , I havent tested yet, but looking for insight from experienced folk.

Thanks

Reply

@Mark:
Oracle Database Vault option shouldn’t have anything to do with this package. At least, I don’t see it mentioned in the licensing guide.

Oracle Audit Vault is actually a separate product by itself and dbms_audit_mgmt package is part of it according to the documentation of Audit Vault itself.

Reply

Any info about oracle application auditing – Audit Trail growth..whats the strategy to purge the audit data?

Thanks,

Reply

@Andy: There is no silver-bullet I’m afraid. Every application has its own audit requirements, retention policies and data access patterns.

Reply

Auto purging Audit records is a bad decision. Audit should only be purged with human intervention. Audit records are very important information which needs to be reviewed carefully and then any action needs to be taken.
The better solution is to restrict the number of audits happening by disabling auditing on objects which are not important.

Reply

@Mehul: I do not agree. There should be an established policy for audit records retention and in many cases, this retention is not unlimited. How these policies should be setup is another question.

The point of the post is that even if you don’t intentionally enable auditing (thus, you might not have an explicit policy) there are some auditing records collected by default and this is already a limited set.

Reply

What can you say regarding making SYS.AUD$ table as partitioned table ?

Reply

@Uri: I know it works for number of people just fine. Though, it does pose a question on supportability by Oracle. Strictly speaking, it’s not supported.

I guess it’s the balance between how much overhead is caused by purging it vs danger of running unsupported configuration (though, Oracle Support would probably never notice).

If you collect way too much data and purging it produces huge overhead, I’d question Audit strategy but never say never.

Reply
Purge Audit Logs Oracle Data base 11g « Daily Raaga
March 15, 2012 5:23 am
Reply
Altemir Soares
August 7, 2012 12:50 pm

Hi,

If you’ve realized that so late, then your aud$ should be very big. So, it would be better to do a loop, performing some commits, while deleting the aud$ table. Looks like this :

create or replace procedure purge_audit_trail (days in number) as
purge_date DATE;
CURSOR cr_aud(pdate IN DATE) IS
SELECT ROWID rid
FROM aud$
where ntimestamp# < pdate;
begin
purge_date := trunc(SYSDATE-days);
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' started');
FOR st_aud IN cr_aud(purge_date) loop
DELETE FROM aud$ WHERE ROWID=st_aud.rid;
IF mod(cr_aud%rowcount, 1000) = 0 THEN
commit;
end if;
END LOOP;
commit;
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' has completed');
END;
/

Reply
Effet caché de la database 11G – Augmentation du volume des traces « EASYTEAM LE BLOG
December 13, 2012 9:29 am

[…] Si vous êtes en version 11.1 ,  créer votre propre tache et inspirez vous pour cela du très bon blog de nos amis de Pythian ici […]

Reply
Pablo Sinisgalli
August 2, 2013 2:06 pm

I remember an old metalink note Note:72460.1 saw : Moving AUD$ to another tablespace and adding triggers to AUD$
but you may have problems with logical standby that need the AUD$ in the SYSTEM tablespace and/or inside SYS schema… see : DBA_REGISTRY show Packages and Types as INVALID and DBMS_INTERNAL_LOGSTDBY has compile errors (Doc ID 337119.1)
DBA_REGISTRY show Packages and Types as INVALID and DBMS_INTERNAL_LOGSTDBY has compile errors (Doc ID 337119.1)

Regards.
Pablo

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *