In Oracle’s most recent release, the audit options have been expanded and modified to create the Unified Audit Trail, a ‘one-stop shop’ for recording and reporting audit information. A new view, UNIFIED_AUDIT_TRAIL, provides access to the audit details:
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- ----------------------------
AUDIT_TYPE VARCHAR2(64)
SESSIONID NUMBER
PROXY_SESSIONID NUMBER
OS_USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(30)
INSTANCE_ID NUMBER
DBID NUMBER
AUTHENTICATION_TYPE VARCHAR2(1024)
DBUSERNAME VARCHAR2(30)
DBPROXY_USERNAME VARCHAR2(30)
EXTERNAL_USERID VARCHAR2(1024)
GLOBAL_USERID VARCHAR2(32)
CLIENT_PROGRAM_NAME VARCHAR2(48)
DBLINK_INFO VARCHAR2(4000)
XS_USER_NAME VARCHAR2(128)
XS_SESSIONID RAW(33)
ENTRY_ID NUMBER
STATEMENT_ID NUMBER
EVENT_TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE
ACTION_NAME VARCHAR2(64)
RETURN_CODE NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTION_ID RAW(8)
SCN NUMBER
EXECUTION_ID VARCHAR2(64)
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SQL_TEXT CLOB
SQL_BINDS CLOB
APPLICATION_CONTEXTS VARCHAR2(4000)
CLIENT_IDENTIFIER VARCHAR2(64)
NEW_SCHEMA VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJECT_EDITION VARCHAR2(30)
SYSTEM_PRIVILEGE_USED VARCHAR2(1024)
SYSTEM_PRIVILEGE VARCHAR2(40)
AUDIT_OPTION VARCHAR2(40)
OBJECT_PRIVILEGES VARCHAR2(19)
ROLE VARCHAR2(30)
TARGET_USER VARCHAR2(30)
EXCLUDED_USER VARCHAR2(30)
EXCLUDED_SCHEMA VARCHAR2(30)
EXCLUDED_OBJECT VARCHAR2(128)
ADDITIONAL_INFO VARCHAR2(4000)
UNIFIED_AUDIT_POLICIES VARCHAR2(4000)
FGA_POLICY_NAME VARCHAR2(30)
XS_INACTIVITY_TIMEOUT NUMBER
XS_ENTITY_TYPE VARCHAR2(32)
XS_TARGET_PRINCIPAL_NAME VARCHAR2(30)
XS_PROXY_USER_NAME VARCHAR2(30)
XS_DATASEC_POLICY_NAME VARCHAR2(30)
XS_SCHEMA_NAME VARCHAR2(30)
XS_CALLBACK_EVENT_TYPE VARCHAR2(32)
XS_PACKAGE_NAME VARCHAR2(30)
XS_PROCEDURE_NAME VARCHAR2(30)
XS_ENABLED_ROLE VARCHAR2(30)
XS_COOKIE VARCHAR2(1024)
XS_NS_NAME VARCHAR2(30)
XS_NS_ATTRIBUTE VARCHAR2(4000)
XS_NS_ATTRIBUTE_OLD_VAL VARCHAR2(4000)
XS_NS_ATTRIBUTE_NEW_VAL VARCHAR2(4000)
DV_ACTION_CODE NUMBER
DV_ACTION_NAME VARCHAR2(30)
DV_EXTENDED_ACTION_CODE NUMBER
DV_GRANTEE VARCHAR2(30)
DV_RETURN_CODE NUMBER
DV_ACTION_OBJECT_NAME VARCHAR2(128)
DV_RULE_SET_NAME VARCHAR2(90)
DV_COMMENT VARCHAR2(4000)
DV_FACTOR_CONTEXT VARCHAR2(4000)
DV_OBJECT_STATUS VARCHAR2(1)
OLS_POLICY_NAME VARCHAR2(30)
OLS_GRANTEE VARCHAR2(30)
OLS_MAX_READ_LABEL VARCHAR2(4000)
OLS_MAX_WRITE_LABEL VARCHAR2(4000)
OLS_MIN_WRITE_LABEL VARCHAR2(4000)
OLS_PRIVILEGES_GRANTED VARCHAR2(30)
OLS_PROGRAM_UNIT_NAME VARCHAR2(30)
OLS_PRIVILEGES_USED VARCHAR2(128)
OLS_STRING_LABEL VARCHAR2(4000)
OLS_LABEL_COMPONENT_TYPE VARCHAR2(12)
OLS_LABEL_COMPONENT_NAME VARCHAR2(30)
OLS_PARENT_GROUP_NAME VARCHAR2(30)
OLS_OLD_VALUE VARCHAR2(4000)
OLS_NEW_VALUE VARCHAR2(4000)
RMAN_SESSION_RECID NUMBER
RMAN_SESSION_STAMP NUMBER
RMAN_OPERATION VARCHAR2(20)
RMAN_OBJECT_TYPE VARCHAR2(20)
RMAN_DEVICE_TYPE VARCHAR2(5)
DP_TEXT_PARAMETERS1 VARCHAR2(512)
DP_BOOLEAN_PARAMETERS1 VARCHAR2(512)
DIRECT_PATH_NUM_COLUMNS_LOADED NUMBER
A wealth of information is supplied, at the database and O/S level, which makes auditing tasks much easier as only one view needs to be accessed to generate an audit report. To get started the ‘Unified Auditing’ option must be enabled:
SQL> --
SQL> -- Verify unified auditing option is enabled
SQL> --
SQL> select value from v$option where parameter = 'Unified Auditing';
VALUE
----------------------------------------------------------------
TRUE
SQL>
If that value is FALSE some basic features of Unified Auditing will work but the full functionality will be disabled. To enable Unified Auditing the Oracle kernel will need to be re-linked to include Unified Auditing:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
Unified Auditing requires that an auditing policy be created against the table or tables that need to be audited. In the example below one table is created and selected for audit:
SQL>
SQL> --
SQL> -- Create a table to test the unified audit trail
SQL> --
SQL> create table audit_tst_tbl
2 as select * from dba_objects
3 where rownum < 2;
Table created.
SQL>
SQL> --
SQL> -- Create an audit policy for the table
SQL> --
SQL> CREATE AUDIT POLICY my_audit_policy
2 ACTIONS INSERT ON audit_tst_tbl,
3 UPDATE ON audit_tst_tbl;
Audit policy created.
SQL>
The policy, once created, needs to be enabled using the AUDIT statement:
SQL>
SQL> --
SQL> -- Enable the policy
SQL> --
SQL> AUDIT POLICY my_audit_policy;
Audit succeeded.
SQL>
It’s a good idea to verify that the policy is enabled and contains the desired actions:
SQL>
SQL> --
SQL> -- Now verify that the policy is enabled
SQL> --
SQL> set line 180
SQL> col user_name format a20
SQL> col policy_name format a20
SQL> select * from audit_unified_enabled_policies
2 where policy_name = 'MY_AUDIT_POLICY';
USER_NAME POLICY_NAME ENABLED_ SUC FAI
-------------------- -------------------- -------- --- ---
ALL USERS MY_AUDIT_POLICY BY YES YES
SQL>
SQL> --
SQL> -- And verify that the policy has update and insert
SQL> --
SQL> set line 180
SQL> col audit_option format a20
SQL> col object_name format a20
SQL> select policy_name, audit_option, object_name, object_type
2 from AUDIT_UNIFIED_POLICIES
3 where policy_name in ('MY_AUDIT_POLICY');
POLICY_NAME AUDIT_OPTION OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- -------------------- -----------------------
MY_AUDIT_POLICY INSERT AUDIT_TST_TBL TABLE
MY_AUDIT_POLICY UPDATE AUDIT_TST_TBL TABLE
SQL>
The first act, after configuring and enabling the policy, is to test it. This, however, can provide some confusing results when run from the session that created and enabled the policy; in this case inserts and updates can be performed with absolutely no record generated in the Unified Audit Trail:
SQL>
SQL> --
SQL> -- Insert some data
SQL> --
SQL> insert into audit_tst_tbl
2 (select * from dba_objects where rownum < 10);
9 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Update data
SQL> --
SQL> update audit_tst_tbl
2 set object_id = object_id+1000;
10 rows updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Flush the trail to get any data out
SQL> --
SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Check the trail for entries
SQL> --
SQL> -- This fails from the session that created the policy
SQL> --
SQL> -- It succeeds when executed from a new session
SQL> --
SQL> col system_privilege_used format a20
SQL> col object_name format a20
SQL> col action_name format a20
SQL> col event_timestamp format a30
SQL>
SQL> select event_timestamp, action_name, system_privilege_used, object_name
2 from unified_audit_trail
3 where (object_name = 'MY_AUDIT_POLICY'
4 or object_name = 'AUDIT_TST_TBL' )
5 and action_name in ('INSERT','UPDATE')
6 order by event_timestamp;
no rows selected
SQL>
Once the policy is created and enabled every new session that connects to the database will generate audit records if inserts, updates, or both are performed on the table configured for auditing:
SQL>
SQL> --
SQL> -- Create a new session
SQL> --
SQL>
SQL> connect bing/##############
Connected.
SQL>
SQL> --
SQL> -- Insert some data
SQL> --
SQL> insert into audit_tst_tbl
2 (select * from dba_objects where rownum < 10);
9 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Update data
SQL> --
SQL> update audit_tst_tbl
2 set object_id = object_id+1000;
19 rows updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Flush the trail to get any data out
SQL> --
SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Check the trail for entries
SQL> --
SQL> col system_privilege_used format a20
SQL> col object_name format a20
SQL> col action_name format a20
SQL> col event_timestamp format a30
SQL>
SQL> select event_timestamp, action_name, system_privilege_used, object_name
2 from unified_audit_trail
3 where (object_name = 'MY_AUDIT_POLICY'
4 or object_name = 'AUDIT_TST_TBL' )
5 and action_name in ('INSERT','UPDATE')
6 order by event_timestamp;
EVENT_TIMESTAMP ACTION_NAME SYSTEM_PRIVILEGE_USE OBJECT_NAME
------------------------------ -------------------- -------------------- --------------------
02-MAR-17 10.48.25.687388 AM INSERT AUDIT_TST_TBL
02-MAR-17 10.48.25.689887 AM UPDATE AUDIT_TST_TBL
SQL>
Other records will be generated, including records for enabling the policy, but the session that creates the audit won’t be audited for insert and update operations.
The audit trail can grow large and Oracle has provided a procedure to purge the Unified Audit Trail that can be run manually or scripted to run through DBMS_SCHEDULER or through the operating system scheduler. An example of running it manually in a non-container 12c database is shown below:
SQL> BEGIN
2 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
4 USE_LAST_ARCH_TIMESTAMP => FALSE
5 );
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
It can also process the Unified Audit Trail in container databases by using the CONTAINER parameter; that parameter accepts the following values:
DBMS_AUDIT_MGMT.CONTAINER_CURRENT
DBMS_AUDIT_MGMT.CONTAINER_ALL
Using the first processes the trail in the current container; the second processes all available containers in the CDB.
The USE_LAST_ARCH_TIMESTAMP parameter controls which records get purged, in a rather basic fashion. If the value passed is TRUE then all audit records written before the last archive timestamp are purged, leaving current records in the trail. Changing this to FALSE purges the entire trail.
It’s nice to see that Oracle has somewhat simplified the audit trail and has made it easier to audit specific actions on selected tables as well as auditing multiple tables in a single policy. You may not need this functionality now, but it’s good to know how it is configured so you’re ready when the requirement is made.
