Pete Finnigan's Oracle Security Weblog

In Oracle a user is the same as a schema - well not 100% true - so lets explain a bit.

Firstly at a logical high level in Oracle a user is an account used by a real person to connect to the database and do work on other users (I know) data. A schema is an account that owns objects such as tables, views, procedures etc.

BUT, a user intended to be a logical user, used by a real person to connect and do things in the database can also have objects. A schema intended to only own the objects of the application

In Oracle when you create a user or schema you use the CREATE USER command. For instance:

SQL> create user pete_u identified by pete_u;

User created.

SQL> create user pete_s identified by pete_s;

User created.

SQL>

I create a user and a schema. For the user i can grant privileges to login and to access other objects or what is needed. For the schema I also create a user. The intention with the schema is to not log in; even for creating objects and for the schema to own objects that are needed and make grants on those objects to other users and schemas.

From an Oracle perspective a user is a schema and a schema is a user BUT, each user has a schema created at the same time as the user and each schema has a user created at the same time. We cannot separate the two except for making a user/schema a schema only account; i.e. it cannot be logged into. More likely we need to manage our accounts and never log into schemas and never create objects in users.

Oracle does not really have a true separation between a schema and a user.

From 18c we can create a schema only account but in truth this could simply be altered and a password added. A schema only account is created as follows:

SQL> create user pete_o no authentication;

User created.

SQL>

As we can see below the password is just zeros:

SQL> select password,spare4 from sys.user$ where name='PETE_O';

PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------

S:0000000000000000000000000000000000000000FAD4D632D13FC635FD36;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000FA258C558BB96EE9061BA13F6547AC98


SQL>

It was possible before 18c to simply create a user with the undocumented IDENTIFIED BY VALUES clause to achieve the same thing and indeed I have done this for over 20 years and advised others the same.

The key to this design is to also use proxy to connect to the schema. So that no one ever directly logs into the schema but proxies to it to manage it and create objects. We are still connected in a sense to our own user (logged in) and accessing the schema without authenticating to the schema.

So, In Oracle a user is a schema and a schema is a user.

Oracle also supports a CREATE SCHEMA statement BUT this does not create a schema without a user or add a schema to an existing user. We cannot separate the two. The CREATE SCHEMA command can be used to add multiple objects at the same time to an existing user in a sense creating a schema. Or if the user does not exist then it creates the user account. Lets try all the possible types of CREATE SCHEMA. First lets try creating a schema for an existing user:

SQL> create schema pete_o create table test1 (col1 number);
create schema pete_o create table test1 (col1 number)
              *
ERROR at line 1:
ORA-02420: missing schema authorization clause


SQL>

OK, so what if we change that to not use an existing user:

SQL> create schema pete_x create table test1 (col1 number);
create schema pete_x create table test1 (col1 number)
              *
ERROR at line 1:
ORA-02420: missing schema authorization clause


SQL>

Still does not work; lets create a schema PETE_X and authenticate via the user PETE_O:

SQL> create schema pete_x authorization pete_o create table test1 (col1 number);
create schema pete_x authorization pete_o create table test1 (col1 number)
              *
ERROR at line 1:
ORA-02420: missing schema authorization clause


SQL>

Still does not work. If we just authorise in PETE_O which exists

SQL> create schema authorization pete_o create table test1 (col1 number);
create schema authorization pete_o create table test1 (col1 number)
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier


SQL>

Hmm, should work. The account PETE_O exists and we are in essence adding objects via the CREATE SCHEMA command. What does this error mean?

ORA-02421: missing or invalid schema authorization identifier
02421. 00000 - "missing or invalid schema authorization identifier"
*Cause: the schema name is missing or is incorrect in an authorization
clause of a create schema statement.
*Action: If the name is present, it must be the same as the current
schema.

So, I think we can do two things. First grant create table and quota to PETE_O so it has the right to create a table and second set the current_schema to PETE_O:

SQL> grant create table to pete_o;

Grant succeeded.

SQL> grant unlimited tablespace to pete_o;

Grant succeeded.

SQL> alter session set current_schema=pete_o;

Session altered.

SQL> select sys_context('userenv','current_schema') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
PETE_O

SQL> create schema authorization pete_o create table test1 (col1 number);
create schema authorization pete_o create table test1 (col1 number)
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier


SQL>

Still does not work

So, it would seem that alter session set current_schema does not work as we actually need to be connected as the user who CREATE SCHEMA is executed for not pretending to be that user:

SQL> alter user pete_o grant connect through pete_u;

User altered.

SQL> grant create session to pete_o;

Grant succeeded.

SQL> grant create session to pete_u;

Grant succeeded.

SQL>

Now connect to PETE_O and try again:

SQL> connect pete_u[pete_o]/pete_u@//192.168.56.33:1539/xepdb1
Connected.
SQL> create schema authorization pete_o create table test1 (col1 number);

Schema created.

SQL>

OK, we finally get CREATE SCHEMA to work. What a faff.

