DBA Hub

📋Steps in this guide1/6

Multitenant : Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

This article shows how to manage users and privileges for container databases (CDB) and pluggable databases (PDB) in Oracle Database 12c Release 1 (12.1)

oracle 12cconfigurationintermediate
by OracleDba
19 views
1

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 clause added to allow them to be directed to the current container or all containers. Its usage will be demonstrated in the sections below.
2

Create Common Users

When creating a common user the following requirements must all be met. - You must be connected to a common user with the privilege. - The current container must be the root container. - The username for the common user must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters. - The username must be unique across all containers. - The , , and must all reference objects that exist in all containers. - You can either specify the 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 clause from the root container.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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;
3

Create Local Users

When creating a local user the following requirements must all be met. - You must be connected to a user with the privilege. - 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 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 clause from the root container. If a local user is to be used as a DBA user, it requires the PDB_DBA role granted locally to it.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;
4

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 and the privileges granted commonly. - The current container must be the root container. - The role name for the common role must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters. - The role name must be unique across all containers. - The role is created with the clause The following example shows how to create a common role and grant it to a common and local user. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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;
5

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 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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;
6

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 clause. The rules for common and local grants are displayed here. - Common Grants - Local Grants For more information see: - Introduction to the Multitenant Architecture - Overview of the Multitenant Architecture - Managing a Multitenant Environment - CREATE USER - Overview of Privilege and Role Grants in a CDB - Multitenant : All Articles - Multitenant : Querying Container Data Objects (CONTAINER_DATA) - Multitenant : Manage Users and Roles for CDBs and PDBs Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
-- 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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!