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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: