Oracle | Auditing

Oracle Auditing Version 20c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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.

Dependencies

AUDIT$	AUD$	STMT_AUDIT_OPTION_MAP
AUDIT_ACTIONS	DBA_AUDIT_TRAIL	V$PARAMETER

Startup (init) Parameters

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

Value	Description
FALSE	Do not audit SYS
TRUE	Audit SYS

sho parameter audit

      

      NAME                           
      TYPE        VALUE

      ------------------------------ ----------- 
      --------------------------------------

      audit_file_dest                
      string /u01/app/oracle/admin/test21db_iad25g/adump

      audit_sys_operations           
      boolean TRUE

      audit_syslog_level             
      string

      audit_trail                    
      string DB

      unified_audit_common_systemlog string

      unified_audit_systemlog        string



      ALTER SYSTEM SET audit_trail=OS 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_AUDITED_SYSTEM_ACTIONS	CDB_AUDIT_STATEMENT	DBA_STMT_AUDIT_OPTS
ALL_AUDIT_POLICIES	CDB_AUDIT_TRAIL	DBA_XS_AUDIT_POLICY_OPTIONS
ALL_AUDIT_POLICY_COLUMNS	CDB_COMMON_AUDIT_TRAIL	DBA_XS_AUDIT_TRAIL
ALL_DEF_AUDIT_OPTS	CDB_OBJ_AUDIT_OPTS	DBA_XS_ENABLED_AUDIT_POLICIES
ALL_UNIFIED_AUDIT_ACTIONS	CDB_PRIV_AUDIT_OPTS	DBMS_AUDIT_MGMT
AUDIT$	CDB_STMT_AUDIT_OPTS	DBMS_AUDIT_UTIL
AUDIT_ACTIONS	CDB_UNIFIED_AUDIT_TRAIL
AUDIT_NG$	CDB_XS_AUDIT_POLICY_OPTIONS	GV$XML_AUDIT_TRAIL
	CDB_XS_AUDIT_TRAIL
	CDB_XS_ENABLED_AUDIT_POLICIES
	DBA_AUDIT_EXISTS	SM$AUDIT_CONFIG
AUDTAB$TBS$FOR_EXPORT	DBA_AUDIT_MGMT_CLEANUP_JOBS
AUD$	DBA_AUDIT_MGMT_CLEAN_EVENTS	UNIFIED_MISC_AUDITED_ACTIONS
AUD$UNIFIED	DBA_AUDIT_MGMT_CONFIG_PARAMS	USER_AUDIT_OBJECT
AUD_OBJECT_OPT$	DBA_AUDIT_MGMT_LAST_ARCH_TS
AUD_POLICY$	DBA_AUDIT_OBJECT	USER_AUDIT_POLICY_COLUMNS
CDB_AUDIT_EXISTS	DBA_AUDIT_POLICIES	USER_AUDIT_SESSION
CDB_AUDIT_MGMT_CLEANUP_JOBS	DBA_AUDIT_POLICY_COLUMNS	USER_AUDIT_STATEMENT
CDB_AUDIT_MGMT_CLEAN_EVENTS	DBA_AUDIT_SESSION	USER_AUDIT_TRAIL
CDB_AUDIT_MGMT_CONFIG_PARAMS	DBA_AUDIT_STATEMENT	USER_OBJ_AUDIT_OPTS
CDB_AUDIT_MGMT_LAST_ARCH_TS	DBA_AUDIT_TRAIL	VW_X$AUD_XS_ACTIONS
CDB_AUDIT_OBJECT	DBA_COMMON_AUDIT_TRAIL
CDB_AUDIT_POLICIES	DBA_OBJ_AUDIT_OPTS
CDB_AUDIT_POLICY_COLUMNS	DBA_PRIV_AUDIT_OPTS	V_$XML_AUDIT_TRAIL
CDB_AUDIT_SESSION

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

System Privileges

AUDIT ANY

AUDIT SYSTEM

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
SQL Translation Profile	ON SQL TRANSLATION PROFILE
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 sys@pdbdev 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>;

conn sys@pdbdev as sysdba

      

      SELECT * FROM stmt_audit_option_map;

      

      SELECT * FROM audit$;

      

      AUDIT CREATE PROCEDURE;

      AUDIT CREATE TABLE;

      AUDIT CREATE TRIGGER;

      AUDIT CREATE USER;

      AUDIT CREATE VIEW;

      

      SELECT * FROM audit$;

      

      conn uwclass/uwclass@pdbdev

      

      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 sys@pdbdev as sysdba SELECT * FROM audit$; audit select on scott.emp; audit select any table whenever successful; audit select any table whenever not successful; SELECT * FROM audit$; conn scott/tiger@pdbdev SELECT COUNT(*) FROM emp; SELECT * FROM emp WHERE sal < 3000; conn sys@pdbdev as sysdba SELECT COUNT(*) FROM aud$;