Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, September 28, 2011

How to Audit Connect AS SYSDBA Using Oracle Server

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).
 
  You can choose to 
   filter out only the entries that are of your intrest. From the View Menu, 
   choose "Filter Events" and in the Filter Dialog Box pick Oracle.your_SID as 
   the source. Click OK.
whenever you access the DB as sysdba (logon/logoff/disconnect) , an entry  is 
written in event log (event viewer) under security.
 
 

No comments: