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

Some brief news on Oracle and Security and personal news!!

I was emailed a couple of weeks or so ago that I was awarded membership of the Oracle ACE program for now until end of May 2026 and yesterday I received the email/pdf version of my ACE certificate. Great to be part of the ACE program again for another.

Today as I write this its the 26th June 2025; the 29th June is coming up on Sunday and that is a day that has two events for me.

The first connection to June 29th was 30 years ago and is that I passed my degree in Electronic and electrical systems on June 29th 1995 and got a first class honours. I got an average mark in all subjects of 79.xx % over the whole degree course. in Some subjects in some years I got 100% mark; for instance in Math in the first year. My final year project was writing a multi-tasking operating system in C and some intel assembler for low level tasks. The OS also had a TUI (Text User Interface) that was similar in style to products like Borland C++ or Turbo Pascal. So windows and menus and dialogs and status bars etc but in direct memory access and using text characters written direct to the screen. The OS also had obviously multi-tasking and also inter process communication with semaphores, task blocks, task switching, system calls, waits, messages between applications and more. It was possible to create a demo program that ran 4 or more separate programs running each in separate TUI windows and using sync and messaging between processes.

I started to write the OS on 13th June 1994 as the first entry in my log book one showed and the last entry in log book 2 showed the 11th May 1995:

The photo shows my two log books and my actual degree project / thesis that was handed in. The thesis was 294 pages long and I was told by the printers mine was way bigger than anyone else's.

I got a first class honours degree in electronics and also won the IEEE prize for the best student in this category across the country for 1995. BUT, i have not used the details of the degree since I completed it as I was made redundant from my job as a designer in the railway and I moved into IT and programming and eventually into Oracle security which of course I still do today.

My second connection to June 29th was 49 years years ago when my friend Paul and I were at Selby railway station and we watched Deltic 55004 on the 17:00 from London Kings Cross pass Selby at 19:15 and a half:

As the report shows and as I remember. We saw something big dragging on the floor from one of the carriages (numbered E6111). We decided to report this to the station manager who didn't believe us at first as we were 13 years old. We showed him how the dragging piece had ripped big chunks of wood from the crossing next to the swing bridge and then he believed us. He telephoned the signal man and had the train stopped. The side of the bogie frame was dragging on the floor as the axle end had sheared off. The train was stopped about 6 miles north of Selby and was not yet at full speed. We stopped an express train from almost certainly crashing

We became big news and there were articles in every major newspaper and also local ones. The press came to see us at home and other places. Here is a couple of articles from the time. First the Daily Mail

And an article after one of the trips:

This picture shows Paul and I after we went on a free trip from York to London Paddington and then on a brand new (at the time) HST to Bristol and back to York. We had a man from York railway PR accompany us on the trip. The photo was taken by the press when we got back. We were given some brochures for the HST and the railway museum in York. I still have these:

We also had a trip before that with British Railways who took us in a cab ride to London in a class 47 and then for lunch in the post office tower revolving restaurant:

One thing that stands out now is why did the press make so many mistakes (names wrong, spelled wrong, ages wrong and more) and also why did they print our addresses in national newspapers.

Some time later Selby council also awarded us a silver limited Selby Abbey Festival medal:


It was nice to get a medal and have free trips but the best thing was saving peoples lives at the time.

Sorry, not much Oracle security in this post; Some posts coming soon though on new Oracle Security research; the key vault for DBMS_CRYPTO and more on an interpreter written in PL/SQL and a new subject. I have quite a lot of blog materials ready to post when i get a chance over the coming weeks.

June the 29th seems to be a lucky day for me, passing my 1st class honours degree 30 years ago on that date and stopping a train crash 49 years ago

#oracleace #oracle #security #traincrash #degree #electronics #june29th

This is a short history of our PFCLScan product and therefore also a history of the other apps now built on top of PFCLScan such as PFCLObfuscate, PFCLCode, PFCLForensics and more.

Obviously after such a long time and without keeping detailed notes of the development some of this is based on hazy long ago memories. No one else probably can contradict any of this so please bear in mind some of the detail may not be 100% accurate but its still a valid history of how it was developed.

I might come back and enhance this in the future with more details and I might move it to the PFCLScan product pages but for now it is a blog.

I started PeteFinnigan.com Limited on the 12th February 2003 and my goal was to provide services, training and eventually products to help customers secure data in their Oracle databases. Of course I also wanted to help the public generally as well through blog and other posts on my website to secure data in their Oracle databases - so I have posted a huge amount of free content and papers, talks and of course tools written in SQL and PL/SQL. One of my key goals in creating software was to make it easier for me personally review customers databases by having tools that I could easily use BUT the goal was to also create commercial tools to do the same and sell licenses for these.

So just after I started PeteFinnigan.com Limited on the 24th April 2003 I started a C program called oscan.exe that would be the core engine of my database scanner. The main C file has a comment for version 0.0.1 on the 24 April 2003 and is shown here:

/*============================================================================*/
/*   File Name   : oscan.c                                                    */
/*   Author      : Pete Finnigan                                              */
/*   Date        : April 2003                                                 */
/*   Name        : OSCAN                                                      */
/*============================================================================*/
/*   Description : This program is the PeteFinnigan.com Limited Oracle        */
/*                 database security scanner. The project is conceived to     */
/*                 replace the current PL/SQL scripts and shell scripts and   */
/*                 may form part of a commercial scanner offering.            */
/*============================================================================*/
/*   maintainer  : Pete Finnigan (http://www.petefinnigan.com)                */
/*   Copyright   : Copyright (C) 2003, 2008, 2009, 2010 PeteFinnigan.com      */
/*                 Limited                                                    */
/*                 All rights reserved. All registered trademarks recognised  */
/*                                                                            */
/*                 The contents of this file are protected by copyright. Any  */
/*                 unauthorised copying, duplication of its contents are in   */
/*                 breach of the copyright.                                   */
/*                                                                            */
/*============================================================================*/
/*   History                                                                  */
/*                                                                            */
/*   Wh	    Ver			Date            Description                           */
/*   ===    ===			==========      ===========                           */
/*   PF     0.0.1       24-Apr-2003     First release                         */
/*   PF	    0.1.1		26-Jun-????	    **previous updates removed***         */
/*   PF     0.1.2	    29-Dec-????     Move to VCC EXP                       */
/*   PF     0.1.3       28-Mar-????     Added code access functions           */
/*   PF     0.1.6       28-Jun-????     Work to add in command loop           */
/*   PF     0.1.7       31-Oct-2007     General progress                      */
/*   PF     0.1.9       01-Apr-2008     Move to OSCAN                         */
/*   PF     0.1.10      02-Apr-2008     Test compile with cleaned structure   */
...

The file was clearly updated in 2010 and as the header did not contain comments after this time, this is when I started to add changed to release notes instead but the earlier comments are valid.

As writing in C is much slower than writing in SQL and PL/SQL I quickly put the C program to one side and decided to create a SQL - PL/SQL scanner for me to use in customer engagements and not to sell. The SQL*Plus script was a wrapper around calls to specific scripts and these scripts acted as internal functions. This allowed the main script to be a container for hundreds of checks and reports with just define statements to set names/values etc and then call each separate script (like a function call). This set of scripts grew to 40-50,000 lines or more of code in SQL and PL/SQL but was useful for me to perform security audits. But, it was not useful to sell though as a product as it was just scripts and no reports/words/advice.

At some point I decided to go back to my C based scanner oscan.exe and started work on it again so that eventually I could sell it and of course use it internally on consulting engagements.

This restart, from the comments above happened sometime in 2007 but i do not remember exactly when. I completed the first version of oscan.exe to load a policy of checks and run it against an Oracle database. Already it had some sophistication in that it could execute any SQL or PL/SQL based check in one policy. It also had built in trace and logging to help with support. The whole program has its own config file that can be used to control it. The policies were stored in XML files and as part of that development I created an XML parser in C to load the policies. The output from processing a policy was also stored in XML files.

I made a decision to not use a database as the repository of the tool as then that database would need to be locked down and protected.

I also decided to use OCI (Oracle Call Interface - not cloud) to connect to the database and do SQL and PL/SQL and some other bits such as breaking a running session.

I made a blog post (C code API to encapsulate OCI) showing oscan.exe running for the first time publicly on t on April 7th 2008. So, the core engine was done and running checks in policies then:

C:\oscan -c oscan.conf -v

OSCAN: Release 0.1.12 - Alpha on Mon Apr 07 11:18:26 2008

Copyright (c) 2003, 2008, PeteFinnigan.com Limited. All rights reserved.

[2008 Apr 07 10:18:26] Logger: Starting OSCAN...
[2008 Apr 07 10:18:26] Logger: Running Scanner
....
[2008 Apr 07 10:27:32] Logger: Closing Down OSCAN

Sometime after as we had only a C program to run a single policy passed in and it was a Windows command line program I needed to make it a proper Windows application. At this time it was not called PFCLScan. As i registered the domain on the 6th July 2009 then I assume that on that day I came up with the name PFCLScan.

The GUI originally started as a C++ / MFC program and whilst I could probably find the exact date of that development I wont as it was a 2 week detour. I started on C++ / MFC as the engine was in C and the plan originally was to move oscan.exe into the Windows program as C and C++ can play together. The learning curve in MFC was high and whilst fairly good looking GUI could be created quickly it was much harder to then change, tweak, add things to. Probably I should have stayed with MFC but i wanted progress faster so switched the GUI to vb.net.

This switch meant that we needed to call oscan.exe from the GUI as a command line tool. This was a good choice as it meant that PFCLScan can also be run from the command line very simply and indeed it still can be.

At this point I needed to create projects that could contain any number of policies that are then run by oscan.exe. So the idea of projects was born where a project is also an XML file and it contains any number of policies and each of these policies contain any number of actual checks. This meant we can define a project in the GUI and then run each policy through oscan.exe. This also meant I had the opportunity to create more sophistication. I realised that I could create a check in SQL or PL/SQL that took input from a previous policy or check and fed the values from any column of data to the new check. This allows checks to be written where we reduce database trips. A good example is that we have a policy/check that gets a list of default users or default roles and these are fed into subsequent checks that need a list of default users and roles as text to be added to SQL or PL/SQL.

We also added loop checks; a variation on the static dependencies above. This means that the results of one check - i.e. rows of result data can be fed line by line into a subsequent check.

I also saw the opportunity to add libraries where a check is used more than once it is a library and the code for the library can be loaded in and executed. We can of course mix and match and libraries / static dependencies (think #define in C) and loops all work with libraries as well. A library is just a policy / check so can be written and edited through the GUI.

The programs used to process the static dependencies and loops and libraries are lock.exe and load.exe so I know roughly when these ideas/features were added; these programs were started on the 10th and 11th January 2010 so this development at least started then.

At this time I also recognised that PFCLScan needed a reporting language so I invented one. I also for the first time saw the advantage of recursion and re-use. So the first job for the report language (PFCLReport) was to create projects from the passed in data obtained from the user via a Windows wizard - name, file name, location and type of project and a template for the project. The repo.exe tool was started on 15th January 2010. The report tool is simple and loop based. We have a template which can be any text file and this is parsed and any data found substituted in the output where there are variables. The language is rich and has hundreds of variables and is loop based. So you can loop over databases that have been scanned and policies and checks. The language is easy to use

So, I used repo.exe in the GUI to go off and create a project when a user chose to start a new project. Re-use!

Around the same time I came up with the idea of adding plugins to PFCLScan. A plugin is simply a project in PFCLScan. So it can be created in the GUI and policies and checks added and then converted into a plugin - it can be run as a project in the GUI. Plugins can be run ad-hoc from the plugins screen or can be run from hook points throughout the application. A hook point can have any plugin associated with it. This means an end user could if they wished write a new plugin and replace ours. Powerful.

The first two plugins created were to test the connection to a database and test the connection to a server with ssh.

So around the 2009 / 2011 timeframe we added the ability to run shell commands on a server. To allow a plugin to connect to the database through OCI in oscan.exe we also at this time added Lua to oscan.exe. This was a neat extendable way to do this and also meant we can now write checks in Lua!!

Most of the core features were added around this 2009 / 2011 timeframe. I showed a picture of PFCLScan on September 1st 2009 presumably using the old mechanism for testing a connection as this was before plugins. Search for blog post (A book, a database scanner and a magazine column and a few bugs) to see this picture.

We started to add applications with PFCLObfuscate as the first one in 2019 / 2020 by building apps on the top of PFCLScan so that they could easily use the core features of the main product and remove the need to rewrite things again in each app. This also was to make build and deployment easier as we now had one software build rather than separate ones for each product. The natural next step was also to use PFCLScan plugins to build and activate PFCLScan itself. Full circle!

I added the product website around March 2017. Version 1.3 was released on the 18th October 2013 and version 3 on 11 July 2019 and version 4 before xmas 2024. Version 5 (2025) is being worked on now.

The first public demo was on May 13th 2013 in Edinburgh and the first sale was on the 10th February 2013

We are enow working on version 2025 and also adding a new product as an App, PFCLUserRights that will do a detailed analysis of users and their privileges in the database plus updates to all the apps and the core scanner.

So, that is a brief history of PFCLScan and apps and as I said I might come back and extend this in the future with more details

#oracle #security #pfclscan #vulnerability #scanner #breach #databreach #datasecurity