• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to create Users and Roles in Oracle database 12c

How to create Users and Roles in Oracle database 12c

August 6, 2020 by techgoeasy Leave a Comment

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

  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;

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

Related Articles
ORA-01017: invalid username/password; logon denied
how to get table definition in oracle
oracle create tablespace
Create table in oracle
https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234

Filed Under: Oracle, Oracle Database, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us