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

Jul 3, 2008 / By Alex Gorbachev

Tags:

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.

23 Responses to “Oracle 11g — Audit Enabled by Default, But What About Purging?”

  • Doug Burns says:

    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

  • Doug Burns says:

    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.

  • Neil says:

    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.

  • Doug Burns says:

    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.

  • Rohit Singh says:

    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

  • Alex Gorbachev says:

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

  • Praveen says:

    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?

  • Alex Gorbachev says:

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

  • Mark says:

    @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

  • Alex Gorbachev says:

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

  • Andy says:

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

    Thanks,

  • Alex Gorbachev says:

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

  • Mehul says:

    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.

  • Alex Gorbachev says:

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

  • Uri says:

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

  • Alex Gorbachev says:

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

  • 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;
    /

  • [...] 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 [...]

  • Pablo Sinisgalli says:

    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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>