Create an Oracle 12c user

Create an Oracle 12c user

Oracle 12c accounts can be divided into public accounts and local users. A common account is created under CDB and can be used by all PDB users. A local account can only be created in PDB.

Create a public user
Alter session set container = CDB $ ROOT;
Create user c # u02 identified by oracle container = all;
Create a local user
Alter session set container = pdb4;
Create USER u01 identified by oracle container = current;

SQL> SELECT USERNAME, CON_ID, USER_ID FROM CDB_USERS WHERE USERNAME = 'u01 ';
USERNAME CON_ID USER_ID
--------------------------------------------------
U01 4 105

Create a test account in CDB
1. The account created in CDB must start with C #.
Alter session set container = CDB $ ROOT;
SQL> create user orcl identified by;
Create user orcl identified by
*
ERROR at line 1:
ORA-65096: invalid common user or role name

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

SQL> create user c # a identified by;
User created.

SQL> select username from dba_users where username like 'C ##% ';
USERNAME
--------------------------------------------------------------------------------
C ##

--- Note that the local user cannot start with C #
Alter session set container = pdb4;
SQL> create user c # B identified by B;
Create user c # B identified by B
*
ERROR at line 1:
ORA-65094: invalid local user or role name

SQL> SELECT USERNAME, CON_ID, USER_ID FROM CDB_USERS WHERE USERNAME like 'C ##% 'ORDER BY USERNAME;
USERNAME CON_ID USER_ID
--------------------------------------------------
C ## BOSWLL 4 103
C ## BOSWLL 3 110
C ## BOSWLL 1 102
C ## BOSWLL 5 103
C ## U02 4 106
C ## U02 5 104
C ## U02 1 103
C ## U02 3 111

--- You cannot create a public user in pdb when creating a public user. You must create a public user in cdb.
SQL> alter session set container = pdb4;
Session altered.

SQL> create USER C # u02 identified by oracle container = all;
Create user c # u02 identified by oracle container = all
*
ERROR at line 1:

The ORA-65050: Common DDLs only allowed in CDB $ ROOT

Summary:
1. A public user must start with C #. A local user must start with a letter and cannot start with C #.
2 when creating a public user, it must be in the CDB $ ROOT
3. If a user or role already exists in PDB, the same account or role name cannot be created in CDB.