Pete Finnigan's Oracle Security Weblog

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

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

One of the new security features of Oracle database 23c that was mentioned at the end of last year in talks such as the keynote at the UKOUG was the inclusion of a new developer role for developers to use in an Oracle database. It was said that in the past developers tended to get granted CONNECT and RESOURCE. My experience in performing security audits on Oracle databases is that not just developers tended to be granted CONNECT and RESOURCE. I often see a large percentage of accounts in customer systems with these two roles. I will come back to this point a bit later in the post.

Let's compare CONNECT, RESOURCE and the new DB_DEVELOPER_ROLE. First lets see CONNECT:

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> @find_all_privs



find_all_privs: Release 1.0.7.0.0 - Production on Thu Apr 06 15:25:13 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

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

User => CONNECT has been granted the following privileges
====================================================================
        SYS PRIV => CREATE SESSION grantable => NO
        SYS PRIV => SET CONTAINER grantable => NO

PL/SQL procedure successfully completed.

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

SQL>

I have always said since 12c why include SET CONTAINER? in a PDB and for a LOCAL user SET CONTAINER doesn't make sense. If CONNECT only had CREATE SESSION then there is no problem using this Oracle designed role but because of SET CONTAINER we should not use it as it has one extra right that you do not need.

Here is an example of trying to use it from a LOCAL user in the 23c PDB:

SQL> create user vc identified by vc;

User created.

SQL> grant connect to vc;

Grant succeeded.

SQL> connect vc/vc@//192.168.56.18:1521/freepdb1
Connected.
SQL> alter session set container=cdb$root;
ERROR:
ORA-01031: insufficient privileges


SQL>

OK, we cannot use SET CONTAINER to traverse to the CDB as a LOCAL user. Next lets look at the rights for RESOURCE:

SQL> @find_all_privs



find_all_privs: Release 1.0.7.0.0 - Production on Thu Apr 06 15:31:51 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

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

User => RESOURCE has been granted the following privileges
====================================================================
        ROLE => SODA_APP which contains =>
                TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_ADMIN grantable => NO
                TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_USER_ADMIN grantable => NO
                TABLE PRIV => READ object => XDB.JSON$USER_COLLECTION_METADATA grantable => NO
        SYS PRIV => CREATE ANALYTIC VIEW grantable => NO
        SYS PRIV => CREATE ATTRIBUTE DIMENSION grantable => NO
        SYS PRIV => CREATE CLUSTER grantable => NO
        SYS PRIV => CREATE HIERARCHY grantable => NO
        SYS PRIV => CREATE INDEXTYPE grantable => NO
        SYS PRIV => CREATE MATERIALIZED VIEW grantable => NO
        SYS PRIV => CREATE OPERATOR grantable => NO
        SYS PRIV => CREATE PROCEDURE grantable => NO
        SYS PRIV => CREATE PROPERTY GRAPH grantable => NO
        SYS PRIV => CREATE SEQUENCE grantable => NO
        SYS PRIV => CREATE SYNONYM grantable => NO
        SYS PRIV => CREATE TABLE grantable => NO
        SYS PRIV => CREATE TRIGGER grantable => NO
        SYS PRIV => CREATE TYPE grantable => NO
        SYS PRIV => CREATE VIEW grantable => NO

PL/SQL procedure successfully completed.

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

SQL>

The grants to RESOURCE have grown since earlier versions. There were 9 system grants in 11.2 for instance and now 15. So the size and reach of this role grows as Oracle grown. If we grant CONNECT and RESOURCE then the user/schema gets 17 SYSTEM PRIVILEGES. Does a user really need 17 system rights.

In the talks that occurred last year in advance of 23c some Oracle staff showed use of a package DBMS_DEVELOPER_ADMIN to grant this role. Lets check the role exists in 23c FREE:

SQL> select * from dba_roles where role like '%DEV%';

ROLE
--------------------------------------------------------------------------------
   ROLE_ID PASSWORD AUTHENTICAT COM O INH IMP
---------- -------- ----------- --- - --- ---
EXTERNAL_NAME
--------------------------------------------------------------------------------
DB_DEVELOPER_ROLE
        94 NO       NONE        YES Y YES NO


GRAPH_DEVELOPER
       115 NO       NONE        YES Y YES NO



2 rows selected.

SQL>

Yes, it does, its called DB_DEVELOPER_ROLE. The package referenced by some people DBMS_DEVELOPER_ADMIN is not in the 23c Free database:

SQL> select object_name,owner,object_type from dba_objects where object_name like '%DEVELOPER%';

OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
APEX_WORKSPACE_DEVELOPERS
PUBLIC
SYNONYM

APEX_APPL_DEVELOPER_COMMENTS
PUBLIC
SYNONYM

APEX_DEVELOPER_ACTIVITY_LOG
PUBLIC
SYNONYM

WWV_FLOW_DEVELOPERS
APEX_220200
TABLE

WWV_FLOW_DEVELOPERS_PK
APEX_220200

OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
INDEX

WWV_FLOW_DEVELOPERS_PK2
APEX_220200
INDEX

WWV_FLOW_DEVELOPERS_ID
APEX_220200
INDEX

WWV_FLOW_DEVELOPERS_FKIDX
APEX_220200
INDEX

WWV_FLOW_DEVELOPER_WORKSPACES
APEX_220200
VIEW


OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
WWV_FLOW_DEVELOPERS_T1
APEX_220200
TRIGGER

WWV_FLOW_FND_DEVELOPER_API
APEX_220200
PACKAGE

WWV_FLOW_DEVELOPER_TOOLBAR
APEX_220200
PACKAGE

WWV_BIU_FLOW_DEVELOPERS_AUDIT
APEX_220200
TRIGGER

WWV_FLOW_FND_DEVELOPER_API
APEX_220200

OBJECT_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
PACKAGE BODY

WWV_FLOW_DEVELOPER_TOOLBAR
APEX_220200
PACKAGE BODY

APEX_WORKSPACE_DEVELOPERS
APEX_220200
VIEW

APEX_APPL_DEVELOPER_COMMENTS
APEX_220200
VIEW

APEX_DEVELOPER_ACTIVITY_LOG
APEX_220200
VIEW


18 rows selected.

SQL>

Check rights granted to the DB_DEVELOPER_ROLE:

SQL> @find_all_privs



find_all_privs: Release 1.0.7.0.0 - Production on Thu Apr 06 15:39:48 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

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

User => DB_DEVELOPER_ROLE has been granted the following privileges
====================================================================
        ROLE => CTXAPP which contains =>
                SYS PRIV => CREATE SEQUENCE grantable => NO
                TABLE PRIV => DELETE object => CTXSYS.DR$DICTIONARY grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.CTX_ANL grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.CTX_DDL grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.CTX_ENTITY grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.CTX_OUTPUT grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.CTX_THES grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.CTX_ULEXER grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.DRIENTL grantable => NO
                TABLE PRIV => EXECUTE object => CTXSYS.DRITHSL grantable => NO
                TABLE PRIV => INSERT object => CTXSYS.DR$DICTIONARY grantable => NO
                TABLE PRIV => INSERT object => CTXSYS.DR$THS grantable => NO
                TABLE PRIV => INSERT object => CTXSYS.DR$THS_BT grantable => NO
                TABLE PRIV => INSERT object => CTXSYS.DR$THS_FPHRASE grantable => NO
                TABLE PRIV => INSERT object => CTXSYS.DR$THS_PHRASE grantable => NO
                TABLE PRIV => SELECT object => CTXSYS.DR$DICTIONARY grantable => NO
                TABLE PRIV => UPDATE object => CTXSYS.DR$DICTIONARY grantable => NO
                TABLE PRIV => UPDATE object => CTXSYS.DR$THS_PHRASE grantable => NO
        ROLE => SODA_APP which contains =>
                TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_ADMIN grantable => NO
                TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_USER_ADMIN grantable => NO
                TABLE PRIV => READ object => XDB.JSON$USER_COLLECTION_METADATA grantable => NO
        SYS PRIV => CREATE ANALYTIC VIEW grantable => NO
        SYS PRIV => CREATE ATTRIBUTE DIMENSION grantable => NO
        SYS PRIV => CREATE CUBE grantable => NO
        SYS PRIV => CREATE CUBE BUILD PROCESS grantable => NO
        SYS PRIV => CREATE CUBE DIMENSION grantable => NO
        SYS PRIV => CREATE DIMENSION grantable => NO
        SYS PRIV => CREATE DOMAIN grantable => NO
        SYS PRIV => CREATE HIERARCHY grantable => NO
        SYS PRIV => CREATE JOB grantable => NO
        SYS PRIV => CREATE MATERIALIZED VIEW grantable => NO
        SYS PRIV => CREATE MINING MODEL grantable => NO
        SYS PRIV => CREATE MLE grantable => NO
        SYS PRIV => CREATE PROCEDURE grantable => NO
        SYS PRIV => CREATE SEQUENCE grantable => NO
        SYS PRIV => CREATE SESSION grantable => NO
        SYS PRIV => CREATE SYNONYM grantable => NO
        SYS PRIV => CREATE TABLE grantable => NO
        SYS PRIV => CREATE TRIGGER grantable => NO
        SYS PRIV => CREATE TYPE grantable => NO
        SYS PRIV => CREATE VIEW grantable => NO
        SYS PRIV => DEBUG CONNECT SESSION grantable => NO
        SYS PRIV => EXECUTE DYNAMIC MLE grantable => NO
        SYS PRIV => FORCE TRANSACTION grantable => NO
        SYS PRIV => ON COMMIT REFRESH grantable => NO
        TABLE PRIV => EXECUTE object => SYS.JAVASCRIPT grantable => NO
        TABLE PRIV => READ object => SYS.V_$PARAMETER grantable => NO
        TABLE PRIV => READ object => SYS.V_$STATNAME grantable => NO
        TABLE PRIV => SELECT object => SYS.DBA_PENDING_TRANSACTIONS grantable => NO

