Auditing in Oracle Database

The Oracle Server provides several auditing options. For example, you can choose to audit certain types of SQL statements, such as INSERT or UPDATE statements. You can audit statements that use specific system privileges, such as CREATE TABLE or ALTER TABLE. You can also audit the unsuccessful attempts to perform some type of action in the database, such as failed login attempts. Auditing can be limited to a specific user or group of users, or can even be limited to actions performed on a specific schema object on a "by session" or "by access" basis.

The following three types of audits are provide by Oracle

 1. Session audits (LOGON,LOGOFF etc)
 2. Database action and object audits and
 3. DDL(CREATE, ALTER & DROP of objects)

The three main views to see the AUDIT Information are:

• DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).


• DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$) [For 10g].


• DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing   [For 10g].

To enable database auditing, you must provide a value for the AUDIT_TRAIL parameter.

The initialization parameters of audit facility of Oracle

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB

Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

DB              Auditing is enabled. Audit records will be written to the
                SYS.AUD$ table.
OS              Auditing is enabled. Audit records will be written to an
                audit trail in the operating system.
db,extended     As db, but the SQL_BIND and SQL_TEXT columns are also populated.
NONE            Auditing is disabled (default).
xml-            Auditing is enabled, with all audit records stored
                as XML format OS files.
xml,extended    As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
TRUE            This value is supported for backward-compatibility
                with versions of Oracle;it is equivalent to the DB value.
FALSE           This value is supported for backward-compatibility
                with versions of Oracle;it is equivalent to the NONE value.

In Oracle 10g Release 1, db_extended was used in place of db,extended. The XML options are new to Oracle 10g Release 2.



The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

Audit in SYS.AUD$ desertion Table

Set audit_trail to DB in pfile (audit_trail = DB) .

Enable auditing and direct audit records to the database audit trail

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             230689644 bytes
Database Buffers          377487360 bytes
Redo Buffers                2940928 bytes
Database mounted.

The command to begin auditing connects (login) attempts is:

AUDIT SESSION;

AUDIT SESSION WHENEVER SUCCESSFUL;

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

To view the report of Audit session run the following query.

SQL Code:  
SELECT os_username,
     username,
     terminal,
     returncode,
     TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
     TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session;

Disable Session Audit

NOAUDIT SESSION;

Audit a User

AUDIT CREATE TABLE BY scott;

Disable User Audit

NOAUDIT CREATE TABLE BY scott;

Audit a User Table

It is also possible to audit SELECT, INSERT, UPDATE, and DELETE operations on specific database tables. Depending on the type of audit you wish to conduct, you can include either the BY SESSION or BY ACCESS clause in the AUDIT command. This clause is available for both object audits and system-level action audits.

It denotes whether an audit record will be written once for each session (BY SESSION) or once for each time a schema objects is accessed (BY ACCESS).

AUDIT INSERT ON scott.emp1 BY SESSION;

AUDIT INSERT ON scott.emp2 BY ACCESS;

Disable User Table Audit

NOAUDIT INSERT ON scott.emp1;

Set audit_trail to OS file destination, in pfile (audit_trail = OS)

audit_file_dest = /u01/oracle/product/10.2.0/admin/orcl/adump

Open your init.ora file and check the AUDIT_FILE_DEST parameter for the location of your operating system audit trail files.

Open the file in Unix system and If you are using Windows NT, then go to Start -> Programs -> Administrative Tools -> Event Viewer. In the menu bar of the Event Viewer, choose Log -> Application. Double click on the events listed.

To find out the name of the database action that corresponds to the numeric code of 100, query the AUDIT_ACTIONS data dictionary view.

SQL Code:
SELECT action, name
FROM audit_actions
WHERE action = 100;

Similarly, to discover the name of the privilege used to allow you to perform this database action, query the STMT_AUDIT_OPTION_MAP data dictionary view. While still logged on as user

SQL Code:
SELECT option#, name
FROM stmt_audit_option_map
WHERE option# = 5;

View Audit Trail

The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views:

SQL Code:
SQL> SELECT view_name
  2  FROM   dba_views
  3  WHERE  view_name LIKE 'DBA%AUDIT%'
  4  ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS