Enabling Common Users to Access Data in Specific PDBs

 

Extracted from: http://docs.oracle.com/cd/E16655_01/network.121/e17607/authorization.htm#DBSEG806

To enable common users to access data about specific PDBs, you must run the ALTER USER statement in the root.

Example 4-6 shows how to run the ALTER USER statement to enable the common user c##hr_admin to view information pertaining to the CDB$ROOT, SALES_PDB, and hrpdb containers in all container data objects that he can access, using the V$SESSION view.

Example 4-6 Enabling a Common User to View Specific Object Data

CONNECT SYSTEM@root
Enter password: password
Connected.

ALTER USER c##hr_admin
SET CONTAINER_DATA = (CDB$ROOT, SALESPDB, HRPDB) 
FOR V$SESSION CONTAINER=CURRENT;

In this specification:

• 
  

  CDB$ROOT, SALES_PDB, hrpdb refer to the containers that must be accessible to user c##hr_admin. You must include CDB$ROOT.

  
  



• 
  

  FOR V$SESSION specifies the CONTAINER_DATA dynamic view, which common user c##hr_admin will query.

  
  



• 
  

  CONTAINER = CURRENT must be specified because when you are connected to the root, CONTAINER=ALL is the default for the ALTER USER statement, but modification of the CONTAINER_DATA attribute must be restricted to the root.