Oracle DBMS_AUDIT

Oracle DBMS_AUDIT_MGMT Version 21c

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.

Purpose Provides utilities that enable audit administrators to manage the audit trail. In a mixed mode environment, these audit trails comprise the database, operating system (OS), and XML audit trails. In a unified auditing environment, this comprises the unified audit trail.

AUTHID DEFINER

Constants

Name	Data Type	Value
Audit Trail Types
AUDIT_TRAIL_AUD_STD	NUMBER	1
AUDIT_TRAIL_FGA_STD	NUMBER	2
AUDIT_TRAIL_DB_STD (both AUD + FGA)	NUMBER	3
AUDIT_TRAIL_OS	NUMBER	4
AUDIT_TRAIL_XML	NUMBER	8
AUDIT_TRAIL_FILES (both OS & XML)	NUMBER	12
AUDIT_TRAIL_ALL	NUMBER	15
AUDIT_TRAIL_UNIFIED	NUMBER	51
OS Audit File Configuration parameters
OS_FILE_MAX_SIZE	NUMBER	16
OS_FILE_MAX_AGE	NUMBER	17
Miscellaneous
CLEAN_UP_INTERVAL	NUMBER	21
DB_AUDIT_TABLEPSACE	NUMBER	22
DB_DELETE_BATCH_SIZE	NUMBER	23
TRACE_LEVEL	NUMBER	24
AUD_TAB_MOVEMENT_FLAG	NUMBER	25
FILE_DELETE_BATCH_SIZE	NUMBER	26
Purge Job Status values
PURGE_JOB_ENABLE	NUMBER	31
PURGE_JOB_DISABLE	NUMBER	32
NG Audit Trail write mode configuration
AUDIT_TRAIL_WRITE_MODE	NUMBER	33 Deprecated
Write Mode values
AUDIT_TRAIL_QUEUED_WRITE	NUMBER	1
AUDIT_TRAIL_IMMEDIATE_WRITE	NUMBER	2
Trace Level values
TRACE_LEVEL_DEBUG	PLS_INTEGER	1
TRACE_LEVEL_ERROR	PLS_INTEGER	2
Container Values
CONTAINER_CURRENT	NUMBER	1
CONTAINER_ALL	NUMBER	2
Flush Types
FLUSH_CURRENT_INSTANCE	NUMBER	1
FLUSH_ALL_INSTANCES	NUMBER	2
Partition Interview
DEFAULT_INTERVAL_NUMBER	NUMBER	1
DEFAULT_INTERVAL_FREQUENCY	VARCHAR2(5)	'MONTH'

Dependencies

ALL_TAB_COLS	DBA_TABLESPACES	DBMS_UTILITY
AMGT$DATAPUMP	DBA_TABLESPACE_USAGE_METRICS	DUAL
AUD_PDB_LIST	DBA_TAB_PARTITIONS	GET_AUD_PDB_LIST
CLI_TAB$	DBA_USERS	GV$INSTANCE
DAM_CLEANUP_JOBS$	DBMS_ASSERT	OBJ$
DAM_CONFIG_PARAM$	DBMS_AUDIT_MGMT_LIB	PLITBLM
DAM_LAST_ARCH_TS$	DBMS_INTERNAL_LOGSTDBY	REGISTRY$
DBA_AUDIT_MGMT_CONFIG_PARAMS	DBMS_LOCK	V$CONTAINERS
DAM_CLEANUP_JOBS$	DBMS_PDB_EXEC_SQL	V$DATABASE
DAM_CONFIG_PARAM$	DBMS_SCHEDULER	V$INSTANCE
DAM_LAST_ARCH_TS$	DBMS_SQL	V$OPTION
DBA_FREE_SPACE	DBMS_STANDARD	V$VERSION
DBA_TABLES

Documented Yes

Exceptions

Error Code	Reason
ORA-46273	DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-55906	Secure file log [id: 0 name: ORA$AUDIT_NEXTGEN_LOG] does not exist

First Available 11.1

Security Model Owned by AUDSYS with EXECUTE granted to the AUDIT_ADMIN and EXECUTE_CATALOG_ROLE roles

