Kevin Kempf's Blog

June 3, 2011

11i RDBMS Auditing

Filed under: 11g — kkempf @ 12:50 pm

Everyone loves auditors

Everywhere I’ve worked with Oracle, some 3rd party has come around every year to ensure some minimalistic level of compliance with industry standards in areas such as security, backups, disaster recovery and best practices.  While taken at different levels of importance depending upon industry and company type, one “mandatory” fix which annoyed me was to set my 11g RDBMS audit_trail to OS.   Effectively what this does, is take hundreds of thousands of events per year, and write a text file about each of them to $ORACLE_HOME/rdbms/audit (or whatever you have your audit_file_dest set to).   The logic, this 3rd party said, was that in this way, the DBA could not be the sole owner (or manipulator) of audit-able events.   I did this for a year, and ended up with about a million files in this directory, which then became effectively useless because you can’t hardly search or organize a million files in one Linux directory.   I know you can use grep or some other tool to find what you’re looking for, but it’s still rather unmanageable.  Given that we’re a small shop, where the line between sysadmin and dba is often blurry, I made the command decision to set the parameter back to DB so that if something ever did happen, I could actually find the event in sys.aud$ rather than hunt through a million files for the data.  But this brought up some bigger points, regarding RDBMS auditing and 11i.

  • Some files will always get written to audit_file_dest, regardless of your AUDIT_TRAIL parameter.  These include database startups, shutdowns, and sysdba/sysoper accesses.  See note 308066.1; it was new information to me.
  • When AUDIT_TRAIL is set to DB (or DB Extended), the sys.aud$ table (probably in your SYSAUX tablespace) grows without bounds.
  • There is not existing method for managing these files or this table

Enter Cron and EM

My solution was simple, but easy to manage.   For the files written to audit_file_dest, add a cron job checking every day for files over 31 days, and deleting them.  (Obviously, you need to consider your retention requirements)

  • 15 7 * * * /usr/bin/find /u01/oracle/proddb/11.1.0/rdbms/audit -mtime +31 -exec rm -rf {} \;

To keep the database table sys.aud$ from growing forever, add an EM job which deletes records after a certain age (1 year in my case)

  • delete from sys.aud$ where ntimestamp# < sysdate – 365;

Blog at