Oracle 12c Common and Local users Creation

                Oracle 12c Common and Local users Creation

Error : ORA-65096 ORA-65049

In 12c when you login to a CDB database you default to the CDB$ROOT container.

[oracle@rac1 db_1]$ echo $ORACLE_SID
T12
[oracle@rac1 db_1]$ ps -ef | grep pmon
oracle   21182     1  0 13:21 ?        00:00:00 ora_pmon_T12

[oracle@rac1 db_1]$ sqlplus / as sysdba

SQL> show con_name

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

An attempt to create users in the CDB$ROOT container may result in ORA-65096 or ORA-65049 .

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

oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:  An attempt was made to create a common user or role with a name
//          that wass not valid for common users or roles.  In addition to
//          the usual rules for user and role names, common user and role
//          names must start with C## or c## and consist only of ASCII
//          characters.
// *Action: Specify a valid common user or role name.
//

SQL> create user test identified by test container=current;
create user test identified by test container=current
                               *
ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

oerr ora 65049
65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
// *Cause:   An attempt was made to create a local user or role in CDB$ROOT.
// *Action:  If trying to create a common user or role, specify CONTAINER=ALL.

//

You first need to decide if you want a "common" user in the CDB which could be used to manage multiple PDBs, or a "local" user in only one PDB.  Check that you have set the container to the correct database before creating the user.

SQL> select PDB from v$services;

PDB
------------------------------
T12P2
T12P1
CDB$ROOT
CDB$ROOT
CDB$ROOT
CDB$ROOT

6 rows selected.

To create a "common" user in CDB$ROOT with name starting with c## :-


SQL> show con_name

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

SQL> create user c##test identified by test;

User created.

To create a "local" user in PDB T12P1 :-

SQL> alter session set container=T12P1;

Session altered.

SQL> show con_name

CON_NAME
-----------
T12P1

SQL> create user test identified by test;

User created.

In PDB T12P1 we can see that DBA_USERS lists both the local user, and the common user.

SQL> select username from dba_users where username like '%TEST%';

USERNAME
-----------------------------------------------------------------
C##TEST
TEST

Thanks
Anil Vejendla