PL/SQL procedure successfully completed.

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

SQL>

That's a lot of grants both system and object and role grants. There is one duplicate system privilege grant, that is CREATE SEQUENCE, granted direct to the role and via the CTXAPP role. So there are 25 system privileges granted. Wow. The duplicate is not good. The grant on v_$PARAMETER not good for security as if an attacker gets access to a user with DV_DEVELOPER_ROLE then they can find out all parameters. Also the grant to SYS.JAVASCRIPT would give too much away. also the grant to DEBUG CONNECT SESSION would allow debugging on the owners objects or objects where debug rights exist; this could be used to learn "secret" values such as passwords, keys etc passed and used in PL/SQL where not normally visible. Is CREATE MLE really necessary for all developers? no, only if they wish to create Javascript procecures. The same argument can be applied across the board.

If a developer has CONNECT, RESOURCE or DB_DEVELOPER_ROLE then they have too many rights and if these role grants end up in production then change can take place or new objects be created.

We can compare just the system privilege grants in CONNECT and RESOURCE compared to DB_DEVELOPER_ROLE here:



In principal this new role is worse then RESOURCE in terms of rights BUT also neither matches your own application and objects or least rights.

A better approach is to not use Oracle designed roles BUT to design your own roles. For instance if a developer designs a simple application with TABLES, VIEWS, PL/SQL, SEQUENCES and TYPES then create your own role that has just these rights only as well as CREATE SESSION. Grant this role during development and revoke it afterwards during testing. If an update is needed to the objects then grant the role back. Also use a schema only account to own the objects and grant your role for deployment and use proxy to do the deploy as the schema.

Think about least rights at development time and at run time

#oracleace
#23c
#dbsec

The Oracle 23c database was released on Wednesday for download either as an rpm, a docker image or a pre-defined VirtualBox VM. The links to download 23c and some initial details are here.

I chose to download the Oracle Virtualbox VM and had that up and running quickly; and its only about 7 gig. Here I am logged in with an 11.2.0.4 client remotely as that is what I had available on the PC:

C:\mac_nov_2019\audit_class_2_day\2_day_master\V2.21.02\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 6 07:35:19 2023

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


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

SQL>

This version 23c release is amazing as far as I can tell its an update on the Free XE style model with a name change. It has got install limits, size etc but you can use it internally for free - see the limitations here. This 23c free version is now called "FREE" instead of XE. Also its released first instead of later after the EE and SE releases. This is so developers and the community can download and develop their applications against 23c early and have them ready before the final Enterprise or Standard Edition releases.

This means we can all be beta testers without getting onto the beta program and most importantly we can talk about it!!

I connected to my 23c VM and ran some commands and also used a PC client of 11.2.0.4 and also tried a 19c client. The first simple update that is noticeable is the change to no longer need DUAL.

