Oracle Auditing

Oracle Auditing Version 11.2.0.3

General Information

Startup (init) Parameters

AUDIT_ SYS_OPERATIONS {FALSE | TRUE} SCOPE=SPFILE SID='*';

Value	Description
FALSE	Do not audit SYS
TRUE	Audit SYS

ALTER SYSTEM SET audit_trail=OS SCOPE=SPFILE SID='*';

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE SID='*';

Script that turns off auditing and auditing options

{$ORACLE_HOME}/rdbms/admin/undoaud.sql

Audit Trail Types

Value	Description
DB	Enables database auditing and directs all audit records to the SYS.AUD$ table. With windows if audit_sys_operations = TRUE the SYSOPER and SYSDBA audit trail are written to the Windows event log.
DB, EXTENDED	Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table) inlcuding the SQLBIND and SQLTEXT CLOB columns
NONE	Disables database auditing
OS	Enables database auditing and directs all audit records to the operating system's audit trail. With Windows the audit trail records are written to the Windows event log.
XML	Enables database auditing and writes all audit records to XML format OS files
XML, EXTENDED	Enables database auditing and prints allaudit trail columns, including SqlText and SqlBind values

Data Dictionary Objects

ALL_DEF_AUDIT_OPTS	DBA_AUDIT_POLICIES	DBA_COMMON_AUDIT_TRAIL
AUD$	DBA_AUDIT_POLICY_COLUMNS	DBA_OBJ_AUDIT_OPTS
AUDIT_ACTIONS	DBA_AUDIT_SESSION	DBA_PRIV_AUDIT_OPTS
DBA_AUDIT_EXISTS	DBA_AUDIT_STATEMENT	DBA_STMT_AUDIT_OPTS
DBA_AUDIT_OBJECTS	DBA_AUDIT_TRAIL	GV$XML_AUDIT_TRAIL

DCL Statement Options

GRANT DIRECTORY	GRANT TYPE	REVOKE ON SEQUENCE
GRANT PROCEDURE	REVOKE ON DIRECTORY	REVOKE ON TABLE
GRANT SEQUENCE	REVOKE ON PROCEDURE	REVOKE ON TYPE
GRANT TABLE

DDL Statement Options

Category	Audit Specifications
ALTER SYSTEM	ALTER SYSTEM
CLUSTER	CREATE CLUSTER ALTER CLUSTER DROP CLUSTER TRUNCATE CLUSTER
CONTEXT	CREATE CONTEXT DROP CONTEXT
DATABASE LINK	CREATE DATABASE LINK DROP DATABASE LINK
DIMENSION	CREATE DIMENSION ALTER DIMENSION DROP DIMENSION
DIRECTORY	CREATE DIRECTORY DROP DIRECTORY
INDEX	CREATE INDEX ALTER INDEX ANALYZE INDEX DROP INDEX
MATERIALIZED VIEW	CREATE MATERIALIZED VIEW ALTER MATERIALIZED VIEW DROP MATERIALIZED VIEW
NOT EXISTS	SQL statements that fail because the object does not exist
PROCEDURE	CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION DROP LIBRARY DROP PACKAGE DROP PROCEDURE
PROFILE	CREATE PROFILE ALTER PROFILE DROP PROFILE
PUBLIC DATABASE LINK	CREATE PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK
PUBLIC SYNONYM	CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM
ROLE	CREATE ROLE ALTER ROLE DROP ROLE SET ROLE
ROLLBACK SEGMENT	CREATE ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT DROP ROLLBACK SEGMENT
SEQUENCE	ALTER SEQUENCE CREATE SEQUENCE DROP SEQUENCE
SESSION	LOGONS
SYNONYM	CREATE SYNONYM DROP SYNONYM
SYSTEM AUDIT	AUDIT NOAUDIT
SYSTEM GRANT	GRANT REVOKE
TABLE	ALTER TABLE COMMENT TABLE CREATE TABLE DROP TABLE TRUNCATE TABLE
TABLESPACE	CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE
TRIGGER	CREATE TRIGGER ALTER TRIGGER (with ENABLE and DISABLE clauses)
TYPE	CREATE TYPE CREATE TYPE BODY ALTER TYPE DROP TYPE DROP TYPE BODY
USER	CREATE USER ALTER USER DROP USER
VIEW	CREATE VIEW DROP VIEW

DML Statement Options

Category	Audit Specifications
PL/SQL	EXECUTE PROCEDURE
TABLE	DELETE TABLE INSERT TABLE LOCK TABLE SELECT TABLE UPDATE TABLE

Syntax Options

Audit All Operations

AUDIT ALL;

Stop All Audit Operations

NOAUDIT ALL;

Audit Schema Object

AUDIT <ALL | sql_operation> ON schema_name.object_name;

Audit On Directory

AUDIT <sql_operation> ON DIRECTORY <directory_name>;

Audit Mining Model

AUDIT <sql_operation> ON MINING MODEL <schema_name.model>;

Audit Default

AUDIT <sql_operation> ON DEFAULT;

Audit Network

AUDIT NETWORK BY <SESSION | ACCESS> WHENEVER [NOT] SUCCESSFUL;

Audit Changes to the Audit Trail

AUDIT SYSTEM AUDIT;

Audit Demo 1

Sys Audit

conn / as sysdba

SELECT *
FROM audit_actions
ORDER BY 2;

set linesize 121
col name format a40
col value format a40

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';

NAME VALUE
-------------------- --------------------
audit_sys_operations FALSE
audit_file_dest C:\ORACLE\PRODUCT\ADMIN\ORABASE\ADUMP
audit_trail DB

-- fails
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT='Begin auditing SYS'
SCOPE=BOTH;

-- fails
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT='Begin auditing SYS'
SCOPE=MEMORY;

-- succeeds
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT='Begin auditing SYS'
SCOPE=SPFILE;

-- auditing will begin after a restart

shutdown immediate

startup

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';

CREATE USER xyz
IDENTFIED BY xyz;

DROP USER xyz;

-- look in the audit_file_dest directory for a file named ora_<pid>.aud where "pid" is the operating system process ID

ALTER SYSTEM SET audit_trail='XML'
COMMENT='Audit trail as XML'
SCOPE=SPFILE;

shutdown immediate

startup

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';

CREATE USER xyz
IDENTFIED BY xyz;

DROP USER xyz;

-- look in the audit_file_dest directory for a file named ora_<pid>.aud where "pid" is the operating system process ID

ALTER SYSTEM SET audit_trail='db'
COMMENT='Change auditing to sys.aud$'
SCOPE=BOTH;

Audit Demo 2

Audit CREATE OBJECT

AUDIT <sql_statement_clause> BY <session> [WHENEVER [NOT] SUCCESSFUL];

AUDIT <sql_statement_clause> BY <access> [WHENEVER [NOT] SUCCESSFUL];

AUDIT <schema_object_clause>;

AUDIT NETWORK;

conn / as sysdba

AUDIT CREATE PROCEDURE;
AUDIT TABLE;
AUDIT CREATE TABLE;
AUDIT CREATE TRIGGER;
AUDIT CREATE VIEW;

conn uwclass/uwclass

CREATE TABLE t (
newcol VARCHAR2(20));

CREATE PROCEDURE p IS
BEGIN
NULL;
END;
/

CREATE VIEW v AS
SELECT * FROM t;

conn / as sysdba

SELECT COUNT(*)
FROM aud$;

NOAUDIT TABLE;
NOAUDIT ALL;

Audit SELECT

conn / as sysdba

audit select on scott.emp;
audit select any table whenever successful;
audit select any table whenever not successful;

conn scott/tiger

SELECT COUNT(*)
FROM emp;

SELECT *
FROM emp
WHERE sal < 3000;

conn / as sysdba

SELECT COUNT(*)
FROM aud$;