Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Creating a DIRECTORY - Forensics Example in 23c

I want to talk about the foibles of DIRECTORY creation in the Oracle database. This is not a 23c specific issue but one that goes back years. I want to understand what rights and objects are created when we make a DIRECTORY object. Why? well this is useful for forensic analysis of a breach of an Oracle database. If we know exactly what happens when something is created or granted then when we see artefacts (pieces of evidence) then we can perhaps understand at a higher level what happened and why.

First connect to the 23c database:

C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 26 11:47:56 2023
Version 19.12.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL>

Now, lets see what system privileges exist for DIRECTORIES.

SQL> select * from system_privilege_map where name like '%DIR%';

PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-177 CREATE ANY DIRECTORY 0
-178 DROP ANY DIRECTORY 0

SQL>

There is no specific CREATE DIRECTORY that a schema owner can use to create a directory in their own schema. Therefore a user must have CREATE ANY DIRECTORY to create a DIRECTORY object.

Which users have CREATE ANY DIRECTORY in my 23c database?

who_has_priv: Release 1.0.3.0.0 - Production on Fri May 26 11:12:38 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK [SELECT ANY TABLE]: CREATE ANY DIRECTORY
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]:
USER TO SKIP [TEST%]:

Privilege => CREATE ANY DIRECTORY has been granted to =>
====================================================================
User => HRREST (ADM = NO)
User => SYS (ADM = NO)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYSBACKUP (ADM = NO)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
User => SYS (ADM = YES)
Role => DBA (ADM = NO) which is granted to =>
User => AV (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => OSAK_ADMIN_ROLE (ADM = NO) which is granted to =>
User => SYS (ADM = YES)

PL/SQL procedure successfully completed.

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

SQL>

SYS has the grant 7 times; once direct and 6 via various roles; this is typical in Oracle databases for grants to be made many times. First lets create a sample DIRECTORY as SYS:

SQL> sho user
USER is "SYS"
SQL> create directory test as '/test';

Directory created.

SQL>

So an interesting point, the folder "/test" does not exist on the server BUT the DIRECTORY object was created so if we used it it would fail. What about the owner and grants:

SQL> select * from dba_directories where directory_name='TEST';

OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
TEST
/test
3


1 row selected.

SQL>
SQL> @get_tab2



get_tab2: Release 1.2.0.0.0 - Production on Fri May 26 11:19:43 2023
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [SYS.TEST]


GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -

PL/SQL procedure successfully completed.


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

SQL>

So the DIRECTORY was created and it is owned by SYS and there are no grants by default for this DIRECTORY. Obviously we can then make grants on the DIRECTORY for it to be used. What if we want to create a directory in another schema from SYS. The privilege does have the keyword ANY so it would imply we can create a DIRECTORY in another schema. If we have CREATE ANY TABLE for instance we can create a table in a different schema. For instance SYS has this and we can create a table in my VA schema:

SQL> sho user
USER is "SYS"
SQL> create table va.test_table (col01 number);

Table created.

SQL>

So, we should be able to create a DIRECTORY in my VA schema:

SQL> create directory va.test2 as '/test';
create directory va.test2 as '/test'
*
ERROR at line 1:
ORA-02000: missing AS keyword


SQL>

So we cannot; this means that this %ANY% permission works differently to other %ANY% permissions such as CREATE ANY TABLE. hmmm. So can we grant CREATE ANY DIRECTORY to VA and create a directory as VA:

C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 26 12:47:37 2023
Version 19.12.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> grant create any directory to va;

Grant succeeded.

SQL> connect va/va@//192.168.56.18:1521/freepdb1
Connected.
SQL> create directory test2 as '/test';

Directory created.

SQL>

Lets have a look at the ownership of TEST2:

SQL> col owner for a20
SQL> col owner for a20
SQL> col directory_name for a30
SQL> col directory_path for a100
SQL> set lines 220
SQL> l
1* select owner,directory_name,directory_path from dba_directories
SQL> /

OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
SYS SDO_DIR_ADMIN /opt/oracle/product/23c/dbhomeFree/md/admin
SYS XMLDIR /opt/oracle/product/23c/dbhomeFree/rdbms/xml
SYS XSDDIR /opt/oracle/product/23c/dbhomeFree/rdbms/xml/schema
SYS ORACLE_BASE /opt/oracle
SYS ORACLE_HOME /opt/oracle/product/23c/dbhomeFree
SYS OPATCH_INST_DIR /opt/oracle/product/23c/dbhomeFree/OPatch
SYS DATA_PUMP_DIR /opt/oracle/admin/FREE/dpdump/F87259FB7D3C3519E0530100007F5D4C
SYS DBMS_OPTIM_LOGDIR /opt/oracle/product/23c/dbhomeFree/cfgtoollogs
SYS DBMS_OPTIM_ADMINDIR /opt/oracle/product/23c/dbhomeFree/rdbms/admin
SYS OPATCH_SCRIPT_DIR /opt/oracle/product/23c/dbhomeFree/QOpatch
SYS OPATCH_LOG_DIR /opt/oracle/product/23c/dbhomeFree/rdbms/log

OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
SYS JAVA$JOX$CUJS$DIRECTORY$ /opt/oracle/product/23c/dbhomeFree/javavm/admin/
SYS TEST2 /test
SYS TEST /test
SYS LOG_FILE_DIR /tmp/
SYS DATA_FILE_DIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/sales_history/
SYS MEDIA_DIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/product_media/
SYS SUBDIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/order_entry//2002/Sep
SYS SS_OE_XMLDIR /opt/oracle/userhome/oracle/unzipdemos/db-sample-schemas-master/order_entry/

19 rows selected.

SQL>

So, our TEST2 DIRECTORY is owned by SYS even though VA created the directory. What about the grants:

SQL> col grantee for a20
SQL> col owner for a20
SQL> col table_name for a20
SQL> col grantor for a20
SQL> col grantable for a3
SQL> set lines 220
SQL> l
1* select grantee, owner, table_name, grantor, privilege, grantable from dba_tab_privs where owner='SYS' and table_name='TEST2'
SQL> /

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
-------------------- -------------------- -------------------- -------------------- ---------------------------------------- ---
VA SYS TEST2 SYS EXECUTE YES
VA SYS TEST2 SYS READ YES
VA SYS TEST2 SYS WRITE YES

3 rows selected.

SQL>

hmmm, VA has been granted READ, WRITE and EXECTUTE on SYS.TEST2 by SYS. So VA had CREATE ANY DIRECTORY but this works differently to other privileges. If we look at CREATE ANY TABLE and the table we created in the VA schema; TEST_TABLE earlier this is owned by VA and has no grants:

SQL> select owner from dba_tables where table_name='TEST_TABLE';

OWNER
--------------------
VA

1 row selected.

SQL> @get_tab2



get_tab2: Release 1.2.0.0.0 - Production on Fri May 26 12:04:57 2023
Copyright (c) 2007, 2017, PeteFinnigan.com Limited. All rights reserved.

OBJECT TO CHECK [XXX_XXXX]: TEST_TABLE
SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: VA
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Testing root object => [VA.TEST_TABLE]


GRANTOR GRANTEE R S I U D A F D I R Q C E
------------- -------------- - - - - - - - - - - - - -

PL/SQL procedure successfully completed.


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

SQL>

This CREATE ANY TABLE is different behaviour to CREATE ANY DIRECTORY. If we create a DIRECTORY as SYS then its owned by SYS and no grants are made. We can of course make grants. If we grant CREATE ANY DIRECTORY to another user and then as that user create the DIRECTORY then its still owned by SYS and there are grants WITH GRANT for READ, WRITE and EXECUTE granted to the executor and grantee of CREATE ANY DIRECTORY but the grants were made by SYS and not the grantee of CREATE ANY DIRECTORY (VA in this example).

This is confusing and as I stated this knowledge is useful if we are forensically analysing a database to understand what happened. To analyse Oracle to understand what happened we must understand what happens in Oracle when we create things or use commands

The other point of note is that CREATE DIRECTORY .... does nor check if the directory on the OS exists or not.

#23c #dbsec #oracle #forensics

Are Oracle 23c Shipped Profiles Weak

Whilst the 23c version shipped by Oracle is a free developer release we should not complain as its free and we should also recognise that this is not production and this 23c version is not intended to be used in production and it is also aimed at developers.

But, that said, even if this is a free developer release should we accept weak security settings?

Well, no, of course not but this is the story I see though in a lot in customers databases. Security should be applied in all databases in an organisation so that the development, test and pre-production databases are all secured to the same level as production. If we don't we are maintaining two different security models, why? also if data ends up in pre-production databases then if it has weak security then that data is also put in danger.

Let's have a look at the shipped profiles in the Oracle 23c Free and see how secure they are for use:

SQL> @profiles



profiles.sql: Release 1.0.0.0.0 - Production on Tue May 23 11:22:15 2023
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.

F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
USER Profile F T S L M G L V
================================================================================
SYS DEFAULT 10 U U 1 U 7 U NULL
SYSTEM DEFAULT 10 U U 1 U 7 U NULL
APEX_LISTENE DEFAULT 10 U U 1 U 7 U NULL
APEX_PUBLIC_ DEFAULT 10 U U 1 U 7 U NULL
APEX_REST_PU DEFAULT 10 U U 1 U 7 U NULL
AV DEFAULT 10 U U 1 U 7 U NULL
PDBADMIN DEFAULT 10 U U 1 U 7 U NULL
SYSRAC DEFAULT 10 U U 1 U 7 U NULL
HR DEFAULT 10 U U 1 U 7 U NULL
VE DEFAULT 10 U U 1 U 7 U NULL
ORDS_PUBLIC_ DEFAULT 10 U U 1 U 7 U NULL
ORDS_METADAT DEFAULT 10 U U 1 U 7 U NULL
BI DEFAULT 10 U U 1 U 7 U NULL
VC DEFAULT 10 U U 1 U 7 U NULL
OE DEFAULT 10 U U 1 U 7 U NULL
PM DEFAULT 10 U U 1 U 7 U NULL
VA DEFAULT 10 U U 1 U 7 U NULL
VF DEFAULT 10 U U 1 U 7 U NULL
VB DEFAULT 10 U U 1 U 7 U NULL
HRREST DEFAULT 10 U U 1 U 7 U NULL
VG DEFAULT 10 U U 1 U 7 U NULL
IX DEFAULT 10 U U 1 U 7 U NULL
PFCLSCAN DEFAULT 10 U U 1 U 7 U NULL
SH DEFAULT 10 U U 1 U 7 U NULL
VJ DEFAULT 10 U U 1 U 7 U NULL
XS$NULL DEFAULT 10 U U 1 U 7 U NULL
LBACSYS DEFAULT 10 U U 1 U 7 U NULL
OUTLN DEFAULT 10 U U 1 U 7 U NULL
DBSNMP DEFAULT 10 U U 1 U 7 U NULL
APPQOSSYS DEFAULT 10 U U 1 U 7 U NULL
APEX_220200 DEFAULT 10 U U 1 U 7 U NULL
DBSFWUSER DEFAULT 10 U U 1 U 7 U NULL
GGSYS DEFAULT 10 U U 1 U 7 U NULL
ANONYMOUS DEFAULT 10 U U 1 U 7 U NULL
FLOWS_FILES DEFAULT 10 U U 1 U 7 U NULL
CTXSYS DEFAULT 10 U U 1 U 7 U NULL
DVSYS DEFAULT 10 U U 1 U 7 U NULL
DVF DEFAULT 10 U U 1 U 7 U NULL
AUDSYS DEFAULT 10 U U 1 U 7 U NULL
GSMADMIN_INT DEFAULT 10 U U 1 U 7 U NULL
GGSHAREDCAP DEFAULT 10 U U 1 U 7 U NULL
OLAPSYS DEFAULT 10 U U 1 U 7 U NULL
MDSYS DEFAULT 10 U U 1 U 7 U NULL
XDB DEFAULT 10 U U 1 U 7 U NULL
WMSYS DEFAULT 10 U U 1 U 7 U NULL
GSMCATUSER DEFAULT 10 U U 1 U 7 U NULL
MDDATA DEFAULT 10 U U 1 U 7 U NULL
SYSBACKUP DEFAULT 10 U U 1 U 7 U NULL
REMOTE_SCHED DEFAULT 10 U U 1 U 7 U NULL
GSMUSER DEFAULT 10 U U 1 U 7 U NULL
OJVMSYS DEFAULT 10 U U 1 U 7 U NULL
DIP DEFAULT 10 U U 1 U 7 U NULL
SYSKM DEFAULT 10 U U 1 U 7 U NULL
DGPDB_INT DEFAULT 10 U U 1 U 7 U NULL
SYS$UMF DEFAULT 10 U U 1 U 7 U NULL
SYSDG DEFAULT 10 U U 1 U 7 U NULL
================================================================================
USER Profile F T S L M G L V

PL/SQL procedure successfully completed.

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

SQL>

Hmm, this is not good. There is no verify function used and the lifetime is unlimited so the passwords can never be forced to change and even if they are changed they can be reset back to the same weak values. There are no other profiles used on any user. The Grace time is defaulted to 7 days, why??. What profiles have been shipped:

SQL> @prof
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
PROFILE F T S L M G L V
================================================================================
ORA_CIS_PROFILE 5 365 10 1 20 5 90 ORA12C_VERIFY_FUNCTION
ORA_STIG_PROFILE 3 175 D U 5 0 35 ORA12C_STIG_VERIFY_FUNCTION
DEFAULT 10 U U 1 U 7 U NULL
================================================================================
PROFILE F T S L M G L V

PL/SQL procedure successfully completed.

SQL>

Just the DEFAULT profile and the CIS and STIG profiles. These are better than the DEFAULT profile but they are not your designs. If we compare 21c we get:

C:\scripts>sqlplus sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 23 12:59:59 2023

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


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

SQL> @profiles



profiles.sql: Release 1.0.0.0.0 - Production on Tue May 23 12:59:52 2023
Copyright (c) 2007, 2009 PeteFinnigan.com Limited. All rights reserved.

F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
USER Profile F T S L M G L V
================================================================================
SYS DEFAULT 10 U U 1 U 7 180 NULL
SYSTEM DEFAULT 10 U U 1 U 7 180 NULL
XS$NULL DEFAULT 10 U U 1 U 7 180 NULL
LBACSYS DEFAULT 10 U U 1 U 7 180 NULL
OUTLN DEFAULT 10 U U 1 U 7 180 NULL
DBSNMP DEFAULT 10 U U 1 U 7 180 NULL
APPQOSSYS DEFAULT 10 U U 1 U 7 180 NULL
GGSYS DEFAULT 10 U U 1 U 7 180 NULL
ANONYMOUS DEFAULT 10 U U 1 U 7 180 NULL
DBSFWUSER DEFAULT 10 U U 1 U 7 180 NULL
CTXSYS DEFAULT 10 U U 1 U 7 180 NULL
DVSYS DEFAULT 10 U U 1 U 7 180 NULL
DVF DEFAULT 10 U U 1 U 7 180 NULL
AUDSYS DEFAULT 10 U U 1 U 7 180 NULL
GSMADMIN_INT DEFAULT 10 U U 1 U 7 180 NULL
OLAPSYS DEFAULT 10 U U 1 U 7 180 NULL
MDSYS DEFAULT 10 U U 1 U 7 180 NULL
XDB DEFAULT 10 U U 1 U 7 180 NULL
WMSYS DEFAULT 10 U U 1 U 7 180 NULL
GSMCATUSER DEFAULT 10 U U 1 U 7 180 NULL
USER05 DEFAULT 10 U U 1 U 7 180 NULL
FEED01 DEFAULT 10 U U 1 U 7 180 NULL
MDDATA DEFAULT 10 U U 1 U 7 180 NULL
AA DEFAULT 10 U U 1 U 7 180 NULL
ATKD DEFAULT 10 U U 1 U 7 180 NULL
SYSBACKUP DEFAULT 10 U U 1 U 7 180 NULL
PDBADMIN DEFAULT 10 U U 1 U 7 180 NULL
GSMUSER DEFAULT 10 U U 1 U 7 180 NULL
EMIL DEFAULT 10 U U 1 U 7 180 NULL
REMOTE_SCHED DEFAULT 10 U U 1 U 7 180 NULL
DEV02 DEFAULT 10 U U 1 U 7 180 NULL
USER03 DEFAULT 10 U U 1 U 7 180 NULL
PETE DEFAULT 10 U U 1 U 7 180 NULL
USE DEFAULT 10 U U 1 U 7 180 NULL
SYSRAC DEFAULT 10 U U 1 U 7 180 NULL
C##ATKD DEFAULT 10 U U 1 U 7 180 NULL
FRED DEFAULT 10 U U 1 U 7 180 NULL
BACK01 DEFAULT 10 U U 1 U 7 180 NULL
SI_INFORMTN_ DEFAULT 10 U U 1 U 7 180 NULL
ERIC DEFAULT 10 U U 1 U 7 180 NULL
IMPORTER DEFAULT 10 U U 1 U 7 180 NULL
OJVMSYS DEFAULT 10 U U 1 U 7 180 NULL
SCH DEFAULT 10 U U 1 U 7 180 NULL
JIM DEFAULT 10 U U 1 U 7 180 NULL
DIP DEFAULT 10 U U 1 U 7 180 NULL
RISK01 DEFAULT 10 U U 1 U 7 180 NULL
USER07 DEFAULT 10 U U 1 U 7 180 NULL
C##ATKR DEFAULT 10 U U 1 U 7 180 NULL
DEV DEFAULT 10 U U 1 U 7 180 NULL
ORDPLUGINS DEFAULT 10 U U 1 U 7 180 NULL
BILL DEFAULT 10 U U 1 U 7 180 NULL
ZULIA DEFAULT 10 U U 1 U 7 180 NULL
'x.hack();' DEFAULT 10 U U 1 U 7 180 NULL
VA DEFAULT 10 U U 1 U 7 180 NULL
FACADM DEFAULT 10 U U 1 U 7 180 NULL
BATCH01 DEFAULT 10 U U 1 U 7 180 NULL
USER06 DEFAULT 10 U U 1 U 7 180 NULL
SYSKM DEFAULT 10 U U 1 U 7 180 NULL
DEV03 DEFAULT 10 U U 1 U 7 180 NULL
C##ATKA DEFAULT 10 U U 1 U 7 180 NULL
DGPDB_INT DEFAULT 10 U U 1 U 7 180 NULL
ORDDATA DEFAULT 10 U U 1 U 7 180 NULL
ORABLOG DEFAULT 10 U U 1 U 7 180 NULL
BB DEFAULT 10 U U 1 U 7 180 NULL
ATKA DEFAULT 10 U U 1 U 7 180 NULL
ORACLE_OCM DEFAULT 10 U U 1 U 7 180 NULL
SYS$UMF DEFAULT 10 U U 1 U 7 180 NULL
PFCL_VD DEFAULT 10 U U 1 U 7 180 NULL
ATK DEFAULT 10 U U 1 U 7 180 NULL
ATKR DEFAULT 10 U U 1 U 7 180 NULL
USER01 DEFAULT 10 U U 1 U 7 180 NULL
SYSDG DEFAULT 10 U U 1 U 7 180 NULL
ORDSYS DEFAULT 10 U U 1 U 7 180 NULL
USER02 DEFAULT 10 U U 1 U 7 180 NULL
USER04 DEFAULT 10 U U 1 U 7 180 NULL
ORASCAN DEFAULT 10 U U 1 U 7 180 NULL
C##ATK DEFAULT 10 U U 1 U 7 180 NULL
UU DEFAULT 10 U U 1 U 7 180 NULL
DEV01 DEFAULT 10 U U 1 U 7 180 NULL
PFCL_VP DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
USER Profile F T S L M G L V

PL/SQL procedure successfully completed.

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

SQL>

The only difference in the default profile is that the password lifetime is still 180 days in 21c. This doesnt make sense and has been a value shipped in the default profile for years. 180 days lifetime without a verify function to enforce a password that may outlive a password cracker is meaningless. The shipped profiles in 21c are:

SQL> @prof
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
PROFILE F T S L M G L V
================================================================================
ORA_CIS_PROFILE 5 365 10 1 20 5 90 ORA12C_VERIFY_FUNCTION
ORA_STIG_PROFILE 3 175 D U 5 0 35 ORA12C_STIG_VERIFY_FUNCTION
DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
PROFILE F T S L M G L V

PL/SQL procedure successfully completed.

SQL>

The STIG and CIS profiles that are shipped are the same in 21c as in 23c. These default settings in the same form have been around since Oracle 10g/11g. These settings are not good enough; they are not designed by you to suit your security requirements. You should never use these default settings and should instead design your own profiles. I normally advise people to design multiple profiles as follows for these types of users:

  • Oracle Default Users

  • Application schemas

  • Admin type users such as DBA and support

  • General users who can direct connect to the database


Each of these should have settings suitable to the users type. For instance schemas can have a failed login of 1, so that only one attempt is allowed to log into a schema. Or the life time of an admin user should be short and a verify function to enforce strong passwords that can last longer than the life time buy a substantial amount.

Also ensure that the DEFAULT profile has the strongest individual settings from each of the other profiles. This means that if a user is created and is not assigned to its correct profile then it will still have the strongest rules.

Ensure that each user is assessed and assigned to the correct group. This means assigning the correct profile.

Just coming up with profile settings and designs is not trivial and some thought has to be used in these designs based on existing company standards, risk and use of each user type. If you need more groups of users create them, the above is just an example. The example of the failed logons for schemas is a good one. If we enforce a failed logins of 1 for schemas then they should only ever be accessed via a proxy for release or changes to the schema and the schema should not be used as the "connection user" for the application. This way we can enforce the strong settings we need.

Oracle have provided weak out of the box profiles for all users in 23c BUT its your job to change that and design profiles that you need.



Are we Securing Oracle or are we Securing Data in Oracle?

I have spoken about this before in this blog and I have advised and taught people for years about the same idea. My focus is Oracle Security but what does that mean?

I always tell people we are NOT securing Oracle the database; we are BUT not with that as the primary focus. We are in fact securing DATA. DATA is the focus and what we are aiming to do. When I say "we" I mean you should be thinking this as well.

If, a company just follows a guide from someone else such as the CIS Oracle benchmark then yes, this is supposed to be an industry consensus BUT it doesn't state company X or company Y or your Product A or Product B or your sensitive data Q or sensitive data P. How does this secure YOUR DATA? it doesn't of course. It does help on two levels though; you do something but it doesn't per-se secure YOUR CRITICAL DATA. There is also a side effect of following and implementing something like CIS Oracle benchmark; it gets staff thinking about and implementing some level of security; so this is good BUT what is actually needed to secure YOUR DATA?

I can talk for days about this idea and subject BUT I want to limit this to a shortish post to get people thinking. The goal of any Oracle security project should be to secure DATA not to simply implement an Oracle feature or follow a generic list. Lots of companies spend time and money on functional requirement and performance BUT think about security of data only when its too late

You must have a goal to protect certain data from certain attacks or from certain loss of that data from the system. To do this task we need to briefly consider the following:

  • What data are we protecting? - simple but if you don't know what you are protecting then how can you protect it?

  • Where is the data we are protecting? - We must know all copies of the data we want to protect. This is in the database and outside of it. If you wish to protect a certain data but that data is extracted from the database and stored on the file system of the database server or other servers or users PC's then no amount of database security will protect that data as I can steal it from elsewhere instead

  • How is the data processed? - How does the data get into the database and how does it leave?. For instance end users may enter data via forms in an application and data may leave via extracts though ETL or backups or?? we must know how data flows into and out of the system to be able to secure that data

  • Appreciate that Oracle is complex - The database is complex and your applications are complex. Data gets cached or copied and we must know how the data is flowed through Oracle and how it can be accessed via views, tables, dumps

  • What existing security policies exist? - Most companies do not have an Oracle specific security policy BUT do usually have an organisation wide security policy. Ensure that those company wide policy rules are reflected in the Oracle database such as password rules.

  • Create a data security standard - You cannot secure data randomly but as I stated above we also should not simply just follow a general list as that does not focus on your data security. Create your own standard based on data

  • Understand your skills and your budget - There are many ways to get to the same result in Oracle and the same applies to securing data. Plan your counter-measures to secure your data. It is obvious but you can only do what you have time and money and people to do those tasks with. Consider your budget. Spend as little as possible to secure as much data as possible

  • Secure the data in the database - If you secure the data in the database, do not let it leave the database otherwise the security in the database pointless.

  • Database Access Controls - Only let people directly access the database if there is an absolute need to do so. Do not lets swathes of staff share accounts and access. Use technical controls to prevent un-authorised access

  • Least Rights - Ensure every user authorised to access the database has only the privileges necessary to do their work and no more. This includes DBA, support and other users as well.

  • Data Access controls - This is the opposite end of the least privilege above except that we instead look from the point of view of the data rather than the user. Ensure that data is only accessible when absolutely necessary and no other times.

  • Context based security - When possible use context based security. This can be Oracle products such as VPD, TSDP or Database Vault but can also be home grown with views, triggers and other code. The normal object level security is very granular at the macro level and not at the micro level. We can use context based security to ensure that some specific data is accessed only on Wednesday by Jim between 15:00 and 16:00 and using screen 4 of the application only

  • Basic hardening - Do basic hardening such as removing defaults or samples and changing standard settings

  • Do secure coding - Ensure that all your applications that access the database and therefore the data are coded to secure coding standards to avoid possible code based attacks such as SQL Injection

  • Use audit - Design succinct audit trails that will capture any possible attacks against your database and your data. This audit trail design like the overall security design must be design based and not random. Create audit events that will capture things that should not happen


Wow, that's a brief overview of the key elements that should be considered when securing data that is held in an Oracle database. There are elements missing such as Operating system security and network security that must also be part of the overall plan. We didn't cover encryption of data both at rest, in motion in the database and in-motion across the estate.

Do not consider just buying a security add on until you have designed and built all of the core data security we have briefly covered above. i.e. I like Oracle Database Vault but what is the point of deploying Database Vault out of the box with no core data security or with one or more persons having access to root, oracle, SYSDBA and DV accounts. Also remember security products such as Database Vault are just applications and also should be secured!!

Also worth considering is when an application framework such as APEX is used. In this case we must secure our application code, secure coding, users privileges and more BUT also we must secure APEX itself, its settings and its repository in general and then we must secure the core database that APEX is deployed in.

Hope that this is useful as an overview to what can be involved in securing data in Oracle

#oracleace #23c #dbsec #oracle #database #security

Oracle 23c Schema Level Grants

One of the new security features added in Oracle 23c and one which we can use and test in the 23c FREE database released by Oracle is the ability to now grant ANY privileges at the schema level and not across the whole database.

Up until 23c we can either make direct grants on a schemas objects such as tables, views or procedures to another schema or user OR we can grant say SELECT ANY TABLE or EXECUTE ANY PROCEDURE but the issue then is that the grantee gets access to all other schemas objects in the database except SYS (provided the database supports o7_dictionary_accessibility and its turned on). Since 23c we now have dictionary protection that can be used to protect other Oracle managed users from system %ANY% privileges.

So, this is a new right that allows the equivalence of a SYSTEM %ANY% grant but only to one schema. Lets test this:

C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 14:06:36 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> grant select any table on schema va to vc;

Grant succeeded.

SQL>

We can also make a PL/SQL grant as well with this syntax:

SQL> grant execute any procedure on schema va to vc;

Grant succeeded.

SQL>

So, in this example VC can now use SELECT ANY TABLE and EXECUTE ANY PROCEDURE on VA objects. as a security auditor I want to know how I can check which grants have been made in a database and review and advise on them. For normal direct grants they are visible in DBA_TAB_PRIVS and role grants in DBA_ROLE_PRIVS and system grants in DBA_SYS_PRIVS. We can also use ALL_%, CDB_% as well in some circumstances but the DBA_% views are more consistent at the database level.

Let's first check these standard views for the new grants made above:

SQL> select * from dba_sys_privs where grantee='VC';

no rows selected

SQL> select * from dba_tab_privs where grantee='VC';

no rows selected

SQL> select * from dba_role_privs where grantee='VC';

GRANTEE
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF COM INH
--- --- --- --- ---
VC
CONNECT
NO NO YES NO NO


SQL>

The grants are not stored in the standard views BUT we didn't expect that really!. We will come back to the recording of the grants in a few minutes.

So, we granted SELECT ANY TABLE to VC on VA's tables and at the time of the grant VA had one table called TEST. What if we connect to VA and create two more tables, are they now also granted?

SQL> connect va/va@//192.168.56.18:1521/freepdb1
Connected.
SQL> create table test3 (col01 number);

Table created.

SQL> create table test4 (col01 number);

Table created.

SQL>

Show tables now for the VA user:

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST
TEST3
TEST4

SQL>

Only TEST was available when the grant made on VA objects to VC for SELECT ANY TABLE. Can VC access TEST4 created after the new schema level grant:

SQL> connect vc/vc@//192.168.56.18:1521/freepdb1
Connected.
SQL> select * from va.test4;

no rows selected

SQL>

Yes, we can access the new table. So it is better in one sense than direct grants BUT is it better than direct grants? There is no future control as every table created under VA in this case is available to VC.

In the old way we would create grants as a script maybe or manually for all VA's tables and grant to VC. In that case it is controlled as we make individual grants but we need to remember to make the grants specifically as the scheme is extended.

If sure we will always allow access to a scheme then this new right is OK but what about least rights?

This new privilege is useful and more fine grained than SELECT ANY TABLE generally BUT if we want to have a database that is designed to least rights then it is highly likely that these new grants are not least rights; in other words a grantee will inherit access to excess tables not needed or PL/SQL or...

Another example

C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 14:16:12 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL>

Make Direct grants the old way but from a DBA:

SQL> grant select on va.test to vd;

Grant succeeded.

SQL> grant select on va.test4 to vd;

Grant succeeded.

SQL>

We can script this if necessary to achieve the same results as the new schema level grants:

SQL> select 'grant select on '||owner||'.'||table_name||' to vd;'||chr(10)
2 from dba_tables where owner='VA' and table_name like 'TEST%';

'GRANTSELECTON'||OWNER||'.'||TABLE_NAME||'TOVD;'||CHR(10)
--------------------------------------------------------------------------------
grant select on VA.TEST to vd;
grant select on VA.TEST3 to vd;
grant select on VA.TEST4 to vd;

SQL>

We can then run the generated script.

Lets go back to the issue of finding where the new grants are stored

SQL> select view_name from dba_views where owner='SYS' and view_name like 'DBA_%SCHEMA%PRIV%';

VIEW_NAME
--------------------------------------------------------------------------------
DBA_SCHEMA_PRIVS
DBA_UNUSED_SCHEMA_PRIVS
DBA_UNUSED_SCHEMA_PRIVS_PATH
DBA_USED_SCHEMA_PRIVS
DBA_USED_SCHEMA_PRIVS_PATH

SQL>

Lets look at DBA_SCHEMA_PRIVS:

SQL> col grantee for a30
SQL> col privilege for a30
SQL> col schema for a30
SQL> col admin_option for a3
SQL> col common for a3
SQL> col inherited for a3
SQL> set lines 220
SQL> select * from dba_schema_privs where grantee='VC';

GRANTEE PRIVILEGE SCHEMA ADM COM INH
------------------------------ ------------------------------ ------------------------------ --- --- ---
VC EXECUTE ANY PROCEDURE VA NO NO NO
VC SELECT ANY TABLE VA NO NO NO

SQL>

These are the grants that I made at the start of this example.

We don’t need SELECT ANY TABLE on the whole database. Do we need SELECT ANY TABLE on a whole schema? Probably not. Yes it’s a saving to have this BUT it probably reduces the chance of a least privilege. Maybe an enhancement to this new privilege is an exception clause, i.e. GRANT SELECT ANY TABLE ON SCHEMA VA TO VC EXCLUDE ('TEST','TEST4'); or we should do it the old way and grant exactly what is needed only.

#23c #dbsec #oracleace #grants #oracle #database #security

Oracle 23c Dictionary Protection

One of the new features of 23c Free is dictionary protection.

This is clearly a replacement for the o7_dictionary_accessibility parameter that used to protect the SYS schema from system "ANY" privileges. SELECT ANY DICTIONARY was added to bypass this to some level to allow access to dictionary tables BUT some tables are excluded even from SELECT ANY DICTIONARY such as SYS.USER$. I wrote a blog about the difference between SELECT ANY DICTIONARY and the SELECT_CATALOG_ROLE some time ago that looks what is actually granted to SELECT ANY DICTIONARY and what is excluded from this system privilege. Oracle does not document either.

What does dictionary protection mean and how do we turn it on. Note this was written before Oracle released the limited security documentation.

So let us see if there are any system privileges related to this new protection or is it syntax based:

SQL> select name from system_privilege_map where name like '%PROT%';

no rows selected

SQL> select name from system_privilege_map where name like '%DIC%';

NAME
----------------------------------------
ANALYZE ANY DICTIONARY
SELECT ANY DICTIONARY

SQL>

Nothing, must be syntax based. Which users already have DICTIONARY PROTECTION enabled:

SQL> select username from dba_users where dictionary_protected='YES';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
XS$NULL
LBACSYS
CTXSYS
DVF
DVSYS
AUDSYS
GSMADMIN_INTERNAL
GGSHAREDCAP
XDB
SYSRAC
SYSBACKUP

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
SYSKM
SYSDG

13 rows selected.

SQL>

A very interesting observation is that SYS is not included in this list from the PDB. Lets check the O7_dictionary_accessibilty parameter?

C:\scripts>sqlplus sys/oracle@192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 2 13:48:13 2023
Version 19.12.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> sho parameter o7
SQL> sho parameter 07
SQL> sho parameter O7

This parameter has gone of course in 23c. But if we create a user with SELECT ANY TABLE can it access a table in the SYS schema:

SQL> create user vj identified by vj;

User created.

SQL> grant create session to vj;

Grant succeeded.

SQL> connect vj/vj@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> grant select any table to vj;

Grant succeeded.

SQL> connect vj/vj@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

This is only a test to access a single SYS table but it should be representative. So, it seems that even with out O7_dictionary_accessibility and SYS not having DICTIONARY PROTETION that SYS tables cannot be accessed with ANY system privileges. Just for completeness just check the new value for SYS in the root container.

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter session set container=cdb$root;

Session altered.

SQL> select username from dba_users where dictionary_protected='YES';

USERNAME
--------------------------------------------------------------------------------
SYSRAC
XS$NULL
LBACSYS
CTXSYS
DVF
DVSYS
AUDSYS
GSMADMIN_INTERNAL
GGSHAREDCAP
XDB
SYSBACKUP

USERNAME
--------------------------------------------------------------------------------
SYSKM
SYSDG

13 rows selected.

SQL>

We have to assume SYS is handled differently now.

So how do we use this new setting. Lets search $ORACLE_HOME for it:

[oracle@localhost admin]$ grep -i "dictionary_protect" *
cdenv_mig.sql:Rem nishchau 12/22/21 - Proj 89500: add DBA_USERS.DICTIONARY_PROTECTED
cdenv_mig.sql: PROTECTED, READ_ONLY, DICTIONARY_PROTECTED)
cdenv_mig.sql: -- DICTIONARY_PROTECTED
cdenv_mig.sql: MANDATORY_PROFILE_VIOLATION, PROTECTED, READ_ONLY, DICTIONARY_PROTECTED)
cdenv_mig.sql: -- DICTIONARY_PROTECTED
cdenv.sql:Rem nishchau 12/22/21 - Proj 89500: add DBA_USERS.DICTIONARY_PROTECTED
cdenv.sql:comment on column USER_USERS.DICTIONARY_PROTECTED is
cdenv.sql:comment on column DBA_USERS.DICTIONARY_PROTECTED is
cdenv.sql: DICTIONARY_PROTECTED)
cdenv.sql: -- DICTIONARY_PROTECTED
cdenv.sql:comment on column ALL_USERS.DICTIONARY_PROTECTED is
dsec.bsq:REM nishchau 12/22/21 - Proj 89500: Mark AUDSYS DICTIONARY_PROTECTED
e21.sql:Rem nishchau 12/22/21 - Proj 89500: clear DICTIONARY_PROTECTED bit in
e21.sql:Rem DICTIONARY_PROTECTED
[oracle@localhost admin]$
[oracle@localhost admin]$ cat dsec.bsq | grep "dictionary prote"
REM dictionary protected
REM skulhari 10/21/22 - Bug 34453879: Mark SYSKM user dictionary protected
REM vivnatar 09/27/22 - Bug 34453883: SYSDG dictionary protection
create user AUDSYS enable dictionary protection no authentication account lock
enable dictionary protection
create user sysdg no authentication account lock enable dictionary protection
create user syskm no authentication account lock enable dictionary protection
create user sysrac no authentication enable dictionary protection
[oracle@localhost admin]$

