vpd實驗:驗證動態、靜態、上下context相關三種型別的策略的執行機制

denglt發表於2012-03-27
vpd:
vpd的策略型別分為:
  動態、靜態(共享和非共享)、上下context相關(共享和非共享)
動態策略:每次SQL進行解析時策略函式都會呼叫。
這對效能有一定的影響,靜態、上下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跟蹤看看,確實每次執行SQL時策略函式執行了2次。
Parsing時執行一次,執行時會再次執行一次。
結論:對於DYNAMIC型別的策略,在執行SQL時,SQL中物件的策略函式會執行兩次。物件效能有影響
----測試靜態策略-----
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,多次執行的SQL,策略函式會在parsing時執行,在真正執行SQL時不再觸發策略函式;
   但當parsing後,如果有dbms_session.set_context的呼叫修改了上下文環境,將會再次觸發策略函式的執行。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/195110/viewspace-719607/,如需轉載,請註明出處,否則將追究法律責任。

相關文章