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