This is the syntax used to enable this new feature. Lets test this on other users and see what it does.

First lets check to see which schemas have objects and in which tablespace:

SQL> select distinct tablespace_name from dba_tables;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX

USERS

SQL>

Note that there seems to be a blank tablespace name. Lets continue the investigate:

SQL> select count(*),owner from dba_tables where tablespace_name='SYSTEM' group by owner;

COUNT(*) OWNER
---------- --------------------------------------------------------------------------------------------------------------------------------
969 SYS
26 SYSTEM
3 OUTLN
1 GSMADMIN_INTERNAL
6 OJVMSYS
22 LBACSYS

6 rows selected.

SQL> select count(*),owner from dba_tables where tablespace_name='SYSAUX' group by owner;

COUNT(*) OWNER
---------- --------------------------------------------------------------------------------------------------------------------------------
580 SYS
50 GSMADMIN_INTERNAL
33 WMSYS
42 CTXSYS
2 SYSTEM
3 DBSFWUSER
1 GGSHAREDCAP
12 DBSNMP
5 APPQOSSYS
34 XDB
2 OLAPSYS

COUNT(*) OWNER
---------- --------------------------------------------------------------------------------------------------------------------------------
129 MDSYS
335 APEX_220200
1 FLOWS_FILES
43 DVSYS

