12c Multitenant Internals: VPD for V$ views

By Franck Pachot

.
I described in an earlier post on AWR views how the dictionary views were using metadata and object links to show information from other containers. But this mechanism cannot work for fixed views (aka V$) because they don’t have their definition in the dictionary.

The big difference is that most of V$ views are available long before the dictionary is opened or even created. Just start an instance in NOMOUNT and you can query the V$ views. Even in multitenant, you can switch to different containers in MOUNT, and query V$ views, when no dictionary is opened.

SQL> alter database mount;
Database altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB1                           MOUNTED
 
SQL> alter session set container=pdb1;
Session altered.
 
SQL> show pdbs;
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           MOUNTED

V$ views query information from the instance and this information pertain to one container:

• CON_ID=0 for the CDB itself
• CON_ID=1 for CDB$ROOT
• CON_ID=2 for PDB$SEED
• CON_ID=3 for the first PDB you have created

When you are in root, the V$ views are queried as normal and show all information – from all containers – with their related CON_ID

When you are in a PDB, you must see the objects that belong to your PDB, but not those that belong to other PDBS. But this is not sufficient. For example, you may query the version, and the version is related to the CDB itself, with CON_ID=0:

SQL> alter session set container=CDB$ROOT;
Session altered.
 
SQL> select * from v$version;
 
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                  0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

Then, in a PDB you should see your PDB objects and the CON_ID=0 ones. Oracle needs a new mecanism for that. One way would be to switch to root, query the V$ and filter on CON_ID. We don’t need that. Context switch is there to access data from a different container tablespace, because tablepaces are not shared. But V$ views expose data from the instance, and the instance is shared. Any container can see all rows, and we just want to filter some rows.

Here is the execution plan when querying V$VERSION from a PDB:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL> explain plan for select * from v$version;
Explained.
 
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 1078166315
 
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    68 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$VERSION |     1 |    68 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("CON_ID"=0 OR "CON_ID"=3) AND
              "INST_ID"=USERENV('INSTANCE'))

An additional predicate (“CON_ID”=0 OR “CON_ID”=3) is added to the view. How is it done? Oracle has a security feature for that: Virtual Private Database – aka Row Level Security – which adds a where clause dynamically.

One way to get more information about virtual private databases is to have an error on its execution and I know that a user with only select privilege cannot EXPLAIN PLAN (see MOS Note 1029064.6).

I connect to a PDB with a low privileged user:

SQL> connect scott/tiger@//localhost/PDB1
Connected.

I explain plan the V$VERSION fixed view.

SQL> explain plan for select * from v$version;
 
Error starting at line : 10 File @ /media/sf_share/122/blogs/multitenant-vpd.sql
In command -
explain plan for select * from v$version
Error report -
ORA-28113: policy predicate has error
28113. 00000 -  "policy predicate has error"
*Cause:    Policy function generates invalid predicate.
*Action:   Review the trace file for detailed error information.

Interesting error which confirms the guess: this is a VPD error and it generates a trace:

*** 2017-06-26T22:45:17.838507+02:00 (PDB1(3))
*** SESSION ID:(141.17865) 2017-06-26T22:45:17.838537+02:00
*** CLIENT ID:() 2017-06-26T22:45:17.838541+02:00
*** SERVICE NAME:(pdb1) 2017-06-26T22:45:17.838545+02:00
*** MODULE NAME:(java@VM104 (TNS V1-V3)) 2017-06-26T22:45:17.838548+02:00
*** ACTION NAME:() 2017-06-26T22:45:17.838552+02:00
*** CLIENT DRIVER:(jdbcoci : 12.2.0.1.0) 2017-06-26T22:45:17.838555+02:00
*** CONTAINER ID:(3) 2017-06-26T22:45:17.838558+02:00
 
-------------------------------------------------------------
Error information for ORA-28113:
Logon user     : SCOTT
Table/View     : SYS.V_$VERSION
VPD Policy name    : CON_ID
Policy function: SYS.CON_ID
RLS view  :
SELECT  "BANNER","CON_ID" FROM "SYS"."V_$VERSION"   "V_$VERSION" WHERE (con_id IN (0, 3) )
ORA-01039: insufficient privileges on underlying objects of the view
-------------------------------------------------------------

There’s no container switch here, all is running in PDB1 with CON_ID=3 and the internal VPD has added a where clause to filter rows with CON_ID=0 and CON_ID=3

Do not search for the VPD policy name ‘CON_ID’ and function ‘CON_ID’ in the dictionary views because this happens even when the dictionary is not accessible. This is an internal policy used when querying fixed views in multitenant and which probably use some of the VPD code only.