Pete Finnigan's Oracle Security Weblog

The internal storage for the unified audit trail in the Oracle database since 12c is stored in the table AUDSYS.AUD$UNIFIED and it is interesting to note that the name of the audit trail storage in standard audit is SYS.AUD$. Unified audit was a secure file and memory queues when first released in 12c but moved to a relational table later and also moved to the AUDSYS schema. The table name is AUD$ + UNIFIED i.e. AUD$UNIFIED, interesting progression from standard audit.

The view UNIFIED_AUDIT_TRAIL is now a UNION ALL on AUD$UNIFIED and GV$UNIFIED_AUDIT_TRAIL.

We can see in the definition of the table AUD$UNIFIED that the SQL and binds are held in a secure file still ( here is a snippet from $ORACLE_HOME/rdbms/admin/catuat.sql) :

CREATE TABLE AUDSYS.AUD$UNIFIED (
 INST_ID                                    NUMBER,
 ...
 OBJECT_TYPE                                NUMBER
 )
 LOB (SQL_TEXT, SQL_BINDS, RLS_INFO) STORE AS SECUREFILE (TABLESPACE SYSAUX)
 PARTITION BY RANGE (EVENT_TIMESTAMP) INTERVAL(INTERVAL '1' DAY)
 (PARTITION aud_unified_p0 VALUES LESS THAN
 (TO_TIMESTAMP('2014-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
 TABLESPACE SYSAUX) TABLESPACE SYSAUX;

Also of interest is the fact that this table is partitioned even if the customer does not have a partitioning license. We can see this in the SQL file (another snippet from $ORACLE_HOME/rdbms/admin/catuat.sql):

...
em Project 46892 - Introduce a new relational table AUDSYS.AUD$UNIFIED.
Rem Structure of this table should be same as that of GV$UNIFIED_AUDIT_TRAIL.
Rem ER 13716158 - Add CURRENT_USER column to capture the effective user name
Rem Bug 24974960 - Make this table as always partitioned, irrespective of db
Rem editions
Rem
Rem ER 30532917 - Make one day as default interval partition for
Rem AUDSYS.AUD$UNIFIED
-- Turns off partition check --
-- We would like to create a partitioned table even when Partitioning Option
-- is not Enabled.
alter session set events  '14524 trace name context forever, level 1';
...
create table...
...
-- Turns on partition check --
alter session set events  '14524 trace name context off';

grant read on sys.gv_$unified_audit_trail to audsys;
grant read on sys.all_unified_audit_actions to PUBLIC;

Rem Project 46892
Rem UNIFIED_AUDIT_TRAIL is now UNION ALL on gv$unified_audit_trail and
Rem new relational table AUDSYS.AUD$UNIFIED

So, the check for partitioning is controlled by an event so that the lack of a license in this case can be ignored. Also note that the table is created by SYS in the AUDSYS schema.

The focus of this set of articles though is the security of the audit trail and user. There are two main facts about the unified audit trail and these are 1) no one can log into the schema owner AUDSYS and 2) the audit trail itself is READONLY - which is not strictly true as it allows INSERT by the audit C code itself in the database engine as audit records are added and the DBMS_AUDIT_MGMT package can be used to truncate/delete based on factors such as delete records older than a date in the past. BUT, its not possible to delete specific records in the audit trail as could be done in the past with AUD$ direct via SQL

It is also worth pointing out a third security aspect is that parts of the audit trail can be written to SYSLOG since 18c. The record lengths are limited to avoid going over the 1024 line length set by the syslog RFC. Because the logs can be written to syslog it means that even if an attacker manages to find a way to circumvent the security of AUDSYS and AUD$UNIFIED the remaining audit trail can be verified against events written to syslog.

Connect as AUDSYS

So, Oracle has made AUDSYS so that it cannot be connected as. The creation of the user is without a password and locked as we can see in this snippet from $ORACLE_HOME/rdbms/admin/dsec.bsq:

REM LRG 22544275: Having no default tablespace for AUDSYS user could result in
REM ORA-01950 for temp object creations during say DBMS_REDEFINITION
create user AUDSYS no authentication account lock default tablespace SYSAUX
/
revoke inherit privileges on user AUDSYS from PUBLIC
/
alter user AUDSYS quota unlimited on SYSAUX
/
grant create table to AUDSYS
/

Nothing special or extra here that would stop a login after a password is added and unlocked. Let us try:

SQL> alter user audsys account unlock;
alter user audsys account unlock
*
ERROR at line 1:
ORA-65146: account cannot be unlocked in a PDB while it is locked in the root


SQL>

SQL> alter session set container = cdb$root;

Session altered.

SQL> alter user audsys account unlock;

User altered.

SQL> 

SQL> alter user audsys identified by audsys;

User altered.

SQL>

SQL> connect audsys/audsys@//192.168.56.33:1539/xepdb1
ERROR:
ORA-46370: cannot connect as AUDSYS user


Warning: You are no longer connected to ORACLE.
SQL>

We cannot unlock or add a password for the AUDSYS user in the PDB as it is a COMMON user. So we did it in the root container and tried to log in but get an ORA-46370 error.

How does this work? :

SQL> set serveroutput on
SQL> @sc_print 'select * from dba_users where username=''''AUDSYS'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from dba_users where username=''AUDSYS''','''','''''');
Executing Query [select * from dba_users where username='AUDSYS']
USERNAME                      : AUDSYS
USER_ID                       : 8
PASSWORD                      :
ACCOUNT_STATUS                : LOCKED
LOCK_DATE                     : 20-JAN-22
EXPIRY_DATE                   :
DEFAULT_TABLESPACE            : SYSAUX
TEMPORARY_TABLESPACE          : TEMP
LOCAL_TEMP_TABLESPACE         : TEMP
CREATED                       : 17-AUG-21
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             :
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : NONE
PROXY_ONLY_CONNECT            : N
COMMON                        : YES
LAST_LOGIN                    :
ORACLE_MAINTAINED             : Y
INHERITED                     : YES
DEFAULT_COLLATION             : USING_NLS_COMP
IMPLICIT                      : NO
ALL_SHARD                     : NO
EXTERNAL_SHARD                : NO
PASSWORD_CHANGE_DATE          :
MANDATORY_PROFILE_VIOLATION   : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

The user details above do not show anything special that stands out and the creation of the user from the .bsq scripts did not show anything either so we must assume that Oracle internally checks if the connection is as AUDSYS and block it.

NOTE: Because Oracle do not want us to connect as AUDSYS we must assume that if we could connect as AUDSYS there is probably a way to either directly update audit records or delete them OR there is a way to change or nullify the protection on the audit trail

One other factor of AUDSYS is that it is dictionary protected in the same way as SYS. So that privileges with the keyword %ANY% do not work on AUDSYS objects. This is another layer of protection that would be harder to achieve without this feature. In fact it would need a Database Vault Realm. This is the feature that the O7_DICTIONARY_ACCESSIBILITY was used for in earlier releases. in 23c there is now a fine grained DICTIONARY PROTECTION that can be added to Oracle Maintained users.

In the case of AUDSYS then Oracle clearly do not want to allow %ANY% access to the audit trails.

We can prove this is the case. First create a user with SELECT ANY TABLE and check if we can access AUDSYS.AUD$UNIFIED:

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

User created.

SQL> grant create session, select any table to aud1;

Grant succeeded.

SQL>
SQL> connect aud1/aud1@//192.168.56.33:1539/xepdb1
Connected.
SQL> select count(*) from audsys.aud$unified;
select count(*) from audsys.aud$unified
                            *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

We cannot access the table with SELECT ANY TABLE. What if we have SEELCT ANY DICTIONARY:

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

User created.

SQL> grant create session, select any dictionary to aud2;

Grant succeeded.

SQL>
SQL> connect aud2/aud2@//192.168.56.33:1539/xepdb1
Connected.
SQL> select count(*) from audsys.aud$unified;

  COUNT(*)
----------
     29743

SQL>

Yes, we can. So AUDSYS is treated like SYS but there is no obvious syntax in its creation to allow this so again probably hard coded in the C engine of Oracle.

Can we do DML/DDL on AUDSYS.AUD$UNIFIED?

First lets see if we can truncate the unified audit trail directly:

SQL> truncate table audsys.aud$unified;
truncate table audsys.aud$unified
                      *
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table
"AUDSYS"."AUD$UNIFIED".


SQL>

No, we are not allowed to truncate the AUD$UNIFIED table. What if we try and delete from the table:

SQL> delete from audsys.aud$unified;
delete from audsys.aud$unified
                   *
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table
"AUDSYS"."AUD$UNIFIED".


SQL>

This is interesting as we cannot directly change the AUD$UNIFIED table. I am aware of many possible ways this could be achieved and we will look at how Oracle might have implemented this in the next part as well as checking if certain technologies were used.

Why am I interested in the AUDSYS user and AUDSYS.AUD$UNIFIED table? it should be obvious really. If Oracle have ways in the database to create a user that cannot be logged into and where an asset can be protected or used only in ways that we allow then I want to do the same for my customers.

In my Secure Coding in PL/SQL Class I discuss a design pattern to secure something in the Oracle database. This could be a privilege, a resource, access externally or a database object. I have taught and used this design pattern for more than 15 years so when I see Oracle doing similar I want to know more. It looks that oracle use internal protections BUT it would be good if we could use the exact same design patterns.

If I was Oracle and i wanted to protect something like this i would probably hard code the rules in the C or at much better use some internal security policies that are not normally visible so that the rules and options can be extended in the future. For instance internal security policies can be as close to the C as possible and not end user/customer configurable but it means more rules can be added more easily. If we use standard database features to achieve the same then there is always a way to turn it off.

But, that is why I am interested. I want to have secure ways to block or control things BUT if Oracle made it generic someone can turn it off.

#oracleace #sym_42 #oracle #security #audit #trail #hacking #databreach #audittrail #unifiedaudit #grants #permissions #readonly

During testing of the DDL capture trigger to see if we can react to disable or enable of a trigger I created a USER and there is a silent GRANT done
whilst creating the user.

I have come across something similar to this many many years ago as when the RESOURCE role was granted to a user they silently were granted the system privilege UNLIMITED TABLESPACE. This is fixed now.

I am not sure what I think about silent GRANTS. I am not sure if this is a bug or feature. If I issue a single DDL command I think the database should not issue others at the same time.

My method of testing this is to create a DDL trigger that outputs all DDL issued as print statements so we can see the command we issued and the DDL actually issued. I know if we run a trace we see a lot more details such as the recursive SQL that has been run and sometimes that is useful to understand exactly what happens when a SQL command is issued. First lets create the simple toolkit:

SQL> get ddl
  1  create or replace trigger atk_ddl
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7  begin
  8     --
  9     lv_n:=ora_sql_txt(lv_sql);
 10     for i in 1 .. lv_n loop
 11             lv_stmt:=lv_stmt||lv_sql(i);
 12     end loop;
 13     --
 14     dbms_output.put_line('DDL>'||lv_stmt);
 15* end;
SQL> @ddl

Trigger created.

SQL>

Now let us create a simple user:

SQL> set serveroutput on
SQL> create user tst1 identified by tst1;
DDL>GRANT INHERIT PRIVILEGES ON USER "TST1" TO PUBLIC
DDL>create user tst1 identified by *

User created.

SQL>

Wow, first attempt and we get lucky and not only does the simple tool catch "create user tst1 identified by*" but we also capture the "GRANT INHERIT PRIVILEGES ON USER "TST1" TO PUBLIC"

I am also not sure why each DDL is not in the right order; clearly we cannot grant inherited until the user actually exists. Must be a trigger or DBMS_OUTPUT issue. A trace would resolve this, maybe.

Now lets drop the user:

SQL> set serveroutput on
SQL> drop user tst1;
DDL>drop user tst1

User dropped.

SQL>

No additional DDL. never mind, lets create a role:

SQL> create role atk_ddl;
DDL>create role atk_ddl

Role created.

SQL>

This is interesting as there is just one DDL command shown BUT we know that the creator of a role is granted the role. We can see this here:

SQL> select grantee from dba_role_privs where granted_role='ATK_DDL';

GRANTEE
--------------------------------------------------------------------------------
SYS

SQL>

So, there is also a silent grant to SYS as well as the role create. This is not shown in the DDL grabber. If we do an alter system we can see if that is captured:

SQL> alter system set "_system_trig_enabled"=true;

System altered.

SQL>

This as expected is not captured. What if we create a procedure:

SQL> create or replace procedure atk_proc
  2  is
  3  begin
  4  null;
  5  end;
  6  /
DDL>create or replace procedure atk_proc
is
begin
null;
end;

Procedure created.

SQL>

No hidden DDL. What about granting our role:

SQL> grant atk_ddl to pete;
DDL>grant atk_ddl to pete

Grant succeeded.

SQL>

No hidden DDL, what about a table:

SQL> create table atk_test2 (col1 number);
DDL>create table atk_test2 (col1 number)

Table created.

SQL>

What if we grant resource?

SQL> grant resource to pete;
DDL>grant resource to pete

Grant succeeded.

SQL>

Nothing, seems like we know of two so far, CREATE USER and CREATE ROLE where there is silent DDL; one that was shown and one that was not. Also we proved that ALTER SYSTEM whilst being DDL is not DDL for the DDL trigger.

If I find more in the future I will let you know here

#oracleace #sym_42 #oracle #database #security #ddl #grants #privileges #hacking

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 23ai 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