• Database Blog
  • GoldenGate 12c
    • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Master Oracle 12c DBA
  • Oracle Tutorials
    • Oracle Tips & Tricks
    • Oracle VirtualBox
  • Learn Excel

VitalSoftTech

Database Administration | Oracle | Oracle Training | DBA Support | Oracle 12c DBA | GoldenGate | GoldenGate 12c | DBA Interview Questions | Goldengate Interview Questions | Natik Ameen

  • Digital Marketing
  • Blogging Tutorial and Tips
  • Cool Names
  •   Login
« Previous Post
Next Post »

Oracle 12c Database: Managing Common and Local Users, Roles and Privileges

July 2, 2015 by Natik Ameen   /  1Z0-060, Multitenant, Oracle 12c, Oracle 12c Security, OraFAQ, ToadWorld

Managing users, roles and privileges is a very important aspect of the database administration. With databases which are multitenant, as Oracle 12c is, there is a second dimension to controlling the user access and privileges. This includes specifying the PDB database in the CDB container which the user is available in and access to objects in it. The same is the true for roles. In this article, we will look at managing the users, roles and privileges in the CDB container and the PDB databases.


To understand this better, you need to first become familiar with the Common User and Local User concept introduced in Oracle 12c.


Related Articles
• SGA Instance Parameters in CDB and PDB
• Managing CDB and PDB tablespaces
• Flashback a CDB Database
• Create 12c CDB PDB Using OUI
• Managing PDBs using Database Configuration Assistant

Common Users, Roles and Privileges

Common users are users who have been created in the CDB database container. They are also automatically created in every PDB database which are attached to the CDB container. This user is however not created in the PDB SEED database.

Create Common User

The common user name will always start with C##. You will not be able to create the common user without a C## prefix.

SQL> CREATE USER foo IDENTIFIED BY foo CONTAINER=ALL;
CREATE USER foo IDENTIFIED BY foo CONTAINER=ALL
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE USER C##FOO IDENTIFIED BY foo CONTAINER=ALL;
User created.


Now try to connect to a PDB with this user.

$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 31 08:48:02 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: c##foo/[email protected]:1521/pdb12c
ERROR:
ORA-01045: user C##FOO lacks CREATE SESSION privilege; logon denied


As you can see the C##Foo user gets created in PDB as well but is lacking the privilege required to connect.

Create Common Roles

Similar is the case for common roles. They are created in a same manner and work the same way as common users.

SQL> CREATE ROLE r1 CONTAINER=ALL;
CREATE ROLE r1 CONTAINER=ALL
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE ROLE c##r1 CONTAINER=ALL;
Role created.

Grant Privileges to Common Users

Once the common users are created, they need the required privileges to connect to each container. At a minimum, they will require the create session privilege to connect to a PDB or CDB database. The concept of common privileges comes into play here. You can grant a privilege commonly to a user so that the user will have that privilege cascade across all the PDB’s, with the exception of the PDB Seed.

SQL> SHOW CON_NAME
CON_NAME
--------------
CDB$ROOT

SQL> GRANT CREATE SESSION TO c##foo CONTAINER=ALL;
Grant succeeded.

$ sqlplus c##foo/[email protected]:1521/pdb12c
SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 31 08:54:47 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_NAME
CON_NAME
--------------
PDB12c


This example demonstrates the concept. The create session privilege was granted to the C##Foo user in the ROOT container but the “CONTAINER=ALL” clause makes sure that this privilege is also available in all the PDBs for C##Foo common user. Granting common privileges to common roles works the same way as well.


While granting common privileges you must be connected to ROOT. And also the common privileges cannot be granted to local users.

Local Users, Roles and Privileges

On the other hand, local users are users who are created inside a specific container like a PDB. They belong to that specific PDB and cannot connect to other PDB’s in same as CDB itself.

Create Local Users

SQL> SHOW CON_NAME
CON_NAME
--------------
PDB12c

SQL> CREATE USER foo IDENTIFIED BY foo;
User created.

$ sqlplus foo/[email protected]:1521/cdb12c
SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 31 09:01:25 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied


The user foo was created in PDB and hence the CDB fails to recognize it.


You cannot create local users in a ROOT container. Also local users do not have to follow a naming convention.

SQL> sho con_name
CON_NAME
--------------
CDB$ROOT

SQL> CREATE USER test IDENTIFIED BY test;
CREATE USER test IDENTIFIED BY test
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE USER test IDENTIFIED BY test container=current;
CREATE USER test IDENTIFIED BY test container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

Create Local Roles

The creation of local roles follow the same convention as the users. They can only be created in PDB’s, do not follow a specific naming convention and their use is limited to container in which they were created.


SQL> SHOW CON_NAME
CON_NAME
----------------
PDB12c

SQL> CREATE ROLE r1;
Role created.

SQL> SHOW CON_NAME
CON_NAME
--------------
CDB$ROOT

SQL> CREATE ROLE r1;
CREATE ROLE r1
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE ROLE r1 CONTAINER=ALL;
CREATE ROLE r1 container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

Grant Local Privileges

Privileges can be granted locally as well. This is necessary because every database has its own requirement and even a common user may have different role and functions to perform in the different containers. So local privileges can be granted to the local users as well as to common users.

SQL> SHOW CON_NAME
CON_NAME
--------------
CDB$ROOT

SQL> GRANT CREATE TABLE TO c##foo CONTAINER=CURRENT;
Grant succeeded.

$ sqlplus c##foo/[email protected]:1521/pdb12c
SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 31 09:09:55 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Last Successful login time: Sat Aug 31 2013 08:54:47 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table test1(c1 number);
create table test1(c1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges


As you can see that though the user is a common user but since the privilege was granted locally in the CDB (CONTAINER=CURRENT), it did not receive those privileges in the PDB database.


References
• Overview of the Multitenant Architecture

Filed Under: 1Z0-060, Multitenant, Oracle 12c, Oracle 12c Security, OraFAQ, ToadWorld Tagged With: 12c Database, CDB, Multitenancy, PDB

« Previous Post
Next Post »

Join over 3,000 others My posts. Your Inbox. Beautiful.

Database    GoldenGate

Natik Ameen

About Natik Ameen

Natik Ameen is an Oracle Production DBA, Oracle Certified RAC Expert and a DBA track Certification trainer for over 17 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences. He writes on topics such as Database Administration, RAC, GoldenGate and the Cloud.

Who is Natik Ameen?

Guides

  • My GoldenGate Tutorials
  • Oracle 12c Database Tutorials
  • GoldenGate GGSCI Command Series
  • My OraTips and Tricks

Popular Blogs

  • My GoldenGate Home Page
  • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Top 30 RAC Interview Questions That Helped Me. Are You Prepared?
  • Oracle GoldenGate Concepts and Architecture Made Simple!
  • Virtualize Oracle RAC 12c on My Laptop
  • Step by Step Oracle 12c Database Install on Virtual Box

Popular Oracle Articles

Oracle Announces Beta Availability of Oracle Database 12c Release 2
Top 5 Announcements at Oracle Open World
News on Oracle Database STANDARD EDITION 12.1.0.2

Technology Centers

Oracle Business Intelligence and DW
Cloud Computing, SOA, Virtualization
Big data, Java, Linux, PLSQL, Security
Enterprise Architecture, Digital Experience

Oracle Documentation

10g R1, 10g R2, 11g R1, 11g R2, 12c R1
Oracle CPU's, EM 11g, EM 12c
GoldenGate 12c, Oracle VM, VirtualBox

Copyright ©2021

BLOGOracle Tutorials