SQL> select sys_context('userenv','con_name');

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

1 row selected.

SQL>

You can still use DUAL of course:

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

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

1 row selected.

SQL>

It was covered in various posts that 21c XE included cost options for free under the same restrictions as XE itself such as Database Vault or Oracle Label Security and Real Application Security were included in the XE version. For instance Paul Bullen covered this in his article on linkedin. It is unclear if the 23c Free also includes all the same features for free. I did a quick check in 23c FREE:

C:\mac_nov_2019\audit_class_2_day\2_day_master\V2.21.02\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 5 15:06:54 2023

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


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

SQL>

SQL> col comp_name for a40
SQL> col status for a10
SQL> col version for a15
SQL> set lines 220
SQL> select comp_name,status,version from dba_registry;

COMP_NAME                                STATUS     VERSION
---------------------------------------- ---------- ---------------
Oracle Database Catalog Views            VALID      23.0.0.0.0
Oracle Database Packages and Types       VALID      23.0.0.0.0
Oracle Real Application Clusters         OPTION OFF 23.0.0.0.0
JServer JAVA Virtual Machine             VALID      23.0.0.0.0
Oracle XDK                               VALID      23.0.0.0.0
Oracle Database Java Packages            VALID      23.0.0.0.0
OLAP Analytic Workspace                  VALID      23.0.0.0.0
Oracle XML Database                      VALID      23.0.0.0.0
Oracle Workspace Manager                 VALID      23.0.0.0.0
Oracle Text                              VALID      23.0.0.0.0
Oracle OLAP API                          VALID      23.0.0.0.0

COMP_NAME                                STATUS     VERSION
---------------------------------------- ---------- ---------------
Spatial                                  VALID      23.0.0.0.0
Oracle Label Security                    VALID      23.0.0.0.0
Oracle APEX                              VALID      22.2.0
Oracle Database Vault                    VALID      23.0.0.0.0

15 rows selected.

SQL>

and Database Vault and Label Security seem to be there for instance. If we check v$option we can see that Database Vault and Label security are FALSE:

SQL> select parameter from v$option where value=FALSE;

PARAMETER
----------------------------------------------------------------
Real Application Clusters
Parallel backup and recovery
Parallel execution
Change Data Capture
Managed Standby
Database resource manager
Automatic Storage Management
Enterprise User Security
Oracle Data Guard
Oracle Label Security
Streams Capture

PARAMETER
----------------------------------------------------------------
Oracle Database Vault
Real Application Testing
Active Data Guard
Server Flash Cache
Management Database
I/O Server
ASM Proxy Instance
Exadata Discovery
Global Data Services
Cache Fusion Lock Accelerator
Data Guard for Pluggable Databases

PARAMETER
----------------------------------------------------------------
SQL Firewall

23 rows selected.

SQL>

Interestingly SQL Firewall, the new feature is also FALSE. Can we use it and test it? is it even there? I don't know yet but I will check

A quick check of the users in the PDB shows 49 users installed:

SQL> col username for a20
SQL> col account_status for a15
SQL> col password_versions for a10
SQL> col read_only for a3
SQL> col dictionary_protected for a3
SQL> set lines 220
SQL> col protected for a3
SQL> col mandatory_profile_violation for a3
SQL> select username,account_status,password_versions,read_only,dictionary_protected, protected, mandatory_profile_violation from dba_users;

USERNAME             ACCOUNT_STATUS  PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
SYS                  OPEN            11G 12C    NO  NO  NO  NO
SYSTEM               OPEN            11G 12C    NO  NO  NO  NO
APEX_LISTENER        OPEN            11G 12C    NO  NO  NO  NO
APEX_PUBLIC_USER     OPEN            11G 12C    NO  NO  NO  NO
APEX_REST_PUBLIC_USE OPEN            11G 12C    NO  NO  NO  NO
R

AV                   OPEN            11G 12C    NO  NO  NO  NO
PDBADMIN             OPEN            11G 12C    NO  NO  NO  NO
SYSRAC               OPEN                       NO  YES NO  NO
HR                   OPEN            11G 12C    NO  NO  NO  NO

