Row-Level Security with Virtual Private Database - VPD Performance

A clear and obvious concern when implementing any type of security is performance. While VPD provides the best security protection—it’s consistent and constant—it is not magical. That is, the modified query will be executed and overall performance will be based on this final query string.

One of the easiest ways to ensure high performing VPD is to create indexes on the predicate values. If your policy function returns “username = USER”, then an index on the USERNAME column will increase VPD execution.

Bind variables help to ensure high performance by allowing the database to save valuable computing resources when queries differ only by variable values.

The first area to investigate is the performance of the returned predicate. Because the actual SQL to be executed includes not only the original SQL, but also the SQL returned from the predicate, you have to ensure that this SQL string performs well.

Bind variables are the staple of performance in an Oracle database. Bind variables allow the database to reuse SQL between database sessions; that is, the database can share a single parsed plan for multiple open cursors. The performance is achieved because the database doesn’t have to reparse the SQL.

In some of the previous examples, the returned strings consisted of the SYS_CONTEXT function. This is critical because the policy function could have resolved the SYS_CONTEXT function and returned that string. The following will return “deptno = 20” for the SCOTT user:

CREATE OR REPLACE FUNCTION dept_only (
  p_schema IN VARCHAR2 DEFAULT NULL,
  p_object IN VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2
AS
BEGIN
  -- Return predicate with value resolved.
  RETURN 'deptno = ' || sys_context('people_ctx','deptno')
;END; /

This implementation is functionally equivalent to the one implemented before, but it is not equivalent from a performance perspective. Ensuring VPD performance comes from the fact that the SYS_CONTEXT is treated as a bind variable. If you do not use bind variables, but rather return the actual resolved value, for example, “deptno = 20”, the database will spend a lot of time reparsing the SQL statements.

Performance as measured here is not based on how the SQL is generated. In the example, the VPD policy is invoked, and the predicate is produced. However, the application could have produced the same or similar SQL. The point is that it doesn’t matter how the SQL was generated—if you want to achieve stellar performance, you have to produce good SQL; bind variables are generally a good way to go.

Another question on the design revolves around whether the SQL should be modified at the database or at the application. Essentially, from a performance perspective, it does not matter. The same process will have to occur regardless of where it occurs. That is, some procedural logic will fire, check some things, and then determine how to reform the SQL query, thus securing the data for the user.

From a security perspective, the database implementation is much better. It guarantees that the SQL, and thus security, will always be enforced. This has value when the data may be needed by other applications and also helps provide defense in depth in the case that the web application is successfully attacked. In the latter case, the security of the application itself has been compromised, and it is only the database security that will now ensure that an attacker does not gain access to unauthorized data.

Another question arises on the performance regarding the time required for the database to invoke         VPD—a.k.a. “overhead.” Because VPD invokes a function each time a statement or cursor is issued, performance can be a concern.

To help ensure things are running extremely fast, the database allows you to cache the VPD policy. In Oracle 9i Database, this capability was introduced with a new STATIC_POLICY parameter. When set to TRUE while registering the policy with the DBMS_RLS.ADD_POLICY procedure, the database will cache, on the first execution of the VPD policy, the results from your policy function. This can result in significant performance improvements because the PL/SQL code implementing your VPD policy will not be called in further queries.

You can still create a VPD policy and set the STATIC_POLICY parameter to true; however, that parameter is deprecated in Oracle Database 10g. The STATIC_POLICY was a good start, but Oracle realized there are still some enhancements that can be done. In Oracle Database 10g, the RLS package supports five new variations of policy caching. The replacement is a parameter called POLICY_TYPE, which allows you to set the caching to one of five different values:

• STATIC  Equivalent to the STATIC_POLICY=TRUE setting in Oracle9i Database. The policy function is executed once, and the resulting string (the predicate) is stored in the Shared Global Area (SGA).
  
• SHARED_STATIC  Allows the predicate to be cached across multiple objects that use the same policy function.
  
• CONTEXT_SENSITIVE  The server always executes the policy function on statement parsing. The server will only execute the policy function on statement execution if it detects context changes. This makes it ideal for connection pooling solutions that share a database schema and use application contexts to actually perform the user identity switching (see Chapter 6 for an example of how to do this).
  
• SHARED_CONTEXT_SENSITIVE  The same as CONTEXT_SENSITIVE except that the policy can be shared across multiple objects that use the same policy function.
  
• DYNAMIC  The default, which makes no assumptions about caching. The policy will be invoked every time the SQL statement is parsed or executed.

You will see three examples of the caching: STATIC, SHARED_STATIC, and SHARED_ CONTEXT_SENSITIVE. You will not see DYNAMIC because that means no caching, and the CONTEXT_SENSITIVE caching is a subset of the SHARED_CONTEXT_SENSITIVE.

The first caching example will use the STATIC option, which is equivalent to setting the STATIC_ POLICY parameter to TRUE in the Oracle 9i Database procedure. To test the caching, the policy function will be modified to incorporate an artificial latency. The DBMS_LOCK.SLEEP procedure simulates a policy function that takes two seconds to execute and will make the caching visible to you. The policy function owner will require execute privileges on the DBMS_LOCK package. Because the RLS will call this function, you can measure the execution time of your query to determine the latency this function causes.

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION user_only (
  2    p_schema  IN  VARCHAR2 DEFAULT NULL,
  3    p_object  IN  VARCHAR2 DEFAULT NULL)
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7    -- stall for 2 seconds
  8    DBMS_LOCK.sleep (2);
  9    RETURN 'username = sys_context
   (''userenv'',''session_user'')';
 10  END;
 11  /
Function created.

Recall this is the policy function guarding the SALARY column of the PEOPLE table. You can test the caching by querying the salaries from the table. The first query will be done prior to enabling the caching:

scott@KNOX10g> SELECT username, salary FROM people
  2   WHERE deptno = 20;
USERNAME      SALARY
---------- ---------
SMITH
JONES
SCOTT           3000
ADAMS
FORD
Elapsed: 00:00:04.10

It took four seconds, not two. The policy is invoked once during the SQL parse phase and once during the statement execution. You can alter the RLS policy to employ the use of the “static” caching for the column-sensitive policy previously defined for the SALARY column of the PEOPLE table:

sec_mgr@KNOX10g> BEGIN
  2    -- Remove current policy
  3    DBMS_RLS.drop_policy
  4         (object_schema      => 'SCOTT',
  5          object_name        => 'PEOPLE',
  6          policy_name =>
   'people_sel_sal');
  7    -- Add policy again but now with 
   Caching
  8    DBMS_RLS.add_policy
  9        (object_schema    => 'SCOTT',
 10         object_name      => 'PEOPLE',
 11         policy_name      =>
   'people_sel_sal',
 12         function_schema  => 'SEC_MGR', 
 13         policy_function  => 'user_only',
 14         statement_types  => 'SELECT',
 15         sec_relevant_cols => 'SALARY', 
 16         policy_type    =>
   DBMS_RLS.STATIC);
 17  END;
 18  /
PL/SQL procedure successfully completed.

Enabling the timing feature of SQL*Plus, you can see the benefit to using static caching. The first execution will invoke the policy, and the predicate will be stored in the Shared Global Area:

scott@KNOX10g> SELECT username, salary FROM people
  2   WHERE deptno = 20;
USERNAME      SALARY
---------- ---------
SCOTT           3000
Elapsed: 00:00:02.05

The two seconds were introduced by PRED_FUNCTION. Any subsequent execution by any user will use the cached policy. To illustrate this, rerun the query. Because the cached predicate is stored in the SGA, the caching can be used for all user sessions across all schemas:

scott@KNOX10g> SELECT username, salary FROM people
  2   WHERE deptno = 20;
USERNAME      SALARY
--------- ----------
SCOTT           3000
Elapsed: 00:00:00.01
scott@KNOX10g> -- Reconnect. Establish a different session for same user scott@KNOX10g> conn scott/tiger
Connected.
scott@KNOX10g> set timing on
scott@KNOX10g> SELECT username, salary FROM people
  2   WHERE deptno = 20;
USERNAME      SALARY
---------- ---------
SCOTT           3000
Elapsed: 00:00:00.01
scott@KNOX10g> -- Connect as a different user
scott@KNOX10g> conn system/manager Connected.
system@KNOX10g> SET timing on system@KNOX10g> SELECT username, salary FROM scott.people
scott@KNOX10g> -- Connect as a different user scott@KNOX10g> conn system/manager Connected.
system@KNOX10g> SET timing on system@KNOX10g> SELECT username, salary FROM scott.people
  2   WHERE deptno = 20;
no rows selected
Elapsed: 00:00:00.02

While the policy will be cached for all users, the returned records are not necessarily the same. In the previous example, the policy always returns the same predicate. When the SYSTEM user executes the query, the policy is cached and different results are returned because the SYS_CONTEXT function returns a different user identity.

Next: SHARED_STATIC Caching >>
 

More Oracle Articles
More By McGraw-Hill/Osborne

Comments On: Row-Level Security with Virtual Private Database

· This article is an excerpt from the book "Row-Level Security with Virtual Private...   >>> Post your comment now!

Buy this book...

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.