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).
Related articles.
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.
When creating a common user the following requirements must all be met.
CREATE USER
privilege.DEFAULT TABLESPACE
, TEMPORARY TABLESPACE
, QUOTA
and PROFILE
must all reference objects that exist in all containers.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;
When creating a local user the following requirements must all be met.
CREATE USER
privilege.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.
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.
CREATE ROLE
and the SET CONTAINER
privileges granted commonly.CONTAINER=ALL
clauseThe 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.
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.
CREATE ROLE
privilege.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.
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 normal view: https://oracle-base.com/articles/12c/multitenant-manage-users-and-privileges-for-cdb-and-pdb-12cr1