USERNAME             ACCOUNT_STATUS  PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
ORDS_PUBLIC_USER     OPEN            11G 12C    NO  NO  NO  NO
ORDS_METADATA        OPEN                       NO  NO  NO  NO
BI                   OPEN            11G 12C    NO  NO  NO  NO
OE                   OPEN            11G 12C    NO  NO  NO  NO
PM                   OPEN            11G 12C    NO  NO  NO  NO
HRREST               OPEN            11G 12C    NO  NO  NO  NO
IX                   OPEN            11G 12C    NO  NO  NO  NO
PFCLSCAN             OPEN            11G 12C    NO  NO  NO  NO
SH                   OPEN            11G 12C    NO  NO  NO  NO
XS$NULL              LOCKED                     NO  YES NO  NO
LBACSYS              LOCKED                     NO  YES NO  NO

USERNAME             ACCOUNT_STATUS  PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
OUTLN                LOCKED                     NO  NO  NO  NO
DBSNMP               LOCKED                     NO  NO  NO  NO
APPQOSSYS            LOCKED                     NO  NO  NO  NO
APEX_220200          LOCKED                     NO  NO  NO  NO
DBSFWUSER            LOCKED                     NO  NO  NO  NO
GGSYS                LOCKED                     NO  NO  NO  NO
ANONYMOUS            LOCKED                     NO  NO  NO  NO
FLOWS_FILES          LOCKED                     NO  NO  NO  NO
CTXSYS               LOCKED                     NO  YES NO  NO
DVSYS                LOCKED                     NO  YES NO  NO
DVF                  LOCKED                     NO  YES NO  NO

USERNAME             ACCOUNT_STATUS  PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
AUDSYS               LOCKED                     NO  YES NO  NO
GSMADMIN_INTERNAL    LOCKED                     NO  YES NO  NO
GGSHAREDCAP          LOCKED                     NO  YES NO  NO
OLAPSYS              LOCKED                     NO  NO  NO  NO
MDSYS                LOCKED                     NO  NO  NO  NO
XDB                  LOCKED                     NO  YES NO  NO
WMSYS                LOCKED                     NO  NO  NO  NO
GSMCATUSER           LOCKED                     NO  NO  NO  NO
MDDATA               LOCKED                     NO  NO  NO  NO
SYSBACKUP            LOCKED                     NO  YES NO  NO
REMOTE_SCHEDULER_AGE LOCKED                     NO  NO  NO  NO

USERNAME             ACCOUNT_STATUS  PASSWORD_V REA DIC PRO MAN
-------------------- --------------- ---------- --- --- --- ---
NT

GSMUSER              LOCKED                     NO  NO  NO  NO
OJVMSYS              LOCKED                     NO  NO  NO  NO
DIP                  LOCKED                     NO  NO  NO  NO
SYSKM                LOCKED                     NO  YES NO  NO
DGPDB_INT            LOCKED                     NO  NO  NO  NO
SYS$UMF              LOCKED                     NO  NO  NO  NO
SYSDG                LOCKED                     NO  YES NO  NO

49 rows selected.

SQL>

Some interesting things here. This is a higher number of default accounts in this 23c PDB than previous versions; 48 really as I created one user PFCLSCAN. There are of course the samples that are talked about for developers to use BUT this is a backwards step in terms of security as these were not installed by default in 21c XE. There is a new column added from 21c MANDATORY_PROFILE_VIOLATION which I will look at in a later post and also a READ_ONLY column not there in 21c and is now here in 23c. There are no users that are marked as READ_ONLY by default. The column is also not described in the 23c documentation but I will look at it in a later post. Also there are two columns, PROTECTED and DICTIONARY_PROTECTED. There are no users that are PROTECTED but some users are DICTIONARY_PROTECTED. I will discuss both of these as well in the next post on more details.

Profiles are interesting as the settings are worse than earlier:

SQL> @profiles



profiles.sql: Release 1.0.0.0.0 - Production on Thu Apr 06 06:42:49 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
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
OE              DEFAULT         10  U   U   1   U   7   U   NULL
PM              DEFAULT         10  U   U   1   U   7   U   NULL
HRREST          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
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>

For instance the lifetime of 180 is gone but the grace time of 7 days is still there. Both settings make no sense anyway. I will discuss profiles in more details in the next posts.

Well, that's it for now. I just wanted to get a 23c post out there quickly but I will post in much more details about Oracle database security in 23c.

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