Tuesday, February 22, 2011

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:
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

11 comments:

  1. Thanks for the info, I actually open an SR to oracle asking why in ASM adump generate a lot of audit logs even the parameter set to FALSE. Do you think this is a bugs or purposely ?

    ReplyDelete
  2. What level of the Audit Sysy should you set to capture user activity and logons.....? I hv tried a few..

    ReplyDelete
  3. @Anonymous 1: well, there's a mandatory auditing that you can't turn off even when you set your auditing parameters to false. The database will always record three important things: database startup, shutdown and users authenticated with the SYSDBA or SYSOPER roles. In the case of ASM, that would be the SYSASM role. It has to dump these info in OS files to allow one to determine if an admin has disabled auditing and is now restarting the database.

    ReplyDelete
  4. @Anonymous 2: the best answer I can give you for this is to grab a copy of David C. Knox's book "Effective Oracle Database 10g Security by Design" and read chapter 8 "Effective Auditing for Accountability" (ISBN: 0-07-223130-0). In short, if you want to audit standard users connecting to your database, you can create a trigger which fires at logon which uses SYS_CONTEXT to get the user's name, IP address, etc. To capture user activity, then you'll need to check at fine grained auditing. Have fun!

    ReplyDelete
  5. This is an interesting Metalink Doc ID about auditing:

    Audit SYS User Operations (Doc ID 174340.1)

    ReplyDelete
  6. @Anonymous 1: you might want to check Doc ID 308066.1 - AUDIT_SYS_OPERATIONS Set To FALSE Yet Audit Files Are Generated.

    ReplyDelete
  7. thx!! that's all i'am looking for in one place

    ReplyDelete
  8. find -atime 15
    will only give you files 14-15 days old on an HP-UX.
    For older files the value for "-atime" should be "+15",
    but maybe Linux is cleverer.

    ReplyDelete
  9. Thanks for sharing. Small Typo: alter system set audit_syslog_level='local0.info' scope=spfile sid='*';
    l of level was missing
    :-)
    Kind regrads
    Marco

    ReplyDelete
    Replies
    1. Hey Marco, glad I could help. And thanks for catching and reporting the typo. I'll fix this in a sec.

      Cheers!

      Delete