vpd實驗：驗證動態、靜態、上下context相關三種型別的策略的執行機制
vpd：
vpd的策略型別分為：
動態、靜態(共享和非共享)、上下context相關（共享和非共享）
vpd的策略型別分為：
動態、靜態(共享和非共享)、上下context相關（共享和非共享）
動態策略：每次ＳＱＬ進行解析時策略函式都會呼叫。
這對效能有一定的影響，靜態、上下context相關為Oracle10g推出新的策略型別：
這對效能有一定的影響，靜態、上下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:
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.
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;
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;
/
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
);
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;
/
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)
);
create table t_sheet(
fgsid number(3),
sheetcode varchar2(200)
);
insert into t_sheet
select '200', table_name from dba_tables where rownum<=10;
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;
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
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;
/
pk_action.act_type := 'DYNAMIC';
end;
/
SQL> select count(1) from t_sheet;
COUNT(1)
----------
30
----------
30
SQL> select count(1) from t_sheet;
COUNT(1)
----------
30
----------
30
SQL> begin
2 set_context('sheet_security','fgsid',200);
3 end;
4 /
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
----------
10
SQL> begin
2 set_context('sheet_security','fgsid',755);
3 end;
4 /
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
----------
20
SQL> /
COUNT(1)
----------
20
----------
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
-------------------------------------------------- -------------------
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,每次會有兩條記錄.
why,每次會有兩條記錄.
使用10046跟蹤看看,確實每次執行ＳＱＬ時策略函式執行了2次。
Parsing時執行一次，執行時會再次執行一次。
Parsing時執行一次，執行時會再次執行一次。
結論：對於DYNAMIC型別的策略，在執行ＳＱＬ時，ＳＱＬ中物件的策略函式會執行兩次。物件效能有影響
－－－－測試靜態策略－－－－－
SQL> col namespace format a30
SQL> col ATTRIBUTE format a30
SQL> col VALUE format a30
SQL> select * from session_context;
SQL> col ATTRIBUTE format a30
SQL> col VALUE format a30
SQL> select * from session_context;
NAMESPACE ATTRIBUTE VALUE
------------------------------ ------------------------------ ------------------------------
SHEET_SECURITY FGSID 755
------------------------------ ------------------------------ ------------------------------
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;
/
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';
----------
20
SQL> select * from t_log where act_type='STATIC';
ACT_TYPE ACT_TIME
-------------------------------------------------- -------------------
STATIC 2012-03-19 15:51:41 --一次
-------------------------------------------------- -------------------
STATIC 2012-03-19 15:51:41 --一次
SQL> select count(1) from t_sheet;
COUNT(1)
----------
20
----------
20
SQL> select * from t_log where act_type='STATIC';
ACT_TYPE ACT_TIME
-------------------------------------------------- -------------------
STATIC 2012-03-19 15:51:41 --策略函式沒有execute
-------------------------------------------------- -------------------
STATIC 2012-03-19 15:51:41 --策略函式沒有execute
修改fgsid為200
SQL> begin
2 set_context('sheet_security','fgsid',200);
3 end;
4 /
SQL> begin
2 set_context('sheet_security','fgsid',200);
3 end;
4 /
SQL> select * from session_context;
NAMESPACE ATTRIBUTE VALUE
------------------------------ ------------------------------ ------------------------------
SHEET_SECURITY FGSID 200
------------------------------ ------------------------------ ------------------------------
SHEET_SECURITY FGSID 200
SQL> select count(1) from t_sheet;
COUNT(1)
----------
20
注：結果不對，應該為10,說明策略函式沒有執行
再查查日誌：
SQL> select * from t_log where act_type='STATIC';
----------
20
注：結果不對，應該為10,說明策略函式沒有執行
再查查日誌：
SQL> select * from t_log where act_type='STATIC';
ACT_TYPE ACT_TIME
-------------------------------------------------- -------------------
STATIC 2012-03-19 15:51:41
-------------------------------------------------- -------------------
STATIC 2012-03-19 15:51:41
沒有增加
結論：對於dbms_rls.STATIC型別的策略，僅在第一次使用時觸發策略函式的執行。
另外：其他new session 連線，執行select count(1) from t_sheet也不會觸發策略的執行。
因為，該型別的PREDICATE是cache在SGA中的，為全域性共享。
故：效能不存在問題，但策略函式返回的PREDICATE一定要正確。
另外：其他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;
/
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
------------------------------ ------------------------------ ------------------------------
SHEET_SECURITY FGSID 200
SQL> select count(1) from t_sheet;
COUNT(1)
----------
10
SQL> select * from t_log where act_type='CONTEXT_SENSITIVE';
----------
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;
-------------------------------------------------- -------------------
CONTEXT_SENSITIVE 2012-03-19 16:28:43
SQL> select count(1) from t_sheet;
COUNT(1)
----------
10
----------
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:28:43
CONTEXT_SENSITIVE 2012-03-19 16:32:11
SQL> select count(1) from t_sheet;
COUNT(1)
----------
10
----------
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
-------------------------------------------------- -------------------
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 /
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
-------------------------------------------------- ---------
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.
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
-------------------------------------------------- ---------
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
-------------------------------------------------- ---------
CONTEXT_SENSITIVE 23-MAR-12
f執行了兩次，但策略函式執行了一次.
SQL> select * from session_context;
NAMESPACE
------------------------------------------------------------
ATTRIBUTE
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
SHEET_SECURITY
FGSID
200
------------------------------------------------------------
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
-------------------------------------------------- ---------
CONTEXT_SENSITIVE 23-MAR-12
SQL> begin
2 set_context('sheet_security','fgsid',755);
3 end;
4 /
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 23-MAR-12
CONTEXT_SENSITIVE 23-MAR-12
注意日誌增加了。
結論：CONTEXT_SENSITIVE型別的策略明顯比DYNAMIC的執行次數少，消耗效能少。
適合於一次parse，多次執行的ＳＱＬ，策略函式會在parsing時執行，在真正執行SQL時不再觸發策略函式；
但當parsing後，如果有dbms_session.set_context的呼叫修改了上下文環境，將會再次觸發策略函式的執行。
適合於一次parse，多次執行的ＳＱＬ，策略函式會在parsing時執行，在真正執行SQL時不再觸發策略函式；
但當parsing後，如果有dbms_session.set_context的呼叫修改了上下文環境，將會再次觸發策略函式的執行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/195110/viewspace-719607/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 登入的三種驗證機制Oracle
- web動態驗證碼的實現Web
- 實現動態驗證碼的思路
- java執行緒實現的三種方式以及靜態代理Java執行緒
- angular中關於表單動態驗證的一種新思路Angular
- 多執行緒的三種實現方式及靜態代理模式執行緒模式
- 靜態路由綜合實驗路由
- 偽靜態、靜態和動態的區別
- 淺談程式語言型別的強型別，弱型別，動態型別，靜態型別型別
- 實驗3.直連靜態路由實驗路由
- java實現動態驗證碼原始碼——繪製驗證碼的jspJava原始碼JS
- 理解 TypeScript 的靜態型別TypeScript型別
- JqueryValidate 動態新增驗證jQuery
- python傳送手機動態驗證碼Python
- 「資料分析」2種常見的反爬蟲策略，資訊驗證和動態反爬蟲爬蟲
- Python的靜態型別之旅Python型別
- React的靜態型別檢查React型別
- 不等號影響執行計劃的相關實驗
- django-驗證碼/靜態檔案處理Django
- 遊戲機制設計：動態難度平衡、心流體驗與相關設計中的問題遊戲
- 漏洞挖掘-靜態分析實驗筆記筆記
- 靜態SDRAM和動態SDRAM的區別
- 動態庫和靜態庫的區別
- 計算機網路實驗10：靜態路由協議計算機網路路由協議
- 預設及非預設埠的動態監聽/靜態監聽實驗彙總
- 多執行緒的執行緒狀態及相關操作執行緒
- 如何使用策略組來實現行級別的VPD
- Vue中的靜態型別檢查Vue型別
- 動態IP與靜態IP的主要區別
- ios靜態庫與動態庫的區別iOS
- 驗證代理IP的三種方法
- MySQL中資料型別的驗證MySql資料型別
- Oracle 靜態引數與動態引數型別介紹Oracle型別
- 配置rip動態路由實驗路由
- OMBA可以進行憑證型別相關的配置型別
- 動態試驗
- java執行原理、靜態代理和動態代理區分Java
- javascript 資料型態/結構驗證庫 : SkeletonsJavaScript