Standard Database Auditing

Overview

Standard Database Auditing records user database actions. Records can be stored in the database audit trail or in files on the operating system. Standard auditing includes operations on privileges, schemas, objects, and statements.

To enable Standard Database Auditing requires a parameter change and issuing the appropriate commands to specify what to be audited. In Oracle 11g auditing is on by default. In Oracle 10g you must enable it but nothing is audited by default once it is active. The table SYS.AUD$ is used to store audit records and is stored in the SYSTEM tablespace. Depending on what is being audited it can get quite large so it is recommended to move this to another tablespace.

Oracle 11g default auditing settings:

ALTER ANY PROCEDURE CREATE ANY JOB DROP ANY TABLE
ALTER ANY TABLE CREATE ANY LIBRARY DROP PROFILE
ALTER DATABASE CREATE ANY PROCEDURE DROP USER
ALTER PROFILE CREATE ANY TABLE EXEMPT ACCESS POLICY
AUDIT ROLE BY ACCESS CREATE EXTERNAL JOB GRANT ANY OBJECT PRIVILEGE
ALTER SYSTEM CREATE PUBLIC DATABASE LINK GRANT ANY PRIVILEGE
ALTER USER CREATE SESSION GRANT ANY ROLE
AUDIT SYSTEM CREATE USER
AUDIT SYSTEM BY ACCESS DROP ANY PROCEDURE

Audit Trail Views

STMT_AUDIT_OPTION_MAP Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.

AUDIT_ACTIONS Contains descriptions for audit trail action type codes.

ALL_DEF_AUDIT_OPTS Contains default object-auditing options that will be applied when objects are created.

DBA_STMT_AUDIT_OPTS Describes current system auditing options across the system and by user.

DBA_PRIV_AUDIT_OPTS Describes current system privileges being audited across the system and by user.

DBA|USER_OBJ_AUDIT_OPTS Describes auditing options on all objects.
The USER view describes auditing options on all objects owned by the current user.

DBA|USER_AUDIT_TRAIL Lists all audit trail entries.
The USER view shows audit trail entries relating to current user.

DBA|USER_AUDIT_OBJECT Contains audit trail records for all objects in the system.
The USER view lists audit trail records for statements concerning objects that are accessible to the current user.

DBA|USER_AUDIT_SESSION Lists all audit trail records concerning CONNECT and DISCONNECT.
The USER view lists all audit trail records concerning connections and disconnections for the current user.

DBA|USER_AUDIT_STATEMENT Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements
throughout the database, or for the USER view, issued by the user.

DBA_AUDIT_EXISTS Lists audit trail entries produced BY AUDIT NOT EXISTS.

DBA_AUDIT_POLICIES Shows all the auditing policies on the system.

DBA_FGA_AUDIT_TRAIL Lists audit trail records for value-based auditing.

DBA_COMMON_AUDIT_TRAIL Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format.

AUDIT_TRAIL Values (DB|EXTEND|XML|NONE)

DB or TRUE

Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).

DB,EXTENDED

As db, but the SQL_BIND and SQL_TEXT columns are also populated.

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.

Auditing is enabled, with all audit records directed to the operating system's audit trail.

NONE or FALSE

Auditing is disabled.

Display

SELECT count(*) FROM SYS.AUD$;

COL terminal    FORMAT a15
COL action_name FORMAT a20
SELECT username, terminal, action_name, to_char(timestamp,'DDMMYYYY:HHMISS') timestamp, returncode
FROM dba_audit_session
WHERE rownum <= 25;

SELECT extended_timestamp,username,owner,obj_name,action_name 
FROM   dba_audit_trail
WHERE cast(extended_timestamp AS DATE) >= sysdate-.15
ORDER BY timestamp ASC;

Enable

Create AUDIT tablespace.
Move SYS.AUD$ to the AUDIT tablespace.
Set the database for auditing.
ALTER SYSTEM SET audit_trail=<DB|EXTEND|XML|NONE> SCOPE=spfile;
Bounce database.

CREATE BIGFILE TABLESPACE "AUDIT" 
DATAFILE 'c:\oradata\DB1\audit.dbf'
SIZE 100m AUTOEXTEND ON NEXT 5m MAXSIZE 10g
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
LOGGING;

ALTER TABLE AUD$ MOVE TABLESPACE "AUDIT"
LOB (sqltext) STORE AS aud_sqltext (TABLESPACE "AUDIT") 
LOB (sqlbind) STORE AS aud_sqlbind (TABLESPACE "AUDIT");

ALTER SYSTEM SET audit_trail=DB SCOPE=spfile;

SHUTDOWN IMMEDIATE
STARTUP

Disable

Unset the database for auditing.
Bounce database.

ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile;

SHUTDOWN IMMEDIATE
STARTUP

Auditing Command Examples

Command Format

AUDIT {statement_option|privilege_option}
[by user] [by {session|access}] [ whenever {successful|unsuccessful}];

-- Capturing logon and logoff attempts for all users except privileged attempts.
AUDIT CREATE SESSION;

-- Auditing any selects, inserts or updates all tables (this could generate a lot of overhead).
AUDIT select table, insert table, update table;
AUDIT select table, insert table, update table WHENEVER unsuccessful;

-- Auditing any selects, inserts or updates on specific users.
AUDIT select table, insert table, update table BY hr,oe;

-- Auditing any selects, inserts or updates on a specific table.
AUDIT select ON hr.employees;
AUDIT insert ON hr.employees;
AUDIT update ON hr.employees;

AUDIT select, insert, update ON hr.employees; -- Equivlient to above three commands.

-- Setting Default Auditing Options
AUDIT alter, grant, insert, update, delete ON DEFAULT;

Complete AUDIT command reference: Oracle Docs