15 rows selected.

SQL>

We have default schemas that have objects in the SYSTEM tablespace and the SYSAUX tablespace. Notice that from earlier and here, for instance, GSMADMIN_INTERNAL is dictionary protected and OUTLN is not. First lets grant SELECT ANY TABLE TO our sample user VB:

SQL> grant select any table to vb;

Grant succeeded.

SQL>

Now find tables owned by GSMADIN_INTERNAL and OUTLN so we can test the ANY grant against them:

SQL> select table_name from dba_tables where owner='OUTLN' and tablespace_name='SYSTEM';

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
OL$
OL$HINTS
OL$NODES

SQL>
SQL> select table_name from dba_tables where owner='GSMADMIN_INTERNAL' and tablespace_name='SYSTEM';

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
SHARD_TS

SQL>

Connect to our sample VB user and test:

SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from outln.ol$;

COUNT(*)
----------
0

SQL> select count(*) from gsmadmin_internal.shard_ts;
select count(*) from gsmadmin_internal.shard_ts
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

This seems to prove the technology, GSMADMIN_INTERNAL is protected and OUTLN is not. Check tables owned my XDB also which is DICTIONARY PROTECTED>

SQL> select table_name from dba_tables where tablespace_name='SYSAUX' and owner='XDB';

TABLE_NAME
--------------------------------------------------------------------------------
X$NM7NOP924P5RJP3E0KOT51368GPG
X$QN7NOP924P5RJP3E0KOT51368GPG
X$PT7NOP924P5RJP3E0KOT51368GPG
XDB$TTSET
XDB$ROOT_INFO
XDB$H_INDEX
XDB$XDB_READY
XDB$DBFS_VIRTUAL_FOLDER
XDB$NONCEKEY
XDB$CDBPORTS
XDB$IMPORT_TT_INFO

