How to configure SYSLOG auditing in 11gr2

How to configure SYSLOG auditing in 11gr2

Step:

1. LOGON TO DB WITH sysdba user and set the following parameters

alter system set audit_trail=OS scope=spfile;

2. create pfile from spfile

create pfile from spfile;

3. shutdown database

shutdown immediate;

4. add the following lines in the pfile.

AUDIT_SYSLOG_LEVEL=local1.warning

see more info about audit_syslog_level parameter here

5. logon to the computer that contain /etc/syslog.conf file with superuser (root)

6. add the audit file destination in syslog configuration file (syslog.conf)

for eg: local1.warning /var/log/audit.log

7. restart syslog logger

$/etc/rc.d/init.d/syslog restart

8. conn to database with sysdba user

conn / as sysdba

9. create spfile from pfile;

create spfile from pfile;

10.  startup database

startup

Benefits of enabling syslog audit

1. normal database sys audit files (.aud) can be edited by root user or any one who has access to that files., to provide more security to OS .aud file we should enabled the syslog audit.

read more here

SQL> conn / as sysdba
Connected.
SQL> alter system set audit_trail=OS scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

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

SQL> --adding the audit_syslog_level parameter in pfile
SQL> host vi /db/product/11.2.0/dbhome_1/dbs/initORAMFE.ora

SQL> --logon with root user and add the line in syslog.conf file
SQL> host su root
Password:
[root@recovery bin]# vi /etc/syslog.conf

[root@recovery bin]# /etc/rc.d/init.d/syslog restart
Shutting down kernel logger:                               [  OK  ]
Shutting down system logger:                               [  OK  ]
Starting system logger:                                    [  OK  ]
Starting kernel logger:                                    [  OK  ]
[root@recovery bin]# exit
exit

SQL> --now create new spfile with edited pfile
SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size                  1337548 bytes
Variable Size             939525940 bytes
Database Buffers         1090519040 bytes
Redo Buffers               10858496 bytes
Database mounted.
Database opened.

SQL> host tail -10 /var/log/audit.log
tail: cannot open `/var/log/audit.log' for reading: Permission denied

SQL> host su root
Password:

[root@recovery bin]# tail -5 /var/log/audit.log
Sep 19 00:46:18 recovery Oracle Audit[13501]: LENGTH : '148' ACTION :[7] 'CONNEC                                                                             T' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT                                                                              TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0] ''
Sep 19 00:46:18 recovery Oracle Audit[13501]: LENGTH : '424' ACTION :[281] 'SELE                                                                             CT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA,   SUM(V                                                                             ALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA    UNION                                                                              ALL    SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE,    DECODE (null,'', 'bytes','                                                                             ') units_col_plus_show_sga FROM V$SGA' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYS                                                                             DBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0                                                                             ] ''
Sep 19 00:48:01 recovery Oracle Audit[13501]: LENGTH : '175' ACTION :[22] 'ALTER                                                                              DATABASE   MOUNT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6]                                                                              'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '3012735072'
Sep 19 00:48:01 recovery Oracle Audit[13572]: LENGTH : '159' ACTION :[7] 'CONNEC                                                                             T' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT                                                                              TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '3012735072'
Sep 19 00:48:39 recovery Oracle Audit[13572]: LENGTH : '172' ACTION :[19] 'ALTER                                                                              DATABASE OPEN' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'o                                                                             racle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '3012735072'
[root@recovery bin]#