Tuesday, November 25, 2008

Audting SYSDBA Users

I recently came accross this feature in Oracle introduced in 9i where all operations performed by a user connecting as SYSDBA are logged to an OS file. I'm sure most DBA's are familiar with this
feature already but I have only just been enlightened!!

To enable this feature auditing must be enabled and the AUDIT_SYS_OPERATIONS parameter must be set to TRUE. For example:

sys@ORCLINS1> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE SCOPE=SPFILE;

FALSE is the default value for this parameter. Pretty obvious from the above statement but the database must be restarted for the parameter to take affect.

All the audit records are then written to an operating system. The location of this file is determined by the AUDIT_FILE_DEST parameter.

sys@ORCLINS1> show parameter AUDIT_FILE_DEST

NAME TYPE VALUE

-------------- ------------------------------------------

audit_file_dest string /oracle/oracle/admin/orclpad/adump


sys@ORCLINS1>


An audit file will be created for each session started by a user logging in as SYSDBA. The audit file will contain the process ID of the server session that Oracle started for the user in its file name.

Most people are probably already familiar with this handy feature but I like to have it documented for myself somewhere so I put it here!

No comments: