SunQuest
 
       Oracle
  Home arrow Oracle arrow Page 5 - Row-Level Security with Virtual Privat...
Dev Shed Forums 
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Sun Developer Network 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Actuate Whitepapers 
VeriSign Whitepapers 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ORACLE

Row-Level Security with Virtual Private Database
By: McGraw-Hill/Osborne
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 26
    2005-10-20

    Table of Contents:
  • Row-Level Security with Virtual Private Database
  • RLS In-Depth
  • Creating the Policy Function
  • The RLS Layer of Security
  • Debugging RLS Policies
  • Invalid SQL
  • Null Application Context Values and Recursive Lookups
  • VPD Performance
  • SHARED_STATIC Caching
  • Caching Caution

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    Row-Level Security with Virtual Private Database - Debugging RLS Policies


    (Page 5 of 10 )

    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:

    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

    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.

    More Oracle Articles
    More By McGraw-Hill/Osborne


       · This article is an excerpt from the book "Row-Level Security with Virtual Private...
     

    Buy this book now. 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.

       

    ORACLE ARTICLES

    - Tuning PL/SQL Code
    - Debugging PL/SQL Code
    - Testing PL/SQL Code
    - Working With PL/SQL Code
    - Conditional Compilation for Oracle Database ...
    - Compile-Time Warnings for Oracle DB 10g
    - Compiling PL/SQL Code for an Oracle Database
    - Troubleshooting PL/SQL Code
    - Managing PL/SQL Code
    - Data Manipulation and More for HTML DB Appli...
    - Oracle Database Fundamentals
    - Adding Processes to HTML DB Applications
    - Adding Computations, Processes, and Validati...
    - Sub-templates and More with Oracle HTML DB
    - Focusing on Templates in Oracle HTML DB





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway