Viewing VPD predicates with DBMS_XPLAN

1 Oct

Oracle Virtual Private Database (VPD), also known as Row Level Security (RLS), provides a very high level of flexibility in exposing data to users. It is also a very convenient tool for forcing hard parsing of a SQL statement either on every execution or depending on some criteria (e.g. see here). VPD works by appending invisible (e.g. query text in V$SQL doesn’t contain them) predicates to SQL statements. This invisibility is mostly a good thing, but in some cases it can also be a nuisance (e.g. when troubleshooting SQL). ┬áThere are a couple of blogs describing how to view these predicates (here and here), both suggesting DBMS_XPLAN as one of the ways.

However, it doesn’t always work as expected, and sometimes it doesn’t work at all. Consider an example:


select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

drop table t1;

create table t1(x number);

create or replace function f1(p_schema varchar2, p_object varchar2) return varchar2 is
begin
 return 'x=1';
end;
exec dbms_rls.add_policy(object_name =>'T1', policy_name => 'norows_policy', policy_function => 'F1');

select * from t1;

select * from table(dbms_xplan.display_cursor(null, null, 'predicate last'));

SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("X"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

So far so good: we’ve created a VPD predicate X=1, and we can see it in the predicate section of the plan. Now let’s change the predicate to 1=2, and look at the dbms_xplan.display_cursor output again:

SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     1 (100)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

Now we see an internally transformed version (NULL IS NOT NULL) instead of the original one. Let’s try ‘1=1′:

SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Now the predicate section is not showing anything at all. Apparently, the optimizer has recognized the redundancy of this predicate and removed it. But this type of predicates, even though logically redundant, is very handy for forcing hard-parsing (see the link above), so not being able to see it can be a problem.

Fortunately, the predicate can be still viewed in V$VPD_POLICY

select predicate from v$vpd_policy where sql_id = '27uhu2q2xuu7';
---
1=1

Conclusion

Using the predicate section of DBMS_XPLAN.DISPLAY_CURSOR output for checking VPD predicates is not reliable, one should use V$VPD_POLICY instead.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 267 other followers

%d bloggers like this: