Row-Level Security with Virtual Private Database - Invalid SQL
(Page 6 of 10 )
A second possible reason a policy may fail is because the SQL returned by the policy function creates an invalid SQL statement. Recall the policy’s return value is added to the original SQL. The database will try to parse and execute this final SQL statement. If the SQL is invalid, the policy will throw an error.
Unfortunately, the error message does not indicate how the SQL is malformed. (It’s not even clear what the problem is.) For complex policies, or objects that have multiple policies, this can create some frustration.
For this example, the policy function has been modified to support a policy that restricts DML to just the user’s record. Unfortunately, the SQL column NAME should really be ENAME. Because the return value is a string, this error does not manifest itself until runtime:
scott@KNOX10g> -- Create policy function that returns
scott@KNOX10g> -- records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
2 p_schema IN VARCHAR2 DEFAULT NULL,
3 p_object IN VARCHAR2 DEFAULT NULL)
4 RETURN VARCHAR2
5 AS
6 BEGIN
7 -- Restricting records by mapping
ENAME
8 -- to authenticated database user's
name
9 RETURN 'name = user';
10 END;
11 /
Function created.
scott@KNOX10g> SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp
*
ERROR at line 1:
ORA-28113: policy predicate has error
An ORA-28113 is the indication that the SQL is bad. It’s impossible for the database to know why this is true. Your policy function could have derived the predicate string in an infinite number of ways. The only thing you know is that the resulting SQL is invalid. To debug it, first try to inspect the value returned by the policy function:
scott@KNOX10g> SELECT pred_function "Policy Predicate" FROM DUAL;
Policy Predicate
-------------------
name = user
Note you can query the DBA_POLICIES view (as shown later in the “Viewing the Original SQL and Predicate” section) to see the PREDICATE but only if the policy doesn’t throw an error.
At this point, your best alternative may be to access a trace file. To do this easily, create a simple view (based on another Tom Kyte contribution from asktom.oracle.com) that can determine the trace filename the database will be using:
sec_mgr@KNOX10g> -- Create view to display session's current trace file
sec_mgr@KNOX10g> CREATE OR REPLACE VIEW get_trace_filename
2 AS
3 SELECT c.VALUE
4 || decode(instr
(c.value,'\'),0,'/','\')
5 || INSTANCE
6 || '_ora_'
7 || LTRIM (TO_CHAR (a.spid,
'fm99999'))
8 || '.trc' filename
9 FROM v$process a,
10 v$session b,
11 v$parameter c,
12 v$thread c
13 WHERE a.addr = b.paddr
14 AND b.audsid = USERENV
('sessionid')
15 AND c.NAME = 'user_dump_dest';
View created.
sec_mgr@KNOX10g> GRANT SELECT ON get_trace_filename TO PUBLIC;
Grant succeeded.
sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM get_trace_filename
2 FOR get_trace_filename;
Synonym created.
Connect as a user and reproduce the query that caused the policy error. This is done here as SCOTT:
scott@KNOX10g> -- Enable tracing scott@KNOX10g> ALTER SESSION SET EVENTS
2 '10730 trace name context forever, level 12';
Session altered.
scott@KNOX10g> -- Reproduce error. scott@KNOX10g> -- Issue query; note only selecting ENAME
scott@KNOX10g> SELECT ename FROM emp;
SELECT ename FROM emp
*
ERROR at line 1:
ORA-28113: policy predicate has error
scott@KNOX10g> -- view trace file scott@KNOX10g> COL filename format a50 scott@KNOX10g> SELECT * FROM get_trace_filename;
FILENAME
--------------------------------------------
C:\ORACLE\ADMIN\KNOX10G\UDUMP\knox10g_ora_3740.trc
scott@KNOX10g> -- fix the policy function scott@KNOX10g> -- Create policy function that returns
scott@KNOX10g> -- records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
2 p_schema IN VARCHAR2 DEFAULT NULL,
3 p_object IN VARCHAR2 DEFAULT NULL)
4 RETURN VARCHAR2
5 AS
6 BEGIN
7 -- Restricting records by mapping
ENAME
8 -- to authenticated database user's
name
9 RETURN 'ename = user';
10 END;
11 /
Function created.
scott@KNOX10g> -- Re-query to verify policy works
scott@KNOX10g> SELECT ename FROM emp;
ENAME
---------
SCOTT
Viewing the contents of the trace file with the name returned from GET_TRACE_FILENAME provides helpful information. The relevant information from the trace file just generated from the previous session is displayed here:
--------------------------------------------
Logon user : SCOTT
Table/View : SCOTT.EMP
Policy name : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS view :
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL",
"COMM","DEPTNO" FROM
SCOTT"."EMP" "EMP" WHERE (name = user)
*** 2004-04-05 13:32:02.874
--------------------------------------------
Error information for ORA-28113:
Logon user : SCOTT
Table/View : SCOTT.EMP
Policy name : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS predicate:
name = user
ORA-00904: "NAME": invalid identifier
---------------------------------------
Logon user : SCOTT
Table/View : SCOTT.EMP
Policy name : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS view :
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL",
"COMM","DEPTNO" FROM
SCOTT"."EMP" "EMP" WHERE (ename = user)
This file is very useful, especially the section that displays information on the ORA-28113 error. The RLS predicate record (bolded) pinpoints the erroneous SQL.
Be careful, there is a very misleading fact printed, too.
CAUTION
The RLS view is not the actual SQL the database executes.
Many incorrectly believe it is. This should be obvious because the original SQL statement asked only for the ENAME column; the RLS view would have you believe all columns were selected.
Viewing the Original SQL and Predicate Another view you may find helpful in inspecting your VPD implementations is the V$VPD_ POLICY view. This will tell you what policies have been successfully applied to your SQL statements. The PREDICATE column will actually show the SQL string that was appended to the query. You can join this record with V$SQL, which has the original SQL statement as follows:
sec_mgr@KNOX10g> -- Nothing in pool - a fresh database
sec_mgr@KNOX10g> SELECT * FROM v$vpd_policy;
no rows selected
sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> -- this will seed the v$vpd_policy view
scott@KNOX10g> SELECT COUNT (*) FROM emp;
COUNT(*)
--------
1
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- show VPD Policy results sec_mgr@KNOX10g> COL object_owner format a12 sec_mgr@KNOX10g> COL object_name format a12 sec_mgr@KNOX10g> COL policy format a6 sec_mgr@KNOX10g> COL sql_fulltext format a26 sec_mgr@KNOX10g> COL predicate format a12 sec_mgr@KNOX10g> SELECT object_owner,
2 object_name,
3 policy,
4 sql_fulltext,
5 predicate
6 FROM v$vpd_policy p, v$sql s
7 WHERE p.sql_id = s.sql_id
8 AND predicate IS NOT NULL;
OBJECT_OWNER OBJECT_NAME POLICY SQL_FULLTEXT PREDICATE |
------------ ------------ ------ ------------------------- |
SCOTT EMP DEBUG SELECT COUNT (*) FROM emp ename = user |
This is a good way of inspecting the policies, but there is one important caveat: no records are recorded if the policy throws an error. For policy errors, you will have to use tracing, and even then the real SQL is not given.
Next: Null Application Context Values and Recursive Lookups >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article was excerpted from chapter 11 of Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300). Check it out at your favorite bookstore. Buy this book now.
|
|
