沉寂 燃烧

需求

客户一个安全性较高的应用，有如下需求：
●在HR用户下有一张name_all表，包含id及name两个列；
●有其他三个用户，分别为zibo、jining、qingdao，每个用户会在自己的schema下创建和hr.name_all同样结构的表name_bd，并插入数据；
●不允许三个用户直接查询hr.name_all全表；
●三个用户可以用各自的name_bd与hr.name比对，查询在hr.name_all和各自name_bd中共有的数据，并返回结果；

分析
综合客户需求，其实需要当每一个用户登录时，对hr.name_all的sql语句转换为如下即可：
select * from hr.name_all where name in (select name from name_bd);
利用oracle vpd完全可以满足需求

vpd实现
3.1、创建示例表
SQL> create user hr identified by hr;
SQL> grant connect,resource to hr;
SQL> create table hr.name_all(id int,name varchar(24));
SQL> insert into hr.name_all(id,name) values (1,'zhangsan');
SQL> insert into hr.name_all(id,name) values(2,'lisi');
SQL> insert into hr.name_all(id,name) values(3,'wangwu');
SQL> commit;

SQL> create user zibo identified by zibo;
SQL> create user jining identified by jining;
SQL> create user qingdao identified by qingdao;
SQL> grant connect,resource to zibo,jining,qingdao;

SQL> create table zibo.name_bd(id int,name varchar(24));
SQL> create table jining.name_bd(id int,name varchar(24));
SQL> create table qingdao.name_bd(id int,name varchar(24));

SQL> insert into zibo.name_bd(id,name) values (1,'zhangsan');
SQL> insert into zibo.name_bd(id,name) values (2,'zhaoliu');

SQL> insert into jining.name_bd(id,name) values(1,'lisi');
SQL> insert into jining.name_bd(id,name) values(2,'sujiu');

SQL> insert into qingdao.name_bd(id,name) values(1,'wangwu');
SQL> insert into qingdao.name_bd(id,name) values(2,'zhoushi');

3.2、授权
SQL> grant select on hr.name_all to zibo,jining,qingdao;
SQL> grant select on zibo.name_bd to hr;
SQL> grant select on jining.name_bd to hr;
SQL> grant select on qingdao.name_bd to hr;
SQL> grant execute on dbms_rls to hr,zibo,jining,qingdao;

3.3 创建vpd函数
创建函数，函数的目的只是为了生成where字句

SQL> CREATE OR REPLACE FUNCTION hr.fc_vpd_test (s_schema IN VARCHAR2, s_object IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
if (sys_context('USERENV','SESSION_USER') ='ZIBO') then
RETURN 'name in (select name from zibo.name_bd)';
elsif (sys_context('USERENV','SESSION_USER') ='JINING') then
RETURN 'name in (select name from jining.name_bd)';
elsif (sys_context('USERENV','SESSION_USER') ='QINGDAO') then
RETURN 'name in (select name from qingdao.name_bd)';
else
return '1=1';
end if;
END;
/

SQL> grant execute on hr.fc_vpd_test to public;

3.4、将函数与需要保护的表进行关联
SQL> Begin
Dbms_Rls.Add_Policy(
Object_Schema =>'HR',
Object_Name =>'NAME',
Policy_Name =>'POLICY_LIMITED_QUERY_T',
Function_Schema =>'HR',
Policy_Function =>'FC_VPD_TEST',
Enable =>True
);
end;
/

3.5 测试
当用三个用户分别查询hr.name_all时，应该只能查询到和本表的name列重合的字段。

3.5.1 当三个用户name_bd表中存在数据时：
SQL> conn zibo/zibo
SQL> Select * from hr.name_all;
ID NAME
---------- ------------------------
1 zhangsan

SQL> Conn jining/jining
SQL> Select * from hr.name_all;
ID NAME
---------- ------------------------
2 lisi

SQL> Conn qingdao/qingdao
SQL> Select * from hr.name_all;
ID NAME
---------- ------------------------
3 wangwu

SQL> Conn hr/hr
SQL> Select * from hr.name_all;
ID NAME
---------- ------------------------
1 zhangsan
2 lisi
3 wangwu
结果表明，当三个用户访问hr.name_all时，只返回name_bd中name列存在的数值，其他用户访问不受影响。
3.5.2 当三个用户name_bd表中不存在数据时：
SQL> conn qingdao/qingdao
Connected.
SQL> delete from name_bd;
SQL> commit;
SQL> select * from hr.name_all;
no rows selected
说明当name_bd中不存在数据时，查询hr.name_all不会返回值。
 

以上即是一个最简单的vpd实现。

This entry was posted on 2012-1-13 9:45:45 and is filed under hxre. You can follow any responses. Tags:oracle vpd