Oracle | DBMS_AUDIT

Oracle DBMS_AUDIT_MGMT Version 21c

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.

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: Packages and Types Reference

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;