Notes on Auditing in Oracle Database

Notes on Auditing in Oracle Database (10g)

Auditing purposes:
--auditing what kinds of privileges are being used to uncover abuse or misuse of privileges
--auditing what objects are being accessed


Auditing location depending on initialization parameter AUDIT_TRAIL (SYS.AUD$, OS file):
--NONE, FALSE: disable auditing
--OS: enable auditing. Send audit results to an OS file
--DB, TRUE: enable auditing and send to SYS.AUD$
--DB_EXTENDED: enable auditing and send results to SYS.AUD$, store additional info in BLOB columns SQLBIND and SQLTEXT.


Auditing types

1. Statement auditing: audit SQL statements by the type of statement regardless of the specific schema objects being accessed. One or more users can be specified to be audited for a particular statement.

AUDIT sql_statement_clause BY {SESSION ACCESS} WHENEVER [NOT] SUCCESSFUL;
--ACCESS: every time
--SESSION: once, default
--WHENEVER SUCCESSFUL: successful action, statement did not generate an error
--WHENEVER NOT SUCCESSFUL: unsuccessful action, statement fails for insufficient privilege, syntax error, running out of space in the tablespace
--statement option:
--all, includes CLUSTER, CONTEXT, DATABASE_LINK, DIMENSION, DIRECTORY, INDEX, MATERIALIZED VIEW, NOT EXISTS, PROCEDURE, PROFILE, PUBLIC SYNONYM, ROLE, ROLLBACK SEGMENT, SEQUENCE, SESSION, SYNONYM, SYSTEM AUDIT, SYSTEM GRANT, TABLE, TABLESPACE, TRIGGER, TYPE, USER, VIEW.
--explicitly specified statements: ALTER SEQUENCE, ALTER TABLE, COMMENT TABLE, DELETE TABLE, EXECUTE PROCEDURE, GRANT DIRECTORY, GRANT PROCEDURE, GRANT SEQUENCE, GRANT TABLE, GRANT TYPE, INSERT TABLE, LOCK TABLE, SELECT SEQUENCE, SELECT TABLE, UPDATE TABLE

--example 1: audit index by scott
SQL>audit index by scott whenever successful;
SQL>select username, to_char(timestamp, 'MM/DD/YY HH24:MI') timestamp, obj_name, action_name, sql_text from dba_audit_trail where username='SCOTT';
SQL>noaudit index by scott;

--example 2: login audit
SQL>audit session whenever successful;
SQL>audit session whenever not successful;
SQL>select username, to_char(timestamp, 'MM/DD/YY HH24:MI') timestamp, obj_name, returncode, action_name, sql_text from dba_audit_trail where action_name in ('LOGON','LOGOFF') order by timestamp desc;

--example 3: Oracle database startup and shutdown audit
--shutdown immediate in SYS.AUD$
--startup: folder $ORACLE_HOME/rdbms/audit/ (determined by init parameter audit_file_dest)

--example: protect audit trail
SQL>audit all on sys.aud$ by access;


2. Privilege auditing: audit system privileges, such as CREATE TABLE, ALTER INDEX. Can specify one or more particular users as target of the audit.
--audit SYSDBA and SYSOPER privilege:
--set initialization parameter audit_sys_operations=true
--audit OS files sent to audit_file_dest

--example 1: audit every time
SQL>audit ALTER TABLESPACE by access whenever successful;


3. Schema object auditing: audit specific statements operating on a specific schema object (UPDATE). Applies to all users in the database.
--AUDIT schema_audit_clause BY {SESSION ACCESS} WHENEVER [NOT] SUCCESSFUL;
--schema_audit_clause: ALTER, AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INDEX, INSERT, LOCK, READ, RENAME, SELECT, UPDATE

--example 1:
SQL>audit insert, update on hr.jobs by access whenever successful;


4. Fine-grained auditing (FGA): audit table access and privileges based on the contents of the objects being accessed. DBMS_FGA to set up a policy.
--ADD_POLICY, DROP_POLICY, DISABLE_POLICY, ENABLE_POLICY
--DBA_FGA_AUDIT_TRAIL

--example: audit access to salary column
begin
dbms_fga.add_policy(
object_schema => 'HR',
object_name => 'EMPLOYESS',
policy_name => 'SAL_SELECT_AUDIT',
audit_condition => 'instr(job_id, ''_MAN'') > 0',
audit_column => 'SALARY'
);
end;



Auditing-related dictionary views
AUDIT_ACTIONS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS


Notes:
1. A non-DBA user within an Oracle database cannot enable the auditing features.

2. If auditing has been enabled, there are data dictionary views that anyone can use to view the audit trail.

3. The single audit trail table: SYS.AUD$.

4. USER_AUDIT_TRAIL: all audit records for many different types of actions, many of the columns may be inapplicable for any given row.
USER_AUDIT_OBJECT: for statements concerning objects
USER_AUDIT_SESSION: for connections and disconnections
USER_AUDIT_STATEMENT: for grant, revoke, audit, noaudit, and alter system commands issued by the user

DBA_AUDIT_TRAIL:
DBA_AUDIT_OBJECT: for statements concerning objects
DBA_AUDIT_SESSION: for connections and disconnections
DBA_AUDIT_STATEMENT: for grant, revoke, audit, noaudit, and alter system commands

5. View audit options:
DBA_OBJ_AUDIT_OPTS: describes auditing options on all objects.
USER_OBJ_AUDIT_OPTS: describes auditing options on all objects owned by the current user. This view does not display the OWNER column.
ALL_DEF_AUDIT_OPTS: contains default object-auditing options that will be applied when objects are created.
-/-: no default auditing
S/-: auditing whenever successful
-/S: auditing whenever not successful

6. View commands that can be audited:
AUDIT_ACTIONS: describes audit trail action type codes. This table can be used to map action type numbers to action type names.

7. DBA views without USER counterparts:
DBA_AUDIT_EXISTS:
DBA_PRIV_AUDIT_OPTS:
DBA_STMT_AUDIT_OPTS:
STMT_AUDIT_OPTION_MAP:


Reference: Oracle 10g DBA Handbook