Hold On There SYSter

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 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – 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: [email protected] (TNS V1-V3)

Mon Mar 3 07:04:30 2003
ACTION : ‘CONNECT’
DATABASE USER: ‘sys’
PRIVILEGE : SYSDBA
CLIENT USER: jkoopmann
CLIENT TERMINAL: JKOOPMANN
STATUS: 0

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
ACTION : ‘CONNECT’
DATABASE USER: ‘sys’
PRIVILEGE : NONE
CLIENT USER: jkoopmann
CLIENT TERMINAL: 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:

Today

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


SQL> SHUTDOWN
SQL> STARTUP

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles