How to Enable auditing for SYSDBA (sys) user in 11gr2

Database security is one of the major and important task for DBA's.

Steps:

1. logon to database with sysdba user

2. set AUDIT_SYS_OPERATIONS parameter to TRUE    //this is mandatory

Note:- Default value of this parameter is FALSE

Note:- This parameter is static means we required to bounce DATABASE to take effect of this parameter value.

3. set AUDIT_TRAIL parameter as per desired value.

Note:- Default value of this parameter is “none”

4. set AUDIT_FILE_DEST = location path for audit records log file

Note:- Default value of this parameter %ORACLE_BASE%/ORACLE_SID/adump

IF AUDIT_FILE_DEST parameter doesn't set or not point to valid directory then oracle will create audit file in %ORACLE_HOME%/rdbms/audit directory.

5. restart database

6. Setting the size or age of the operating system audit files

http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm#CEGBDFCF

Example:[oracle@oracledb bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 17 21:46:36 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

System altered.

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

System altered.

SQL> alter system set audit_file_dest='/db/admin/oradb/adump/' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2042241024 bytes

Fixed Size                  1337548 bytes

Variable Size             973080372 bytes

Database Buffers         1056964608 bytes

Redo Buffers               10858496 bytes

Database mounted.

Database opened.

SQL> ! ls -lrt /db/admin/ oradb /adump/

total 32

-rw-rw---- 1 oracle oracle 2585 Jan 17 21:48 oramfe_ora_12487_1.aud

-rw-rw---- 1 oracle oracle  750 Jan 17 21:48 oramfe_ora_12500_2.aud

-rw-rw---- 1 oracle oracle 1451 Jan 17 21:50 oramfe_ora_12602_1.aud

-rw-rw---- 1 oracle oracle  980 Jan 17 21:51 oramfe_ora_12671_1.aud

SQL> host cat /db/admin/oradb/adump/oramfe_ora_12671_1.aud

Audit file /db/admin/oradb/adump/oramfe_ora_12671_1.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /db/product/11.2.0/dbhome_1/

System name:    Linux

Node name:      oracledb.nikhil.local

Release:        2.6.18-53.el5

Version:        #1 SMP Wed Oct 10 16:34:02 EDT 2016

Machine:        i686

Instance name: oradb

Redo thread mounted by this instance: 1

Oracle process number: 20

Unix process pid: 12671, image: oracle@oracledb.nikhil.local (TNS V1-V3)

Sat Jan 17 21:50:07 2016 +04:00

LENGTH : '160'

ACTION :[7] 'CONNECT'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[10] '3012735072'

Sat Jan 17 21:51:21 2016 +04:00

LENGTH : '173'

ACTION :[19] 'ALTER DATABASE OPEN'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[10] '3012735072