Source {ORACLE_HOME}/rdbms/admin/dbmsamgt.sql

Subprograms

ALTER_PARTITION_INTERVAL	GET_AUDIT_COMMIT_DELAY	LOAD_UNIFIED_AUDIT_FILES
CLEAN_AUDIT_TRAIL	GET_AUDIT_TRAIL_PROPERTY_VALUE	MOVE_DBAUDIT_TABLES
CLEAR_AUDIT_TRAIL_PROPERTY	GET_CLI_PART_ORANUM	SET_AUDIT_TRAIL_LOCATION
CLEAR_LAST_ARCHIVE_TIMESTAMP	GET_LAST_ARCHIVE_TIMESTAMP	SET_AUDIT_TRAIL_PROPERTY
CREATE_PURGE_JOB	GET_PART_HIGHVAL_AS_CHAR	SET_DEBUG_LEVEL
DEINIT_CLEANUP	INIT_CLEANUP	SET_LAST_ARCHIVE_TIMESTAMP
DROP_OLD_UNIFIED_AUDIT_TABLES	IS_CLEANUP_INITIALIZED	SET_PURGE_JOB_INTERVAL
DROP_PURGE_JOB	IS_CLEANUP_INITIALIZED2	SET_PURGE_JOB_STATUS
FLUSH_UNIFIED_AUDIT_TRAIL	IS_DROPPABLE_PARTITION	TRANSFER_UNIFIED_AUDIT_RECORDS

ALTER_PARTITION_INTERVAL

Alters the interval of partitioned table AUDSYS.AUD$UNIFIED dbms_audit_mgmt.alter_partition_interval( interval_number IN PLS_INTEGER := DEFAULT_INTERVAL_NUMBER, interval_frequency IN VARCHAR2 := DEFAULT_INTERVAL_FREQUENCY);

exec dbms_audit_mgmt.alter_partition_interval(dbms_audit_mgmt.default_interval_frequency, 'DAY');

CLEAN_AUDIT_TRAIL

Deletes entries in audit trail according to the timestamp set in set_last_archive_timestamp dbms_audit_mgmt.clean_audit_trail( audit_trail_type IN PLS_INTEGER, use_last_arch_timestamp IN BOOLEAN := TRUE, container IN PLS_INTEGER := CONTAINER_CURRENT, database_id IN NUMBER := NULL, container_guid IN VARCHAR2 := NULL);

See IS_CLEANUP_INITIALIZED Demo Below

CLEAR_AUDIT_TRAIL_PROPERTY

Clears an audit trail property dbms_audit_mgmt.clear_audit_trail_property( audit_trail_type IN PLS_INTEGER, audit_trail_property IN PLS_INTEGER, use_default_values IN BOOLEAN := FALSE);

DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_os;

       atp NUMBER := dbms_audit_mgmt.os_file_max_age; 

      BEGIN

        dbms_audit_mgmt.set_audit_trail_property(att, atp, 30);

        dbms_audit_mgmt.clear_audit_trail_property(att, atp, TRUE);

      END;

      /

CLEAR_LAST_ARCHIVE_TIMESTAMP

Deletes the timestamp set by set_last_archive_timestamp dbms_audit_mgmt.clear_last_archive_timestamp( audit_trail_type IN PLS_INTEGER, rac_instance_number IN PLS_INTEGER := NULL, container IN PLS_INTEGER, database_id IN NUMBER, container_guid IN RAW);

DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_os;

      BEGIN

        dbms_audit_mgmt.clear_last_archive_timestamp(att, NULL, dbms_audit_mgmt.container_all);

        dbms_audit_mgmt.clear_last_archive_timestamp(att, NULL, dbms_audit_mgmt.container_all);

        dbms_audit_mgmt.clear_last_archive_timestamp(att, NULL, dbms_audit_mgmt.container_all);

      END;

      /

CREATE_PURGE_JOB

Creates a purge job for an audit trail dbms_audit_mgmt.create_purge_job( audit_trail_type IN PLS_INTEGER, audit_trail_purge_interval IN PLS_INTEGER, -- hours audit_trail_purge_name IN VARCHAR2, use_last_arch_timestamp IN BOOLEAN := TRUE, container IN PLS_INTEGER := CONTAINER_CURRENT);

DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

       pje NUMBER := dbms_audit_mgmt.purge_job_enable;

      BEGIN

        dbms_audit_mgmt.create_purge_job(att, 48, 'UW_PURGE', TRUE);

        dbms_audit_mgmt.set_purge_job_interval('UW_PURGE', 48);

        dbms_audit_mgmt.set_purge_job_status('UW_PURGE', 
      dbms_audit_mgmt.purge_job_enable);

        dbms_audit_mgmt.drop_purge_job('UW_PURGE');

      END;

      /

DEINIT_CLEANUP

De-Initialize DBMS_AUDIT_MGMT dbms_audit_mgmt.deinit_cleanup( audit_trail_type IN PLS_INTEGER, container IN PLS_INTEGER := CONTAINER_CURRENT);

See IS_CLEANUP_INITIALIZED Demos Below

DROP_OLD_UNIFIED_AUDIT_TABLES

Drops the given Old Unified Audit (CLI based) tables dbms_audit_mgmt.drop_old_unified_audit_tables(container_guid IN VARCHAR2);

SELECT con_id, name, guid

      FROM v$pdbs

      ORDER BY 1;

      

      exec dbms_audit_mgmt.drop_old_unified_audit_tables('4C690F3954EC4B2FBECFAA0CFA1BD955');

DROP_PURGE_JOB

Drops the purge job for an audit trail dbms_audit_mgmt.drop_purge_job(audit_trail_purge_name IN VARCHAR2);

See CREATE_PURGE_JOB Demo Above

FLUSH_UNIFIED_AUDIT_TRAIL

Writes the unified audit trail records in the SGA queue to disk dbms_audit_mgmt.flush_unified_audit_trail( flush_type IN PLS_INTEGER := FLUSH_CURRENT_INSTANCE, container IN PLS_INTEGER := CONTAINER_CURRENT);

Deprecated

GET_AUDIT_COMMIT_DELAY

GETs the audit commit delay set in the database. The default is 15. dbms_audit_mgmt.get_audit_commit_delay RETURN PLS_INTEGER;

SELECT dbms_audit_mgmt.get_audit_commit_delay

      FROM dual;

GET_AUDIT_TRAIL_PROPERTY_VALUE

Retrieves the value of the property set by set_audit_trail_property dbms_audit_mgmt.get_audit_trail_property_value( audit_trail_type IN PLS_INTEGER, audit_trail_property IN PLS_INTEGER) RETURN NUMBER;

DECLARE

       att PLS_INTEGER := dbms_audit_mgmt.audit_trail_os;

       atp PLS_INTEGER := dbms_audit_mgmt.os_file_max_age;

       n   NUMBER;

      BEGIN

       n := dbms_audit_mgmt.get_audit_trail_property_value(att, atp);

      END;

      /

GET_CLI_PART_ORANUM

Returns the ORACLE NUMBER corresponding to the HIGH_VALUE of CLI Partition dbms_audit_mgmt.get_cli_part_oranum(partname IN VARCHAR2) RETURN NUMBER;

SELECT 
      dbms_audit_mgmt.get_cli_part_oranum('AUD_UNIFIED_P0')

      FROM dual;

GET_LAST_ARCHIVE_TIMESTAMP

Returns the timestamp set by set_last_archive_timestamp for the current instance dbms_audit_mgmt.get_last_archive_timestamp(audit_trail_type IN PLS_INTEGER) RETURN TIMESTAMP;

DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

       lat TIMESTAMP := TO_TIMESTAMP('15-MAR-17 14:42:42.00','DD-MON-YYYY HH24:MI:SS.FF');

      BEGIN

        dbms_audit_mgmt.init_cleanup(att, 24, dbms_audit_mgmt.container_all);

        dbms_audit_mgmt.set_last_archive_timestamp(att, lat, NULL);

      END;

      /

      

      DECLARE

       att PLS_INTEGER := dbms_audit_mgmt.audit_trail_xml;

       tsp TIMESTAMP;

      BEGIN

        tsp := dbms_audit_mgmt.get_last_archive_timestamp(att);

      END;

      /

      

      DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

      BEGIN

        IF dbms_audit_mgmt.is_cleanup_initialized(att, dbms_audit_mgmt.container_all) THEN

          dbms_output.put_line('Cleanup Is Initialized');

          dbms_audit_mgmt.deinit_cleanup(att, dbms_audit_mgmt.container_all);

        END IF;

      END;

      /

