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

RMAN Restore and Recover for Multitenant Architecture

In this demo I will show how to handle different types of RMAN restore and recover in a Multitenant environment.

In a Multitenant environment for cloud supporting data centers we will have container databases on many servers with one to many pluggable databases. These container and pluggable databases will have different database administrators and different SLAs.

I will show how we can recover and restore the container database as a whole or one to many pluggable databases. 

CDB = Container Database

PDB = Pluggable Database

Environment

CDB- TESTDBS

PDB- DEV1DB, DEV2DB and TSTDB

Demo

I want to grant a mid-level DBA privileges to only have access to recover and restore the CDB as a whole or just one or many PDBs.

The DBAs name is Jane Doe and she will have access to the database server. She will log in to the server with her own account.

Create OS Group and User

$groupadd backupdba

$useradd -g oinstall -G backupdba jane_doe

$password jane_doe

Create Common User

Common users are database accounts that only exist in a Multitenant environment. Common users are accounts that exist in the root CDB and all PDBs plugged into the CDB. Common users names need to start with C## or c##.

I will create a common user called C##ORADBMAINT which our DBA Jane Doe will have the password to log into the CDB and PDB.

In database 12c there is a new database system privilege called SYSBACKUP. In reviewing the SYSBACKUP database system privilege it has the same system privileges as the database system privilege SYSOPER.

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

$sqlplus / as sysdba

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

SQL>GRANT CONNECT TO C##ORADBMAINT CONTAINER=ALL;

SQL>GRANT SYSBACKUP TO C##ORADBMAINT CONTAINER=ALL;

In the CDB or PDB we can review who has the sysdba, sysoper or sysbackup privileges granted.

• $sqlplus / as sysdba
• SQL>select * from v$pwfile_users;

Restore and Recover CDB

In this restore we need to recover the CDB due to a full loss of spfile, datafiles, controlfiles, achivelogs and redologs for the CDB and all PDB.

Note: The account that is going to execute the restore must have privileges to write files to the location that the files will be restored.

Startup Nomount

$rman

RMAN>connect target "c##oradbmaint/temp1234 as sysbackup"

RMAN>startup nomount

Restore SPFILE

RMAN>restore spfile from '/orabkup/testdbs/rman/c-2422992650-20130807-00';

Shutdown and Startup Nomount

RMAN shutdown immediate

RMAN>startup nomount

Restore Controlfile

RMAN>restore controlfile from '/orabkup/testdbs/rman/c-2422992650-20130807-00';

Mount Database

RMAN>alter database mount;

Restore Database

RMAN>restore database;

You can see below that datafiles for the CDB and all PDBs are restored.

You can see below that datafiles for the CDB and all PDBs are restored.

You can see below that datafiles for the CDB and all PDBs are restored.

You can see below that datafiles for the CDB and all PDBs are restored.

Restore is complete

Recover Database

RMAN>recover database;

Open Database

RMAN>alter database open resetlogs;

Check PDBs

After the CDB is open the PDBs are in a mounted state and need to be open.

New feature in Database 12c RMAN is the ability to execute SQL command without the need to have SQL '<statement>'.

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

Open PDBs

RMAN>alter pluggable database all open;

Check PDBs

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

CDB and all PDBs have now been restore and recovered. CDB and all PDBs are now open for read write.

Recover and Restore PDB

In this restore we need to recover the PDB TSTDB due to a human error which all datafiles for PDB TSTDB were deleted.

Error from trying to open the PDB

Below you can see the files that need to be recovered

Restore PDB

You can connect to the container or directly to the PDB. In the example below I will be connecting to the CDB TESTDBS.

$rman

RMAN>connect target "c##oradbmaint/temp1234 as sysbackup"

RMAN>restore plugable database tstdb;

The restore will begin as you can see only the datafiles for pluggable database TSTDB are restored.

Recover PDB

RMAN>recover pluggable database tstdb;

Recovery will begin current archivelogs are already on disk so they do not need to be restored.

