How to create Users and Roles in Oracle database 12c

Last updated on March 9th, 2019 at 04:02 am

We already know how to create user and grant privileges in a traditional non-CDB database

How to Create User in Oracle and Assign privileges

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

  1. In a non-CDB, a user name cannot begin with C## or c##.
  2. 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;

Related Articles

ORA-01017: invalid username/password; logon denied Tips

How to Create User in Oracle and Assign privileges

how to find the metadata of any object in Oracle

Leave a Reply