By default common users do not have privileges to query information related to other PDBs when are connected to CDB$ROOT container. If appropriate privileges are given common users can query PDBs information when are connected to specific PDB container. The point of this post is to show how can information access for specific (or all) PDBs is given to common users when are connected to CDB$ROOT container. Restrictions can be defined on views and tables which are defined as container data objects. We can list all views which are defined as container data objects from [DBA|ALL|USER]_[VIEWS|TABLES].
In the release which I’m running (18.104.22.168.0) there are total of 2665 views. I’ve created a common user with select privileges to CDB_DATA_FILES dictionary view. We’ll see how can we enable and restrict access to information only for XDB1 pluggable database when C##IARSOV is connected to CDB$ROOT container.
If I try to query datafile information from CDB_DATA_FILES for XDB1 pluggable database from CDB$ROOT I should get zero results. With only these settings C##IARSOV is only available to see datafile information (container information) for the container to which is connected.
From XDB1 pluggable database:
In order for C##IARSOV to be able to query information for XDB1 from CDB$ROOT we need to alter the user and set CONTAINER_DATA attribute. With SET CONTAINER_DATA clause we specify for which containers the user can see information and with FOR container_data_object we specify the container data object. In this case to restrict C##IARSOV to be able to see information from CDB_DATA_FILES I’ve used the following alter user definition.
Note that CDB$ROOT must be included with SET CONTAINER_DATA. Further, we can check the definition for already given accesses in CDB_CONTAINER_DATA dictionary view and whether the access is given for all containers (see ALL_CONTAINERS column) or it’s given for specific PDBs (see CON_NAME column).
Now, C##IARSOV should be able to query XDB1 information from CDB$ROOT container.
If we want to define CONTAINER_DATA attribute for all containers we can use SET CONTAINER_DATA = ALL.
After CONTAINER_DATA attribute is set for specific container data object we can use ADD CONTAINER_DATA clause to add additional containers.
If we try to use ADD CONTAINER_DATA for container data object that is not already defined with SET CONTAINER_DATA or CONTAINER_DATA attribute is already set to ALL we will hit ORA-65060: CONTAINER_DATA attribute is not set.
To remove specific PDB container from container_data attribute we use REMOVE CONTAINER_DATA clause.
For more information and syntax details check container_data_clause
You can comment via Twitter.
Link for this post: comment