vpd实验：验证动态、静态、上下context相关三种类型的策略的运行机制

vpd：
vpd的策略类型分为：
　　动态、静态(共享和非共享)、上下context相关（共享和非共享）

动态策略：每次ＳＱＬ进行解析时策略函数都会调用。
这对性能有一定的影响，静态、上下context相关为Oracle10g推出新的策略类型：

Policy Types
The correct use of policy types can increase the performance of VPD by caching the output of the policy function and applying it to subsequent queries without executing the policy function again. The POLICY_TYPE parameter of the DBMS_RLS.ADD_POLICY procedure is used to set one of the five policy types:

STATIC - The return value of the policy function is cached and reused repeatedly for an individual object. By definition the return value of the policy function must be static.
SHARED_STATIC - The same as STATIC but the resulting predicate can be applied to several objects.
CONTEXT_SENSITIVE - Used when policy is based around local application context. The result of the policy function is cached and reused. The policy function is only executed again when the value of the application context changes.
SHARED_CONTEXT_SENSITIVE - The same as CONTEXT_SENSITIVE but the resulting predicate can be applied to several objects.
DYNAMIC - The policy function is executed for every SQL statement.

CONTEXT_SENSITIVE
Server re-evaluates the policy function at statement execution time if it detects context changes since the last use of the cursor.
For session pooling where multiple clients share a database session, the middle tier must reset context during client switches.
Note that the server does not cache the value returned by the function for this policy type; it always executes the policy function on statement parsing. Applies to only one object.

策略组：是一套策略的组合。当对于同一组对象（table,view）不同的application访问需要采取不同的策略时，
　　　　可以使用策略组来分类策略，并使用
　　　　　　   dbms_rls.add_policy_context(object_schema   IN VARCHAR2 := NULL,
                        object_name     IN VARCHAR2,
                        namespace       IN VARCHAR2,
                        attribute       IN VARCHAR2);
　　　　方法指定一个上下文属性来获取策略组的名称，并仅使用该策略组中的策略。

 

实验：以下的实验主要用来验证：动态、静态、上下context相关三种类型的策略的运行机制。

实验准备：

1、创建管理context的函数
create or replace procedure set_context(namespace varchar2,
                             attribute varchar2,
                             value     varchar2) as
begin
  dbms_session.set_context(namespace, attribute, value);
end;
/
create  or replace context sheet_security using set_context;

 

begin
  set_context(upper('sheet_security'),'name','denglt');
end; 
/

select * from session_context;

 
NAMESPACE                                                    ATTRIBUTE                                                    VALUE
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
SHEET_SECURITY                                               NAME                                                         denglt
 

2、创建日志表和全局变量，记录策略函数的调用
drop table t_log;
create table t_log (
   act_type varchar2(50),
   act_time date
);

create or replace package pk_action
is
  act_type varchar2(50);
end pk_action;
/

3、创建策略函数
create or replace function f_policies_sheet(pOwner       varchar2,
                                            pObject_name varchar2)
  return varchar2 as
  v_fgsid varchar2(30);
begin
  insert into t_log values (pk_action.act_type, sysdate);
  commit;
  select sys_context('sheet_security', 'fgsid') into v_fgsid from dual;
  if v_fgsid is null then
    return null;
  end if;
  return 'fgsid=' || v_fgsid;
　--return 'fgsid= sys_context(''sheet_security'', ''fgsid'') ';
end;
/

4、创建测试表和数据
create table t_sheet(
  fgsid number(3),
  sheetcode  varchar2(200)
);

insert into t_sheet
  select '200', table_name from dba_tables where rownum<=10;

insert into t_sheet
  select '755', object_name from dba_objects where rownum<=20; 
commit; 

 

--------首先测试动态策略---------------
begin
   dbms_rls.add_policy(object_schema => user,
                       object_name => 't_sheet',
                       policy_name => 'vpd_t_sheet',
                       function_schema =>user ,
                       policy_function =>'f_policies_sheet' ,
                       statement_types =>'select',
                       update_check => false,
                       enable => true,
                       static_policy => false,
                       policy_type => dbms_rls.DYNAMIC
                       );
