Auditing in Oracle needs to know - part1

No matter how good your security policies are, there will be occasions when a policy is not enough.The most extreme example of this is you, the database administrator. Anyone with the SYSDBA privilege can do anything within the database. So it is necessary to audit all SYSDBA activity. For normal users, you may also wish to track what they are doing. You may not be able to prevent them from breaking company rules on access to data, but you can track the fact that they did it.

Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file. From Oracle 11g, auditing is enabled for some system level privileges.

SYSDBA auditing
There is an instance parameter AUDIT_SYS_OPERATIONS. If it is set to TRUE (the default is FALSE), then every statement issued by a user connected AS SYSDBA or AS SYSOPER is written out to the operating system’s audit trail. This gives a complete record of all work done by the DBA.Your system needs to be configured in such a way that the DBA has no access to the audit records that track his activity: they should be accessible only to the computer’s system administrator. If the DBA were also the system administrator, then the auditing would be useless.
The destination of the SYS audit records is platform specific. On Windows, it is the Windows Application Log; on Unix it is controlled by the AUDIT_FILE_DEST parameter. This parameter should point to a directory on which the Oracle owner has write permission

How to setup SYSDBA auditing
1. Before setting up any kind of database auditing, an instance parameter must be set: AUDIT_TRAIL. This has four possible values:
• NONE (or FALSE) Database auditing is disabled, no matter what auditing you attempt to configure and is default.
• OS Audit records will be written to the operating system’s audit trail: the Application Log on Windows, or the AUDIT_FILE_DEST directory on Unix.
• DB (or TRUE) The audit records are written to a data dictionary table, SYS.AUD$. There are so many views that let you see the contents of this table.
• DB_EXTENDED As DB, but including information on the SQL statements with bind variables that generated the audit records.

Example:-

SQL> sho user;
USER is "SYS"
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\CRAZE\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

SQL> alter system set audit_trail=DB;
alter system set audit_trail=DB
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

SQL> alter system set audit_sys_operations=TRUE scope=spfile;

System altered.

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\CRAZE\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  931135488 bytes
Fixed Size                  2070096 bytes
Variable Size             285215152 bytes
Database Buffers          637534208 bytes
Redo Buffers                6316032 bytes
Database mounted.
Database opened.
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\CRAZE\ADUMP
audit_sys_operations                 boolean     TRUE
audit_trail                          string      DB
SQL> alter user mahi identified by mahi;

User altered.

SQL> alter user mahi default tablespace USERS;

User altered.

SQL>

You can see the activity done by user connected as sysdba in event viewer

Here I usedaudit_trail to DB for my audit purpose .The audit records go to the filesystem (in UNIX) or Event Log (in Windows), not to the database tables, even if the parameter audit_trail is set to DB. The location in UNIX is the filesystem specified by the initialization parameter audit_file_dest, which defaults to $ORACLE_HOME/rdbms/audit.This parameter is independent of the other audit parameter audit_trail. The SYSDBA auditing can be enabled or disabled regardless of whether regular auditing is set.