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