end;
/ 

begin
  pk_action.act_type := 'DYNAMIC';
end;
/

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        30

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        30

SQL> begin
  2    set_context('sheet_security','fgsid',200);
  3  end; 
  4  /

PL/SQL procedure successfully completed.

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        10

SQL> begin
  2    set_context('sheet_security','fgsid',755);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        20

SQL> /

  COUNT(1)
----------
        20

QL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from t_log;

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- -------------------
DYNAMIC                                            2012-03-19 10:59:48
DYNAMIC                                            2012-03-19 10:59:48
DYNAMIC                                            2012-03-19 10:59:32
DYNAMIC                                            2012-03-19 10:59:32
DYNAMIC                                            2012-03-19 11:01:47
DYNAMIC                                            2012-03-19 11:01:47
DYNAMIC                                            2012-03-19 11:05:16
DYNAMIC                                            2012-03-19 11:05:16
DYNAMIC                                            2012-03-19 11:06:41
DYNAMIC                                            2012-03-19 11:06:42

10 rows selected.

10条记录。表查询了5次，应该是5条才对?????
why,每次会有两条记录.

使用10046跟踪看看,确实每次执行ＳＱＬ时策略函数执行了2次。
Parsing时执行一次，执行时会再次执行一次。

结论：对于DYNAMIC类型的策略，在执行ＳＱＬ时，ＳＱＬ中对象的策略函数会执行两次。对象性能有影响

－－－－测试静态策略－－－－－

SQL> col namespace format a30
SQL> col ATTRIBUTE format a30
SQL> col VALUE format a30
SQL> select * from session_context;

NAMESPACE                      ATTRIBUTE                      VALUE
------------------------------ ------------------------------ ------------------------------
SHEET_SECURITY                 FGSID                          755

begin
  dbms_rls.drop_policy(object_schema => user,
                       object_name   => 't_sheet',
                       policy_name   => 'vpd_t_sheet');
end;
/
               
begin
   dbms_rls.add_policy(object_schema => user,
                       object_name => 't_sheet',
                       policy_name => 'vpd_t_sheet',
                       function_schema =>user ,
                       policy_function =>'f_policies_sheet' ,
                       statement_types =>'select',
                       update_check => false,
                       enable => true,
                       static_policy => false,
                       policy_type => dbms_rls.STATIC
                       );
end;
/       

begin
  pk_action.act_type := 'STATIC';
end;
/
          

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        20
SQL> select * from t_log where act_type='STATIC';

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- -------------------
STATIC                                             2012-03-19 15:51:41    --一次

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        20

SQL> select * from t_log where act_type='STATIC';

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- -------------------
STATIC                                             2012-03-19 15:51:41    --策略函数没有execute

修改fgsid为200
                  
SQL> begin
  2    set_context('sheet_security','fgsid',200);
  3  end; 
  4  /         

SQL> select * from session_context;

NAMESPACE                      ATTRIBUTE                      VALUE
------------------------------ ------------------------------ ------------------------------
SHEET_SECURITY                 FGSID                          200

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        20  
       
注：结果不对，应该为10,说明策略函数没有执行
再查查日志：
SQL> select * from t_log where act_type='STATIC';

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- -------------------
STATIC                                             2012-03-19 15:51:41　　　

没有增加

结论：对于dbms_rls.STATIC类型的策略，仅在第一次使用时触发策略函数的执行。
另外：其他new session 连接，执行select count(1) from t_sheet也不会触发策略的执行。
　　　因为，该类型的PREDICATE是cache在SGA中的，为全局共享。
　　　故：性能不存在问题，但策略函数返回的PREDICATE一定要正确。

－－－测试CONTEXT_SENSITIVE类型的策略函数-------

begin
  dbms_rls.drop_policy(object_schema => user,
                       object_name   => 't_sheet',
                       policy_name   => 'vpd_t_sheet');
end;
/
               
