Multitenant : Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article shows how to manage users and privileges for container databases (CDB) and pluggable databases (PDB).
Introduction
Create Common Users
Create Local Users
Create Common Roles
Create Local Roles
Granting Roles and Privileges to Common and Local Users
Introduction
When connected to a multitenant database the management of users and privileges is a little different to traditional Oracle environments.
In multitenant environments there are two types of user.
Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
Likewise, there are two types of roles.
Common Role : The role is present in all containers (root and all PDBs).
Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.
Some DDL statements have a CONTAINER clause added to allow them to be directed to the current container or all containers. Its usage will be demonstrated in the sections below.
Create Common Users
When creating a common user the following requirements must all be met.
1 )You must be connected to a common user with the
CREATE USER privilege.
The current container must be the root container.
2)The username for the common user must be
prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters.
3)The username must be unique across all containers.
4)The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers.
5)You can either specify the CONTAINER=ALL clause, or omit it, as this is the default setting when the current container is the root.
The following example shows how to create common users with and without the CONTAINER clause from the root container.
CONN / AS SYSDBA
-- Create the common user using the CONTAINER clause.
CREATE USER
c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO
c##test_user1 CONTAINER=ALL;
-- Create the common user using the default CONTAINER setting.
CREATE USER c##test_user2 IDENTIFIED BY password1;
GRANT CREATE SESSION TO c##test_user2;
Create Local Users
When creating a local user the following requirements must all be met.
1)You must be connected to a user with the CREATE USER privilege.
2)The username for the local user
must not be prefixed with "C##" or "c##".
The username must be unique within the PDB.
You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current container is a PDB.
The following example shows how to create local users with and without the CONTAINER clause from the root container.
CONN / AS SYSDBA
-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;
-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;
-- Connect to a privileged user in the PDB.
CONN system/password@pdb1
-- Create the local user using the default CONTAINER setting.
CREATE USER test_user4 IDENTIFIED BY password1;
GRANT CREATE SESSION TO test_user4;
If a local user is to be used as a DBA user, it requires the PDB_DBA role granted locally to it.
Create Common Roles
Similar to users described previously, roles can be common or local. All Oracle-supplied roles are common and therefore available in the root container and all PDBs. Common roles can be created, provided the following conditions are met.
You must be connected to a common user with CREATE ROLE and the SET CONTAINER privileges granted commonly.
1)The current container must be the root container.
2)
The role name for the common role must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters.
3)The role name must be unique across all containers.
The role is created with the CONTAINER=ALL clause
The following example shows how to create a common role and grant it to a common and local user.
CONN / AS SYSDBA
-- Create the common role.
CREATE ROLE c##test_role1;
GRANT CREATE SESSION TO c##test_role1;
-- Grant it to a common user.
GRANT c##test_role1 TO c##test_user1 CONTAINER=ALL;
-- Grant it to a local user.
ALTER SESSION SET CONTAINER = pdb1;
GRANT c##test_role1 TO test_user3;
Only common operations can be granted to common roles. When the common role is granted to a local user, the privileges are limited to that specific user in that specific PDB.
Create Local Roles
Local roles are created in a similar manner to pre-12c databases. Each PDB can have roles with matching names, since the scope of a local role is limited to the current PDB. The following conditions must be met.
You must be connected to a user with the CREATE ROLE privilege.
If you are connected to a common user, the container must be set to the local PDB.
The role name for the local role must not be prefixed with "C##" or "c##".
The role name must be unique within the PDB.
The following example shows how to create local a role and grant it to a common user and a local user.
CONN / AS SYSDBA
-- Switch container.
ALTER SESSION SET CONTAINER = pdb1;
-- Alternatively, connect to a local or common user
-- with the PDB service.
-- CONN system/password@pdb1
-- Create the common role.
CREATE ROLE test_role1;
GRANT CREATE SESSION TO test_role1;
-- Grant it to a common user.
GRANT test_role1 TO c##test_user1;
-- Grant it to a local user.
GRANT test_role1 TO test_user3;
When a local role are granted to common user, the privileges granted via the local role are only valid when the common user has its container set to the relevant PDB.
Granting Roles and Privileges to Common and Local Users
The rules for granting privileges and roles can seem a little confusing at first. Just remember, if you connect to a PDB and only deal with local users and roles, everything feels exactly the same as pre-12c databases. It's only when you start to consider the scope of common users and roles that things become complicated.
The basic difference between a local and common grant is the value used by the CONTAINER clause.
-- Common grants.
CONN / AS SYSDBA
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_role1 CONTAINER=ALL;
GRANT c##test_role1 TO c##test_user1 CONTAINER=ALL;
-- Local grants.
CONN system/password@pdb1
GRANT CREATE SESSION TO test_user3;
GRANT CREATE SESSION TO test_role1;
GRANT test_role1 TO test_user3;