This is not necessary. We should just proxy to PETE_O in my case as the schema only account and simply create the tables, views, procedures we need. We still need separate grants to the schema for each object type. Schema is not a separate thing.

The Oracle documentation for the CREATE SCHEMA says:

This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.
Oracle 18c CREATE SCHEMA

All of the statements for multiple tables, views etc still need to be executed. The only saving is a semi-colon after each one. Not worth the hassle; just create users and if there are no objects it is a user and if there are it is a schema. For schemas set an impossible password or make it schema only (same thing). Do not mix the two.

#oracleace #sym_42 #oracle #database #security #create #schema

In the recent blog first part of this series on the security of AUDSYS.AUD$UNIFIED we looked at the main features of the AUDSYS user and its ability or design to stop anyone from randomly deleting or updating or doing DDL on the audit trail held in AUD$UNIFIED.

As I said at the end of that post I am interested because I have been teaching in my Secure Coding in PL/SQL Class a design pattern to protect things in an Oracle database such as the use of a privilege or access to a resource or external access or access to a database object such as a table or data.

This design pattern is something I want to do for my customers. There are many ways we could achieve this BUT Oracle have done it with AUD$UNIFIED so i would like to get more details and see if we can do the same.

If we wanted to make AUD$UNIFIED readonly then we could just make the table READONLY and if any DDL or DML was attempted it would result in a ORA-12081 - Update operation not allowed on table "schema"."table". This feature stops DML and also some DDL on the table, so it is is similar to the security of AUD$UNIFIED. This feature is turned on with:

SQL> alter table orablog.bof_pay_details read only;

And turned off with:

SQL> alter table orablog.bof_pay_details read write;

This is OK as a solution to stop changes to data that should not change BUT this would not work with AUD$UNIFIED as the table needs to be written to on a continuous basis and also needs to be deleted from periodically via DBMS_AUDIt_MGMT. So, we could flip between READ ONLY and READ WRITE but one session could make it READ WRITE to insert a record BUT another session could then jump in whilst its READ WRITE and delete audit records.

Another option would be a READ ONLY view with the WITH READ ONLY syntax. This would allow read but not DML so we would get an Oracle error such as ORA-01733 if we tried an INSERT. Again for the same reasons as above this would not work for the AUD$UNIFIED case.

Another option could be a READ ONLY TABLESPACE in a similar way to above we can alter a tablespace and make it read only or tread write. But this has the same problems as above for the AUD$UNIFIED case. We cannot simply switch a tablespace between read only and read write as each time we switch to read only all existing read / write transactions need to complete. This is not an option for this security case.

Yes another option is to use a constraint such as the following:

SQL> @cs
Connected.
USER is "SYS"
SQL> alter table orablog.bof_pay_details add constraint bof_pay_details_read_only check(1=1) disable validate;

Table altered.

SQL>

Now test it

SQL> select id,name_on_card from orablog.bof_pay_details;

        ID
----------
NAME_ON_CARD
--------------------------------------------------------------------------------
         1
Mr David Bentley

         2
Mr Martin Chisholm


SQL> update orablog.bof_pay_details set last_four='1111';
update orablog.bof_pay_details set last_four='1111'
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint
(ORABLOG.BOF_PAY_DETAILS_READ_ONLY) disabled and validated


SQL> delete from orablog.bof_pay_details;
delete from orablog.bof_pay_details
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint
(ORABLOG.BOF_PAY_DETAILS_READ_ONLY) disabled and validated


SQL>

Clearly this stops DML but again for the AUD$UNIFIED it would not work to allow the fine grained DML whilst being essentially READ ONLY for everyone else. The constraints on AUDSYS.AUD$UNIFIED confirm this:

