Multitenant Architecture Common Users and Roles

In this demo I will show the new common users and common roles that exist in a Multitenant environment. I will show how to create common users and roles and how they can be used.

Non-CDB - Pre 12c database or a non container 12c database
CDB - Container database
PDB - Pluggable database

Local users are what we traditional use in non-CDB databases. Common users are users that exist in the CDB root and all PDBs.

Local users can only be created in a PDB and can only interact with that specific PDB. Note a local user can interact with other PDBs but it would use traditional methods like using a DBlink.

Common users exist in the root CDB as well as the PDBs plugged into the CDB. Common user privileges can be granted differently in each PDB plugged into the CDB. The same is true that common users can be granted a privilege commonly across the CDB and all PDBs plugged into the CDB. Common roles can be created to commonly grant privileges to common users.

You maybe asking why create a common user. You could have a situation where you want a DBA to perform activities across all pluggable databases but with out giving the SYSDBA privilege. You could have another situation where you want a DBA to only support one or two pluggable databases in the CDB.

When new pluggable databases are created in the container database the common users and roles will automatically be created in the new pluggable databases. The common user will have the same privileges as in the existing pluggable databases with the common roles.

Environment

CDB- TESTDBS

PDB- DEV1DB, DEV2DB and TSTDB

Create Common User

I will create a common user called C##ORADBADMIN which will have access to the CDB as well as all PDBs plugged into the CDB. I will also create a common user called C##ORADBDEV2 that will have access only to one of the PDBs.

Note:
Common users names need to start with C## or c##.

Below you will see CONTAINER=ALL meaning that the statement executes in the root CDB and all PDBs plugged into the CDB.

Create User

Connected to the container root I create both common users.

$sqlplus / as sysdba
SQL>show con_name con_id

SQL>CREATE USER C##ORADBADMIN PROFILE DEFAULT IDENTIFIED BY temp1234 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK CONTAINER=ALL;

SQL>CREATE USER C##ORADBDEV2 PROFILE DEFAULT IDENTIFIED BY temp1234 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK CONTAINER=ALL;

Grant Connect Privilege

Connected to the container root I create both common users.
$sqlplus / as sysdba
SQL>show con_name con_id
SQL>GRANT CONNECT TO C##ORADBADMIN CONTAINER=ALL;

Switch containers
SQL>alter session set container=dev2db;
SQL>show con_name con_id
SQL>GRANT CONNECT TO C##ORADBDEV2;

Review Users and Privileges

Connected to the container root I use the new dictionary view CDB_USERS. Note you can still use the DBA_USERS or ALL_USERS but there is no con-id column.

$sqlplus / as sysdba

SQL>show con_name con_id

SQL>select con_id,username,common from cdb_users

where username like'C##%'

order by con_id asc;

Below you see the common users exist in the root container and all pluggable databases.

Switch containers
SQL>alter session set container=dev2db;
SQL>show con_name con_id

SQL>select con_id,username,common from cdb_users

where username like'C##%'

order by con_id asc;

Below you see connected to one of the pluggable databases we also see the common users.

Connected to the container root I use the new dictionary view CDB_ROLE_PRIVS. Note you can still use the DBA_ROLE_PRIVS but there is no con-id column.

$sqlplus / as sysdba

SQL>show con_name con_id

SQL>select con_id,grantee,granted_role,common from cdb_role_privs

where grantee like'C##%'

order by con_id asc;

Below you can see that C##ORADBADMIN has the connect role granted across all pluggable databases but C##ORADBDEV2 only has the connect role granted to pluggable database 5. Since the role is not commonly granted across all pluggable databases for C##ORADBDEV2 is labeled as not common.

SQL>alter session set container=dev2db;

SQL>show con_name con_id

SQL>select con_id,grantee,granted_role,common from cdb_role_privs

where grantee like'C##%'

order by con_id asc;

Below you see connected to the pluggable database DEV2DB we can also see the common and not common privileges granted to the common users.

SQL>alter session set container=dev1db;

SQL>show con_name con_id

SQL>select con_id,grantee,granted_role,common from cdb_role_privs

where grantee like'C##%'

order by con_id asc;

Below you can see when connected to pluggable database 4 you can see the common role granted to the common user C##ORADBADMIN but you do not see the common user C##ORADBDEV2 as we only granted the connect role to the common user C##ORADBDEV2 for pluggable database 5.

Test Connection

$sqlplus /nolog

SQL>connect C##ORADBDEV2/temp1234@dev2db

SQL>show con_name con_id

SQL>connect C##ORADBDEV2/temp1234@dev1db

Below you can see we can connect successfully to pluggable database dev2db but the connect command fails to connect to dev1db due to lack of create session privilege.

Create Common Role

I will create a common role with privileges to create, drop and alert users for all pluggable databases. Then I will grant the new role to the common users we created.

Note:
Common role names need to start with C## or c##.

Create Role

Connected to the root container I create the role
$sqlplus
SQL>show con_name con_id
SQL>CREATE ROLE C##MIDLEVELDBA NOT IDENTIFIED CONTAINER=ALL;
SQL>grant drop user to C##MIDLEVELDBA container=all;
SQL>grant create user to C##MIDLEVELDBA container=all;
SQL>grant alter user to C##MIDLEVELDBA container=all;

Grant Role

Connected to the root container I grant the roles
$sqlplus
SQL>show con_name con_id
SQL>grant C##MIDLEVELDBA to C##ORADBADMIN container=all;

SQL>alter session set container=dev2db;
SQL>show con_name con_id
SQL>grant C##MIDLEVELDBA to C##ORADBDEV2;

Review Users and Privileges

SQL>alter session set container=cdb$root;

SQL>select con_id,username,common from cdb_users

where username like'C##%'

order by con_id asc;

SQL>select con_id,grantee,granted_role,common from cdb_role_privs

where grantee like'C##%'

order by con_id asc;

Below you can see that the C##MIDLEVELDBA role is granted commonly across all pluggable databases for common user C##ORADBADMIN. The role C##MIDLEVELDBA granted to common C##MIDLEVELDBA is not common as it was not commonly granted across all pluggable database only granted for pluggable database 5.

With the privileges granted common user C##ORADBADMIN can administor user accounts on all pluggable databases. The common user C##ORADBDEV2 can only administor user account on pluggable database 5 DEV2DB.

New Pluggable Databases

If new pluggable database are created the common users and roles will automatically exist in the new pluggable databases.

Below you can see that I create 4 new pluggable databases and the common users and roles exist in the new pluggable databases.

Follow me on Twitter