Audit users connecting as SYSDBA and SYSOPER - a workaround

How to audit SQL statements issued by SYS, including users connecting as SYSDBA and SYSOPER?

DBAs who want to audit the activity performed by SYS/SYSDBA/SYSOPER.

1. Only SYS/SYSDBA/SYSOPER actions are audited to address accountability. The actions performed by DBAs other than user SYS are not audited.

2. The new parameter AUDIT_SYS_OPERATIONS allows the audit of all statements issued by SYS/SYSDBA/SYSOPER in an OS audit trail file.

The SYS audit records must go to OS files since the user SYS can delete his actions from AUD$, whereas if the files are written to the OS, they can be secured from the Oracle DBA by root (root must have some means to transfer the files to a secure location). It is not possible to configure that these records go into the AUD$ table.

Details:

Without AUDIT_SYS_OPERATIONS init.ora parameter set
---------------------------------------------------
1. Startup database with AUDIT_SYS_OPERATIONS left to the default value FALSE.

SQL> col name format a20;
SQL> col VALUE format a40;
SQL> col ISDEFAULT format a10;

SQL> select name, value , isdefault
from v$parameter where name like 'audit%';
NAME               VALUE                ISDEFAULT
-------------------- ---------------   ----------
audit_sys_operations     FALSE         TRUE
audit_file_dest        /u01/app/oracle/admin/PROD/adump        FALSE
audit_syslog_level       TRUE
audit_trail              DB            FALSE
SQL> 

Note: Use “show parameter audit;” command to find audit location also.
2. The operations performed by SYS, SYSDBA and SYSOPER are not audited.

SQL> connect / as sysdba
Connected.
SQL> create table hr.emp1(en number,ename varchar2(15));
Table created.
SQL> insert into hr.emp1 values(101,'GOURANGA');
SQL> insert into hr.emp1 values(102,'MOHAPATRA');
SQL> insert into hr.emp1 values(103,'GAUTAM');
SQL> commit;


$ cat PROD_ora_11993124_20150801155724873718143795.aud
Audit file /u01/app/oracle/admin/PROD/adump/PROD_ora_11993124_20150801155724873718143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    AIX
Node name:      rac1
Release:        1
Version:        6
Machine:        00F7249F4C00
Instance name: PROD
Redo thread mounted by this instance: 1
Oracle process number: 148
Unix process pid: 11993124, image: oracle@DB3 (TNS V1-V3)

Sat Aug  1 15:57:24 2015 +05:30
LENGTH : '158'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '513090113'


With AUDIT_SYS_OPERATIONS init.ora parameter set
------------------------------------------------
1. Startup database with AUDIT_SYS_OPERATIONS set to TRUE.
AUDIT_FILE_DEST assumes its default value.

SQL> alter system set AUDIT_SYS_OPERATIONS=TRUE scope=spfile;
System altered.
(bounce the database)

Let us Check now :

SQL> show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
audit_file_dest                      string      /u01/app/oracle/admin/PROD/adump    audit_sys_operations            boolean     TRUE
audit_syslog_level                  string
audit_trail                          string      DB
SQL>

SQL> select name, value , isdefault from v$parameter where name like 'audit%';

NAME                     VALUE              ISDEFAULT
--------------------    ------------------   ----------
audit_sys_operations     TRUE                FALSE
audit_file_dest          /u01/app/oracle/admin/PROD/adump     FALSE
audit_syslog_level       TRUE
audit_trail              DB                  FALSE


(Note : It is not mandatory to keep audit_trail=db to audit super user)
(sample example from Oracle Doc):

NAME VALUE ISDEFAULT
------------------------------ ----------------- ---------
audit_sys_operations     TRUE             FALSE
audit_file_dest         ?/rdbms/audit     TRUE
audit_trail              NONE             TRUE

2. The operations performed by SYS, SYSDBA and SYSOPER are audited to the OS
audit trail in $ORACLE_HOME/rdbms/admin. The operations done by DBAs and regular user are not audited.
Note: Below two new users are created with 'SYSDBA' and 'SYSOPER' access.

SQL> select * from V$PWFILE_USERS;

USERNAME                           SYSDBA     SYSOPER     SYSAS
------------------------------     ---------  ----------  ----------
SYS                                TRUE          TRUE          FALSE
BKP_USER                           TRUE          FALSE         FALSE
ALERT                              FALSE         TRUE          FALSE

SQL>

Example 1: SYS connected
========= =============
SQL> connect / as sysdba
password:
Connected.
SQL> insert into hr.emp1 values(104,'SCOTT');
SQL> commit;

SQL> select * from hr.emp1

Now see the audit file:
$ cat PROD_ora_9896154_20150801165557925377143795.aud
…..
…...
Sat Aug  1 16:55:57 2015 +05:30
LENGTH : '158'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '513090113'
…...
…...
Sat Aug  1 16:56:03 2015 +05:30
LENGTH : '191'
ACTION :[39] 'insert into hr.emp1 values(104,'SCOTT')'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '513090113'
…..

The above examples showing all statements are audited now. As it is OS level then it will have less impact performance impact as compare to audit_trail DB. If your audit_trail is DB, then follow my below post, how to move audit tables from SYSTEM tablespaces to other tablespace.

Click here to see Moving Audit trail objects to different table-space to gain performance.

 Note : Keep a job to zip all audit files (*.aud) and move to some secure place and keep it as per your audit policy. Try to keep for 1 year .