TABLE_NAME
--------------------------------------------------------------------------------
XDB$TSETMAP
XDB$D_LINK
XDB$COLUMN_INFO
XDB$PATH_INDEX_PARAMS
XDB$IMPORT_QN_INFO
XDB$IMPORT_NM_INFO
XDB$IMPORT_PT_INFO
APP_USERS_AND_ROLES
APP_ROLE_MEMBERSHIP
XDB$REPOS
XDB$MOUNTS

TABLE_NAME
--------------------------------------------------------------------------------
JSON$COLLECTION_METADATA
JSON$USERS
JSON$USER_CREDENTIALS
JSON$USER_ROLES
XDB_INDEX_DDL_CACHE
XDB$CHECKOUTS
XDB$DXPTAB
XDB$XIDX_PART_TAB
XDB$XIDX_PARAM_T
XDB$XTAB
XDB$XTABNMSP

TABLE_NAME
--------------------------------------------------------------------------------
XDB$XTABCOLS

34 rows selected.

SQL>

Check if we can access one of these tables as VB:

SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from xdb.xdb$xtab;
select count(*) from xdb.xdb$xtab
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

This also works. Lets try and create a table as our sample VA user and then try and protect it>

C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 15:28:13 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> create table va.test5(col01 number) tablespace system;

