Sometimes, the requirement may arise to audit all actions of the Oracle Database superuser sys. This feature was introduced already in the 9i version, together with the parameter AUDIT_SYS_OPERATIONS. If set to true, that leads to the generation of files in the directory, determined by the parameter AUDIT_FILE_DEST. These files will contain then a protocol of all the actions of sys. By default, the files in that directory – all with the suffix aud – contain only the protocol of all connections as sys, but not all the actions done after the connect. They look like this:

Audit file /u01/app/oracle/admin/orcl/adump/ora_1057.aud

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

System name:    Linux

Node name:      uhesse

Release:        2.6.9-55.ELsmp

Version:        #1 SMP Fri Apr 20 17:03:35 EDT 2007

Machine:        i686

Instance name: orcl

Redo thread mounted by this instance: 0 <none>

Oracle process number: 0

Unix process pid: 1057, image: oracle@uhesse

Tue Feb  2 17:43:12 2010

ACTION : 'CONNECT'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL: pts/6

STATUS: 0

But even if we set AUDIT_SYS_OPERATIONS=true, although the audit file will then contain all the actions of sys also, it belongs to the operating system user, used to install the database – usually the user oracle. And likely, the person who has the privilege to connect as sys also has the privilege to connect as oracle on the OS level. That makes the approach with AUDIT_SYS_OPERATIONS somewhat useless from a security viewpoint. That is why we introduced in version 10g (already) the possibility to have the audit file owned by root – which may make it harder to manipulate or delete it for the DBA person. This is how that works:

First, we have to set two parameters – the cooperation of someone able to logon as sysdba is required here…

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
 mp
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

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

System altered.

SQL> alter system set audit_syslog_level='LOCAL1.WARNING' scope=spfile;

System altered.

Then, we logon as root and modify the file /etc/syslog.conf:

-bash-3.00$ su -
Password:
[root@uhesse ~]# echo local1.warning /var/log/audit.log >> /etc/syslog.conf
[root@uhesse ~]# cat /etc/syslog.conf
# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.*                                                 /dev/console

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none                /var/log/messages

# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure

# Log all the mail messages in one place.
mail.*                                                  -/var/log/maillog

# Log cron stuff
cron.*                                                  /var/log/cron

# Everybody gets emergency messages
*.emerg                                                 *

# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler

# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log
local1.warning /var/log/audit.log

After having done that, we need to restart the syslog logger, also as root user:

[root@uhesse ~]# /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  ]

The last step is to restart the instance, in order to make the previous changes of the non-dynamic parameters active. If afterwards someone connects to the instance as sysdba, all his actions are recorded:

SQL> connect / as sysdba

Connected.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

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

HOST_NAME

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

VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT

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

LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO

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

 1 orcl

uhesse

10.2.0.1.0        02-FEB-10 OPEN         NO           1 STARTED

ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

On the OS level, the oracle user can not even see the content of the file, nor modify or delete it:

-bash-3.00$ ls -rtl /var/log/
total 1260
[...]
-rw-------  1 root root  34457 Feb  2 17:40 cron
-rw-------  1 root root  66395 Feb  2 17:40 boot.log
-rw-------  1 root root 377235 Feb  2 17:44 messages
-rw-------  1 root root   1410 Feb  2 17:44 audit.log

-bash-3.00$ cat /var/log/audit.log
cat: /var/log/audit.log: Permission denied
-bash-3.00$ whoami
oracle

Only root can:

-bash-3.00$ su -
Password:
[root@uhesse ~]# cat /var/log/audit.log | grep instance
Feb  2 17:44:46 uhesse Oracle Audit[1184]:
ACTION : 'select * from v$instance'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6 STATUS: 0

Of course, the usual aspects of auditing apply in this case also: It is an effort to do (for the system and for people who maintain it), it is space consuming, it may impose performance problems – especially if now large actions are performed by sys, like running catalog.sql or catproc.sql, for example. These actions may lead to very much auditing now…