By using a standard audit, the resulting audit trails can be tampered with or deleted by database administrators or by an attacker who gained administrative privileges. This is a considerable security risk.
SYSLOG is a protocol (RFC5424) designed for transmitting event messages and alerts across an IP network. The messages are generated, for example, by an application (ftp, cron, or ssh), and a syslog daemon catches them and integrates them using a device or another remote daemon. In this recipe we will integrate the Oracle audit trails with rsyslog.
- Integration with
syslogrequires the destination of audit trails to be placed externally. Change the audit trail toOSas follows:SQL> alter system set audit_trail=OS scope=spfile; System altered.
rsyslogis a more advanced variant ofsyslogand is the default in Red Hat 6. The configuration file is/etc/rsyslog.conf. The format is the same as forsyslog.conf. Asroot, add the device for logging in/etc/rsyslog(if you havesyslogthen add in/etc/syslog.conf) as follows:#Oracle audit syslog local2.info /var/log/oracle_audit.log Save the /etc/rsyslog.conf file and restart the rsyslog service as follows: [root@nodeorcl1 etc]# service rsyslog restart Shutting down system logger: [ OK ] Starting system logger: [ OK ] [root@nodeorcl1 etc]#
- In Oracle, set the
audit_syslog_levelparameter to the device namelocal2.infoas follows:SQL> alter system set audit_syslog_level='local2.info' scope=spfile; System altered.
- Configure it to audit all operations on
HR.Employeesas follows:SQL> audit all on hr.employees by access; - Bounce the database. The audit trail will be forwarded to
/var/opt/oracle_audit.log. Asrootopen this file with thetail -100fcommand. - In a separate terminal connect as
HRand issue a count against theemployeestable:SQL> select count(*) from employees; - In the terminal where you launched the tail command you should see the audit trails in
/var/opt/oracle_audit.logrecording:Sep 16 23:30:55 nodeorcl1 Oracle Audit[3382]: LENGTH: "249" SESSIONID:[7] "1250004" ENTRYID:[1] "2" STATEMENT:[2] "11" USERID:[2] "HR" USERHOST:[9] "nodeorcl1" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[2] "HR" OBJ$NAME:[9] "EMPLOYEES" OS$USERID:[6] "oracle" DBID:[10] "2310990645"
Every syslog message has a facility and a priority as attributes. As facilities we can have: kernel, user, mail, daemon, auth, syslog, lpr, news, uucp, cron, security, ftp, ntp, logaudit, logalert, clock, or local0-local7 (reserved for custom usage). We used local2 as the facility.
For priorities or severities we can have: merg, alert, crit, error, warning, notice, info, or debug. We used info severity. The configuration file /etc/rsyslog.conf instructs the syslog daemon how to log the events and what to do with the message per each facility and priority.
For both facilities and priorities you can use * use * (where *.* means use all facilities and severities) notation. For configuring rsyslog to send the log messages to a remote server you should check the following article http://www.thegeekstuff.com/2012/01/rsyslog-remote-logging/.