GET_PART_HIGHVAL_AS_CHAR

Returns the aud$unified table partition HIGH_VALUE as a VARCHAR2. dbms_audit_mgmt.get_part_highval_as_char(partname IN VARCHAR2) RETURN VARCHAR2;

SELECT partition_name

      FROM dba_tab_partitions

      WHERE table_name = 'AUD$UNIFIED';

      

      PARTITION_NAME

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

      AUD_UNIFIED_P0

      SYS_P185

      SYS_P687

      

      SELECT dbms_audit_mgmt.get_part_highval_as_char('AUD_UNIFIED_P0')

      FROM dual;

      

      DBMS_AUDIT_MGMT.GET_PART_HIGHVAL_AS_CHAR('AUD_UNIFIED_P0')

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

      2020-12-07 00:00:00

      

      SELECT dbms_audit_mgmt.get_part_highval_as_char('SYS_P687')

      FROM dual;

      

      DBMS_AUDIT_MGMT.GET_PART_HIGHVAL_AS_CHAR('AUD_UNIFIED_P0')

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

      2021-03-15 00:00:00

INIT_CLEANUP

Initializes DBMS_AUDIT_MGMT dbms_audit_mgmt.init_cleanup( audit_trail_type IN PLS_INTEGER, default_cleanup_interval IN PLS_INTEGER, container IN PLS_INTEGER := CONTAINER_CURRENT);

DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

      BEGIN

        dbms_audit_mgmt.init_cleanup(att, 31, dbms_audit_mgmt.container_all);

      END;

      /

IS_CLEANUP_INITIALIZED

Checks if Audit Cleanup is initialized for the audit trail type

Overload 1 dbms_audit_mgmt.is_cleanup_initialized( audit_trail_type IN PLS_INTEGER, container IN PLS_INTEGER) RETURN BOOLEAN;

set serveroutput on

      

      DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

      BEGIN

        IF dbms_audit_mgmt.is_cleanup_initialized(att, dbms_audit_mgmt.container_all) THEN

          dbms_output.put_line('Cleanup Is Initialized');

          dbms_audit_mgmt.set_audit_trail_location(att, 'UWDATA');

          dbms_audit_mgmt.deinit_cleanup(att, dbms_audit_mgmt.container_all);

        ELSE

          dbms_output.put_line('Cleanup Was Not Initialized');

          dbms_audit_mgmt.init_cleanup(att, 24, dbms_audit_mgmt.container_all);

          dbms_audit_mgmt.clean_audit_trail(att, TRUE, dbms_audit_mgmt.container_all);

        END IF;

      END;

      /

Overload 2 dbms_audit_mgmt.is_cleanup_initialized( audit_trail_type IN PLS_INTEGER, container IN PLS_INTEGER, uninitialized_pdbs IN dbms_sql.varchar2s) RETURN BOOLEAN;

DECLARE

       att    NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

       uipdbs dbms_sql.varchar2s;

      BEGIN

        uipdbs(1) := 'ORADEV';

        uipdbs(2) := 'ORATEST';

        IF dbms_audit_mgmt.is_cleanup_initialized(att, 2, uipdbs) THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

IS_CLEANUP_INITIALIZED2

Checks if Audit Cleanup is initialized for the audit trail type and returns VARCHAR2 type dbms_audit_mgmt.is_cleanup_initialized2( audit_trail_type IN PLS_INTEGER, container IN PLS_INTEGER := CONTAINER_CURRENT) RETURN VARCHAR2;

set serveroutput on

      

      DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

       ret VARCHAR2(5);

      BEGIN

        ret := dbms_audit_mgmt.is_cleanup_initialized2(att);

        dbms_output.put_line(ret);

      END;

      /

