Debugging RLS Policies - Oracle

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.

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:

scott@KNOX10g> CONN sec_mgr/oracle10g
Connected.
sec_mgr@KNOX10g> -- Add RLS policy to EMP table;
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3         (object_schema     => 'SCOTT',
  4          object_name       => 'EMP',
  5          policy_name       => 'debug',
  6          function_schema   => 'SCOTT',
  7          policy_function   =>
   'pred_function');
  8  END;
  9  /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> -- Everything initially works fine
scott@KNOX10g> SELECT COUNT(*) FROM emp;
  COUNT(*)
--------
      14

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

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.