Home arrow Oracle arrow Page 10 - Row-Level Security with Virtual Private Database

Caching Caution - 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).

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

A word of caution: cached policies may not prove effective in all situations. The policy function is executed once, and the result is cached. The policy function will never be re-executed, which means any logic used within the function will never be re-executed. There are some situations when this is undesirable. The most obvious is when the predicate changes based on the logic in the policy function. In the previous example, the predicate is constant, while the value returned by the application context changes. This is very desirable and allows for a cached VPD policy.

The implementation of the policy function also influences whether the policy can be cached. For example, the predicate function in the following meets the requirement of restricting access between the hours of 9 A.M. and 5 P.M. However, if this policy is cached, the first access will cause the function to execute, the result will be cached and applied to everyone. If the function first executes at 10 A.M., then the access will be permitted even after 5 P.M.

-- Wrong implementation for caching
CREATE OR REPLACE FUNCTION pred_function_9_to_5 (
  p_schema  IN  VARCHAR2 DEFAULT NULL, 
  p_object  IN  VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2
AS
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24') BETWEEN 9 AND 17
  THEN
   
RETURN '1=1';
  ELSE
    RETURN '1=0';
  END IF;
END;

To correct this, the policy function must either not be cached, or the value must be evaluated each time. The following forces the condition to be evaluated each time:

-- Correct implementation for caching.
CREATE OR REPLACE FUNCTION pred_function_9_to_5 (
  p_schema  IN  VARCHAR2 DEFAULT NULL, 
  p_object  IN  VARCHAR2 DEFAULT NULL)  
  RETURN VARCHAR2
AS
BEGIN
  RETURN 'to_char(sysdate,''HH24'') between 9 and 17';
END;
/

The point is that the policy function’s implementation cannot be done without regard to the caching strategy. In most cases, caching should be considered and the code should be written to ensure security is always enforced. You should first test without caching to ensure your performance is acceptable. Then enable caching and test the policy with the appropriate use cases to ensure that security is working as desired.

Comparing VPD Performance to View-Based RLS

In the view chapter, you saw a performance test that compared the time required to execute RLS in a view that used a function to filter the records. For even comparisons, you can now build a VPD policy that emulates that security functionality and then test the performance:

sec_mgr@KNOX10G> sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION owner_admin (
  2   p_schema  IN  VARCHAR2 DEFAULT NULL,
  3   p_object  IN  VARCHAR2 DEFAULT NULL)
  4   RETURN VARCHAR2
  5 AS
  6 BEGIN
 
7   IF (SYS_CONTEXT ('userenv', 'isdba') =
   'TRUE')
 
8   THEN
  9     RETURN NULL;     -- returns all rows
 10   ELSE
 11     RETURN 'OWNER = USER';
 12   END IF;
 13 END;
 14 /
Function created.

You’ll create a new view over your BIG_TAB table. Add your VPD policy to the view you create:

sec_mgr@KNOX10g> CREATE OR REPLACE VIEW big_vpd_view
  2  AS
  3    SELECT * FROM big_tab;
View created.
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3       (object_name    => 'BIG_VPD_VIEW',
  4        policy_name    =>
   'BIG_VPD_VIEW_SIUD',
  5       policy_function => 'owner_admin');
  6  END;
  7  /
PL/SQL procedure successfully completed.

Now for the tests, query once on the base table where you specify the security predicate directly. Query once on the function-based view that was created in the “Functions in Views for Row-Level Security” section in Chapter 10, and finally, query the VPD-based view:

sec_mgr@KNOX10g> SET timing on sec_mgr@KNOX10g> -- time with security built into SQL
sec_mgr@KNOX10g> SELECT COUNT (*)
 
2    FROM big_tab
  3   WHERE 1 = DECODE (owner, USER, 1, 0)
  4      OR SYS_CONTEXT ('userenv', 'isdba')
   = 'TRUE';
  COUNT(*)
----------
     
1184
Elapsed: 00:00:07.48
sec_mgr@KNOX10g> -- time with RLS built into view
sec_mgr@KNOX10g> SELECT COUNT (*)
  2    FROM big_view;
sec_mgr@KNOX10g> -- time with RLS built into view sec_mgr@KNOX10g> SELECT COUNT (*)
  2 FROM big_view;
 
COUNT(*)
----------
     
1184
Elapsed: 00:01:05.97
sec_mgr@KNOX10g> -- time with VPD sec_mgr@KNOX10g> SELECT COUNT (*)
  2    FROM big_vpd_view;
 
COUNT(*)
----------
     
1184
Elapsed: 00:00:06.99

The query on the VPD view performs on par with the modified SQL because the SQL is modified by the VPD policy before it is executed.

Summary

Virtual Private Database (VPD) helps resolve some of the challenges associated with views. An RLS policy is defined as a mapping from a PL/SQL implemented security function to a table, view, or synonym. The actual PL/SQL implementation that enforces the VPD can be based on whatever is relevant—IP address, time of day, application context values. The policies also are transparent to queries on the protected objects.

New to Oracle Database 10g is the ability to support column-sensitive policies, which allows a more selective invocation of the RLS mechanisms. This is very practical and allows you to more easily store data with different sensitivities within the same table. One of the challenges to implementing VPD is debugging faulty implementations. You saw various best practice techniques for helping to mitigate the debug challenge.

To ensure high performance, the RLS mechanism has been written to modify the SQL before it is parsed and executed. This allows the database to use indexes and optimization plans to ensure fast access to data. Using bind variables and application contexts and enabling policy caching can significantly improve RLS performance.

In Chapter 12, you will explore an implementation (or perhaps an augmentation) of VPD called Oracle Label Security.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

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