Oracle Database Auditing

Oracle Database Auditing allows to profile user database activities.

Auditing is the monitoring, profiling and recording of selected user database actions.

The relevant records can be stored in the db audit trail or in OS files.

Auditing can be done on some individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on.

It's possible to audit both successful and failed activities.

In Oracle 11g auditing is turned on by default.

In Oracle 10g you have to enable it, but nothing is audited by default once it is active.

Oracle audit important parameters:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 -

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/MYDBINST/a
                                                 dump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
SQL>

audit_file_dest - specifies the OS directory where the audit trail is written, when the AUDIT_TRAIL initialization parameter is set to os, xml, or xml,extended

audit_trail - enables or disables database auditing.

Values (for parameter audit_trail):  

none - database auditing will be disabled.

os - will enable db auditing and directs all audit records to the operating system's audit trail.

db - enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).

db,extended - enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

xml - enables database auditing and writes all audit records to XML format OS files.

xml,extended - enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.

In case audit_trail=db or audit_trail=db,extended is enabled, the audit trail is stored in the SYS.AUD$ table.

You can query it directly or via related dictionary views:

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

Example:

-- The db,extend setting will populate the SYS.AUD$ and DBA_AUDIT_TRAIL sql_text field.
-- Note, in Oracle 10g release 1, the db_extended was used in place of db,extended.

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

System altered.

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

Total System Global Area 1887350784 bytes
Fixed Size                  2145504 bytes
Variable Size            1241514784 bytes
Database Buffers          419430400 bytes
Redo Buffers              224260096 bytes
Database mounted.
Database opened.
SQL> AUDIT SELECT TABLE, UPDATE TABLE BY myuser;

Audit succeeded.

SQL> conn myuser/mypass
Connected.
SQL> select * from sometab;

       AAA
----------
       123

SQL>

select * from dba_audit_trail where username = 'MYUSER';

select *
  from dba_audit_trail
where username = 'MYUSER'
   and owner = 'MYUSER';

Audit command format:

AUDIT {statement_option|privilege_option} [by user] [by {session|access}] [ whenever {successful|unsuccessful}];
The difference between BY SESSION and BY ACCESS is that when you specify BY SESSION Oracle will try to merge multiple audit entries into one record when the session and the action audited match. BY SESSION causes Oracle to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes

Oracle to write one record for each access.

Audit Oracle commands examples:

AUDIT SESSION; - will audit all successful and unsuccessful connections to and disconnections from the database, regardless of user.

AUDIT SESSION by myuser; - the same, like a previous option, but set selectively, for individual user 'myuser'.

AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE by myuser by ACCESS; - will audit 'myuser' Oracle user all insert, update, delete activities.

AUDIT SELECT TABLE BY myuser by ACCESS; - will audit selects issued by 'myuser' Oracle user.

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL; - will audit all unsuccessful SELECT, INSERT,

DELETE and UPDATE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement.

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE by myuser BY ACCESS WHENEVER NOT SUCCESSFUL; - will audit all unsuccessful SELECT,

INSERT, DELETE and UPDATE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by <myuser> Oracle database user, and by individual audited statement.

AUDIT ALL BY myuser BY ACCESS; - will audit all activities of the 'myuser' Oracle user. This could generate a lot of audit trace information and could cause overhead.

NOAUDIT ALL; - remove all auditing.

After audit_trail functionality is enabled for some time, the audit_trail related rows will grow quickly in the SYSTEM tablespace, causing performance and/or lack of space issues. The DBA may want to delete audit trail records to free space and to facilitate audit trail management.

There are a few options to perform it:

DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name='SOME_OBJECT';
TRUNCATE TABLE SYS.AUD$;

(SYS.AUD$ is the only SYS object that should ever be directly modified.)

Example:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> NOAUDIT ALL;

Noaudit succeeded.

SQL> TRUNCATE TABLE SYS.AUD$;

Table truncated.