利用Oracle VPD实现行级安全保护（一）

zhangxinba

　　http://boylook.itpub.net/post/43144/520544
　　大家都知道，Oracle的锁机制是行级别的，下面来看看Oracle的安全访问机制：
　　安全的数据过滤，必须在基础数据表的一层就完成，这样用户无论是通过视图还是基础表都无法绕过安全控制。而VPD（virtual private database）最根本的能力就是“会透明的过滤数据”，提供行级安全保护。
　　Oracle8i以后的版本都提供了VPD这样一个强大的功能来实现呼声日益增高的系统安全性要求。通过设置基于VPD的细粒度访问策略，我们可以只通过DBA的工作（不需要修改应用，也就是应用透明化）就可以实现用户只能访问自己有权限访问的数据，当然如果需要更加复杂的权限控制开发人员的参与还是必不可少的。
　　下面用一个简单的例子来实现这样的功能，在EMP表中的用户登录数据库以后只能查询和更新自己所属部门的其它员工资料，不是本部门的不会显示也不允许更新。
　　————————————————————————————————————————————————————
　　1.我们需要两个用户，一个是用于设置VPD策略的hr用户，另外一个是在hr.employees表中有记录的David用户

　　SQL> create user David>　　User created.
　　SQL> grant create session to david;
　　Grant succeeded.
　　SQL> conn hr
　　Enter password:
　　Connected.
　　2.首先用hr用户创建策略权限表。为方便起见我们直接通过employees表创建，本策略表中包含了员工姓名和所属部门编号。其中David用户同时属于60和80这两个部门。
　　SQL> create table rls_test as select * from employees;
　　Table created.
　　SQL> select department_id from employees where first_name = 'David';
　　DEPARTMENT_ID
　　-------------
　　60
　　80
　　80
　　3.hr用户创建VPD策略需要的函数。
　　SQL> @create_vpd_func（见文章结尾）
　　Function created.
　　该函数实现以下功能：
　　如果使用hr用户登录，因为表是属于该用户的，所以不加任何限制。
　　如果使用其它用户登录（SYS用户不受此限制），那么根据employees表中该用户的所属部门决定哪些记录允许该用户操作，本例中60和80这两个部门的员工David用户将都能看见。
　　如果登录的用户不在employees表中，那么该用户查看不到任何数据。
　　注意：
　　VPD策略函数必须包含两个参数，本例中是p_schema和p_table，即使这两个参数在函数中没有用到，也必须包含。否则在后面检索EMP表数据的时候将会报：
　　PLS-00306: 调用 'GET_USER_DEPT_ID' 时参数个数或类型错误。
　　4.用hr用户创建VPD策略。
　　SQL> conn / as sysdba
　　Connected.
　　SQL> grant execute on dbms_rls to hr
　　2 ;
　　Grant succeeded.
　　SQL> conn hr
　　Enter password:
　　Connected.
　　SQL> @test_add_policy;
　　10 /
　　PL/SQL procedure successfully completed.
　　5.至此为止我们的VPD方案就已经设置完毕了。下面我们测试一下--hr用户可以选择出全部的107条记录
　　SQL> select count(*) from employees;
　　COUNT(*)
　　----------
　　107
　　SQL> conn david
　　Enter password:
　　Connected.
　　--David用户只能选出属于部门60，80的39条记录SQL> select count(*) from hr.employees;
　　COUNT(*)
　　----------
　　39
　　附：
　　cat create_vpd_func.sql
　　create or replace function test_vpd(p_schema in varchar2,p_object in varchar2)
　　return varchar2
　　as
　　l_retstr    varchar2(2000);
　　type dept_id_type is table of employees.department_id%type;
　　dept_id_tab   dept_id_type;
　　temp_v varchar2(2000);
　　begin
　　if sys_context('userenv','current_user') = user then
　　return '';
　　end if;
　　select department_id
　　bulk collect into dept_id_tab
　　from rls_test
　　where upper(first_name) = sys_context('userenv','session_user');
　　if dept_id_tab.count = 0 then
　　l_retstr := '1=2';
　　else
　　for i in dept_id_tab.first..dept_id_tab.last
　　loop
　　temp_v := temp_v||','||dept_id_tab(i);
　　end loop;
　　l_retstr := 'department_id in('||ltrim(temp_v,',')||')';
　　end if;
　　return l_retstr;
　　end;
　　/
　　cat test_add_policy.sql
　　declare
　　begin
　　dbms_rls.add_policy(
　　object_schema =>'HR',
　　object_name =>'EMPLOYEES',
　　policy_name =>'HIDE_EMP',
　　function_schema =>'HR',
　　policy_function =>'TEST_VPD');
　　end;