Table created.

SQL>

As the user VB can we access this table?

SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from va.test5;

COUNT(*)
----------
0

SQL>

Yes of course. What if we try and make VA DICTIONARY PROTECTED as we created our test table in the SYSTEM tablespace; I know we should not do this, its just a test:

SQL> alter user va enable dictionary protection;
alter user va enable dictionary protection
*
ERROR at line 1:
ORA-40374: Dictionary protection feature is supported for only Oracle
maintained users.


SQL>

Hmmm, we cannot make our own users DICTIONARY PROTECTED even if we created tables in the SYSTEM tablespace. We could probably work around this and set _oracle_script and recreate the user and the table in the dictionary and test enable dictionary protection. This would likely work but this can break upgrades so we wont do this as the user would be marked as Oracle maintained but its isn’t.

Can we enabled DICTIONARY PROTECTION on other oracle users?

A user not currently protected is OJVMSYS:

SQL> alter user ojvmsys enable dictionary protection;

User altered.

SQL>

And now we can test access to a table owned by this default user:

SQL> select table_name from dba_tables where tablespace_name='SYSTEM' and owner='OJVMSYS';

TABLE_NAME
--------------------------------------------------------------------------------
OJDS$BINDINGS$
OJDS$INODE$
OJDS$ATTRIBUTES$
OJDS$REFADDR$
OJDS$PERMISSIONS$
OJDS$SHARED$OBJ$

