Feb 28 2010
Tracing VPD Predicates
Even though a number of articles and blog posts have already been written on this topic (e.g. on Pete Finnigan’s site I found references dating back from 2003), from time to time I’m still asked “How to trace predicates generated by VPD?”. Hence, here’s yet another blog post about this topic…
Let’s setup the scene before explaining how you can do it:
- The user named CHA owns the schema created with the script ?/sqlplus/demo/demobld.sql.
- The data stored in the EMP table is the following:
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
- The data stored in the EMP table is protected by a VPD predicate created with the following commands:
SQL> CREATE OR REPLACE FUNCTION emp_restrict (p_schema IN VARCHAR2, p_table IN VARCHAR2) RETURN VARCHAR2 AS
2 BEGIN
3 RETURN '''' || sys_context('userenv','session_user') || ''' = ename';
4 END emp_restrict;
5 /
SQL> execute dbms_rls.add_policy('CHA','EMP','EMP_POLICY','CHA','EMP_RESTRICT');
- Because of the VPD predicate, different users see different rows. Here an example:
SQL> connect scott
SQL> SELECT * FROM cha.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
SQL> connect clark
SQL> SELECT * FROM cha.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
If the function used for the VPD policy is simple and does generate a predicate that can be correctly parsed, to view the predicate it is enough to give a look to the output of the dbms_xplan package. The following SQL statements illustrate this:
SQL> SELECT * FROM table(dbms_xplan.display_cursor(sql_id=>'dmc3z4t0u57y1', format=>'basic predicate'));
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM cha.emp
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='SCOTT')
Another possibility, if you have access to the V$VPD_POLICY view, is to execute a query like the following one:
SQL> SELECT predicate FROM v$vpd_policy WHERE sql_id = 'dmc3z4t0u57y1'; PREDICATE ---------------------- 'SCOTT' = ename
However, in case you want to see the whole SQL statement or the predicate generated by the VPD policy leads to an ORA-28113 (policy predicate has error), there is no documented way I’m aware of to display the generated predicate. One of the undocumented ways to do it is to use the event 10730. Note that the event generates a trace file containing the information we are looking for in such situations. Here is an example:
- Enable the event:
SQL> ALTER SESSION SET events '10730 trace name context forever, level 1';
- Run the query that, for example, leads to an error (note that to generate the ORA-28113 I modified the EMP_RESTRICT function…):
SQL> SELECT * FROM cha.emp;
SELECT * FROM cha.emp
*
ERROR at line 1:
ORA-28113: policy predicate has error
- Check the trace file generated by the database engine (note that the V$DIAG_INFO view is available as of 11g only):
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
---------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
SQL> host tail -10 /u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
-------------------------------------------------------------
Error information for ORA-28113:
Logon user : SCOTT
Table/View : CHA.EMP
Policy name : EMP_POLICY
Policy function: CHA.EMP_RESTRICT
RLS view :
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "CHA"."EMP" "EMP" WHERE ('SCOTT' = enamee)
ORA-00904: "ENAMEE": invalid identifier
-------------------------------------------------------------
As you can see, the trace file contains not only the whole SQL statement but also the reason for the ORA-28113 error.