Manage Oracle 11gR2 ASM and RDBMS audit logs on Linux.

If you don't change anything after a new 11gR2 ASM and RDBMS installation, your system's local file systems will slowly fill up with several thousands of audit log files. To fix this problem, you can either:

1. Modify the system's crontab to clean the audit logs directory periodically
2. Configure both the ASM and the RDBMS audit parameters to use syslog.

The crontab solution is easy, but I prefer the syslog solution because:


a) it's the Oracle recommended best practices.
b) it's easier to prove to an auditor that the audit logs have not been tempered with. Why? Because if you use basic seperation of duty, then the Oracle DBA does not have the root password (or sudo ALL) on the database servers. Plus if you send your syslog output to a central syslog server, chances are the DBAs don't have access to this machine either.

So let's implement the crontab solution and then the syslog one. I assume that the ASM owner is the grid user while the RDBMS owner is the oracle user.

1. The crontab solution.

Let's do the ASM audit logs first and then the RDBMS ones second.

Our first task is to find out where the audit log files are created? To do this, switch to the grid user and connect to the local ASM instance.

sudo su - grid
rlwrap sqlplus '/ as sysasm'
SQL> show parameter audit_file_dest;

NAME     TYPE     VALUE
------------------------------------ --------------- ---------------------------------------------------------------------------
audit_file_dest     string     /u01/app/11.2.0.2/grid/rdbms/audit

Ok, so now we know that's where they are. It's in th e $CRS_HOME/rdbms/audit directory. Let's see how much files the audit_file_dest directory contains?

ls -1 /u01/app/11.2.0.2/grid/rdbms/audit/*.aud | wc -l

21524

That's 21 524 files! Let's take care of them by installing a single line in the grid user's crontab.

crontab -e 

Place this line that will simply delete the audit files that are older then 15 days.

# Clear 15 days old ASM audit log files each monday at 1800 hours.
00 18 00 00 1 /usr/bin/find $CRS_HOME/rdbms/audit -iname "*\.aud" -daystart -atime 15 -exec rm {} \;

That's it for the ASM audit logs. Of course, if you running Oracle RAC, then make sure to update every node's grid crontab!

Now let's do the RDBMS audit files. Same here, we first need to check where the files are piling up?

sudo su - oracle
rlwrap sqlplus '/ as sysdba'
SQL> show parameter audit_file_dest;

NAME     TYPE     VALUE
------------------------------------ --------------- --------------------------------------------------------------------------
audit_file_dest     string     /u01/app/oracle/admin/racdb/adump

So the files go into $ORACLE_BASE/admin/DB_NAME/adump. Let's clear it then.

crontab -e

# Clear 15 days old ASM audit log files each monday at 1800 hours.

00 18 00 00 1 /usr/bin/find $ORACLE_BASE/admin/racdb/adump -iname "*\.aud" -daystart -atime 15 -exec rm {} \;

Why do I keep 15 days of audit files? Well, you might need them, so better check with your site's security policies to know how long you should keep them?

You might want to delete all the files from both the ASM and RDBMS audit_file_dest directory in one swift stroke. Do do this, easy, just...

sudo su - grid

rm $CRS_HOME/rdbms/audit/*.aud

exit

sudo su - oracle

rm $ORACLE_BASE/admin/racdb/adump/*.aud

Again, make sure to update all the nodes in your Oracle RAC cluster.

2. The syslog solution.

We will change all of our databases audit trail to SYSLOG audit trails as it's the Oracle recommended best practices. Here's a few links on the topic:

• Oracle Database Security Guide 11g Release 2 (11.2) Chapter 9 Verifying Security Access with Auditing - Configuring Syslog Auditing.
• Oracle Database Reference 11g Release 2 (11.2) - AUDIT_SYSLOG_LEVEL.
• Doc ID 553225.1 - How To Set the AUDIT_SYSLOG _LEVEL Parameter?.
• Doc ID 756708.1 - How To Distinguish The Output Of 2 Or More Databases In The SYSLOG Audit Output.

Start by changing the syslog.conf(5) file to send local0 messages to the Oracle audit trail. You can of course use your own syslog facility for this. Note that this is NOT a complete syslog.conf file. I've listed only the lines which have the local0 key word.

rcsdiff /etc/syslog.conf

sudo co -l /etc/syslog.conf

sudo vi /etc/syslog.conf

<syslog.conf>

# Log anything (except mail) of level info or higher.

# Don't log private authentication messages!

*.info;mail.none;authpriv.none;cron.none;local2.none;local0.none /var/log/messages

# Send Oracle audit logs to /var/log/oracle/audit.log.

local0.* /var/log/oracle/audit.log

# EOF

</syslog.conf>

rcsdiff /etc/syslog.conf

sudo ci -u /etc/syslog.conf

Now create the new log file.

sudo mkdir /var/log/oracle

sudo touch /var/log/oracle/audit.log

Restart syslogd(8) so that it knows about the new configuration.

sudo /etc/init.d/syslog restart

Make sure the new audit file will be rotated.

sudo vi /etc/logrotate.d/oracle.audit 

<oracle.audit>

# /etc/logrotate.d/oracle.audit

#

# $Id$

#

# Oracle audit log file rotation config.

#

# David Robillard, February 1st, 2011.

/var/log/oracle/audit.log {

rotate 7

compress

copytruncate

delaycompress

notifempty

create 0640 oracle oinstall

}

# EOF

</oracle.audit>

Don't forget to update the /etc/syslog.conf file and the /etc/logrotate.d/oracle.audit file on all your cluster nodes.

Now switch to the ASM owner and connect to the ASM instance to change the audit parameters.

sudo su - grid

rlwrap sqlplus '/ as asmadmin'

SQL> alter system set audit_syslog_level='local0.info' scope=spfile sid='*';

SQL> exit

Then do the same for your RDBMS owner. Note that we can't specify scope=both as Oracle will raise an error. So we update only the spfile and bounce the instance. You'll also notice that the RDBMS has an extra parameter that the ASM instances don't have: audit_trail.

sudo su - oracle

export ORACLE_SID=racdb1

rlwrap sqlplus '/ as sysdba'

SQL> show parameter audit_trail;

SQL> alter system set audit_trail='OS' scope=spfile sid='*';

SQL> alter system set audit_syslog_level='local0.info' scope=spfile sid='*';

SQL> exit

Since we need to bounce the ASM and RDBMS instances for the changes to take effect, then if you're running RAC, the easiest way to do this is to reboot the node. If you have multiple RAC databases on your cluster, change all of them before you reboot the node. 

sudo shutdown -r now

Don't forget to restart all your other RAC nodes.

In 11gR2 you can send more then one databases to the same audit log file because they all log their DBID so you can differentiate them. Here I send all the ASM and the RDBMS audit logs to the same file. But you can also create two files, one for ASM and the other for RDBMS.

HTH,

David