Pete Finnigan's Oracle Security Weblog

This is a short blog on Unified auditing and the so called Mixed Mode. If you use Oracle from 12c to 21c then by default Unified Auditing starts in Mixed Mode. Unified Auditing needs to be re-linked to enable unified auditing pure mode where only unified auditing is working and enabled.

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 #

We have a product PFCLATK that is now version 5.0.64.1506 but started out back in 2009 as a way to get customers to deploy useful audit trails quickly. It was perceived then and still now that adding audit trails or activity monitoring to an Oracle database is a big and onerous task and a threat to performance of the database so often the easy answer is to not bother. Hmmm.

Back in 2009 we are asked to help a customer who needed a comprehensive audit trail but it must be a one-click install (one script in SQL*Plus) and must be managed by itself and send out alerts only when necessary whilst not growing too large. They had no staff to design or manage an audit trail. We created PFCLATK as SQL and PL/SQL toolkit at the time to satisfy this requirement and it has been used in many sites to implement audit trails without too much resources in terms of people or machines/storage etc.

PFCATK has a lot of features but the main one is still the first that it can be configured in minutes and deployed with one script and be useful straight away. It detects all sorts of issues from SQL Injection to security changes to privilege escalation and more. The PFCLATK features page has more details as well as the PFCLATK home page linked above.

Above shows some sample alerts from my 21c database.

If your database is breached then often in our experience it happened a long time ago and without an audit trail it is difficult to perform a live response or forensic analysis with tools such as PFCLForensics. This is why it is important to get an audit trail up and running in every database to detect abuse or potential hacks or breaches.

PFCLATK has some additional extra features. One special features is the ability to SCORE the database for security. We can also SCORE the alerts as well. The security of the database needs to stay high and the alerts SCORE needs to stay low:

PFCLATK can be deployed quickly and it can be installed and forgotten and it just sits there doing its thing sending out alerts.

Because we can score the database security and alerts security adaptive security and adaptive auditing can be used. Think of defcon 5 going to defcon 1. We detect the change and can turn on more auditing automatically. The power of this means that under normal quiet circumstances sufficient audit is collected but is something is deemed to be going on the audit can be increased to collect more. This means we can detect an attack and collect more details to help understand the attack.

Another feature of PFCLATK is the ability to act as a Black Box Flight Recorder so that a snapshot of the audit trails and alerts can be downloaded or written on a regular basis and also on detection of a serious even. This can then be used to aid forensic analysis of a database.

#oraclace #sym_42 #oracle #database #security #audit #databreach #hacking #forensics #liveresponse #audittrail #sqlinjection #privilege #escalation #activity #monitoring

Emad just made a blog post - Oracle 23ai Privilege Escalation From GRANT ANY ROLE to DBA Role - that shows how he escalated from GRANT ANY ROLE to DBA.

There are some issues with his example but I will come back to that in a minute.

Emad states in the post that Oracle do not accept his example as a security exploit. I agree with Oracle; it is just the way Oracle works and I will explain why in a minute.

The issue is that people think the goal is DBA but its lots of roles and permissions, even single ones are just as dangerous as DBA as I show in my Oracle Security training. A good example is ALTER USER. If i have ALTER USER I can change another users password, connect as that user and then use their access and rights. This is an escalation BUT its not an exploit as I gave a user ALTER USER.

A good example would be if i gave someone a key to my house and then they entered my house using the key and stole everything; the real problem is that I gave them the key.

Another example could be that I have CREATE ANY PROCEDURE and i can overwrite a procedure in another schema and then in my new procedure I can use privileges of the owner, i.e. steal them. There are protections to limit this and if we are in a PDB we cannot exploit a common user.

A real world example of this is i give a car key to someone and in that car there is the key to my house. If the person with the car key accesses the car then they can find the house key and access my house.

In general in Oracle the issue is not escalation to DBA; there are so many individual rights that could be exploited. Maybe an attacker does not need DBA but just needs SELECT on a specific table. In general every user should have exactly the privileges necessary and no more; i.e. least privilege.

GRANT ANY ROLE is an example of a potential privilege which I also cover in my Oracle Security training. If a user has a privilege (for example GRANT ANY ROLE) then he also potentially has all the privileges he can grant himself by granting himself every role in the database. This is the way Oracle works; we need a privilege to grant any role and there are serious implications with this being granted to anyone!

On to Emad's example. Let us re-run his example:

C:\del_xps\c\mac_nov_2019\____atk\5_0_64_1506>sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 3 08:52:49 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

SQL> @cs
Connected.
USER is "SYS"
SQL> create user tom identified by tom123;

User created.

SQL> grant create session, grant any role to tom;

Grant succeeded.

SQL> alter user tom default role all;

User altered.

SQL> connect tom/tom123@//192.168.56.33:1539/xepdb1
Connected.

SQL> grant dba to tom;
grant dba to tom
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> grant dba to hr;
grant dba to hr
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create user tom2 identified by tom123;
create user tom2 identified by tom123
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> select * from orablog.bof_pay_details;
select * from orablog.bof_pay_details
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> grant imp_full_database to tom;

Grant succeeded.

SQL>
SQL> set role all
  2  /

Role set.

SQL> select * from orablog.bof_pay_details;

        ID PAYMENT_ID
---------- ----------
NAME_ON_CARD
--------------------------------------------------------------------------------
CC34
--------------------------------------------------------------------------------
START_DAT END_DATE  LAST
--------- --------- ----
         1          1
Mr David Bentley
C795E9199A78988F3D375D5297AED40342AAF4A32FE28A2D
01-FEB-11 01-AUG-16 3457


        ID PAYMENT_ID
---------- ----------
NAME_ON_CARD
--------------------------------------------------------------------------------
CC34
--------------------------------------------------------------------------------
START_DAT END_DATE  LAST
--------- --------- ----
         2          2
Mr Martin Chisholm
E634E4CF55C484B4E8924F5CF3C79D29D68ACDD2FC06F8BC
01-APR-12 01-OCT-16 6678


SQL>
SQL> create user tom identified by tom123;

User created.

SQL>
SQL> grant dba to tom;

Grant succeeded.

SQL>

problem 1 - So, we did not need to grant DBA to TOM as we could read the data in the ORABLOG.BOF_PAY_DETAILS table and create TOM2 after we granted IMP_FULL_DATABASE as this role has CREATE USER and SELECT ANY TABLE. We didn't need DBA to do the things.

As you can see we did grant DBA as Emad did but why? This is the real question. What does IMP_FULL_DATABASE have that allows us to grant DBA when GRANT ANY ROLE did not allow us to GRANT DBA?

Problem 2 - This is easy; the extra privilege needed is GRANT ANY PRIVILEGE as some privileges in DBA need this to be granted. So run the example again after dropping the user TOM:

SQL> grant create session, grant any role, grant any privilege to tom;

Grant succeeded.

SQL>

SQL> connect tom/tom123@//192.168.56.33:1539/xepdb1
Connected.
SQL> grant dba to tom;

Grant succeeded.

SQL>

So there are two points, we didn't need DBA to select and create users; IMP_FULL_DATABASE allowed that and the reason we could not grant DBA when we had GRANT ANY ROLE is that we also needed GRANT ANY PRIVILEGE.

This is the way Oracle works; we must understand least privileges and not grant any sweeping rights that allow privilege propagation. I have been teaching this to attendees of my Oracle security classes for years. Don't get hung up on the DBA role; IMP_FULL_DATABASE is just as good for an attacker

#oracleace @oracleace #sym_42 #oracle #security #privilege #escalation #roles #grants #databreach #hacking #training