Row-Level Security with Virtual Private Database - SHARED_STATIC Caching
(Page 9 of 10 )
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
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.
Next: Caching Caution >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 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.
|
|
