How to create Users and Roles in Oracle database 12c

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

Create User in Oracle

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 (12.1.0.1), 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 (12.1.0.2),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:

DEFAULT TABLESPACE
TEMPORARY TABLESPACE
QUOTA
PROFILE

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.

Example

Common user

CREATE USER c##test_user
IDENTIFIED BY test_pwd
DEFAULT TABLESPACE example
QUOTA 20M ON example
TEMPORARY TABLESPACE temp;

Local Role

CREATE ROLE TEST CONTAINER = CURRENT;

Common Role

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

How to create Users and Roles in 12c

Important points to keep while creating common users

Related Posts

Leave a Comment Cancel Reply