Data … as usual

In Oracle 12c new architecture, you have two kinds of users. Users who are declared in the root container, and users who are declared in a pluggable database.

The first ones are named common user, and the others are name local user. The particularity of those common user is that they are synchronised with the pluggable database(s) to be known in each of them.

Oracle make the distinction between common user and local user by their name. Common usernames have a constraint, they have to start with “C## “(or the value of the parameter “COMMON_USER_PREFIX”). For example, C##LAURENT is a common username whereas LAURENT is a local username.

Oracle controls that, because if you create a common user in a PDB, you will throw an “ORA-65094: invalid local user or role name”. At the opposite, you will throw an “ORA-65096: invalid common user or role name” if you try to create a local user in the CDB$ROOT container.

A weird behaviour happened when you plug a PDB which contained synced definition of a common user.

Below, I paste a demo of what I’m saying (this example has been taken in a SE2 database so … with only one PDB, but I reproduced the problem with a PDB which was unplugged from one container and plugged into another one).

• In the pluggable database, common user creation

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL> create user c##laurent identified by laurent container=ALL;

User created.

SQL> grant connect to c##laurent container=ALL;

Grant succeeded.

• In the PDB the common user is synced

SQL> conn sys/oracle@pdb1 as sysdba
Connected.

SQL> select username,common from dba_users where username like 'C##%';

USERNAME                   COM
-------------------------- ---
C##LAURENT                 YES

• Now I unplug my PDB and drop it from the container

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 unplug into '/home/oracle/laurent.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1;

Pluggable database dropped.

• Now, let’s drop the common user in the root container to simulate a new and fresh container database

SQL> select con_id,username,common from cdb_users where username like 'C##%';

    CON_ID USERNAME      COM
---------- ------------- ---
	     1 C##LAURENT    YES

SQL> drop user c##laurent cascade;

User dropped.

• Finally I plug my PDB in the container, open it, and query CDB_USERS to see what happened to my common user c##LAURENT

SQL> create pluggable database newone
  2  using '/home/oracle/laurent.xml'
  3  nocopy tempfile reuse;

Pluggable database created.

SQL> alter pluggable database newone open;

Pluggable database altered.

SQL> select con_id,username,common from cdb_users where username like 'C##%';

    CON_ID USERNAME       COM
---------- -------------- ---
         3 C##LAURENT     YES

SQL> conn c##laurent/laurent@oel6:1521/NEWONE
ERROR:
ORA-28000: the account is locked

Well if you have a closer look to the CON_ID, my “common user” has been affected as a local user (because defined in the PDB). So, if I’m ok with the definition of  local user … it’s a local user, but with a common username … oh dear !!!

Following this URL in the documentation (https://docs.oracle.com/database/121/SQLRF/statements_8003.htm#SQLRF54735), Oracle recommends to set a value for COMMON_USER_PREFIX even with this particular behaviour.

UPDATE : A friend of mine and a fellow Oracle consultant, Pierre Labrousse, gave me a clue In the Oracle MOS Note 1511619.1, Oracle says that if you face a common user who is locked in a newly plugged PDB to do one of the following:

• Leave the user account locked and use the objects of its schema.
• Use Oracle Data Pump to copy these objects to another schema, and then drop the locked user account.
• Close the PDB, connect to the root, and then create a common user with the same name as the locked account. When you re-open the PDB, Oracle Database resolves the differences in the roles and privileges that were commonly granted to the locked user. Afterward, you can unlock this user account in the PDB. Privileges and roles that were locally granted to the user will remain unchanged.