How to Audit Connect AS SYSDBA Using Oracle Server

Subject: How to Audit Connect AS SYSDBA Using Oracle Server
Doc ID: Note:103964.1 Type: BULLETIN
Last Revision Date: 09-MAY-2007 Status: PUBLISHED


"Checked for relevance on 09-May-2007"

Purpose:
~~~~~~~~
How to monitor audit Connect as Sysdba ? It is not possible to audit SYSDBA
connections in the same way as the other Oracle users.


There are no tables within Oracle to write this audit information.
This bulletin describes how to audit these connections to Oracle as an
administrative user for Unix and Windows systems.


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).


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.
A UNIX cron job can be created to delete these files if necessary.


Explanation
-----------
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
objects).

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

This feature was created in order to comply with the NCSC C2 security
evaluation criteria. NCSC deemed that an "oper" user should not be able to
disable this level of auditing.

Example:
~~~~~~~~
-> Unix systems:
$ ls -al $ORACLE_HOME/rdbms/audit

Result:
total 24
drwxrwxr-x 2 server sdb 4096 Nov 12 06:00 ./
drwxrwxr-x 13 server sdb 2048 Jul 17 14:51 ../
-rw-r----- 1 server sdb 562 Nov 11 06:00 ora_17254.aud
-rw-r----- 1 server sdb 562 Nov 12 06:00 ora_27213.aud

$ cat $ORACLE_HOME/rdbms/audit/ora_27213.aud

Result:
Audit file /ots1/app/oracle/product/8.1.7/rdbms/audit/ora_27213.aud
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
ORACLE_HOME = /ots1/app/oracle/product/8.1.7
System name: SunOS
Node name: nlsu22
Release: 5.6
Version: Generic_105181-25
Machine: sun4u
Instance name: v817
Redo thread mounted by this instance: 0
Oracle process number: 0
27213

Mon Nov 12 06:00:41 2001
ACTION : 'connect internal' OSPRIV : DBA
CLIENT USER: server
CLIENT TERMINAL:
STATUS: SUCCEEDED ( 0 )


-> 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)

Open an Oracle event (shown for Windows 2000):

Information 22-11-2001 9:38:11 Oracle.o817

content:

Audit trail: ACTION:'connect INTERNAL' OSPRIV: OPER CLIENT
USER: SYSTEM CLIENT TERMINAL: SROO-NL STATUS
SUCCEEDED (0).

Note
~~~~

In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:

Note 174340.1 : Audit SYS user Operations

Related Documents:
~~~~~~~~~~~~~~~~~~
Note 50508.1 : ALERT: "CONNECT INTERNAL" Syntax to be DeSupported
Note 41800.1 : Quick Reference to Auditing Information
Note 174340.1 : Audit SYS user Operations