Home arrow Oracle arrow 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).

  1. Row-Level Security with Virtual Private Database
  2. RLS In-Depth
  3. Creating the Policy Function
  4. The RLS Layer of Security
  5. Debugging RLS Policies
  6. Invalid SQL
  7. Null Application Context Values and Recursive Lookups
  8. VPD Performance
  9. SHARED_STATIC Caching
  10. Caching Caution
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 33
October 20, 2005

print this article



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.
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)
  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
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   =>
  8  END;
  9  /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> CONN scott/tiger
scott@KNOX10g> -- Everything initially works fine
scott@KNOX10g> SELECT COUNT(*) FROM emp;

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;
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;

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)
5  AS
  6   l_total_recs NUMBER;
  7  BEGIN
  8    -- Dynamic SQL hides the dependency 
    on table T
    FROM t'
 10                INTO l_total_recs;
 11    RETURN '1 <= ' || l_total_recs;
 14    THEN
 15      -- Fail Secure: remove all rows
 16      RETURN '1=0';
 17  END;
 18  /
Function created.
scott@KNOX10g> SELECT COUNT(*) FROM emp;
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;

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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: