Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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

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.

Likewise, there are two types of roles.

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.

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.

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.

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.

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;

The rules for common and local grants are displayed here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.