We already know how to create user and grant privileges in a traditional non-CDB database
In a non-CDB, a user name or Roles cannot begin with C## or c##
With 12c , we have a concept of container database (CDB),so things get change with respect to users. We have common user and local users
Common users are created in root container .Adding a common user involves adding a description of that user in the root and in every PDB while local users are created in respective PDB in a traditional manner.So local user creation is same user creation in NON-CDB database
Some restrictions for users naming in 12c are
- In a non-CDB, a user name cannot begin with C## or c##.
- In a CDB, the requirements for a user name are as follows:
In Oracle Database 12c Release 1 (18.104.22.168), the name of a common user must begin with C## or c## and the name of a local user must not begin with C## or c##.
Starting with Oracle Database 12c Release 1 (22.214.171.124):
The name of a common user must begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C##.
The name of a local user must not begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. Regardless of the value of COMMON_USER_PREFIX, the name of a local user can never begin with C## or c##.
Similar is the case of Roles also. Oracle supplied roles are common roles and they are available to both root and PDB’s . Local roles are created the same way as traditional way as pre 12c. For creating common roles, it should start C## and you should be connected to root container to create it
Important points to keep while creating common users
1 )While creating a common user, any default tablespace, temporary tablespace, or profile specified using the following clauses must exist in all the containers belonging to the CDB:
If these objects do not exist in all the containers, the CREATE USER statement fails.
2. To create a common user, you must be connected to the root. You can optionally specify CONTAINER = ALL, which is the default when you are connected to the root.
3.To create a local user, you must be connected to a PDB. You can optionally specify CONTAINER = CURRENT, which is the default when you are connected to a PDB.
CREATE USER c##test_user
IDENTIFIED BY test_pwd
DEFAULT TABLESPACE example
QUOTA 20M ON example
TEMPORARY TABLESPACE temp;
CREATE ROLE TEST CONTAINER = CURRENT;
The current container must be the root when you issue this statement:
CREATE ROLE c##test CONTAINER = ALL;