Oracle Auditing
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Note Traditional auditing as covered on this library page is essentially obsolete once you move to 12c. If you want to look forward, not backward, click the Unified Audit Policies link at the bottom of the page.
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$;

Related Topics
Audit Policies
Audit Vault
DBMS_AUDIT_MGMT
DBMS_FGA
Fine Grained Auditing
Security
Unified Audit Policies
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved