Probably the most sacred user in all of Oracledom is the SYS user. Let's take a quick look at how you, as a DBA, can take hold of this often misused account.

Prior to Oracle 9iR2, the audit ability of the SYS account was very limited. While we all know that the use of the SYS account should only be used in a very limited manor, its ease of access and unlimited power make it the user of choice for many DBAs. I myself fall victim of using the SYS account almost exclusively to monitor and change just about anything in the database. While under the watch and use of most experienced DBAs, the SYS account may not cause problems. The question I pose to you and to myself is why should we use such a powerful account and neglect basic security principles. We must remember that the SYS account is very powerful and if we forget some small syntax, we could be in a world of hurt. This article will expose you to the current method of monitoring the use of the all too precious SYS account.

I have also seen many systems that run under the SYS use account without any concern for security what so ever. Therefore, the neglected accountability of the SYS account should raise quite a concern. You may not have a need today, but since there is such a heightened buzz around security today, you can rest assured that you will soon have to face this issue. It just does not make sense to audit every user in the system except the most powerful SYS account. Let get started on this very simple mechanism..

How to Audit the SYS Account

From the Beginning

For a long time, perhaps as far back as version 7.0, (since I don't remember doing cleanup work on system audit files in version 6), Oracle would, and still does create a system audit file named ora_<pid>_aud in the directory $ORACLE_HOME/rdbms/audit (in a Unix environment). This file is created every time a user attempts to connect internal through server manager (svrmgrl), or the now current method of connect as sysdba or sysoper. I can recall, and you should take it to heart, a few times when my disk subsystem filled completely up with these audit files. When this happens, (don't let it), no one can log into the database. As a DBA, be sure that you have something in place to clean out these annoying little files on a regular basis.

Valid Connection as SYSDBA

When a connect as SYSDBA is issued, the output is generated in an audit file. The top part if the file is, for the most part, informational. However, it does give some interesting information on the operating system, type and version (kernel level), and operating system process information. Following the "informational" piece of this audit file, is the connection information. It gives a quick glimpse of when and what action was performed, under which database user it was attempted, the privilege accessed, the requesting user and terminal from where it was requested, and the status of the attempt. While these files should be on a disk where they cannot be tampered with, you might want to verify the date stamp inside the audit file with the external system timestamp on the audit file.

An example of the external audit file generated by a valid connection:

sh-2.05$ ls -l $ORACLE_HOME/rdbms/audit/ora_23097.aud

-rw-r----- 1 oracle oracle 677 Mar 3 07:04 ora_23097.aud

An example of the inside of an audit file for a valid connection:

Audit file /u01/app/oracle/product/9.2/rdbms/audit/ora_23097.aud
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production
ORACLE_HOME = /u01/app/oracle/product/9.2
System name:    Linux
Node name:      koopgate
Release:        2.4.9-e.3smp
Version:        #1 SMP Fri May 3 16:48:54 EDT 2002
Machine:        i686
Instance name: saigon
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 23097, image: oracle@koopgate (TNS V1-V3)

Mon Mar  3 07:04:30 2003
CLIENT USER: jkoopmann

Invalid Connection as SYSDBA

While it is true that you should monitor all connection attempts that are registered in the audit files, you should be extra concerned about invalid attempts. A simple way to check to see if any invalid attempts were made is to grep the audit files for any non-zero statuses.

An example of the command that will do this for you:

sh-2.05$ grep -i "status:" *.aud | grep -i -v "status: 0"

Simple output might be:

ora_23101.aud:STATUS: 1017

An example of the connection attempt information in audit file ora_23101.aud:

Mon Mar  3 07:04:47 2003
CLIENT USER: jkoopmann
STATUS: 1017

Under Windows NT

Under the windows environment, this information is sent to the Event Viewer. This is what a typical event looks like under windows:


Oracle has maintained the audit file information thus far discussed in this article but has also added the ability for extended auditing of the SYS account with a new initialization parameter AUDIT_SYS_OPERATIONS. By setting this parameter, we are now able to monitor all commands issued by user SYS and any users connecting as SYSDBA or SYSOPER.

Parameters to Set

When setting up auditing of the SYS account, there are really only two parameters that you need to consider:

audit_file_dest :
Sets the location of where the audit files will reside on the operating system. The default is $ORACLE_HOME/rdbms/audit. This parameter does not need to be changed but I feel it is a good idea to give some form of cloak and dagger to its whereabouts.

NOTE: Under Windows, this parameter does not exist; audits are sent to the Event Viewer.

audit_sys_operations :

This initialization parameter tells Oracle to turn on auditing of the SYS connections. It has either a TRUE or FALSE value.

How to Set

Each of these parameters are non-system modifiable and thus require a shutdown and startup of your database. Use the ALTER SYSTEM command to change these parameters and use the scope=spfile option.

Set the New Audit File Destination

SQL> alter system set audit_file_dest='/u04/app/oracle/oradata/saigon/audit' scope=spfile;

Set the Auditing of the SYS Account

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

Bounce the Database


Check the New Settings

SQL> show parameter %audit%
NAME                     TYPE        VALUE
----------------------   ----------- ------------------------------------
audit_file_dest          string      /u04/app/oracle/oradata/Saigon/audit
audit_sys_operations     boolean     TRUE

You Are Done

Now that you have the settings changed, you can start monitoring the activity on the SYS account. The type of information tracked is just about anything the SYS user does. This information hits all of your most popular DDL and DML statements. I have noticed that the option does not catch most invalid statements submitted. This in itself does pose a bit of a problem since malicious attacks typically do have a pattern of invalid attempts at times. I personally would suggest turning this feature on and start checking the types of information you can catch if you do a lot of work under the SYS account. I have also found that this is a great tool for tracking a ton of database administration tasks. Instead of having to write everything down and wondering what statements have been issued, just look at the audit trail produced.

» See All Articles by Columnist James Koopmann