Audit - Enabling or Disabling Audit Trail

Auditing in Oracle Database

The Oracle Server provides several auditing options.

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.

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

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

The initialization parameters of audit facility of Oracle

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

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

SQL> NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;