The archivelogs that are not on disk are restored and then used to complete the recover.

Check PDB

After the PDB is restored and recovered we must open the PDB for read write.

New feature in Database 12c RMAN is the ability to execute SQL command without the need to have SQL '<statement>'.

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

Open PDB

RMAN>alter pluggable database tstdb open;

Check PDB

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

We now see the TSTDB PDB is open for read write.

Recover and Restore PDB Connected Directly to PDB

You may have a need to connect directly to the PDB to perform the recovery maybe for the reason that certain DBAs will support certain PDBs in a CDB.

Restore PDB

$rman

RMAN>connect target "c##oradbmaint/temp1234@tstdb as sysbackup"

RMAN>restore database;

Note: If you get error RMAN-20021 or RMAN-06031 then exit RMAN and reconnect.

Recover PDB

RMAN>recover database;

Open PDB

RMAN>alter database open;

Check PDB

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

Report Schema

User report schema to review all tablespace and datafiles.

RMAN>report schema;

Recover PDB Tablespaces

We had another human error and the system and users tablespace datafiles for PDB TSTDB were deleted. We will perform a recover of the tablespaces for the TSTDB PDB.

Error

Report Schema to review the tablespaces needing recovery

You can see that the datafiles for tablespace SYSTEM and USERS in the PDB TSTDB is reporting zero.

Restore PDB Tablespace USERS

Note: PDB must be closed to restore tablespace

$rman

RMAN>connect target "c##oradbmaint/temp1234 as sysbackup"

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

RMAN>restore tablespace tstdb:users;

We see that only the datafile for tablespace USERS in the TSTDB PDB is restored.

Recover PDB Tablespace USERS

RMAN>recover tablespace tstdb:users;

We see some of the archivelog are already on disk.

The archivelogs that are not on disk are restored and then used to complete the recover.

Restore PDB Tablespace SYSTEM

RMAN>restore tablespace tstdb:system;

We see that only the datafile for tablespace SYSTEM in the TSTDB PDB is restored.

Recover PDB Tablespace SYSTEM

RMAN>recover tablespace tstdb:system;

We see some of the archivelog are already on disk.

The archivelogs that are not on disk are restored and then used to complete the recover.

Open PDB

RMAN>alter pluggable database tstdb open;

Check PDB

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

Report Schema

User report schema to review all tablespace and datafiles.

RMAN>report schema;

We can see that the datafiles for tablespace SYSTEM and USERS for PDB TSTDB are now report the total size.

Recover and Restore PDB Tablespaces Connected Directly to PDB

You may have a need to connect directly to the PDB to perform the recovery maybe for the reason that certain DBAs will support certain PDBs in a CDB.

Restore PDB Tablespace USERS

$rman

RMAN>connect target "c##oradbmaint/temp1234@tstdb as sysbackup"

RMAN>restore tablespace users;

We can see that the datafile for tablespace USERS for PDB TSTDB is restored.

Recover PDB Tablespace USERS

RMAN>recover tablespace users;

We see some of the archivelog are already on disk.

The archivelogs that are not on disk are restored and then used to complete the recover.

Restore PDB Tablespace System 

Note: If you get error RMAN-20021 or RMAN-06019 exit RMAN and reconnect directly to PDB.

RMAN>recover tablespace system;

Recover PDB Tablespace SYSTEM

RMAN>recover tablespace users;

We see some of the archivelog are already on disk.

The archivelogs that are not on disk are restored and then used to complete the recover.

Open PDB

RMAN>alter database open;

Check PDB

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

Report Schema

User report schema to review all tablespace and datafiles.

RMAN>report schema;

Restore and Recover Root Container

$rman target /

RMAN>startup mount

RMAN>restore database root;

RMAN>recover database root;

RMAN>alter database open;

RMAN>alter pluggable database all open;

RMAN>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

Restore and Recover Seed Tablespace

$rman target /

RMAN>restore tablespace "PDB$SEED":"SYSAUX";

Follow me on Twitter