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
23 Comments. Leave new
I guess this (and Audit Vault) is what was behind the introduction of the DBMS_AUDIT_MGMT package …
https://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm
Actually, this is probably a better link
https://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$?
@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
@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.
Any info about oracle application auditing – Audit Trail growth..whats the strategy to purge the audit data?
Thanks,
@Andy: There is no silver-bullet I’m afraid. Every application has its own audit requirements, retention policies and data access patterns.
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.
@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.
What can you say regarding making SYS.AUD$ table as partitioned table ?
@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.
[…] Here is an interesting article on the net https://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/ […]
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 […]
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