We can check the status of Unified auditing as follows:
SQL> select value from v$option where parameter='Unified Auditing';
VALUE
----------------------------------------------------------------
FALSE
SQL>
FALSE means in my database that Unified Auditing is in Mixed mode. This means it works and generates audit records for policies that are enabled. We can do an action that is caught by my audit trail:
SQL> create user aud3 identified by aud3;
User created.
SQL>
Checking the unified audit trail we can find:
SQL> @sc_print 'select * from unified_audit_trail where action_name=''''CREATE USER'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from unified_audit_trail where action_name=''CREATE USER''','''','''''');
Executing Query [select * from unified_audit_trail where action_name='CREATE
USER']
AUDIT_TYPE : Standard
SESSIONID : 1801490805
PROXY_SESSIONID : 0
OS_USERNAME : pete
USERHOST : WORKGROUP\OFFICE-HACKER
TERMINAL : OFFICE-HACKER
INSTANCE_ID : 1
DBID : 254274359
AUTHENTICATION_TYPE : (TYPE=(DATABASE));(CLIENT
ADDRESS=((PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=64925)));
DBUSERNAME : SYS
DBPROXY_USERNAME :
EXTERNAL_USERID :
GLOBAL_USERID :
CLIENT_PROGRAM_NAME : sqlplus.exe
DBLINK_INFO :
XS_USER_NAME :
XS_SESSIONID :
ENTRY_ID : 4
STATEMENT_ID : 75
EVENT_TIMESTAMP : 21-JUL-25 14.39.32.654441
EVENT_TIMESTAMP_UTC : 21-JUL-25 13.39.32.654441
ACTION_NAME : CREATE USER
RETURN_CODE : 0
OS_PROCESS : 21361
TRANSACTION_ID : 0000000000000000
SCN : 41014126
EXECUTION_ID :
OBJECT_SCHEMA :
OBJECT_NAME : AUD3
SQL_TEXT : create user aud3 identified by *
SQL_BINDS :
APPLICATION_CONTEXTS :
CLIENT_IDENTIFIER :
NEW_SCHEMA :
NEW_NAME :
OBJECT_EDITION :
SYSTEM_PRIVILEGE_USED : SYSDBA, CREATE USER
SYSTEM_PRIVILEGE :
AUDIT_OPTION :
OBJECT_PRIVILEGES :
ROLE :
TARGET_USER :
EXCLUDED_USER :
EXCLUDED_SCHEMA :
EXCLUDED_OBJECT :
CURRENT_USER : SYS
ADDITIONAL_INFO :
UNIFIED_AUDIT_POLICIES : ORA_SECURECONFIG, EVE_1_5
FGA_POLICY_NAME :
XS_INACTIVITY_TIMEOUT :
XS_ENTITY_TYPE :
XS_TARGET_PRINCIPAL_NAME :
XS_PROXY_USER_NAME :
XS_DATASEC_POLICY_NAME :
XS_SCHEMA_NAME :
XS_CALLBACK_EVENT_TYPE :
XS_PACKAGE_NAME :
XS_PROCEDURE_NAME :
XS_ENABLED_ROLE :
XS_COOKIE :
XS_NS_NAME :
XS_NS_ATTRIBUTE :
XS_NS_ATTRIBUTE_OLD_VAL :
XS_NS_ATTRIBUTE_NEW_VAL :
DV_ACTION_CODE :
DV_ACTION_NAME :
DV_EXTENDED_ACTION_CODE :
DV_GRANTEE :
DV_RETURN_CODE :
DV_ACTION_OBJECT_NAME :
DV_RULE_SET_NAME :
DV_COMMENT :
DV_FACTOR_CONTEXT :
DV_OBJECT_STATUS :
OLS_POLICY_NAME :
OLS_GRANTEE :
OLS_MAX_READ_LABEL :
OLS_MAX_WRITE_LABEL :
OLS_MIN_WRITE_LABEL :
OLS_PRIVILEGES_GRANTED :
OLS_PROGRAM_UNIT_NAME :
OLS_PRIVILEGES_USED :
OLS_STRING_LABEL :
OLS_LABEL_COMPONENT_TYPE :
OLS_LABEL_COMPONENT_NAME :
OLS_PARENT_GROUP_NAME :
OLS_OLD_VALUE :
OLS_NEW_VALUE :
RMAN_SESSION_RECID :
RMAN_SESSION_STAMP :
RMAN_OPERATION :
RMAN_OBJECT_TYPE :
RMAN_DEVICE_TYPE :
DP_TEXT_PARAMETERS1 :
DP_BOOLEAN_PARAMETERS1 :
DP_WARNINGS1 :
DIRECT_PATH_NUM_COLUMNS_LOADED:
RLS_INFO :
KSACL_USER_NAME :
KSACL_SERVICE_NAME :
KSACL_SOURCE_LOCATION :
PROTOCOL_SESSION_ID :
PROTOCOL_RETURN_CODE :
PROTOCOL_ACTION_NAME :
PROTOCOL_USERHOST :
PROTOCOL_MESSAGE :
DB_UNIQUE_NAME : XE
OBJECT_TYPE :
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
So, a unified audit record was created for the CREATE USER command that I issued.
Now check the standard audit settings:
SQL> sho parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/oracle/admin/XE/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_systemlog string
SQL>
As we can see SYSDBA audit is OFF but the standard audit trail setting audit_trail is set to DB, so is turned on. Are there any standard audit rules:
SQL> select count(*) from dba_stmt_audit_opts;
COUNT(*)
----------
257
SQL> select count(*) from dba_priv_audit_opts;
COUNT(*)
----------
242
SQL> select count(*) from dba_obj_audit_opts;
COUNT(*)
----------
25
SQL>
In summary, yes, quite a lot of standard audit as well as the around 30 unified audit policies we have set up in this database. If we turn off standard audit what happens to unified audit. First lets turn off standard audit:
SQL> alter session set container=cdb$root;
Session altered.
SQL>
SQL> alter system set audit_trail=none scope=spfile;
System altered.
SQL>
After restart of the database check the audit_trail parameter again:
SQL> sho parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/oracle/admin/XE/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
unified_audit_common_systemlog string
unified_audit_systemlog string
SQL>
Now the standard audit trail is turned off. First truncate the unified audit trail:
SQL> get del
1 select count(*) from unified_audit_trail
2 /
3 begin
4 dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified
5 ,use_last_arch_timestamp=>false);
6 end;
7 /
8* select count(*) from unified_audit_trail
9 .
SQL> @del
COUNT(*)
----------
1
PL/SQL procedure successfully completed.
COUNT(*)
----------
0
SQL>
This immediately is interesting as when we truncate the UNIFIED AUDIT TRAIL there should be an audit record created there that captures this clear event. i.e. unified audit automatically generates an audit record if the audit trail is cleared. We removed one record from UNIFIED_AUDIT_TRAIL with DBMS_AUDIT_MGMT but the delete/clean record was not created. Now lets try and create a database user as before:
SQL> create user aud4 identified by aud4;
User created.
SQL>
Now, check the unified audit trail to see if the CREATE USER was captured.
SQL> set serveroutput on
SQL> @sc_print 'select * from unified_audit_trail where action_name=''''CREATE USER'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from unified_audit_trail where action_name=''CREATE USER''','''','''''');
Executing Query [select * from unified_audit_trail where action_name='CREATE
USER']
PL/SQL procedure successfully completed.
SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
0
SQL>
No audit record.
So, yes by default Oracle is in Mixed Mode after 12c to 23c when standard audit is removed BUT UNIFIED AUDIT only works in Mixed Mode if the existing standard audit is still turned on.
Be aware of this if you create unified policies without enabling pure mode. If the standard audit is disabled then so is your Unified audit trail!
#oracleace #sym_42 #unified #audit #oracle #database #security #audittrail #audit #
