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

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.

Code Location

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.

Policy Caching

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 10 ^g, 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.

STATIC Caching Example

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

SHARED_STATIC Caching

The next cache example uses the SHARED_STATIC setting, which allows the predicate to be cached across multiple objects. This is practical because it’s likely that you will be using the same policy function for object references in the same application. This example requires the use of the EMP and DEPT tables and assumes the security policy governing access to these tables is identical. Before you can add the policy, it’s important to ensure all other policies have been dropped. The following script will drop all policies governing select statements on the EMP and DEPT tables in the SCOTT schema:

-- disable all select policies for EMP and DEPT DECLARE   l_str  VARCHAR2 (100); BEGIN   FOR rec IN (SELECT *                 FROM dba_policies                WHERE object_owner = 'SCOTT'                  AND object_name IN                         ('EMP', 'DEPT')                  AND sel = 'YES')   LOOP     l_str :=          'begin DBMS_RLS.drop_policy(''SCOTT'','''       || rec.object_name       || ''','''       || rec.policy_name       || '''); end;';     DBMS_OUTPUT.put_line (l_str);     EXECUTE IMMEDIATE l_str;   END LOOP; END; /

To cache across objects, the objects have to be sharing the same policy function. This example will use a function in the security manager schema that again sleeps for two seconds. Because the policy predicate used in the previous example referenced the ENAME column, which is not a column in the DEPT table, the policy function has to be modified to prevent an error. Because in this example you are only interested in cache performance, the policy function will sleep for two seconds and then simply return null:

sec_mgr@KNOX10g> -- VPD function, injects 2 second delay sec_mgr@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  BEGIN   7    DBMS_LOCK.sleep (2);   8    -- return all records   9    RETURN NULL;  10  END;  11  / Function created.

This predicate function is then applied to both the EMP and DEPT tables:

sec_mgr@KNOX10g> BEGIN   2    -- Add policy on EMP table   3    DBMS_RLS.add_policy   4             (object_schema      => 'SCOTT',   5              object_name        => 'EMP',   6              policy_name        => 'EMP_SEL_CACHE',   7              function_schema    => 'SEC_MGR',   8              policy_function    => 'PRED_FUNCTION',   9              statement_types    => 'SELECT',  10              policy_type        => DBMS_RLS.shared_static);  11    -- Add policy on DEPT table  12    DBMS_RLS.add_policy  13             (object_schema      => 'SCOTT',  14              object_name        => 'DEPT',  15              policy_name        => 'DEPT_SEL_CACHE',  16              function_schema    => 'SEC_MGR',  17              policy_function    => 'PRED_FUNCTION',  18              statement_types    => 'SELECT',  19              policy_type        => DBMS_RLS.shared_static);  20  END;  21  / PL/SQL procedure successfully completed.

Connect as SCOTT and query either of the tables. Notice the first query will cache the predicate for both tables:

scott@KNOX10g> SET timing on scott@KNOX10g> -- Query one of the tables scott@KNOX10g> SELECT COUNT (*) FROM emp;   COUNT(*) ----------         14 Elapsed: 00:00:02.34 scott@KNOX10g> -- run again to see if predicate is cached   scott@KNOX10g> /     COUNT(*) ----------           14     Elapsed: 00:00:00.01 scott@KNOX10g> -- Query a different table that uses same policy function   scott@KNOX10g> -- The database should use the cached predicate   scott@KNOX10g> SELECT COUNT (*) FROM dept;   COUNT(*) ----------            4     Elapsed: 00:00:00.01 

The behavior with this is consistent with the STATIC example in that the caching exists across sessions and schemas:

scott@KNOX10g> -- Query as a different session   scott@KNOX10g> -- Cache is shared across sessions   scott@KNOX10g> conn system/manager   Connected. system@KNOX10g> SELECT COUNT (*) FROM scott.dept;   COUNT(*) ----------            4     Elapsed: 00:00:00.01 

The benefit gained from this caching technique is directly proportional to the number of tables sharing the policy function.

SHARED_CONTEXT_SENSITIVE

The final caching example uses the SHARED_CONTEXT_SENSITIVE setting. This will allow caching for the user’s session up until a user-defined application context is changed. To begin, first drop the current policies by running the script given in the beginning of the previous example. The output is shown here:

begin DBMS_RLS.drop_policy('SCOTT','DEPT','DEPT_SEL_CACHE'); end; begin DBMS_RLS.drop_policy('SCOTT','EMP','EMP_SEL_CACHE'); end;

Next, add the RLS policy to EMP and DEPT, changing the policy type to SHARED_CONTEXT_SENSITIVE:

sec_mgr@KNOX10g> -- add shared_context_sensitive policies sec_mgr@KNOX10g> BEGIN   2    -- Add policy on EMP table   3    DBMS_RLS.add_policy   4             (object_schema      => 'SCOTT',   5              object_name        => 'EMP',   6              policy_name        => 'EMP_SEL_CACHE',   7              function_schema    => 'SEC_MGR',   8              policy_function    => 'PRED_FUNCTION',   9              statement_types    => 'SELECT',  10              policy_type        => DBMS_RLS.shared_context_sensitive);  11    -- Add policy on DEPT table  12    DBMS_RLS.add_policy  13             (object_schema      => 'SCOTT',  14              object_name        => 'DEPT',  15              policy_name        => 'DEPT_SEL_CACHE',  16              function_schema    => 'SEC_MGR',  17              policy_function    => 'PRED_FUNCTION',  18              statement_types    => 'SELECT',  19              policy_type        => DBMS_RLS.shared_context_sensitive);  20  END;  21  / PL/SQL procedure successfully completed.

The database will cache the policy until a user-defined application context is manipulated. It doesn’t have to be a context that has any direct relationship to the tables the policy protects. Therefore, the application context defined earlier will be adopted for this example by the addition of a new SET_DEPTNO procedure. The new package specification and new procedure code are listed:

sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE people_ctx_mgr   2  AS   3    PROCEDURE set_deptno;   4    PROCEDURE set_deptno(p_deptno in number);   5    PROCEDURE clear_deptno;   6  END;   7  / Package created.

The following code is for the new procedure only:

 21    PROCEDURE set_deptno (p_deptno IN NUMBER)  22    AS  23    BEGIN  24      DBMS_SESSION.set_context  25                        (namespace    => 'people_ctx',  26                         ATTRIBUTE    => 'deptno',  27                         VALUE        => p_deptno);  28    END set_deptno;   29

Execute privileges are granted to SCOTT to allow him to directly manipulate the application context. This is done to illustrate how the caching works, and you wouldn’t grant privileges to execute on this namespace manager to users if the context values it set were to be used for security purposes:

sec_mgr@KNOX10g> GRANT EXECUTE ON people_ctx_mgr TO scott; Grant succeeded.

You can see from the following test results that this type of caching is indeed functionally different from the previous two. The test first queries the EMP table, which causes the predicate to be cached for the EMP and DEPT tables.

scott@KNOX10g> SET timing on   scott@KNOX10g> -- Query one of the tables. Predicate is not yet cached   scott@KNOX10g> SELECT COUNT (*) FROM emp;   COUNT(*) ----------           14     Elapsed: 00:00:02.05 scott@KNOX10g> -- Query a different table that uses same policy function   scott@KNOX10g> -- The database should use the cached predicate   scott@KNOX10g> SELECT COUNT (*) FROM dept;   COUNT(*) ----------           4     Elapsed: 00:00:00.01 

You might think the Client Identifier, which is a type of application context, could be used to trigger a switch in the caching, but it cannot. This is important because you may be relying only on the Client Identifier switch when using a shared schema application pool. If you are, consider securing it using the technique described in Chapter 6.

scott@KNOX10g> -- note that changing the Client Identifier has no   scott@KNOX10g> -- effect on cache   scott@KNOX10g> EXEC dbms_session.set_identifier('Some Value'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00   scott@KNOX10g> -- Predicate is still cached. scott@KNOX10g> SELECT COUNT (*) FROM emp;   COUNT(*) ----------           14     Elapsed: 00:00:00.01 

To invalidate the cache, change the value in a user-defined application context. The new SET_DEPTNO procedure does this:

scott@KNOX10g> -- Execute the namespace manager procedure. scott@KNOX10g> -- This changes a context value which tells the database    scott@KNOX10g> -- to invalidate the cached predicate. scott@KNOX10g> EXEC sec_mgr.people_ctx_mgr.set_deptno(10); PL/SQL procedure successfully completed. Elapsed: 00:00:00.01  scott@KNOX10g> SELECT COUNT (*) FROM emp;   COUNT(*) ----------           14     Elapsed: 00:00:02.05 

A difference between this and the previous example is that the caching is session specific. This is because the cache invalidation is based on (local) application contexts, which are always going to be different across sessions. Reconnect to the database and query and you will see the cache is automatically invalidated:

scott@KNOX10g> -- Query using a different session. scott@KNOX10g> -- Cache is not shared across sessions   scott@KNOX10g> conn scott/tiger   Connected. scott@KNOX10g> SELECT COUNT (*) FROM scott.dept;   COUNT(*) ----------            4     Elapsed: 00:00:02.05 

The policy caching capabilities can increase performance by bypassing the policy invocation. However, there are some situations in which this is not the solution to use.

Caching Caution

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