Locking & unlocking common users in a Multitenant database

2021-03-18 Off By Markus Flechtner

Common users in a multitenant database are defined in CDB$ROOT and are then available in all PDBs. Privileges can be granted locally (for a single container) or for the whole container database (CONTAINER=ALL). But what about locking and unlocking these users on the different level.

TL;DR: A common user which is locked in CDB$ROOT cannot be unlocked on PDB level. But it works the other way round; i.e. a common user can be locked on PDB level.

Let’s create a common user in the test database (Oracle Database 19c (RU 19.10 – January 2021):

CDB$ROOT@CDB1> create user c##test identified by manager container=all;
User created.

CDB$ROOT@CDB1> grant create session to c##test container=all;
Grant succeeded.

CDB$ROOT@CDB1> select con_id,username,account_status from cdb_users where username='C##TEST' order by con_id;
    CON_ID USERNAME			  ACCOUNT_STATUS
---------- ------------------------------ --------------------------------
	 1 C##TEST			  OPEN
	 5 C##TEST			  OPEN
	 7 C##TEST			  OPEN
3 rows selected.

As expected, the user is available and “OPEN” in all containers.

What happens, if I LOCK the user in CDB$ROOT?

CDB$ROOT@CDB1> alter user c##test account lock;
User altered.
    CON_ID USERNAME			  ACCOUNT_STATUS
---------- ------------------------------ --------------------------------
	 1 C##TEST			  LOCKED
	 5 C##TEST			  LOCKED
	 7 C##TEST			  LOCKED

So locking the user in CDB$ROOT affects all PDBs. Trying to add “CONTAINER=LOCAL” to the command results in the error message “ORA-65066: The specified changes must apply to all containers”.

But can I unlock such a user in one of the PDBs?

CDB$ROOT@CDB1> alter session set container=PDB01;
Session altered.

CDB$ROOT@CDB1> alter user c##test account unlock;
alter user c##test account unlock
*
ERROR at line 1:
ORA-65146: account cannot be unlocked in a PDB while it is locked in the root

What about the other way round?

CDB$ROOT@CDB1> alter session set container=CDB$ROOT;
Session altered.

CDB$ROOT@CDB1> alter user c##test account unlock;
User altered.

CDB$ROOT@CDB1> alter session set container=PDB01;
Session altered.

CDB$ROOT@CDB1> alter user c##test account lock;
User altered.

CDB$ROOT@CDB1> alter session set container=CDB$ROOT;
Session altered.

CDB$ROOT@CDB1> select con_id,username,account_status from cdb_users where username='C##TEST' order by con_id;

    CON_ID USERNAME			  ACCOUNT_STATUS
---------- ------------------------------ --------------------------------
	 1 C##TEST			  OPEN
	 5 C##TEST			  OPEN
	 7 C##TEST			  LOCKED

Amazon-Partner-Link: