How to Enable SYS User Auditing in Oracle Database 11g

Enable Auditing for SYSDBA User

This article is basic of enabling auditing for SYS administrative user and Auditing ALTER SYSTEM commands How to enable AUDIT for sys administrative user.

Summery:

1. logon to database with sys user as sysdba
2. Set AUDIT_SYS_OPERATIONS parameter
3. Set AUDIT_TRAIL parameter
4. Set / Specify AUDIT_FILE_DEST Path
5. Restart Database to implement changing 
6. Important Note

Steps:

1. logon to database with sys user as sysdba

2. Set AUDIT_SYS_OPERATIONS parameter to TRUE default value of this           parameter is FALSE

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME                                 TYPE        VALUE

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

audit_sys_operations          boolean     FALSE

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

System altered.

This parameter is static means we required to restart database to take effect of this parameter and after enable parameter SYS and user auditing records will be show in database table or view like SYS.AUD$ etc.
But don’t restart database on this step we will restart at the end after set all required parameters.

3.  Set AUDIT_TRAIL parameter as per desired value. default value of this       

     parameter is none

SQL> show parameter AUDIT_TRAIL

NAME                                 TYPE        VALUE

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

audit_trail                             string      NONE

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

System altered.

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

default value of this parameter $ORACLE_BASE/ORACLE_SID/adump

IF AUDIT_FILE_DEST parameter does not set or not point to valid location then oracle will create audit file in $ORACLE_HOME/rdbms/audit directory.

5. Restart database to Implement Changing 

 SQL> shu immediate

 Database closed.

 Database dismounted.

 ORACLE instance shut down.

 SQL> startup

 ORACLE instance started.

 Total System Global Area 1653518336 bytes

 Fixed Size                  2253784 bytes

 Variable Size             989858856 bytes

 Database Buffers          654311424 bytes

 Redo Buffers                7094272 bytes

 Database mounted.

 Database opened.

Note:

1. Make sure oracle unified auditing is disable
2. if unified auditing is enable then all audit data/record will not be showing at database table & view level like in: SYS.AUD$, SYS.DBA_COMMON_AUDIT_TRAIL, etc
3. To show record at database level please disable unified auditing and their policies
4. Please check other Post how to enable and disable Unified auditing

Keep Smile 🙂