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]#

Enable auditing for SYSDBA (sys) user in 9ir2

SYS audit feature introduce in oracle 9ir2 

http://download.oracle.com/docs/cd/B10501_01/server.920/a96531/ch2_9ir2.htm#74452

How to enable SYS audit?

1. set the following three parameters

1.1 

audit_file_dest == (location of audit files (OS) based)

audit_sys_operations === true ( enable sys audit)

audit_trail = OS 

SQL> select * from v$version where rownum=1;

BANNER

----------------------------------------------------------------

Oracle9i Release 9.2.0.4.0 - Production

SQL> show parameter audit-

>

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      ?/rdbms/audit

audit_sys_operations                 boolean     FALSE

audit_trail                          string      NONE

transaction_auditing                 boolean     TRUE

SQL> host echo $ORACLE_HOME

/disk1/app/oracle/product/9.2.0

SQL>  alter system set audit_file_dest='/disk1/app/oracle/product/9.2.0/audit' s                                                                             cope=spfile;

System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

System altered.

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

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  202445884 bytes

Fixed Size                   451644 bytes

Variable Size             184549376 bytes

Database Buffers           16777216 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

SQL> host ls -lrt $ORACLE_HOME/audit

total 8

-rw-r-----  1 oracle dba 1103 Sep 17 19:44 ora_25676.aud

-rw-r-----  1 oracle dba  734 Sep 17 19:44 ora_25677.aud

SQL>  host cat $ORACLE_HOME/audit/ora_25676.aud

Audit file /disk1/app/oracle/product/9.2.0/audit/ora_25676.aud

Oracle9i Release 9.2.0.4.0 - Production

JServer Release 9.2.0.4.0 - Production

ORACLE_HOME = /disk1/app/oracle/product/9.2.0

System name:    Linux

Node name:      SPI-ISIS

Release:        2.6.9-78.ELsmp

Version:        #1 SMP Wed Jul 9 15:39:47 EDT 2008

Machine:        i686

Instance name: STERLIVE

Redo thread mounted by this instance: 0

Oracle process number: 14

Unix process pid: 25676, image: oracle@SPI-ISIS (TNS V1-V3)

Sat Sep 17 19:44:16 2011

ACTION : 'CONNECT'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL:

STATUS: 0

Sat Sep 17 19:44:16 2011

ACTION : 'SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHO                                                                             W_SGA,    SUM(VALUE), DECODE (null,'', 'bytes','')  FROM V$SGA    UNION ALL    S                                                                             ELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE,    DECODE (null,'', 'bytes','') FROM                                                                              V$SGA'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL:

STATUS: 0

Sat Sep 17 19:44:20 2011

ACTION : 'ALTER DATABASE   MOUNT'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL:

STATUS: 0

Audit Database

Type of Auditing
1.Standard Auditing
2.FGA (Fine Grained Auditing)
3.SYS ( sysdba or sysoper)


DBA_AUDIT_EXISTS
lists audit trail entries produced by AUDIT NOT EXISTS.

DBA_AUDIT_OBJECT
contains audit trail records for all objects in the system.

DBA_AUDIT_SESSION
lists all audit trail records concerning CONNECT and DISCONNECT.

DBA_AUDIT_STATEMENT
lists audit trail records concerning GRANT, REVOKE,AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database.

DBA_AUDIT_TRAIL or sys.aud$
lists all audit trail entries.

DBA_OBJ_AUDIT_OPTS
describes auditing options on all objects.

DBA_PRIV_AUDIT_OPTS
describes current system privileges being audited across the system and by user.

DBA_STMT_AUDIT_OPTS
describes current system auditing options across the system and by user.
Fine-Grained Auditing

DBA_FGA_AUDIT_TRAIL or sys.fga_log$

Audit Example.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> --add AUDIT_TRIAL=TRUE parameter in INIT.ora file
SQL> create spfile from pfile;

File created.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string TRUE
SQL>

SYS ( sysdba or sysoper) Auditing.
Set AUDIT_SYS_OPERATIONS = TRUE for every successful statement from SYS ( sysdba or sysoper) audited.
All audit records for SYS are written to the operating system file that contains the audit trail.
On Windows
audit records are written as events to the Event Viewer log file

On Solaris
AUDIT_FILE_DEST parameter is not specified, the default location is $ORACLE_HOME/rdbms/audit.
AUDIT_FILE_DEST is not supported in Windows
audit file name (unix) is named after the "session", like ora-20043.aud

1.Statement Auditing
DDL statements. As an example, AUDIT TABLE audits all CREATE and DROP TABLE statements. SQL> truncate table aud$;

Table truncated.

1.audit table by access;
BOTH successful or not successful statement
2.audit table by access whenever successful;
SUCCESSFUL statement only
3.audit table by access whenever not successful;
NOT SUCCESSFUL statement only
4.audit table by SESSION;
Oracle to write a single record for all SQL statements of the same type issued in the same session.
5.audit table by ACCESS;
Oracle to write one record for each access.
6.audit select any table by access;
all statements issued by users with the SELECT ANY TABLE privilege are audited.
7.audit [select any table / table ] by USER/SCHEMA;
specific user/schema audit.
8.audit session;
To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION
9.audit delete any table;
10.audit update any table;
11.audit select table, insert table, delete table, execute prodedure
by access whenever not successful;
12.audit session;
13.audit connect;
Note : there is no DIFFERENCE between CONNECT or SESSION auditing.

Disable Auditing
1.noaudit table;
2.noaudit alter table;
3.noaudit select any table;
4.noaudit session;
Note : don't use BY SESSION / BY ACCESS with noaudit command.
5.NOAUDIT ALL PRIVILEGES;
turns off all privilege audit options
6.NOAUDIT ALL;
turns off all statement audit options
Disable Standard Audit
set AUDIT_TRAIL=false


SQL> audit table by access;
Audit successed.

SQL> conn scott/tiger
Connected.
SQL> create table nolog ( no number);

Table created.

SQL> drop table nolog purge;

Table dropped.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT CREATE TABLE
SCOTT DROP TABLE

SQL> audit alter table by access;

SQL> alter table titi1 add ( name1 varchar2(20));

Table altered.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT CREATE TABLE
SCOTT DROP TABLE
SCOTT ALTER TABLE
SCOTT ALTER TABLE
DML statements. As an example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view.
SQL> audit select table by access;

Audit succeeded.

SQL> conn scott/tiger;
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
14

1 row selected.

SQL> select count(*) titi1;
select count(*) titi1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> column obj_name format a20
SQL> select username, action_name,obj_name from dba_audit_trail;
USERNAME ACTION_NAME OBJ_NAME
------------------------------ ---------------------------- --------------------
SCOTT SELECT EMP


Purging Audit Records from the Audit Trail
conn sys as sysdba
password :

Delete from SYS.AUD$;
delete all audit records

Deleting the Audit Trail Views
If auditing is DISABLE and no longer need then audit trail views.
1.conn SYS user
2.run CATNOAUD.SQL scripts

Trace Error Number.

SQL> select returncode from dba_audit_trail where rownum <= 1 and returncode <>
0;

RETURNCODE
----------
2004

1 row selected.

SQL> exec dbms_output.put_line ( sqlerrm(-2004));
ORA-02004: security violation

PL/SQL procedure successfully completed.

what are all the audit options enabled?
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS

Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.

SQL> select count(*) from STMT_AUDIT_OPTION_MAP;

COUNT(*)
----------
183