IS_DROPPABLE_PARTITION

If the identified aud$unified partition is droppable returns 1; otherwise 0 dbms_audit_mgmt.is_droppable_partition( partname IN VARCHAR2, lat IN TIMESTAMP) RETURN NUMBER;

SELECT dbms_audit_mgmt.is_droppable_partition('SYS_P185', SYSTIMESTAMP-16)

      FROM dual;

LOAD_UNIFIED_AUDIT_FILES

Loads all spillover audit files to tables. container is the PDB Container to execute in dbms_audit_mgmt.load_unified_audit_files( container IN PLS_INTEGER DEFAULT := CONTAINER_CURRENT);

exec dbms_audit_mgmt.load_unified_audit_files(dbms_audit_mgmt.container_current);

MOVE_DBAUDIT_TABLES

Moves DB audit tables to specified tablespace dbms_audit_mgmt.move_dbaudit_tables(audit_trail_tbs IN VARCHAR2 DEFAULT 'SYSAUX');

CREATE TABLESPACE audit_data

      DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\ORABASE\AUDDATA.DBF' SIZE 100M

      LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K

      SEGMENT SPACE MANAGEMENT AUTO;

      

      exec dbms_audit_mgmt.move_dbaudit_tables('audit_data');

SET_AUDIT_TRAIL_LOCATION

Sets the destination for an audit trail
Audit trail types are AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_FGA_STD, and AUDIT_TRAIL_DB_STD dbms_audit_mgmt.set_audit_trail_location( audit_trail_type IN PLS_INTEGER, audit_trail_location_value IN VARCHAR2); -- tablespace name

See IS_CLEANUP_INITIALIZED Demo Above

SET_AUDIT_TRAIL_PROPERTY

Sets an property of an audit trail dbms_audit_mgmt.set_audit_trail_property( audit_trail_type IN PLS_INTEGER, audit_trail_property IN PLS_INTEGER, audit_trail_property_value IN PLS_INTEGER);

See CLEAR_AUDIT_TRAIL_PROPERTY Demo Above

SET_DEBUG_LEVEL

Sets the debug level for tracing dbms_audit_mgmt.set_debug_level(debug_level IN PLS_INTEGER := TRACE_LEVEL_ERROR);

exec dbms_audit_mgmt.set_debug_level(dbms_audit_mgmt.trace_level_error);

SET_LAST_ARCHIVE_TIMESTAMP

Sets the timestamp when the last audit records were archived dbms_audit_mgmt.set_last_archive_timestamp( audit_trail_type IN PLS_INTEGER, last_archive_time IN TIMESTAMP, rac_instance_number IN PLS_INTEGER := NULL, container IN PLS_INTEGER := NULL, database_id IN NUMBER := NULL, container_guid IN VARCHAR2 := NULL);

DECLARE

       att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;

       lat TIMESTAMP := TO_TIMESTAMP('14-OCT-14 14:42:42.00','DD-MON-YYYY HH24:MI:SS.FF');

      BEGIN

        dbms_audit_mgmt.set_last_archive_timestamp(att, lat, NULL);

      END;

      /

SET_PURGE_JOB_INTERVAL

Set the interval of the purge job dbms_audit_mgmt.set_purge_job_interval( audit_trail_purge_name IN VARCHAR2, audit_trail_interval_value IN PLS_INTEGER);

See CREATE_PURGE_JOB Demo Above

SET_PURGE_JOB_STATUS

Set the status of the purge job dbms_audit_mgmt.set_purge_job_status( audit_trail_purge_name IN VARCHAR2, audit_trail_status_value IN PLS_INTEGER);

See CREATE_PURGE_JOB Demo Above

TRANSFER_UNIFIED_AUDIT_RECORDS

Transfers audit records from the common logging infrastructure (CLI) swap table to the AUDSYS.AUD$UNIFIED relational table dbms_audit_mgmt.transfer_unified_audit_records( container_guid IN VARCHAR2 DEFAULT CONTAINER_CURRENT);

exec 
      dbms_audit_mgmt.transfer_unified_audit_records;