 Oracle Page 9 - Row-Level Security with Virtual Private Database |
SHARED_STATIC Caching - OracleOracle'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).
print this article SEARCH DEV SHED |
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 := 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 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 This predicate function is then applied to both the EMP and DEPT tables: sec_mgr@KNOX10g> BEGIN 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 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 The benefit gained from this caching technique is directly proportional to the number of tables sharing the policy function. SHARED_CONTEXT_SENSITIVEThe 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 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 Package created. The following code is for the new procedure only: 21 PROCEDURE set_deptno (p_deptno IN 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; 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 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 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. 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. 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.
blog comments powered by Disqus |
|
|
|
|
|
|
|
