Pluggable Databases - Expert Consolidation in Oracle Database 12c - page 257

Common users can be granted the rights to query information about currently attached PDBs, without actually
having to connect to them. This is useful for monitoring accounts where a common user is used by the monitoring
software. The “alter user” command has been extended for common users, and it is possible to specify a container_data
clause. In this new clause you can specify which objects a common user should have access to. The following example
walks you through the creation of such a user. The plan is to grant the user access to v$session without the privilege to
connect to PDB1 directly.
SYS@CDB$ROOT> create user c##perfuser identified by secret ...
User created.
Once the user is created, you can grant it the right to view information in v$session for PDB “pdb1”. The CDB has
three user-PDBs in total.
SYS@CDB$ROOT> alter user c##perfuser
2 set container_data = (CDB$ROOT, PDB1)
3 for v_$session container = current;
User altered.
The “set container_data” clause allows the user “c##perfuser” to query from v$session for information pertaining
to “pdb1”. Remember you need to be connected to the root, and you need to limit the command to the current
container. Let's assume that the “c##perfuser” has a select privilege on v_$session:
SYS@CDB$ROOT> grant create session to c##perfuser;
Grant succeeded.
SYS@CDB$ROOT> grant select on v_$session to c##perfuser;
Grant succeeded.
With the above modification the common user can query the view v$session for “pdb1” with con_id of 3.
C##PERFUSER@CDB$ROOT> select con_id, username from v$session
2 where type='USER' and username <> 'SYS';
CON_ID USERNAME
---------- ------------------------------
1 C##PERFUSER
3 USER1
The “set container_data” clause is available as part of the alteration of users. The above example has been quite
specific: the intention was to only grant the privilege to select from v$session for sessions belonging to “pdb1”. You can
find this stored in the view DBA_CONTAINER_DATA:
SYS@CDB$ROOT> select * from dba_container_data
2 where username = 'C##PERFUSER';
USERNAME D OWNER OBJECT_NAME A CONTAINER_NAME
-------------------- - ----- --------------- - --------------------
C##PERFUSER N SYS V_$SESSION N CDB$ROOT
C##PERFUSER N SYS V_$SESSION N PDB1