Thursday, April 9, 2015

How To Set the AUDIT_SYSLOG_LEVEL Paramete


1. Edit /etc/syslog.conf (upto RHEL5) or /etc/rsyslog.conf (RHEL6 onwards) to including following lines 

(Must put lines before line "*.info ...", otherwise captured to /var/log/messages, rather than /var/log/oracle-audit)

# Classify Oracle audit log into local1.warning
local1.warning    /var/log/oracle-audit.log

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

2. Restart syslogd  or rsyslogd service

[root@vmxdb01 ~]# service syslogd restart <-- font="" rhel5="">
[root@vmxdb01 ~]# systemctl restart rsyslog.service <-- font="" rhel7="">

3. Modify Oracle parameter audit_syslog_level & audit_trail

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/cdborcl/
                                                 adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576

SQL> alter system set audit_trail=OS scope=spfile;

System altered.

SQL> alter system set audit_syslog_level="local1.warning" scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  2924160 bytes
Variable Size             855638400 bytes
Database Buffers          469762048 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/cdborcl/
                                                 adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string      LOCAL1.WARNING
audit_trail                          string      OS
unified_audit_sga_queue_size         integer     1048576

4. Verify Oracle Audit Log generated

[root@vmxdb01 log]# tail -f /var/log/oracle-audit.log
Apr  9 22:18:27 vmxdb01 journal: Oracle Audit[5190]: LENGTH: "274" SESSIONID:[6] "190024" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[10] "C##DONGHUA" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[4] "1045" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834"
Apr  9 22:18:45 vmxdb01 journal: Oracle Audit[5196]: LENGTH: "283" SESSIONID:[6] "200019" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "SYSTEM" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834" PRIV$USED:[1] "5"
Apr  9 22:18:59 vmxdb01 journal: Oracle Audit[5196]: LENGTH: "227" SESSIONID:[6] "200019" ENTRYID:[1] "1" USERID:[6] "SYSTEM" ACTION:[3] "101" RETURNCODE:[1] "0" LOGOFF$PREAD:[1] "4" LOGOFF$LREAD:[4] "3013" LOGOFF$LWRITE:[2] "20" LOGOFF$DEAD:[1] "0" DBID:[10] "2860248834" SESSIONCPU:[2] "13"
Apr  9 22:18:59 vmxdb01 journal: Oracle Audit[5205]: LENGTH: "288" SESSIONID:[6] "200020" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[10] "C##DONGHUA" USERHOST:[20] "vmxdb01.dbaglobe.com" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "2860248834" PRIV$USED:[1] "5"


More information, refer to Oracle support article: How To Set the AUDIT_SYSLOG_LEVEL Parameter? (Doc ID 553225.1)