SQL> set serveroutput on
SQL> @sc_print 'select * from dba_constraints where table_name=''''AUD$UNIFIED'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from dba_constraints where table_name=''AUD$UNIFIED''','''','''''');
Executing Query [select * from dba_constraints where table_name='AUD$UNIFIED']
OWNER                         : AUDSYS
CONSTRAINT_NAME               : SYS_C005872
CONSTRAINT_TYPE               : C
TABLE_NAME                    : AUD$UNIFIED
SEARCH_CONDITION              : "ENTRY_ID" IS NOT NULL
SEARCH_CONDITION_VC           : "ENTRY_ID" IS NOT NULL
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 17-AUG-21
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3
-------------------------------------------
OWNER                         : AUDSYS
CONSTRAINT_NAME               : SYS_C005873
CONSTRAINT_TYPE               : C
TABLE_NAME                    : AUD$UNIFIED
SEARCH_CONDITION              : "STATEMENT_ID" IS NOT NULL
SEARCH_CONDITION_VC           : "STATEMENT_ID" IS NOT NULL
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 17-AUG-21
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3
-------------------------------------------
OWNER                         : AUDSYS
CONSTRAINT_NAME               : SYS_C005874
CONSTRAINT_TYPE               : C
TABLE_NAME                    : AUD$UNIFIED
SEARCH_CONDITION              : "EVENT_TIMESTAMP" IS NOT NULL
SEARCH_CONDITION_VC           : "EVENT_TIMESTAMP" IS NOT NULL
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 17-AUG-21
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3
-------------------------------------------
OWNER                         : AUDSYS
CONSTRAINT_NAME               : SYS_C005875
CONSTRAINT_TYPE               : C
TABLE_NAME                    : AUD$UNIFIED
SEARCH_CONDITION              : "ACTION" IS NOT NULL
SEARCH_CONDITION_VC           : "ACTION" IS NOT NULL
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 17-AUG-21
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3
-------------------------------------------
OWNER                         : AUDSYS
CONSTRAINT_NAME               : SYS_C005876
CONSTRAINT_TYPE               : C
TABLE_NAME                    : AUD$UNIFIED
SEARCH_CONDITION              : "RETURN_CODE" IS NOT NULL
SEARCH_CONDITION_VC           : "RETURN_CODE" IS NOT NULL
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : GENERATED NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 17-AUG-21
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, we know Oracle has not used these ideas because they are not workable. But, how does Oracle make AUD$UNIFIED work in the way that it does?

Lets jump back a step for a short while. We know Oracle also does not allow a connection to AUDSYS and the cynical me would suggest that is because if we can connect direct to AUDSYS then maybe we can delete the audit trail or we can disable the security if we can find it. If we compare AUDSYS with my user ATK as follows:

SQL> @sc_print 'select * from sys.user$ where name=''''AUDSYS'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from sys.user$ where name=''AUDSYS''','''','''''');
Executing Query [select * from sys.user$ where name='AUDSYS']
USER#                         : 8
NAME                          : AUDSYS
TYPE#                         : 1
PASSWORD                      :
DATATS#                       : 1
TEMPTS#                       : 3
CTIME                         : 17-AUG-21
PTIME                         : 10-JUL-25
EXPTIME                       :
LTIME                         :
RESOURCE$                     : 0
AUDIT$                        :
DEFROLE                       : 1
DEFGRP#                       :
DEFGRP_SEQ#                   :
ASTATUS                       : 0
LCOUNT                        : 0
DEFSCHCLASS                   : DEFAULT_CONSUMER_GROUP
EXT_USERNAME                  :
SPARE1                        : 384
SPARE2                        :
SPARE3                        : 16382
SPARE4                        : S:
;T:
SPARE5                        :
SPARE6                        :
SPARE7                        :
SPARE8                        :
SPARE9                        : 3
SPARE10                       : 0
SPARE11                       :
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

And

SQL> @sc_print 'select * from sys.user$ where name=''''ATK'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from sys.user$ where name=''ATK''','''','''''');
Executing Query [select * from sys.user$ where name='ATK']
USER#                         : 668
NAME                          : ATK
TYPE#                         : 1
PASSWORD                      :
DATATS#                       : 5
TEMPTS#                       : 3
CTIME                         : 10-JUL-25
PTIME                         : 10-JUL-25
EXPTIME                       : 06-JAN-26
LTIME                         :
RESOURCE$                     : 0
AUDIT$                        :
DEFROLE                       : 1
DEFGRP#                       :
DEFGRP_SEQ#                   :
ASTATUS                       : 0
LCOUNT                        : 0
DEFSCHCLASS                   : DEFAULT_CONSUMER_GROUP
EXT_USERNAME                  :
SPARE1                        : 0
SPARE2                        :
SPARE3                        : 16382
SPARE4                        :
S:8F8A89214078675093CE2F62F3E90083A8164FB1FDBD6F7FCF7CEDBAB323;T:62B8A47574C8771
0A37D1E93209788A315BB92B6857875E029D31B144A9BDE3EBC14629C9BAF7C1AC66BC703E6675B6
A8153315B86127C09DD36DE0BA334CE7F4E0DB06BD7F4421FF8CB75B7364DD56A
SPARE5                        :
SPARE6                        : 10-JUL-25
SPARE7                        :
SPARE8                        :
SPARE9                        : 3
SPARE10                       : 0
SPARE11                       :
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

The only difference is spare1 being set to 384 for AUDSYS and 0 for ATK. What other values are there for SPARE1 across the database:

SQL> col name for a30
SQL> col spare1 for 9999999999
SQL> col type# for 99
SQL> select name,spare1,type# from sys.user$ where spare1!=0;

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
SYS                                    384     1
PUBLIC                                 400     0
CONNECT                                384     0
RESOURCE                               384     0
DBA                                    384     0
PDB_DBA                                384     0
AUDIT_ADMIN                            384     0
AUDIT_VIEWER                           384     0
AUDSYS                                 384     1
SYSTEM                                 384     1
SELECT_CATALOG_ROLE                    384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
EXECUTE_CATALOG_ROLE                   384     0
CAPTURE_ADMIN                          384     0
SYSBACKUP                            65920     1
SYSDG                                65920     1
SYSKM                                65920     1
SYSRAC                               65920     1
OUTLN                                65920     1
EXP_FULL_DATABASE                      384     0
IMP_FULL_DATABASE                      384     0
AVTUNE_PKG_ROLE                        384     0
REMOTE_SCHEDULER_AGENT               65920     1

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
CDB_DBA                                384     0
APPLICATION_TRACE_VIEWER               384     0
ACCHK_READ                             384     0
LOGSTDBY_ADMINISTRATOR                 384     0
DBFS_ROLE                              384     0
GSMUSER_ROLE                           384     0
GSMROOTUSER_ROLE                       384     0
GSMADMIN_INTERNAL                    65920     1
GSMUSER                              65920     1
DIP                                  65920     1
AQ_ADMINISTRATOR_ROLE                  384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
AQ_USER_ROLE                           384     0
DATAPUMP_EXP_FULL_DATABASE             384     0
DATAPUMP_IMP_FULL_DATABASE             384     0
ADM_PARALLEL_EXECUTE_TASK              384     0
PROVISIONER                            384     0
XS_SESSION_ADMIN                       384     0
XS_NAMESPACE_ADMIN                     384     0
XS_CACHE_ADMIN                         384     0
XS_CONNECT                             384     0
XS$NULL                              65920     1
HS_ADMIN_EXECUTE_ROLE                  384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
DBSFWUSER                            65920     1
GATHER_SYSTEM_STATISTICS               384     0
OPTIMIZER_PROCESSING_RATE              384     0
DBMS_MDX_INTERNAL                      384     0
ORACLE_OCM                           65920     1
BDSQL_ADMIN                            384     0
BDSQL_USER                             384     0
RECOVERY_CATALOG_OWNER                 384     0
RECOVERY_CATALOG_OWNER_VPD             384     0
RECOVERY_CATALOG_USER                  384     0
EM_EXPRESS_BASIC                       384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
EM_EXPRESS_ALL                         384     0
SYSUMF_ROLE                            384     0
SYS$UMF                              65920     1
MAINTPLAN_APP                          384     0
SCHEDULER_ADMIN                        384     0
PPLB_ROLE                              384     0
DGPDB_INT                            65920     1
HS_ADMIN_SELECT_ROLE                   384     0
SODA_APP                               384     0
HS_ADMIN_ROLE                          384     0
GLOBAL_AQ_USER_ROLE                    384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
OEM_ADVISOR                            384     0
OEM_MONITOR                            384     0
DBSNMP                               65920     1
APPQOSSYS                            65920     1
GSMADMIN_ROLE                          384     0
GSM_POOLADMIN_ROLE                     384     0
GDS_CATALOG_SELECT                     384     0
GSMCATUSER                           65920     1
GGSYS                                65920     1
GGSYS_ROLE                             384     0
XDB                                  65920     1

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
ANONYMOUS                            65920     1
XDBADMIN                               384     0
XDB_SET_INVOKER                        384     0
AUTHENTICATEDUSER                      384     0
XDB_WEBSERVICES                        384     0
XDB_WEBSERVICES_WITH_PUBLIC            384     0
XDB_WEBSERVICES_OVER_HTTP              384     0
OLAPSYS                              65920     1
DATAPATCH_ROLE                         384     0
WMSYS                                65920     1
WM_ADMIN_ROLE                          384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
JAVAUSERPRIV                           384     0
JAVAIDPRIV                             384     0
JAVASYSPRIV                            384     0
JAVADEBUGPRIV                          384     0
EJBCLIENT                              384     0
JMXSERVER                              384     0
DBJAVASCRIPT                           384     0
OJVMSYS                              65920     1
JAVA_ADMIN                             384     0
CTXSYS                               65920     1
CTXAPP                                 384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
ORDSYS                               65920     1
ORDDATA                              65920     1
ORDPLUGINS                           65920     1
SI_INFORMTN_SCHEMA                   65920     1
ORDADMIN                               384     0
OLAP_XS_ADMIN                          384     0
DVSYS                                65920     1
DV_SECANALYST                          384     0
OLAP_DBA                               384     0
OLAP_USER                              384     0
MDSYS                                65920     1

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
MDDATA                               65920     1
RDFCTX_ADMIN                           384     0
LBACSYS                              65920     1
LBAC_DBA                               384     0
DVF                                  65920     1
DV_MONITOR                             384     0
DV_ADMIN                               384     0
DV_OWNER                               384     0
DV_ACCTMGR                             384     0
DV_PATCH_ADMIN                         384     0
DV_STREAMS_ADMIN                       384     0

NAME                                SPARE1 TYPE#
------------------------------ ----------- -----
DV_GOLDENGATE_ADMIN                    384     0
DV_XSTREAM_ADMIN                       384     0
DV_GOLDENGATE_REDO_ACCESS              384     0
DV_AUDIT_CLEANUP                       384     0
PDBADMIN                           2097152     1
DV_DATAPUMP_NETWORK_LINK               384     0
DV_POLICY_OWNER                        384     0

128 rows selected.

SQL>

It seems that only the users SYS, SYSTEM and AUDSYS have a SPARE1 of 384, the rest are roles. These values such as 384 are flags so some with higher values may also contain the same flags as AUDSYS. If we look at the dsec.bsq file we can see that 384 = 128+256 which are the flags for Oracle Maintained and CDB Common User. So nothing that explains how AUDSYS cannot be logged into.

A review of AUD$UNIFIED in DBA_TABLES and also SYS.TAB$ does not reveal any significant flags or values in a quick look that could explain how it works the way it does.

A check of dependencies shows that only the view UNIFIED_AUDIT_TRAIL depends on AUD$UNIFIED and no upwards dependencies.

What is Odd is the fact that if you search all the scripts in $ORACLE_HOME/rdbms/admin there is nothing obvious that enables security on AUDSYS or AUD$UNIFIED. BUT, also its obvious that the user and table are created (using SYS) and then altered - i.e. violating the no DML and no DDL protection. So, we would have to assume that because this would be done in non-live status the security is off; i.e. the database is being built.

OK, can we try another option to connect as AUDSYS:

SQL> alter user audsys grant connect through atk;
alter user audsys grant connect through atk
           *
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'AUDSYS'

No, another internal security policy captures this. What privileges does AUDSYS have:

SQL> @cs
Connected.
USER is "SYS"
SQL> @find_all_privs



find_all_privs: Release 1.0.7.0.0 - Production on Sat Jul 12 19:51:16 2025
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF USER TO CHECK                 [ORCL]: AUDSYS
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:

User => AUDSYS has been granted the following privileges
====================================================================
        SYS PRIV => ALTER SESSION grantable => NO
        SYS PRIV => CREATE JOB grantable => NO
        SYS PRIV => CREATE TABLE grantable => NO
        SYS PRIV => SELECT ANY DICTIONARY grantable => NO
        SYS PRIV => SET CONTAINER grantable => NO
        TABLE PRIV => DELETE object => SYS.DAM_CLEANUP_JOBS$ grantable => NO
        TABLE PRIV => DELETE object => SYS.DAM_CONFIG_PARAM$ grantable => NO
        TABLE PRIV => DELETE object => SYS.DAM_LAST_ARCH_TS$ grantable => NO
        TABLE PRIV => EXECUTE object => SYS.AUD_PDB_LIST grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_ASSERT grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_AUDIT_MGMT_SUPPORT grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_INTERNAL_LOGSTDBY grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_LOCK grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_PDB_EXEC_SQL grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_SCHEDULER grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_SESSION grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_SQL grantable => NO
        TABLE PRIV => EXECUTE object => SYS.DBMS_STATS grantable => NO
        TABLE PRIV => EXECUTE object => SYS.GET_AUD_PDB_LIST grantable => NO
        TABLE PRIV => INSERT object => SYS.DAM_CLEANUP_JOBS$ grantable => NO
        TABLE PRIV => INSERT object => SYS.DAM_CONFIG_PARAM$ grantable => NO
        TABLE PRIV => INSERT object => SYS.DAM_LAST_ARCH_TS$ grantable => NO
        TABLE PRIV => READ object => SYS.DBA_XS_AUDIT_POLICY_OPTIONS grantable => NO
        TABLE PRIV => READ object => SYS.GV_$INSTANCE grantable => NO
        TABLE PRIV => READ object => SYS.GV_$UNIFIED_AUDIT_TRAIL grantable => NO
        TABLE PRIV => READ object => SYS.VW_X$AUD_XS_ACTIONS grantable => NO
        TABLE PRIV => READ object => SYS.V_$CONTAINERS grantable => NO
        TABLE PRIV => READ object => SYS.V_$DATABASE grantable => NO
        TABLE PRIV => READ object => SYS.V_$INSTANCE grantable => NO
        TABLE PRIV => READ object => SYS.V_$OPTION grantable => NO
        TABLE PRIV => READ object => SYS.V_$VERSION grantable => NO
        TABLE PRIV => UPDATE object => SYS.DAM_CLEANUP_JOBS$ grantable => NO
        TABLE PRIV => UPDATE object => SYS.DAM_CONFIG_PARAM$ grantable => NO
        TABLE PRIV => UPDATE object => SYS.DAM_LAST_ARCH_TS$ grantable => NO

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

Interesting, well maybe a couple of things. AUDSYS can do ALTER SESSION but we do not know specifically why and it also has SELECT ANY DICTIONARY rather than specific direct grants on other SYS objects but it has a lot of direct grants anyway.

What objects does AUDSYS own:

SQL> col owner for a30
SQL> col object_name for a30
SQL> col object_type for a30
SQL> set lines 220
SQL> l
  1* select object_name,object_type from dba_objects where owner='AUDSYS'
SQL> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
DBMS_AUDIT_MGMT                PACKAGE
AUD$UNIFIED                    TABLE
SYS_IL0000019880C00097$$       INDEX
SYS_LOB0000019880C00097$$      LOB
SYS_IL0000019880C00031$$       INDEX
SYS_LOB0000019880C00031$$      LOB
SYS_IL0000019880C00030$$       INDEX
SYS_LOB0000019880C00030$$      LOB
UNIFIED_AUDIT_TRAIL            VIEW
CDB_UNIFIED_AUDIT_TRAIL        VIEW
DBA_XS_AUDIT_TRAIL             VIEW
CDB_XS_AUDIT_TRAIL             VIEW
DBMS_AUDIT_MGMT_LIB            LIBRARY
DBMS_AUDIT_MGMT                PACKAGE BODY
DBMS_AUDIT_MGMT_LSBY           PACKAGE
DBMS_AUDIT_MGMT_LSBY           PACKAGE BODY
DV$CONFIGURATION_AUDIT         VIEW
DV$ENFORCEMENT_AUDIT           VIEW
AUD$UNIFIED                    TABLE PARTITION
SYS_IL0000019880C00097$$       INDEX PARTITION
SYS_LOB0000019880C00097$$      LOB PARTITION
SYS_IL0000019880C00031$$       INDEX PARTITION

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
SYS_LOB0000019880C00031$$      LOB PARTITION
SYS_IL0000019880C00030$$       INDEX PARTITION
SYS_LOB0000019880C00030$$      LOB PARTITION
AUD$UNIFIED                    TABLE PARTITION
SYS_IL0000019880C00097$$       INDEX PARTITION
SYS_LOB0000019880C00097$$      LOB PARTITION
SYS_IL0000019880C00031$$       INDEX PARTITION
SYS_LOB0000019880C00031$$      LOB PARTITION
SYS_IL0000019880C00030$$       INDEX PARTITION
SYS_LOB0000019880C00030$$      LOB PARTITION
AUD$UNIFIED                    TABLE PARTITION
SYS_IL0000019880C00097$$       INDEX PARTITION
SYS_LOB0000019880C00097$$      LOB PARTITION
SYS_IL0000019880C00031$$       INDEX PARTITION
SYS_LOB0000019880C00031$$      LOB PARTITION
SYS_IL0000019880C00030$$       INDEX PARTITION
SYS_LOB0000019880C00030$$      LOB PARTITION

39 rows selected.

SQL>

Nothing stands out except the two Database Vault tables but a quick look at those shows no rows and does not reveal anything related to protecting the audit trail.

We should try the obvious and grant DELETE to PUBLIC on the audit table:

SQL> grant delete on audsys.aud$unified to public;

Grant succeeded.

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>

Of course this does not work. Lets check some other basics. Are there are triggers on the audit trail:

SQL> select * from dba_triggers where table_name='AUD$UNIFIED';

no rows selected

SQL>

No, any Database Vault:

SQL> select realm_name,owner,object_name,object_type
  2  from dba_dv_realm_object where object_name='AUD$UNIFIED';

no rows selected

SQL>

No, any VPD:

SQL> select * from dba_policies where object_name='AUD$UNIFIED';

no rows selected

SQL>

No, any label security:

SQL> select * from all_sa_schema_policies where schema_name='AUDSYS';

no rows selected

SQL>

SQL> select * from all_sa_table_policies where schema_name='AUDSYS';

no rows selected

SQL>

No, any RAS:

SQL> select * from dba_xs_objects where owner='AUDSYS';

no rows selected

SQL>

There could be parameters that control this behaviour but a quick look at normal parameters and also hidden ones did not reveal anything. Also Oracle may use events to turn this on or off but a quick search of the /rdbms/admin folder did not reveal anything obvious BUT there could be an event that turns on security for these rules

In summary this is only a quick investigation and i might be wrong BUT it does look like Oracle use internal security policies that we cannot access or use ourselves or they are not immediately obvious or it is simply hard coded in C. It would be great if we could use the same facilities BUT that would also open up the possibility of turning them off again.

If we use some of the core database features such as READ ONLY tables or constraints then the owner or a DBA could turn them off. That is the problem as why Oracle really needs to implement things like this in a stronger way

PS: Since I posted the first part of this blog Christoph posted a message on my twitter account that he was able to truncate the AUDSYS.AUD$UNIFIED but only by using gdb to change a value in memory (in the SGA but not exposed via an x$ table). So, I was right that this mechanism is internal by setting a flag. How that flag is set, I do not know. It is likely the C code checks the flag and if set allows the action (truncate, logon,...). How would I guess the flag is set?, an event or perhaps a hidden parameter but more likely an event. But, i do not know

#oracleace #sym_42 #oracle #security #audit #unified #protect #audittrail #databreach

I was emailed by Cameron overnight to tell me that he has written a new unwrapper for PL/SQL.

There have been no public unwrappers for 9ir2 and lower available on the internet for a long time. There were a number of unwrappers available at some points in the past from Russia, China and others. These all disappeared a long time ago and at least one of them, if you downloaded it would infect your PC.

Unwrapping 10g and higher is much simpler than pre 10g as the process is a combination of a substitution cypher, unix compress and base64. There are plenty of free 10g and higher unwrapper tools available.

I first created an unwrapper for PL/SQL over 20 years ago and I have spoken and written about unwrapping PL/SQL many times and I also spoke at Black Hat in las Vegas in 2006 about the same subject. Some examples of some posts and a link to my talk at Black Hat:

• Unwrapping PL/SQL - posted in December 2009
• SQL to show Oracle DIANA, Attributes and more
• Unwrapping PL/SQL Source Code and Proving the Code is Recovered - Posted in 2022 to prove PL/SQL code is recoverable
• Unwrapping PL/SQL - My Black Hat 2006 presentation
• 10g and 11g PL/SQL Unwrapper source code available - Posted in 2010
• PL/SQL Machine Code Trace - event 10928 Showing PL/SQL VM P-code
• PL/SQL, AST, DIANA, Attributes and IDL - extremely detailed post on PL/SQL internals

The unwrapper for 9i and lower released by Cameron is a massive peice of work and is GPL and released on his GitHub - PL/SQL Unwrapper. Have a look and if you still want me to unwrap lost source code then please contact me as our unwrapper is written in C and runs on the command line and does not need access to the database or installing the PL/SQL code in another database or the unwrapper to be installed in the database.

#oracleace #sym_42 #oracle #plsql #unwrap #reverse #engineering #security

Emad has just released a new blog on his website - CVE-2023-22077 " Denial of Service from PDB Level against Container Level (DoS) " breaking PDB Isolation which discusses a bug he found in Oracle a couple of years ago that is now fixed. Emad demonstrates the exploit.

Because of ubiquity of cloud if a customer/user of a client service - in this case an Oracle database - can find a way to get to the container / hypervisor / CDB in this case then that is dangerous as a customer could access the root layer or another customers database. One type of attack that could occur is a denial of service; where all customers are denied access to their database and in this case the root container also.

Emad discusses the fact Lockdown profiles are used to enforce some of the security between the root container and the pluggable database. In the demo by Emad he shows that whilst OS_ACCESS was in place to restrict OS access he was able to disrupt the root container and whole database. Emad shows how he OS_ACCESS worked for one user and blocked granting permissions on a DIRECTORY object that was created. This stopped the exploit BUT he then creates another user that uses DBMS_BACKUP_RESTORE.DELETE_FILE to delete the spfile from the database stopping it to start up.

Oracle fixed this bug in the October 2023 CPU

I want to discuss this further. I have always taught about file system access and the dangers in my 3 day Oracle security class. The wisdom from CIS was to unset utl_file_dir if it was set to a dangerous location (gone now) and to block EXECUTE on UTL_FILE but there is a big problem with this. UTL_FILE is used in many other places in the Oracle code and some of those places are also granted EXECUTE to PUBLIC.

SQL> col name for a30
SQL> col owner for a30
SQL> col type for a30
SQL> set lines 220
SQL> @dep_priv
Enter value for object_to_test: UTL_FILE
old   3: where referenced_name='&&object_to_test'
new   3: where referenced_name='UTL_FILE'
Enter value for owner_to_test: SYS
old   4: and referenced_owner='&&owner_to_test'
new   4: and referenced_owner='SYS'

NAME                           OWNER                          TYPE
------------------------------ ------------------------------ ------------------------------
DBMS_SCHEDULER                 SYS                            PACKAGE BODY
UTL_FILE                       SYS                            PACKAGE BODY
DBMS_ADVISOR                   SYS                            PACKAGE BODY
DBMS_AW_EXP                    SYS                            PACKAGE BODY
DBMS_CUBE                      SYS                            PACKAGE BODY
DBMS_METADATA                  SYS                            PACKAGE BODY
KUPW$WORKER                    SYS                            PACKAGE BODY
KUPM$MCP                       SYS                            PACKAGE BODY
KUPF$FILE                      SYS                            PACKAGE BODY

9 rows selected.

SQL>

So even if we revoke the PUBLIC EXECUTE from UTL_FILE we also should look at these above as they also have grants of EXECUTE to PUBLIC and then for each of these check if they also have grants of EXECUTE to PUBLIC ad-infinitum.

Also this is just PUBLIC grants; but if an attacker has access to other schemas such as defaults then these could also have access to UTL_FILE directly or via other packages. So we should consider all grantees and not just PUBLIC.

There are also other PL/SQL packages that access the file system that perhaps do not use UTL_FILE. We will see more on this in a minute.

OK, that is packages using UTL_FILE. The second way Oracle allows access to the file system is via Java:

SQL> @java_file

G_R PERM            GRANTEE    PERMNAME                                 ACTION
--- --------------- ---------- ---------------------------------------- ----------
G   FilePermission  JAVASYSPRI [[ALL FILES]]                            read,write
G   FilePermission  JAVAUSERPR [[ALL FILES]]                            read
G   FilePermission  JMXSERVER  javavm/lib/management/*                  read
G   FilePermission  JMXSERVER  javavm/lib/management/jmxremote.access   read
G   FilePermission  JMXSERVER  javavm/lib/management/management.propert read
G   FilePermission  MDSYS      md/jlib/*                                read
G   FilePermission  MDSYS      md\jlib\*                                read
G   FilePermission  MDSYS      sdo/demo/georaster/jlibs/*               read
G   FilePermission  MDSYS      sdo\demo\georaster\jlibs\*               read
G   FilePermission  SYSTEM     [[ALL FILES]]                            read

10 rows selected.

SQL>

These default Java VM permissions that relate to file access are included by default. Oracle also write packages that use Java or a user could utilise a Java VM permission in the database to write Java or PL/SQL that uses Java to access the file system. Finding these packages are harder.

The third category after access via utl_file_dir / DIRECTORY objects via packages such as UTL_FILE and Java are packages that use direct C libraries to access the file system such as DBMS_BACKUP_RESTORE. I covered the access to this package in check 3.1.2 in the SANS Oracle Security Step By Step Guide back in 2002/2003. This is a dangerous package and it uses C to access the file system.

Because the SANS book was donated by SANS before publication to CIS for the Oracle Security Benchmark first version then this package access was also included that check list.

So there are three main ways to access the file system from PL/SQL

• Via PL/SQL packages that use utl_file_dir (earlier) and DIRECTORY objects
• Via the Java file permissions in the Java VM used direct or via Java or Java and PL/SQL
• Packages that use C libraries such as DBMS_BACKUP_RESTORE

The next step in understanding the size of the problem is to search Oracle packages that might do something with files:

SQL> col owner for a30
SQL> col object_name for a30
SQL> col procedure_name for a30
SQL> l
  1  select owner,object_name,procedure_name
  2  from dba_procedures
  3  where object_name like '%FILE%' or procedure_name like '%FILE%'
  4* order by 1,2,3
SQL> /

OWNER                          OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------ ------------------------------
AUDSYS                         DBMS_AUDIT_MGMT                LOAD_UNIFIED_AUDIT_FILES
CTXSYS                         CTX_OUTPUT                     LOGFILENAME
CTXSYS                         CTX_OUTPUT                     LOGFILEOVERWRITE
CTXSYS                         DRVIMR                         DIRECT_GET_BFILE
DVSYS                          DBMS_MACADM                    CHECK_DB_FILE_PARM_VARCHAR
DVSYS                          DBMS_MACOLS_SESSION            SET_ACCESS_PROFILE
GSMADMIN_INTERNAL              DBMS_GSM_POOLADMIN             ADDFILE
GSMADMIN_INTERNAL              DBMS_GSM_POOLADMIN             MODIFYFILE
GSMADMIN_INTERNAL              DBMS_GSM_POOLADMIN             REMOVEFILE
GSMADMIN_INTERNAL              DBMS_GSM_POOLADMIN             RETRIEVEFILE
...
XDB                            DBMS_XDB_CONTENT               CREATEFILE
XDB                            DBMS_XDB_CONTENT               CREATEFILE2
XDB                            DBMS_XDB_CONTENT               CREATEFILE3
XDB                            DBMS_XDB_CONTENT               DELETEFILE
XDB                            DBMS_XMLDOM                    SETNODEVALUEASDEFERREDBFILE
XDB                            DBMS_XMLDOM                    WRITETOFILE
XDB                            DBMS_XMLDOM                    WRITETOFILE
XDB                            DBMS_XMLDOM                    WRITETOFILE
XDB                            DBMS_XMLDOM                    WRITETOFILE
XDB                            DBMS_XMLDOM                    WRITETOFILE
XDB                            DBMS_XMLDOM                    WRITETOFILE
XDB                            DBMS_XMLDOM                    WRITETOFILE

OWNER                          OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------ ------------------------------
XDB                            DBMS_XMLDOM                    WRITETOFILE
XDB                            DBMS_XSLPROCESSOR              CLOB2FILE

801 rows selected.

SQL>

There are some that are not files as the SQL was not very clever, i.e. it found some with %PROFILE%. That is OK, we see over 800 rows of potential PL/SQL in the database that does something with files.

We could go much further and write a better query that also takes into consideration parameters that clearly do something with a file but the package name and procedure name do not give this away. I will not do this now.

So, the problem is that the database exposes so much file system activity possibilities but hardening guides recommend revoking PUBLIC EXECUTE from UTL_FILE and DBMS_BACKUP_RESTORE but access to this package is not available by default to PUBLIC.

If we revoked grants on these it would not secure the database as we would need to revoke grants on all packages that use these packages and the packages that use those ad-infinitum as well as locating all of the packages above and revoking them and all the callers as well.

Clearly this is nonsense and we cannot do this.

I have outlined this in my training classes for years and the only obvious solution is to focus on the DIRECTORY objects; do not allow them to be created, check the Oracle defaults and also check existing customer ones. If you have an old database also check utl_file_dir and then review all Java File VM permissions and Oracle roles and users that are granted them; remove and do not allow more to be granted. There is no solution to C based packages except to ensure they are not granted to anyone.

This is a massive task and why lock down profiles OS_ACCESS is great as it clearly blocks the low level as I have been saying for years but there was a gap.

Even if you set up lock down profiles and OS_ACCESS I would also control DIRECTORIES and permission and Java

#oracleace #sym_42 #oracle #database #security #dos #hacking #file #directory #java

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 #