Wondering why you can't view data with a COMMON USER in Oracle 12c? You probably didn't use the CONTAINER_DATA clause

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:

• http://docs.oracle.com/database/121/ADMIN/cdb_mon.htm#ADMIN13931
• http://docs.oracle.com/database/121/SQLRF/statements_4003.htm#SQLRF01103