6 rows selected.

SQL>

Connect as the user VB:

SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;
select count(*) from ojvmsys.ojds$inode$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

This seems to work as planned. Remove the DICTIONARY PROTECTION and test again:

C:\\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 15:43:08 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> alter user ojvmsys disable dictionary protection;
alter user ojvmsys disable dictionary protection
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers


SQL> alter session set container=cdb$root;

Session altered.

SQL> alter user ojvmsys disable dictionary protection;

User altered.

SQL> alter session set container=freepdb1;

Session altered.

SQL> select dictionary_protected from dba_users where username='OJVMSYS';

DIC
---
NO

SQL>

Strange, we added DICTIONARY PROTECTION in the PDB but cannot remove until we are connected to the CDB?? " BUG? (or not, see below as to why!), logically if we cannot remove it in the PDB we should not be able to add it in the PDB. It would make sense to allow protection at the PDB level but if that were the case then we should be able to remove it in the PDB. Seems like a bug at some level to me.

Test now that VB can access the table now that the protection is removed:

SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;

COUNT(*)
----------
3

SQL>

Now protection is removed the user VB with SELECT ANY TABLE works

Check if we ALTER USER in the PDB is the user dictionary protected in the CDB:

C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 15:46:33 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> select sys_context('userenv','con_name')
2 /

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
FREEPDB1

