利用Oracle VPD實現行級安全保護（一）

http://boylook.itpub.net/post/43144/520544

大家都知道，Oracle的鎖機制是行層級的，下面來看看Oracle的安全訪問機制：
安全的資料過濾，必須在基礎資料表的一層就完成，這樣使用者無論是通過視圖還是基礎資料表都無法繞過安全控制。而VPDvirtual private database）最根本的能力就是“會透明的過濾資料”，提供行級安全保護。

Oracle8i以後的版本都提供了VPD這樣一個強大的功能來實現呼聲日益增高的系統安全性要求。通過設定基於VPD的細粒度存取原則，我們可以只通過DBA的工作不需要修改應用，也就是應用透明化）就可以實現使用者只能訪問自己有許可權訪問的資料，當然如果需要更加複雜的許可權控制開發人員的參與還是必不可少的。

下面用一個簡單的例子來實現這樣的功能，在EMP表中的使用者登入資料庫以後只能查詢和更新自己所屬部門的其它員工資料，不是本部門的不會顯示也不允許更新。
————————————————————————————————————————————————————
1.我們需要兩個使用者，一個是用於設定VPD策略的hr使用者，另外一個是在hr.employees表中有記錄的David使用者
SQL> create user David identified by boylook;

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;

本文出自 “MIKE老畢的部落格” 部落格，請務必保留此出處http://boylook.blog.51cto.com/7934327/1298619