Locking & unlocking common users in a Multitenant database
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 LOCKEDSo 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 rootWhat 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 LOCKEDAmazon-Partner-Link: