Auditing SYS users

The SYS user is the most usefull and important user for any Oracle database. Lets take a look how a DBA can take hold of it if it is being misused.

Oracle provides the auditing facility of SYS account since the version 7 but after 9iR2, it has become more powerful in many ways.

How to Audit SYS account

Mainly there are only two parameters which need to be set to audit any SYS account, audit_file_dest and audit_sys_operations. The prior one sets the location for audit files and later one is ON/OFF for SYS auditing.

The audit files are created under the path provided in the parameter audit_file_dest with ora_.aud name. The file usually have following informations:

Audit file /u01/app/oracle/admin/tcolap1/adump/ora_14725.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: hostname.domainname
Release: 2.6.9-34.ELsmp
Version: #1 SMP Fri Feb 24 16:54:53 EST 2006
Machine: i686
Instance name: tcolap1
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 14725, image: oracle@hostname.domainname (TNS V1-V3)

Tue Feb 6 14:11:14 2007
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

With all the information related to the session like 'connect' and all the valid commands, it provides server related information as well, such as OS, version, Machine, etc. It does not store the invalid commands.

Invalid connection as SYSDBA

The audit files also store invalid login information. To check invalid logins to the system, following command can be given:

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

Parameters to set

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

Bounce the database and you are done.

View the Audit Information

Audit information can be viewed in a text format or XML format depending on the parameter audit_trail setting.