| Security Advisory |
Use of this package should be monitored because it is the primary API to managing database auditing.
The built-in objects, functions and procedures, provide audit administrators the ability 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, they comprise the unified audit trail.
A bad actor using just one of the package's capabilities such as the FLUSH_UNIFIED_AUDIT_TRAIL with a single line of code can have a devastating impact on your compliance as well as your ability to monitor actions in the database.
The SQL provided in the "How Oracle Works" sections will show you how to determine which users have access to this package and we highly recommend you treat it like SYSDBA. |
| |
| Recommended Security Rules |
NEVER
- Grant EXECUTE on this package to anyone that has the ability to perform actions, through DCL (Data Control Language) or DDL (Data Definition Language) that is part of what you audit (unfortunately, be default, Oracle does)
- grant EXECUTE on this package to an administrator of Database Vault or other security options installed in your database
WITH GREAT CARE
- Monitor any calls to this package and watch for gaps in the audit trail that might have been caused by misuse of these tools
CAUTIONS
- Oracle has granted EXECUTE on this package to the EXECUTE_CATALOG_ROLE which is an example of asking the fox to protect the hen house
- EXECUTE_CATALOG_ROLE is subsequently granted to SYS, DBA, and two other roles so to monitor access to this package you need to carefully protect all of them
|
| |
| How Oracle Works |
Oracle needs to better lockdown access to this package, for example not granting EXECUTE to the EXECUTE_CATALOG_ROLE which subsequently grants EXECUTE to two other roles.
The code at right will show you the relationships and help you protect your audit trails. |
DBMS_AUDIT_MGMT is an essential tool created as part of the default installation of your database. Note that Oracle has severely restricted access to only two roles and not directly to any user.
SELECT grantee, privilege, grantor
FROM dba_tab_privs
WHERE table_name = 'DBMS_AUDIT_MGMT'
ORDER BY 1;
GRANTEE PRIVILEGE GRANTOR
-------------------- ---------- ----------
AUDIT_ADMIN EXECUTE AUDSYS
EXECUTE_CATALOG_ROLE EXECUTE AUDSYS
What we see is that a number of Oracle schemas require access to this package and Oracle has explicitly granted them EXECUTE.
This is likely intentional on Oracle's part because they "expect" that organizations interested in securing their database will drop the grant to PUBLIC.
SELECT grantee, admin_option, delegate_option, default_role, inherited
FROM dba_role_privs
WHERE granted_role = 'AUDIT_ADMIN'
ORDER BY 1;
GRANTEE ADM DEL DEF INH
------------------------------ --- --- --- ---
SYS YES NO YES NO
SELECT grantee, admin_option, delegate_option, default_role, inherited
FROM dba_role_privs
WHERE granted_role = 'EXECUTE_CATALOG_ROLE'
ORDER BY 1;
GRANTEE ADM DEL DEF INH
------------------------------ --- --- --- ---
DBA NO NO YES NO
EXP_FULL_DATABASE NO NO YES NO
IMP_FULL_DATABASE NO NO YES NO
SYS YES NO YES NO
As you can see from the above SQL, EXECUTE is granted to the AUDIT_ADMIN_ROLE which is subsequently granted to SYS.
SYS is probably the last user in the database that should have the ability to alter the audit trail as SYS has more escalated privileges than any other user.
EXECUTE is also granted to the EXECUTE_CATALOG_ROLE which again grants privileges to SYS, then grants to everyone with the DBA role,
plus to EXP_FULL_DATABASE and IMP_FULL_DATABASE which are often treated a not being highly privileged accounts even though they both have a number of escalated privileges.
If you are not paying attention to DBMS_AUDIT_MGMT then you are not paying attention to the security of your database. |
| |
| DBMS_AUDIT_MGMT Package Information |
| 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 |
| 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 |
| 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 |
| Unified Audit Trail |
| AUDIT_TRAIL_UNIFIED |
NUMBER |
51 |
| 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_USERS |
DUAL |
| AMGT$DATAPUMP |
DBMS_ASSERT |
GET_AUD_PDB_LIST |
| AUD_PDB_LIST |
DBMS_AUDIT_MGMT_LIB |
GV$INSTANCE |
| DAM_CLEANUP_JOBS$ |
DBMS_INTERNAL_LOGSTDBY |
OBJ$ |
| DAM_CONFIG_PARAM$ |
DBMS_LOCK |
PLITBLM |
| DAM_LAST_ARCH_TS$ |
DBMS_PDB_EXEC_SQL |
REGISTRY$ |
| DBA_AUDIT_MGMT_CONFIG_PARAMS |
DBMS_SCHEDULER |
V$CONTAINERS |
| DBA_FREE_SPACE |
DBMS_SQL |
V$DATABASE |
| DBA_TABLES |
DBMS_STANDARD |
V$INSTANCE |
| DBA_TABLESPACES |
DBMS_STATS |
V$OPTION |
| DBA_TAB_PARTITIONS |
DBMS_UTILITY |
V$VERSION |
|
| 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.0.7 |
| 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 |
| 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,
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', pje, dbms_audit_mgmt.container_all);
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); |
DECLARE
flt PLS_INTEGER := dbms_audit_mgmt.flush_current_instance;
con PLS_INTEGER :=
dbms_audit_mgmt.container_current;
BEGIN
dbms_audit_mgmt.flush_unified_audit_trail(flt, con);
END;
/ |
| |
| 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_P1311
SYS_P185
SYS_P2172
SYS_P2501
SYS_P3367
SYS_P3651
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')
----------------------------------------------------------
2014-07-01 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')
----------------------------------------------------------
2019-01-01 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, 24, dbms_audit_mgmt.container_all);
END;
/
-- this may take awhile to complete |
| |
| IS_CLEANUP_INITIALIZED |
returns TRUE if Audit Cleanup is initialized for the audit trail type,
otherwise FALSE
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; |
set serveroutput on
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)
AS "Ok2Drop"
FROM dual;
Ok2Drop
--------
1 |
| |
| LOAD_UNIFIED_AUDIT_FILES |
| Loads all spillover audit files to tables. container is the PDB Container in which it is executing |
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.
Note that the procedure, by default moves it to SYSAUX. Create your own tablespace in a location where it will be safe from being accidentally dropped. |
dbms_audit_mgmt.move_dbaudit_tables(audit_trail_tbs IN VARCHAR2 DEFAULT 'SYSAUX'); |
CREATE TABLESPACE audit_data
DATAFILE 'u01/orabase19/oradata/orabase/auddta.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'); |