By Franck Pachot

.
In Multitenant, there are two ways to connect to a pluggable database. This means that if you want to prevent access to a pluggable database you need to revoke two privileges.

Privilege Analysis

Rather than trying with different combination of privileges there is a clever way to do it in 12c with privilege analysis (remember it’s an option).
The idea is to grant all privileges:


SQL> create user C##USER1 identified by oracle container=all;
User created.
SQL> grant DBA to C##USER1 container=all;
Grant succeeded.

and then start privilege capture to know which privileges are actually used:


SQL> exec dbms_privilege_capture.create_capture (name=>'demo',type =>dbms_privilege_capture.g_role,roles=>role_name_list('DBA'));
PL/SQL procedure successfully completed.
SQL> exec dbms_privilege_capture.enable_capture (name=>'demo');
PL/SQL procedure successfully completed.

Note that privilege capture is done per container, so I activate it also in my pluggable database:


SQL> alter session set container=PDB001;
Session altered.
SQL> exec dbms_privilege_capture.create_capture (name=>'demo',type =>dbms_privilege_capture.g_role,roles=>role_name_list('DBA'));
PL/SQL procedure successfully completed.
SQL> exec dbms_privilege_capture.enable_capture (name=>'demo');
PL/SQL procedure successfully completed.

At the end of each operation, I will get the result running this from each container:


SQL> exec dbms_privilege_capture.disable_capture(name=>'demo');
PL/SQL procedure successfully completed.
SQL> exec dbms_privilege_capture.generate_result(name=>'demo');
PL/SQL procedure successfully completed.
SQL> select con_id,username,sys_priv from cdb_used_sysprivs_path;

CONNECT

The first way to connect to a pluggable database is to connect directly to its service:


SQL> connect C##USER1@//localhost/PDB001.pachot.oraclecloud.internal
Connected.

And, as you expected, the privilege used is the CREATE SESSION in the pluggable database (PDB001 is CON_ID=4) and no privilege at all is needed in CDB$ROOT:


    CON_ID USERNAME   SYS_PRIV
---------- ---------- ----------------------------------------
         4 C##USER1   CREATE SESSION

SET CONTAINER

The second way available to common users only is to connect to CDB$ROOT and then switch to the pluggable database container:


SQL> connect C##USER1
Connected.
SQL> alter session set container=PDB001;
Session altered.

Of course, we see the CREATE SESSION when connecting to the CDB$ROOT>


    CON_ID USERNAME   SYS_PRIV
---------- ---------- ----------------------------------------
         1 C##USER1   CREATE SESSION

and here is the SET CONTAINER privilege which is used on the pluggable database only.


    CON_ID USERNAME   SYS_PRIV
---------- ---------- ----------------------------------------
         4 C##USER1   SET CONTAINER

Conclusion

If you want to prevent access to a pluggable database from a common user, you need to revoke both SET CONTAINER and CREATE SESSION in that container. Same idea if you want to set some session environment through logon trigger, you may also do it in an AFTER SET CONTAINER trigger.
Now, do you think that revoking system privileges is sufficient to prevent C##USER1 from seeing PDB001 data? You should remember that there is another way to see what happens in another container, through the V$ or CDB_ views. Not all data, but lot of information are exposed. The ALTER USER … SET CONTAINER_DATA is there to control that. But that’s for a future post.
Remember that a common user can be very powerful in a public or private cloud. You can control what he has access to, but then think of all ways of access.