SQL> alter user ojvmsys enable dictionary protection;
alter user ojvmsys enable dictionary protection
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers


SQL>

OK, we know now that we cannot set DICTIONARY PROTECTION in the PDB so why did i think that this could be a bug?

Well, I had the _oracle_script set as I was testing whether I could create a DICTIONARY PROTECTED user of my own above. Thats why i was able to add it in the PDB above, so not a BUG!!

Check everything is now fine:

SQL> select dictionary_protected from dba_users where username='OJVMSYS';

DIC
---
NO

SQL> alter session set container=cdb$root;

Session altered.

SQL> select dictionary_protected from dba_users where username='OJVMSYS';

DIC
---
NO

SQL>

Re-run the test from the CDB this time:

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter user ojvmsys enable dictionary protection;

User altered.

SQL> alter session set container=freepdb1;

Session altered.

SQL> select dictionary_protected from dba_users where username='OJVMSYS';

DIC
---
YES

SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;
select count(*) from ojvmsys.ojds$inode$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter session set container=cdb$root;

Session altered.

SQL> alter user ojvmsys disable dictionary protection;

User altered.

SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;

COUNT(*)
----------
3

SQL>

This is correct behaviour. So, this is now more fine grained than it was prior to 23c. We can now limit SYSTEM ANY privileges on each default user by enabling dictionary protection.

This is great but its unclear about SYS, clearly SYS is still protected from system ANY grants but the flag is not set.

It would also be great to protect our own users from system ANY privileges and we could do that by telling Oracle our schema is ORACLE_MAINTAINED but this would not be supported and could potentially cause issues with upgrades. Clearly protecting against system any for a realm is a feature of Database Vault out of the box.

#23c #oracleace #dbsec #oracle #security #protection

Oracle 23c New Longer 1024 Character Passwords

One of new security features of 23c that was mentioned before the Free developer release was that passwords can now be 1024 characters in length, much longer than the previous length of 30 characters. Julian mentioned this in his list back in November 2022. This is a massive difference. If we connect to 21c XE and create a user with a 30 character password:

C:\scripts>sqlplus sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 18 09:48:37 2023
Version 19.12.0.0.0

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


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> create user va identified by aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

User created.

SQL>

It works, but if we add one more "a" to make the password 31 characters, it fails:

SQL> create user vb identified by aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
create user vb identified by aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
*
ERROR at line 1:
ORA-00972: identifier is too long


SQL>

Lets see if there is a change to SYS.USER$. This is 21c:

SQL> desc sys.user$
Name Null? Type
----------------------------------------- -------- ----------------------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
DATATS# NOT NULL NUMBER
...
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
...

SQL>

And compare to 23c:

SQL> desc sys.user$
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
DATATS# NOT NULL NUMBER
...
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
...

SQL>

Obviously there is no change in the table structure. The storage needed for the password does not change as Oracle stores a password hash, not a clear text password. So a password of 30 characters in 21c takes the same space as a password of 1024 characters in 23c. This is because the hash generated is a fixed length; it's just the input string, the password that is longer. The internal processing has changed to allow a longer input; the password.

Let's now see the creation of a 1024 byte password by changing the user VA password:

SQL> sho user
USER is "SYS"
SQL> alter user va identified by aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

User altered.

SQL>

This works as expected of course. What if we try and create a 1025 character password:

SQL> alter user va identified by aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab;
alter user va identified by aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab
*
ERROR at line 1:
ORA-28218: password length more than 1024 bytes


SQL>

Notice that it correctly fails but the error message is not the same as 21c. in 21c its an ORA-00972 error, in 23c we now get a ORA-28218 error that is specific to password length and not the slightly more generic identifier is too long.

But what if I try and connect with the user VA and its 1024 password:

SQL> connect va/aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa@//192.168.56.18:1521/freepdb1
ERROR:
ORA-24960: the attribute OCI_ATTR_PASSWORD is greater than the maximum
allowable length of 255


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

This fails because I am using a 19c client and the new password length is
If we connect using the 23c client on the server then it works of course:

SQL> connect va/aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa@//192.168.56.18:1521/freepdb1
Connected.
SQL>

The instant client for 23c is not available for general download yet but SQLcl for 23c is available. I am using vanilla SQL*Plus.

If we compare the password hashes for a 1024 character password and a short 2 character password:

SQL> select name,password,spare4 from sys.user$ where name in ('VA','VB');

NAME
--------------------------------------------------------------------------------------------------------------------------------
PASSWORD
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VA

S:5CF633AF53721A0F0D18B9243513516EF6AFCCA29CD949083D9A01C3E0C9;T:CEC465D8FD52D0D45AD585E5E6498A332107C15DAC07058B89289DFAD9316AB1C54AD414932BB71C7B8EE32FE3B53C32AD836AB07079A9D4D7C25592A07D94E054CFBDAB81562CE5029D0461E87
51C52

VB


NAME
--------------------------------------------------------------------------------------------------------------------------------
PASSWORD
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:318F8C7E71D1CDCF1F70722B8EF69E2DB8055CAF0D7F5A9EBB00F0DFF433;T:2BD036BD523CBE08F1C76668271D48856795A1456408F98D0DCC019488BEDEA054958D79B3881A81461F760303173A6B1FBB63353617EBA85ECD6871C3AB127FC7D53D58487371AA9766E5C8261
55F1A


SQL>

They look the same; this is correct of course as I stated above as Oracle does not store the password or an encrypted password, its a hash that is fixed length. There is no way to tell from the values in the spare4 column of SYS.USER$

Now the obvious. People cannot remember a 1024 character password and whilst I applaud the massive increase in length that would make cracking these hashes need quantum computers or three letter agency level hardware there is a side channel issue. That is the 1024 password has to be stored, or typed in and passed to the logon prompt. That means theft or interception is possible without the need to crack a 1024 length password. Using these long passwords with a wallet - didn't test this yet - would make sense but if its an auto-open wallet then simple access to the users OS shell would allow use of the 1024 password without knowledge of it. So if we apply a shorter password to the wallet that effectively reduces the size of the database password from 1024 to the length of the wallet password. The biggest issue with long passwords is the management, choice and storage and then subsequent use of those long passwords to actually log onto 23c.


#23c
#oracleace
#dbsec
#oraclesecurity
#password
#cracking

Oracle Protected Users in 23c

In looking at the new Oracle database 23c Free developer release I noticed a new column in the DBA_USERS view called PROTECTED. A search of the 23c documentation and google and also the newly released 23c security guide didn't shed any light on what this column means. The description of DBA_USERS for 23c states that this column is new with 23c and states - This column is set to YES or NO per user and indicates whether the user is PROTECTED or not. It goes on to state that a protected user can only be managed by another protected user or a COMMON user.

As I cannot find any details of this feature we don't know how to turn it on or off or what it actually does. hmmmm.

