We already know how to create user and grant privileges in a traditional non-CDB database. Check the below detailed post to learn about it
Let’s find out now, how to create a user in oracle 12c. In a non-CDB, a user name or Roles cannot begin with C## or c##
How to create Users and Roles in 12c
- 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 (22.214.171.124), 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 (126.96.36.199),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;
I hope you like post on how to create user in oracle 12c, create a common and local users, create common role and local role
ORA-01017: invalid username/password; logon denied
how to get table definition in oracle
oracle create tablespace
Create table in oracle