Multitenant Common Users Accessing Application Tables in PDBs

I recently had a requirement where the Common User in a Multitenant DB environment wanted to access application tables across Pluggable Databases (PDBs) but at the same time access dictionary views across all PDBs without the need to manually switch between containers. 

This was because I had to setup a monitoring user account to monitor all PDBs performance as well as application workload.

In this example I will show you how the Common User (created at CDB level) can be configured to access the application tables (create at PDB level)

Assumptions / Prerequisites:

• An existing Multitenant DB environment exists with at least one PDB (eg. CRMPDB1) created.
• A application schema exist with objects in the PDB  (eg. TNAPP.APP_STATS)

The Steps

And this is how you do it …

1. Connect as SYSDBA  and create the Common User at CDB level

$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 16 01:47:56 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create user C##ADMIN identified by "WElcome12##" container = all;

User created.

2. Grant the system privileges to the Common User

SQL> grant create session, create database link, create synonym to C##ADMIN container=all;

Grant succeeded.

3. Switch to the PDB (eg. CRMPDB1)

SQL> ALTER SESSION SET CONTAINER=CRMPDB1;

Session altered.

4. Grant the object privileges at a PDB Level to the Common User

SQL> grant select on TNAPP.APP_STATS to C##ADMIN container = current;

Grant succeeded.

SQL> exit

5. Connect to the Common User at CDB level and Create Database Link to PDB

$ sqlplus C##ADMIN/WElcome12##

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 16 02:28:32 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Last Successful login time: Tue Jul 16 2019 02:27:47 +00:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> CREATE DATABASE LINK CRMPDB1 CONNECT TO C##ADMIN IDENTIFIED BY "WElcome12##" USING 'CRMPDB1';

Database link created.

6. Create a Synonym (alias) to the Application Table (eg. APP_STATS)

SQL> CREATE OR REPLACE SYNONYM "C##ADMIN"."APP_STATS" FOR "TNAPP"."APP_STATS"@CRMPDB1;

Synonym created.

7. Query the table directly to confirm you can read the data.

SQL> select count(*) from APP_STATS;

COUNT(*)
----------
1

SQL>

And Voila! You can now directly access the application table from the Common User.