Auditing, Standard Database

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\12c 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

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.
OS             Auditing is enabled, with all audit records directed to 
               the operating system's audit trail.
NONE or FALSE  Auditing is disabled.

If OS used you will no longer be able to query into the corresponding Oracle views. You will have to rely on OS and third-party tools to extract information from audit logging.

alter system set audit_trail=OS scope=SPFILE;  
alter system set audit_sys_operations=true scope=SPFILE;

Display

show parameter audit_trail

SELECT count(*) FROM SYS.AUD$;

COL username    FORMAT a25
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 (storing in database)

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

CREATE BIGFILE TABLESPACE "AUDIT"
DATAFILE 'c:\oradata\dbf\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

1. Unset the database for auditing.
2. Bounce database.

SQLPlus> ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile;

SQLPlus> SHUTDOWN IMMEDIATE
SQLPlus> STARTUP

Audit Trail Views

Auditing Command Examples

Command Format

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

Commands

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

AUDIT select table, insert table, update table;
AUDIT select table, insert table, update table WHENEVER unsuccessful;

AUDIT select table, insert table, update table BY hr,oe;

AUDIT select ON hr.employees;
AUDIT insert ON hr.employees;
AUDIT update ON hr.employees;

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

AUDIT alter, grant, insert, update, delete ON DEFAULT;

<- SpecialTopics