Monday, 6 August 2018

ORA-65096: invalid common user or role name | Oracle 12c

Create User Fails with ORA-65096 in Oracle 12c Database

ORA-65096: invalid common user or role name | ORA-65049: creation of local user or role is not allowed in CDB$ROOT


ORA-65096: invalid common user or role name


ORA-65096 Oracle error generally occurs when we try to create a common user under root container. From Oracle 12c onward, we have the concepts of Container and Pluggable databases and also there's a change in the User Types as well. From 12c, we have 2 types of Users in the Oracle database i.e. Common Users & Local Users.

The syntax of creation of both the users is different. Since the purpose of both the users is different. Just to make it clear enough, Common Users belong to Container Databases (CDBs) and the Pluggable Databases (PDBs). But Local Users only belong to PDBs. A Common User can perform any operation in CDB or PDB depending upon the roles are rights (privileges) given to it. But a Local User can only operate inside a PDB.

Below is the example showing you the occurence of the same error:

Here I am trying to create a Common User in Container Root.

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

SQL> create user test1 identified by pass123;
 create user test1 identified by pass123
 *
 ERROR at line 1:
ORA-65096: invalid common user or role name

OR

SQL> create user test1 identified by pass123 container=current;
 create user test1 identified by pass123 container=current
 *
 ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

OR

SQL> create user test1 identified by pass123 container=all;
 create user test1 identified by pass123 container=all
 *
 ERROR at line 1:
ORA-65096: invalid common user or role name


Now changing the syntax and using the one which is required at this point,


SQL> create user C##test1 identified by pass123;

User created.

OR

SQL> create user c##test1 identified by pass123 container=all;

User created.

Points to Consider:
  • Common user will be created under Container Root only.
  • Current container must be set to CDB$ROOT.
I hope this helps !!

No comments:

Post a comment