Oracle Auditing
Version 18.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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_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 GV$UNIFIED_AUDIT_TRAIL
AUDIT_NG$ CDB_XS_AUDIT_POLICY_OPTIONS GV$XML_AUDIT_TRAIL
AUDIT_UNIFIED_ENABLED_POLICIES CDB_XS_AUDIT_TRAIL INT$AUDIT_UNIFIED_ENABLED_POL
AUDIT_UNIFIED_POLICIES CDB_XS_ENABLED_AUDIT_POLICIES INT$AUDIT_UNIFIED_POLICIES
AUDIT_UNIFIED_POLICY_COMMENTS DBA_AUDIT_EXISTS SM$AUDIT_CONFIG
AUDTAB$TBS$FOR_EXPORT DBA_AUDIT_MGMT_CLEANUP_JOBS UNIFIED_AUDIT_TRAIL
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 USER_AUDIT_POLICIES
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 V_$UNIFIED_AUDIT_RECORD_FORMAT
CDB_AUDIT_POLICIES DBA_OBJ_AUDIT_OPTS V_$UNIFIED_AUDIT_TRAIL
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>;

AUDIT NETWORK;
conn sys@pdbdev as sysdba

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


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

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


conn scott/tiger@pdbdev

SELECT COUNT(*)
FROM emp;

SELECT *
FROM emp
WHERE sal < 3000;

conn sys@pdbdev as sysdba

SELECT COUNT(*)
FROM aud$;

Related Topics
Audit Policies
Audit Vault
Built-in Functions
Built-in Packages
DBMS_AUDIT_MGMT
DBMS_FGA
Fine Grained Auditing
Security
Unified Audit Policies
What's New In 18c
What's New In 19c
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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx