Auditing. Nice, but how to get rid of those audit records…

Nov 27, 2007

I was very surprised today. I was working on a system (prototype) that has the intention to enable auditing on several databases and to collect the results in a large table in a central database. The idea would be to use database links from the central database to the monitored databases in order to read out the aud$ table and delete the entries after they are copied to the local database. Now my testcase showed a really strange case:

select * from aud$ where sessionid = … and entryid=… and scn=…;

1 row selected

delete from aud$ where sessionid = … and entryid=… and scn=…; (same values)

0 rows deleted

Strange, isn’t it. However my initial search on Metalink gave me the answer immediately, in Note 388169.1: since 10.2 a security enhancement has been made which states that all DML on the AUD$ table should be audited as well, and that as a consequence only user SYS can succesfully do DML on the AUD$ table.

For security reasons I can really appreciate this, but for database management it is not so easy. This means that SYS access is required to clean up the audit trail. So if you had planned not to use SYS anymore and to try doing all by a ‘normal’ dba like system, you are stuck. Note that on a recent poll question on DBA-Village, 40% of the respondents said that they use user SYSTEM or a separate dba account for dba maintenance tasks.