CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_AUDIT_MGMT

Version 11.1
 
General Information

Note
The following is contained in the file header:
The package can be split into two packages - one intended for use by AV collectors and the one by Audit Admin.
The first 3 procedures will be mainly used by the Collectors and the rest must be executed by Audit Admins.
Alternately, wrapper packages can be written to achieve this Seperation of Duty.
Source {ORACLE_HOME}/rdbms/admin/dbmsamgt.sql
First Available 11.1.0.7

Constants
Name Data Type Value
Audit Trail Types
AUDIT_TRAIL_AUD_STD NUMBER 1
AUDIT_TRAIL_FGA_STD NUMBER 2
AUDIT_TRAIL_OS NUMBER 4
AUDIT_TRAIL_XML NUMBER 8
Both Audit Trail Types 1 and 2
AUDIT_TRAIL_DB_STD NUMBER 3
Both Audit Trail Types 4 and 8
AUDIT_TRAIL_FILES NUMBER 12
All Audit Trail Types
AUDIT_TRAIL_ALL NUMBER 15
OS Audit File Configuration Parameters
OS_FILE_MAX_SIZE NUMBER 16
OS_FILE_MAX_AGE NUMBER 17
Property Parameters
CLEAN_UP_INTERVAL NUMBER 21
DB_AUDIT_TABLEPSACE NUMBER 22
DB_DELETE_BATCH_SIZE NUMBER 23
TRACE_LEVEL NUMBER 24
Values for PURGE_JOB_STATUS
PURGE_JOB_ENABLE NUMBER 31
PURGE_JOB_DISABLE NUMBER 32
Values for TRACE_LVEL
TRACE_LEVEL_DEBUG PLS_INTEGER 1
TRACE_LEVEL_ERROR PLS_INTEGER 2

Dependencies
ALL_TAB_COLS DBA_TAB_PARTITIONS
DAM_CLEANUP_EVENTS$ DBA_USERS
DAM_CLEANUP_JOBS$ DBMS_ASSERT
DAM_CONFIG_PARAM$ DBMS_AUDIT_MGMT_LIB
DAM_LAST_ARCH_TS$ DBMS_SCHEDULER
DBA_AUDIT_MGMT_CLEAN_EVENTS DBMS_STATS
DBA_AUDIT_MGMT_CLEANUP_JOBS DUAL
DBA_AUDIT_MGMT_CONFIG_PARAMS GV$INSTANCE
DBA_AUDIT_MGMT_LAST_ARCH_TS OBJ$
DBA_FREE_SPACE PLITBLM
DBA_TABLES V$VERSION
DBA_TABLESPACES  
Object Privileges Execute is granted to the EXECUTE_CATALOG_ROLE role.
 
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);
See IS_CLEANUP_INITIALIZED Demo Below
 
CLEAR_AUDIT_TRAIL_PROPERTY

Clears an property of an audit trail
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 := 0);
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_os;
BEGIN
  dbms_audit_mgmt.clear_last_archive_timestamp(att, 1);
  dbms_audit_mgmt.clear_last_archive_timestamp(att, 2);
  dbms_audit_mgmt.clear_last_archive_timestamp(att, 3);
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,
audit_trail_purge_name     IN VARCHAR2,
use_last_arch_timestamp    IN BOOLEAN := TRUE);
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');
  dbms_audit_mgmt.set_purge_job_interval('UW_PURGE', 48);
  dbms_audit_mgmt.set_purge_job_status('UW_PURGE', pje);
  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);
See IS_CLEANUP_INITIALIZED Demos Below
 
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
 
GET_AUDIT_COMMIT_DELAY
GETs the audit commit delay set in the database. The default is 5. dbms_audit_mgmt.get_audit_commit_delay RETURN PLS_INTEGER;
SELECT dbms_audit_mgmt.get_audit_commit_delay
FROM dual;
 
INIT_CLEANUP

Initializes DBMS_AUDIT_MGMT
dbms_audit_mgmt.init_cleanup(
audit_trail_type         IN PLS_INTEGER,
default_cleanup_interval IN PLS_INTEGER);
See IS_CLEANUP_INITIALIZED Demo Below
 
IS_CLEANUP_INITIALIZED

Checks if Audit Cleanup is initialized for the audit trail type.
dbms_audit_mgmt.is_cleanup_initialized(audit_trail_type 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) THEN
    dbms_audit_mgmt.set_audit_trail_location(att, 'UWDATA');
    dbms_audit_mgmt.deinit_cleanup(att);
    dbms_output.put_line('Cleanup Is Initialized');
  ELSE
    dbms_audit_mgmt.init_cleanup(att, 24);
    dbms_audit_mgmt.clean_audit_trail(att, TRUE);
    dbms_output.put_line('Cleanup Was Not Initialized');
  END IF;
END;
/
 
SET_AUDIT_TRAIL_LOCATION

Sets the destination for an audit trail
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type           IN PLS_INTEGER,
audit_trail_location_value IN VARCHAR2);   -- tablespace name
See IS_CLEANUP_INITIALZIED 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 := 0);
DECLARE
 att NUMBER := dbms_audit_mgmt.audit_trail_aud_std;
 lat TIMESTAMP := TO_TIMESTAMP('10-MAR-08 14:42:42.00','DD-MON-YYYY HH24:MI:SS.FF');
BEGIN
  dbms_audit_mgmt.set_LAST_ARCHIVE_TIMESTAMP(att, lat, 1);
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
 
Related Topics
Audit Vault
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [260 users online]    © 2010 psoug.org