I did a search of the $ORACLE_HOME/rdbms/admin directory and could not find any clues as to this feature so lets just experiment instead. dcore.bsq and cdenv.sql mention PROTECTED but there is no new details over what the documentation for DBA_USERS states.

OK, lets instead just guess.

Knowing the syntax for DICTIONARY PROTECTED and NO AUTHENTICATION and other features then we can take a guess:

SQL> create user ve identified by ve protected;

User created.

SQL>

That was easy but did it really get turned on:

SQL> select username from dba_users where protected='YES';

USERNAME
--------------------------------------------------------------------------------
VE

SQL>

Yes, its PROTECTED. But can we turn it off by trying to guess the syntax:

SQL> alter user ve unprotected;
alter user ve unprotected
*
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter user ve protected off;
alter user ve protected off
*
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter user ve protected disable;
alter user ve protected disable
*
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter user ve disable protected;
alter user ve disable protected
*
ERROR at line 1:
ORA-02000: missing DICTIONARY keyword


SQL>

I don't know at this point if it can be turned off at all or I just didn't manage to guess the correct syntax. Is it possible also to take an existing user and make it PROTECTED:

SQL> alter user vd protected;
alter user vd protected
*
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter user vd enable protection;
alter user vd enable protection
*
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter user vd enable protected;
alter user vd enable protected
*
ERROR at line 1:
ORA-00922: missing or invalid option


SQL>
SQL> alter user vb identified by vb protected;
alter user vb identified by vb protected
*
ERROR at line 1:
ORA-00922: missing or invalid option


SQL>

So, at this point we can create a user that is PROTECTED and its visible to the database as protected. We can't find the syntax to make another existing user PROTECTED and we can't find the syntax to disable PROTECTED for a PROTECTED user. So, what does PROTECTED do. Our user VE is protected so now create a user VF that is not PROTECTED and see if we can "manage" the PROTECTED user. The manual for DBA_USERS suggests that only COMMON accounts and other PROTECTED accounts can manage a PROTECTED user. So create VF with ALTER USER to see if we can "manage" VE:

SQL> create user vf identified by vf;

User created.

SQL> grant create session to vf;

Grant succeeded.

SQL> grant alter user to vf;

Grant succeeded.

SQL>

Now we can use ALTER USER and try and change the PROTECTED users password. Surely this is "managing" a user:

SQL> connect vf/vf@//192.168.56.18:1521/freepdb1
Connected.
SQL> alter user ve identified by ve;

User altered.

SQL>

This means that PROTECTED does allow a non-PROTECTED user to change its password. What about granting a role to a PROTECTED USER:

C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 10 12:16:17 2023
Version 19.12.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> sho user
USER is "SYS"
SQL> grant grant any role to vf;

Grant succeeded.

SQL> connect vf/vf@//192.168.56.18:1521/freepdb1
Connected.
SQL> grant db_developer_role to ve;

Grant succeeded.

SQL>

Is the role granted and what does "manage" mean:

SQL> connect ve/ve@//192.168.56.18:1521/freepdb1
Connected.
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
DB_DEVELOPER_ROLE
SODA_APP
CTXAPP

SQL>

The role was granted. Now connect to the manager non-protected user and revoke the role from the PROTECTED user:

SQL> connect vf/vf@//192.168.56.18:1521/freepdb1
Connected.
SQL> revoke db_developer_role from ve;

Revoke succeeded.

SQL>

So what does manage mean? let's connect to SYS and grant DROP USER to the manage user (non-protected) and then try and drop the PROTECTED user:

C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 10 12:56:07 2023
Version 19.12.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> grant drop user to vf;

Grant succeeded.

SQL> connect vf/vf@//192.168.56.18:1521/freepdb1
Connected.
SQL> drop user ve;
drop user ve
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> drop user vd;

User dropped.

SQL>

So we can drop VD which is a non-protected user we cannot drop VE which is a protected user. Can we do other ALTER USER commands on the protected user:

SQL> sho user
USER is "VF"
SQL> alter user ve no authentication;

User altered.

SQL> alter user ve no authentication;

User altered.

SQL> alter user ve identified by ve;

User altered.

SQL>

Yes we can, What about creating PL/SQL and compiling it and dropping it:

SQL> grant db_developer_role to ve;

Grant succeeded.

SQL> sho user
USER is "VF"

SQL> connect ve/ve@//192.168.56.18:1521/freepdb1
Connected.
SQL> create procedure test as
2 begin
3 null;
4 end;
5 /

Procedure created.

SQL> connect vf/vf@//192.168.56.18:1521/freepdb1
Connected.
SQL> sho user
USER is "VF"
SQL> alter procedure ve.text compile;

Procedure altered.

SQL> create procedure ve.test1 as
2 begin
3 null;
4 end;
5 /

Procedure created.

SQL> drop procedure ve.test;

Procedure dropped.

SQL>

So in summary we can ALTER PL/SQL, compile PL/SQL, DROP PL/SQL and even create PL/SQL in a PROTECTED schema from a non-protected schema. The only thing we cannot do is as drop a protected user from a non-protected user.

SQL> sho user
USER is "VF"
SQL> drop user ve cascade;
drop user ve cascade
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

There is likely to be more than just blocking DROP, I just didn't find it quickly. But what use is stopping an account from being dropped?

If we can create objects in a PROTECTED schema and drop them and change them then we can simply remove all objects even if we cannot DROP a user/schema. That is just as bad as dropping it as if those objects represent an application then removing them would still destroy the application.

In the past I blogged about dropping SYSTEM and Oracle has a built in mechanism to protect some users such as SYSTEM. That blog is - ORA-28050 - Can I drop the SYSTEM User? - Don't try this in your database:

SQL> drop user system cascade;
drop user system cascade
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped


SQL>

OK, last test for now. If the documentation for DBA_USERS states that a protected user can be managed only by another protected user then we should be able to create a second PROTECTED user and give it DROP USER and drop the first PROTECTED user. Here is an example:

C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 10 13:05:58 2023
Version 19.12.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> select username,protected from dba_users where protected='YES';

USERNAME
--------------------------------------------------------------------------------
PRO
---
VE
YES


SQL>
SQL> create user vg identified by vg protected;

User created.

SQL> select username,protected from dba_users where protected='YES';

USERNAME
--------------------------------------------------------------------------------
PRO
---
VE
YES

VG
YES


SQL>
SQL> grant create session, drop user to vg;

Grant succeeded.

SQL> connect vg/vg@//192.168.56.18:1521/freepdb1
Connected.
SQL> drop user ve cascade;

User dropped.

SQL>

So, we can drop a protected user with another protected user so the manual on DBA_USERS is correct. But just preventing the removal of an account may stop the whole application from being destroyed in a simple one "drop user xxxx cascade" command BUT someone can still remove all the objects one by one. Does this PROTECTED status stop other system grants? I will test going forward to see but at this time the one use i see is preventing a single command destroying a schema.

I can see a use for this single feature as during teaching my Oracle Security training class in Holland I was showing as a demo removal of schemas and users not needed to show working towards least rights / privileges in a database. I dropped my demo schema by accident as the name was similar to the one i was demo'ing. This was easily fixed by simply reverting the VM but having my schema as a PROTECTED user woould have prevented that little error. Thats live demos!!

It would be great to see if there is more aspects to this feature but i need to get on with something else so this was a good first look at PROTECTED users in 23c.

#oracleace
#23c
#oracle
#security
#protected
#users