HomeOracle Page 5 - Row-Level Security with Virtual Private Database
Debugging RLS Policies - Oracle
Oracle's Virtual Private Database technology provides a logical and elegant method for applying security to the data within database tables. This article explains how VPD can be used to provide row-level security. It was excerpted from chapter 11 of Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300).
When an RLS policy fails, the users can no longer access the protected objects. I have found that the chances of this happening are in direct proportion to the complexity of the policy function. When this does happen, having a sound method for troubleshooting the problem is critical to minimizing the down time associated with the error.
There are generally two reasons for a policy error. First, the policy function is invalid and will not recompile or execute. For example, an error will occur if the policy function queries a table that no longer exists. A policy error will also occur if the policy function doesnít exist. This is usually because the function has been dropped or the function has been incorrectly registered in the ADD_POLICY procedure.
The second reason for policy error occurs when the policy function returns a string that, when added to the original SQL, produces an invalid SQL statement. There are many possible reasons why the function can fail. The first step to debugging is to ensure that the function is working.
Broken Policy Functions
Errors caused by an invalid policy function can easily make the VPD transparency disappear. To show this, the following example creates a simple table with a simple policy function. The policy function is dependent on the table.
scott@KNOX10g> -- Create a dependency. This table will scott@KNOX10g> -- be called by the RLS policy function. scott@KNOX10g> CREATE TABLE t AS SELECT * FROM DUAL; Table created. scott@KNOX10g> -- Create policy function. Function scott@KNOX10g> -- is dependent on table T. 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 l_total_recs NUMBER; 7 BEGIN 8 SELECT COUNT (*) 9 INTO l_total_recs 10 FROM t; 11 RETURN '1 <= ' || l_total_recs; 12 END; 13 / Function created.
The point here is to create and then break a dependency. The policy function is dependent on the table T. Add the policy to your table and check to see that no errors occur on access:
Dropping the table T will invalidate the policy function. The first indication that something is wrong may come when you try to query the EMP table:
scott@KNOX10g> -- This drop breaks the policy function scott@KNOX10g> DROP TABLE t; Table dropped. scott@KNOX10g> -- Policy function is invalid and will not recompile; scott@KNOX10g> SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp * RROR at line 1: ORA-28110: policy function or package SCOTT.PRED_FUNCTION has error
Recovering from this is very easy with Oracle Database 10g. You can simply use the Flashback Drop to restore the table T. Once this is done, access to the EMP table is also restored because the policy function will be able to successfully execute:
scott@KNOX10g> -- Recover table scott@KNOX10g> FLASHBACK TABLE t TO BEFORE DROP; Flashback complete. scott@KNOX10g> SELECT COUNT(*) FROM emp; COUNT(*) ---------- 14
Handling Policy Function Exceptions
When the query on EMP was executed after the T table was dropped and before it was recovered, the database threw an error indicating precisely why the query failed. This is very helpful, but it may not be desirable because two potentially sensitive things were revealed. First, it indicated that there is an RLS policy on the table; second, it gave the name of the policy function guarding the table.
You may want to consider suppressing the policy function exceptions to prevent this information from being displayed to the users. The best approach to this requires the use of dynamic SQL that hides the functionís database object dependencies. The function still has to return a value. Returning null will allow the user access to all the records. Failing secure means that you should return zero records if an exception is thrown. This example shows how to fail secure:
scott@KNOX10g> -- User dynamic SQL and exception handling scott@KNOX10g> -- to mask policy function errors 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 l_total_recs NUMBER; 7 BEGIN 8 -- Dynamic SQL hides the dependency on table T 9 EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM t' 10 INTO l_total_recs; 11 RETURN '1 <= ' || l_total_recs; 12 EXCEPTION 13 WHEN OTHERS 14 THEN 15 -- Fail Secure: remove all rows 16 RETURN '1=0'; 17 END; 18 / Function created. scott@KNOX10g> SELECT COUNT(*) FROM emp; COUNT(*) ---------- 14 scott@KNOX10g> -- This drop breaks the policy function scott@KNOX10g> DROP TABLE t; Table dropped. scott@KNOX10g> -- Policy fails secure. No records are displayed or scott@KNOX10g> -- exception messages given to user. scott@KNOX10g> SELECT COUNT(*) FROM emp; COUNT(*) ---------- 0
The downside to this approach is that it will be more difficult to debug the policy yourself. The solution to this is to comment out or remove the exception handling code, but you should only do this while debugging.