Two ways to see predicates added by VPD or FGAC

http://www.bobbydurrettdba.com/2012/07/17/two-ways-to-see-predicates-added-by-vpd-or-fgac/

Two ways to see predicates added by VPD or FGAC

Posted on July 17, 2012 by Bobby

We use a feature called “Virtual Private Database” or VPD on our 11g database.  This looks a lot like what used to be called “Fine Grained Access Control” or FGAC on our 10g database.  The idea behind both of these features is that a particular user in a particular situation would see a tailored view of the data rather than have all users see all of the data all the time.  VPD or FGAC accomplishes this feat by secretly adding predicates to a user’s query’s where clause predicates so they only see the rows allowed by that predicate.

The problem is that when you need to tune a poorly performing query that accesses tables protected by VPD you can’t see the real query through any of the normal methods.  Even a 10046 trace just gives you the unmodified query as the user ran it not the one with the new VPD additions.  I found two ways to see what the real where clause conditions are after the query is modified by VPD – dbms_xplan.display_cursor and 10053 trace.

Here is how to use dbms_xplan.display_cursor to show the VPD predicates:

SQL> select count(*) from test.table_list;

  COUNT(*)
----------
      1858

SQL> select * from table(
dbms_xplan.display_cursor(null,null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_ID  2fuam6x1dyt5v, child number 0
-------------------------------------
select count(*) from test.table_list

Plan hash value: 1374414456

--------------------------------------------------
| Id  | Operation          | Name       | E-Rows |
--------------------------------------------------
|   0 | SELECT STATEMENT   |            |        |
|   1 |  SORT AGGREGATE    |            |      1 |
|*  2 |   TABLE ACCESS FULL| TABLE_LIST |   2028 |
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"<>'SYS')

Note that the predicate owner<>’SYS’ isn’t in the query but was added by the VPD.  The idea here is that the table TEST.TABLE_LIST contains a list of table names but the user doing the query doesn’t have permission to see the names of the tables owned by SYS.

Here is how to use a 10053 trace to see the VPD predicates:

ALTER SESSION SET EVENTS 
'10053 trace name context forever, level 1';

select /* comment to force parse */ count(*) from test.table_list;

ALTER SESSION SET EVENTS '10053 trace name context OFF';

trace output:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TEST"."TABLE_LIST" "TABLE_LIST" 
WHERE "TABLE_LIST"."OWNER"<>'SYS'

I had to add the comment to make sure the query got reparsed.  The 10053 trace only produces a trace when a query is parsed.  Note that the trace file has the description: “Final query after transformations”.  I’m not sure what all transformations are possible but it stands to reason that using a 10053 trace will give you a clearer picture of the real query being parsed.  It shows you the text the parser itself starts with before it starts to break it down into an execution plan that can be run.

alter session set tracefile_identifier='test_lv123';
ALTER SESSION SET EVENTS
'10053 trace name context forever, level 1';

SELECT /* comment to force parse */ * FROM oe_order_headers;

ALTER SESSION SET EVENTS '10053 trace name context OFF';

SELECT U_DUMP.VALUE || '/' || DB_NAME.VALUE || '_ora_' || V$PROCESS.SPID ||
NVL2(V$PROCESS.TRACEID, '_' || V$PROCESS.TRACEID, NULL) || '.trc' "Trace File"
FROM V$PARAMETER U_DUMP
CROSS JOIN V$PARAMETER DB_NAME
CROSS JOIN V$PROCESS
JOIN V$SESSION
ON V$PROCESS.ADDR = V$SESSION.PADDR
WHERE U_DUMP.NAME = 'user_dump_dest'
AND DB_NAME.NAME = 'db_name'
AND V$SESSION.AUDSID = SYS_CONTEXT('userenv', 'sessionid');