Send Oracle audit logs to syslog

The main part of auditing is to see when and if people have been interfering with your system, and you want to be sure that the logs themselves haven’t been tampered with to cover someone’s tracks

Assuming you have clear role separating and the DBA doesn’t have root privs (ahem) then sending the audit logs to syslog stops the DBA from deleting them after they wish to claim total ignorance as to how the customers table was truncated

Firstly, change the syslog.conf file to send local0 messages to the Oracle audit trail. You will need either sudo privs or temporary root to do this

vi /etc/syslog.conf

Remove local0.none from this line if it exists. It didn’t in my config so added here for clearness:

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

And add these lines below

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

Create the new log file for Oracle to send audit logs to:

mkdir /var/log/oracle
touch /var/log/oracle/audit.log

Restart syslogd so that it picks up the new config

/etc/init.d/syslog restart

Make sure the new audit file will be rotated. This script will rotate the log weekly, keep logs for 4 weeks and compress them so as to minimise space usage. Copytruncate is used as Oracle may try to ‘hold’ it’s audit file stopping it being deleted.

vi /etc/logrotate.d/oracle.audit

 

# /etc/logrotate.d/oracle.audit
#
# Oracle audit log file rotation config.

/var/log/oracle/audit.log {
rotate 4
weekly
compress
copytruncate
delaycompress
}
# EOF

Login to the database the change the DB audit parameters and then bounce. audit_trail is a static parameter.

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> shutdown immediate;
SQL> startup;

Check everything is logging correctly. For RAC databases you will need to repeat these steps for all nodes.

ASM can also be set to log to the same syslog file. Follow the same steps as above, but there is no audit_trail parameter on ASM as it logs to the OS by default.

Leave a Reply