begin
   dbms_rls.add_policy(object_schema => user,
                       object_name => 't_sheet',
                       policy_name => 'vpd_t_sheet',
                       function_schema =>user ,
                       policy_function =>'f_policies_sheet' ,
                       statement_types =>'select',
                       update_check => false,
                       enable => true,
                       static_policy => false,
                       policy_type => dbms_rls.CONTEXT_SENSITIVE
                       );
end;
/       

begin
  pk_action.act_type := 'CONTEXT_SENSITIVE';
end;
/
   
begin
  set_context('sheet_security', 'fgsid', 200);
end;
/   
 
SQL> select * from session_context;

NAMESPACE                      ATTRIBUTE                      VALUE
------------------------------ ------------------------------ ------------------------------
SHEET_SECURITY                 FGSID                          200

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        10 
SQL> select * from t_log where act_type='CONTEXT_SENSITIVE';

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- -------------------
CONTEXT_SENSITIVE                                  2012-03-19 16:28:43
       
SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        10

SQL> select * from t_log where act_type='CONTEXT_SENSITIVE';

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- -------------------
CONTEXT_SENSITIVE                                  2012-03-19 16:28:43
CONTEXT_SENSITIVE                                  2012-03-19 16:32:11

SQL> select count(1) from t_sheet;

  COUNT(1)
----------
        10

SQL> select * from t_log where act_type='CONTEXT_SENSITIVE';

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- -------------------
CONTEXT_SENSITIVE                                  2012-03-19 16:28:43
CONTEXT_SENSITIVE                                  2012-03-19 16:32:11
CONTEXT_SENSITIVE                                  2012-03-19 16:32:38

why,怎么每次都运行了策略函数呢?    

google:有老外提到　static sql ,dynamic sql

SQL> delete t_log;

12 rows deleted.

SQL> commit;

Commit complete.

SQL> declare
  2    i number;
  3  begin
  4    for t in 1..10 loop
  5      select count(1) into i from t_sheet;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from t_log;

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- ---------
CONTEXT_SENSITIVE                                  23-MAR-12

Y：只有一条记录。 

SQL> declare
  2    i number;
  3  begin
  4    select count(1) into i from t_sheet;
  select count(1) into i from t_sheet;
  select count(1) into i from t_sheet;
end;
/
 
PL/SQL procedure successfully completed.

SQL> select * from t_log;

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- ---------
CONTEXT_SENSITIVE                                  23-MAR-12
CONTEXT_SENSITIVE                                  23-MAR-12
CONTEXT_SENSITIVE                                  23-MAR-12

SQL> create or replace procedure f as
  2    i number;
  3  begin
  4    for t in 1..10 loop
  5      select count(1) into i from t_sheet;
  6    end loop;
  7  end;
  8  /

Procedure created.

SQL> delete t_log;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> exec f;

PL/SQL procedure successfully completed.

SQL> exec f; 

PL/SQL procedure successfully completed.

SQL> select * from t_log;

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- ---------
CONTEXT_SENSITIVE                                  23-MAR-12

f执行了两次，但策略函数执行了一次.

SQL> select * from session_context;

NAMESPACE
------------------------------------------------------------
ATTRIBUTE
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
SHEET_SECURITY
FGSID
200

SQL> exec f;

PL/SQL procedure successfully completed.

SQL> select * from t_log;

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- ---------
CONTEXT_SENSITIVE                                  23-MAR-12

SQL> begin
  2  set_context('sheet_security','fgsid',755);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> exec f;

PL/SQL procedure successfully completed.

SQL> select * from t_log;

ACT_TYPE                                           ACT_TIME
-------------------------------------------------- ---------
CONTEXT_SENSITIVE                                  23-MAR-12
CONTEXT_SENSITIVE                                  23-MAR-12

注意日志增加了。

 

结论：CONTEXT_SENSITIVE类型的策略明显比DYNAMIC的执行次数少，消耗性能少。
　　　适合于一次parse，多次执行的ＳＱＬ，策略函数会在parsing时执行，在真正执行SQL时不再触发策略函数；
　　　但当parsing后，如果有dbms_session.set_context的调用修改了上下文环境，将会再次触发策略函数的执行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-719607/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-719607/