Introduction
Let's say you've created a common user in your Oracle 12c instance and you granted the user permissions to connect and select some specific dynamic views (e.g. V$PDBS, V$CONTAINERS).
Afterwards, you connect with that common user to the root container (CDB$ROOT), you query V$PDBS but no rows are returned.
This issue was raised in the OTN forum by a user and my answer to that user was very simple - use the
CONTAINER_DATA clause (See:
https://community.oracle.com/message/13301017#13301017).
Basically, when a common user is connected to the ROOT and it executes a query on a container data object (As per Oracle Doc, container data objects include: V$, GV$, CDB_, and some Automatic Worklaod Repository DBA_HIST* view), then that query will only dispay data for the PDBs which are visible for that common user, and this is what you can set using the CONTAINER_DATA clause.
Demonstration
In the first step I'll create a common user, grant him permissions to connect and query V$PDBS and then I'll connect with that user and try to query V$PDBS.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBTEST MOUNTED
PINIDB READ WRITE
SQL> create user C##TEST identified by test;
User created.
SQL> grant connect to C##TEST;
Grant succeeded.
SQL> grant select on sys.v_$pdbs to C##TEST;
Grant succeeded.
SQL> connect C##TEST/test@isrvmrh541-cdb.world
Connected.
SQL> select * from v$pdbs;
no rows selected
As you can see, no rows are returned from the query.
Let's connect again with SYS and verify which PDBs are visible for user C##TEST using the CDB_CONTAINER_DATA dictionary view which displays information about the user-level and object-level CONTAINER_DATA attributes specific in the CDB:
SQL> connect sys@isrvmrh541-cdb.world as sysdba
Enter password:
Connected.
SQL> SELECT username,
2 owner,
3 object_name,
4 all_containers container_name
5 FROM CDB_CONTAINER_DATA
6 WHERE username = 'C##TEST';
no rows selected
As you can see, user C##TEST has no container data attributes.
Let's specify that user C##TEST can see the data of V$PDBS from all the containers:
SQL> alter user C##TEST set container_data=all for sys.v_$pdbs container = current;
User altered.
SQL> SELECT username,
2 owner,
3 object_name,
4 all_containers,
5 container_name
6 FROM CDB_CONTAINER_DATA
7 WHERE username = 'C##TEST'
8 ;
USERNAME OWNER OBJECT_NAME ALL_CONTAINERS CONTAINER_NAME
--------------- ---------- --------------- ------------- --------------------
C##TEST SYS V_$PDBS Y
As you can see, the CONTAINER_NAME is NULL because I sepcific in the aler command that the container_data will be visible for all containers.
If we would like to specify that the data of
every container data object that the user has SELECT permissions to access, you can remove the "for" clause and execute the following command:
SQL> alter user C##TEST set container_data=all container = current;
User altered.
SQL> SELECT username,
2 owner,
3 object_name,
4 all_containers,
5 container_name
6 FROM CDB_CONTAINER_DATA
7 WHERE username = 'C##TEST';
USERNAME OWNER OBJECT_NAME ALL_CONTAINERS CONTAINER_NAME
--------------- ---------- --------------- ------------- --------------------
C##TEST SYS V_$PDBS Y
C##TEST Y
As you can see, now user C##TEST has an access for all the objects which he will granted permissions to SELECT from.
Summary
- Once you create a COMMON USER, you should also specify which PDBs data are visible to that common user for which objects using the CONTAINER_DATA clause
- You can specify the object-level CONTAINER_DATA attributes for a user using the ALTER USER command
- You can view the information about the user-level and object-level CONTAINER_DATA attributes via CDB_CONTAINER_DATA dictionary view
Useful Links:
