Caching Caution - Oracle

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.

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.

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.