Audit Connect AS SYSDBA

I need to admit my lack of working on UNIX flavors.Few months back only i started working on databases on UNIX flavors, before this it was all windows family.Reason for my so much working on windows family needs to asked to my MANAGER 😉

I was informed about the space crunch issue by the support guys on the ORACLE_HOME mount point.As part of free up process all the tracefiles form adump,bdump,udump and cdump were removed.Interestingly, i saw “ora_xxxxx.aud” files in “/data2/oracle/admin/db1/adump/”  which is supposed to be the location for adump.One of the file’s content was :-

-bash-3.00$ cat ora_8238.aud
Audit file /data2/oracle/admin/db1/adump/ora_8238.aud
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /data2/oracle/product/10.2.0/db_1
System name:    SunOS
Node name:      isjfooj1
Release:        5.10
Version:        Generic_137112-08
Machine:        i86pc
Instance name: prd2
Redo thread mounted by this instance: 2
Oracle process number: 22
Unix process pid: 8238, image: oracle@isjfooj1(TNS V1-V3)

Wed Mar 24 12:40:25 2010
LENGTH : '132'
CLIENT USER:[6] 'oracle'
STATUS:[1] '0'

Wed Mar 24 12:41:34 2010
LENGTH : '133'
CLIENT USER:[6] 'oracle'
STATUS:[1] '0'

I immediately logged into the database and check for the values of parameter related to audit

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /data2/oracle/admin/db1/adu
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

The value for parameter audit_sys_opertaion is set FALSE.I had the misconception that as this parameter is set to FALSE, why are then the files getting generated in adump folder??? Sys operations are not enabled to be audited.

With the same parameter values, no files are present in adump folders of db on windows environment,so is it some UNIX property that is audits ” connect as sysdba”?? I asked my friend and he said that the he has seen this in UNIX, but couldn’t give me any solid proof or answer.

So, i searched metalink, and i found Metalink DOC Id 103964.1

Unix Systems
On Unix Systems, the administrative user connections are logged to special log files
created by Oracle and stored in $ORACLE_HOME/rdbms/audit directory.
At a minimum, a new file is created for each startup and shutdown event.

Administrative user connections are not written to database tables as these
connections are needed in order to start and stop the database. The files where
the administrative user connections are written to are stored externally from
the database so they can be accessed when the database is down. For example,
the administrative user connect to startup the database cannot be audited
(written) to a database table so auditing must be done externally to the
database, as opposed to other audited activity (as example: successfull/
unsuccessfull executions of specified SQL statements, auditing privileges or

The administrative user connections are always audited regardless of the init.ora
parameter audit_trail.

Windows Systems
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ‘ as sysdba ‘) in the
event viewer.

The administrative user connections are written to the operating system audit

trail as opposed to the database audit trail (sys.aud$ table).
-> Windows NT/2000/2003/XP

Go for: Start -> Settings -> Control Panel -> Services (Windows NT)
Start -> Settings -> Control Panel ->

Administrative Tools -> Services -> Event viewer->
Application log                     (Windows 2000/2003/XP)

3 thoughts on “Audit Connect AS SYSDBA

  1. Thanks, again!

    I have to notice that the situation is ridiculous. If on Windows, Oracle is able to make use of the ‘event viewer’ why can not I do the same with syslog on Unix?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s