程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle handbook系列之虛擬專用數據庫VPD的使用詳解

Oracle handbook系列之虛擬專用數據庫VPD的使用詳解

編輯：Oracle數據庫基礎

Oracle handbook系列之虛擬專用數據庫VPD的使用是本文我們主要要介紹的內容，VPD，Oracle Virtual Private Database，即Oracle虛擬專用數據庫，是指通過應用一些策略，使得用戶只能訪問被允許訪問的那部分數據。其原理相對簡單，Oracle根據策略自動為相應用戶提交的語句添加Where句，從而控制用戶可以訪問和操作的數據。

首先我們准備演示用的幾張表，並插入少量的測試數據：

CREATETABLEvpdsample_clothing(  
clothing_idNUMBER,  
typeVARCHAR2(30),  
brandVARCHAR2(30),  
descriptonVARCHAR2(100)  
);  
--  
INSERTINTOvpdsample_clothingVALUES(10002,'jacket','ABC','autumnstyle');  
INSERTINTOvpdsample_clothingVALUES(10003,'t-shirt','XYZ','summerstyle');  
commit;  
CREATETABLEvpdsample_books(  
book_idNUMBER,  
nameVARCHAR2(30),  
authorVARCHAR2(20)  
);  
--  
INSERTINTOvpdsample_booksVALUES(10005,'CountryDriving','PeterHessler');  
INSERTINTOvpdsample_booksVALUES(10006,'Lifewithoutlimits','NickVujicic');  
commit;

（以上兩個表模擬一個簡單的庫存情況，庫中有兩類物品，服裝、圖書。這裡我們需要滿足兩個表中的ID的唯一性（可以通過sequence來實現）。）

CREATETABLEvpdsample_users(  
user_nameVARCHAR2(20),  
user_privilegeNUMBER  
);  
--  
INSERTINTOvpdsample_usersVALUES('Jack',1);  
INSERTINTOvpdsample_usersVALUES('Rose',2);  
COMMIT;

（這個表存儲用戶的權限信息，其中的權限即後表vpdsample_privileges中的權限ID字段。）

（這個表存儲每個權限ID對應的權限信息，即對哪些對象（服裝或圖書）有權限。）

第二步，我們要創建一個context（實際上是【context名稱空間】）。可以簡單地把context理解為一個定義在內存中的容器，在此容器中我們可以定義若干個鍵值對，這些鍵值對可以在一定的范圍內被共享（比如同一個session中，或者同一個Oracle實例中）

首先，使用system用戶登錄，賦予創建者相應的權限：grant create any context to user1;然後通過：CREATE OR REPLACE CONTEXT VPD USING pkg_vpdsample AccessED GLOBALLY;這裡我們創建了一個叫’vpd’的context，’using’後面的是一個PLSQL package的名字，出於安全性考慮，Oracle需要你在創建context時指定一個包名，表示後續對些context的修改只能通過此包中的存儲過程進行修改，不能通過dbms_session.set_context()直接進行修改。創建context時，package不存在並不會導致編譯錯誤。

最後’Accessed globally’是一個可選項，如果未添加此項，表示此context使用范圍是某一session；如果指定了此項，則表示該context可以在整個數據庫實例范圍內共享。

欲刪除context同樣需要賦予相應的權限：

grant drop any context to user1;  
drop context VPD;

第三步，建立一個package（即上面的pkg_vpdsample），包中的各個函數及存儲過程的作用會隨後逐一給出：

CREATEORREPLACEPACKAGEpkg_vpdsample  
IS  
PROCEDUREenable_vpd;  
PROCEDUREdisable_vpd;  
PROCEDUREset_context(p_user_nameINVARCHAR2);  
FUNCTIONgen_vpd_predicate(p_column_nameINVARCHAR2)RETURNVARCHAR2;  
FUNCTIONapply_vpd_clothing(p1invarchar2,p2invarchar2)RETURNVARCHAR2;  
FUNCTIONapply_vpd_books(p1invarchar2,p2invarchar2)RETURNVARCHAR2;  
END;  
 
CREATEORREPLACEPACKAGEBODYpkg_vpdsampleIS  
PROCEDUREenable_vpdIS  
BEGIN  
DBMS_SESSION.set_context(namespace=>'VPD',  
attribute=>'ENABLE',  
value=>'1');  
END;  
 
/*======================*/  
PROCEDUREdisable_vpdIS  
BEGIN  
DBMS_SESSION.set_context(namespace=>'VPD',  
attribute=>'ENABLE',  
value=>'0');  
END;  
 
/*======================*/  
PROCEDUREset_context(p_user_nameINVARCHAR2)IS  
l_privilegeVARCHAR2(10);  
BEGIN  
SELECTuser_privilege  
INTOl_privilege  
FROMvpdsample_users  
WHEREuser_name=p_user_name;  
DBMS_SESSION.set_identifIEr(clIEnt_id=>l_privilege);  
END;  
 
/*======================*/  
FUNCTIONgen_vpd_predicate(p_column_nameINVARCHAR2)RETURNVARCHAR2IS  
l_vpd_flagVARCHAR2(1);  
l_privilegeVARCHAR2(10);  
BEGIN  
l_vpd_flag:=NVL(SYS_CONTEXT('VPD','ENABLE'),'0');  
IFl_vpd_flag=0THEN 
RETURNNULL;  
ELSE  
l_privilege:=SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER');  
IFl_privilegeISNULLTHEN  
RETURN'1=2';  
ELSE  
RETURNp_column_name||'IN(SELECTobject_idFROMvpdsample_privilegesWHEREprivilege_id='||l_privilege||')';  
ENDIF;  
ENDIF;  
END;  
 
/*======================*/  
FUNCTIONapply_vpd_clothing(p1invarchar2,p2invarchar2)RETURNVARCHAR2IS  
BEGIN  
RETURNgen_vpd_predicate('clothing_id');  
END;  
 
/*======================*/  
FUNCTIONapply_vpd_books(p1invarchar2,p2invarchar2)RETURNVARCHAR2IS  
BEGIN  
RETURNgen_vpd_predicate('book_id');  
END;  
END;

enable_vpd,disable_vpd：這兩個存儲過程用於設置context vpd下的一個自定義屬性’enable’，1表是啟用vpd，0表禁用vpd，由於我們在創建此context時指定了Accessed globally，所以這些屬性是可以被跨session訪問的。這兩個存儲過程調用了dbms_session，因此需要被賦予相應的權限：grant execute on dbms_session to user1;

set_context：上面我們提到了context，並且在enable_vpd與disable_vpd中使用了dbms_session.set_context來設置自定義的屬性，其實除了這樣自定義的context外，Oracle還提供了預定義的context ’userenv’，該名稱空間下有若干預定義的屬性，比如’client_identifier’，設置此屬性我們不使用set_context，而是使用dbms_session.set_identifIEr()。

本例中此存儲過程根據傳入的用戶名，查找到該用戶的權限ID，並將此ID作為client_identifIEr保存到context ’userenv’中，以便後續使用。在實際應用中，此存儲過程應該是由外部的應用程序調用的，應用程序可以在登錄驗證完成後，調用此存儲過程寫入context。

gen_vpd_predicate：根據傳入的字段名稱生成一個語法正確的where子句。這裡首先判斷了是否啟用了vpd，未設置vpd.enable屬性的也認為的已經啟用了。隨後判斷是否已設置userenv.client_identifier屬性，如果未設置，則返回一個始終為false的where子句以防止用戶查看數據。最後生成的語句中，根據取到的client_identifIEr（即用戶的權限ID）查找權限表vpdsample_privileges得到該用戶有權限的所有對象ID。

apply_vpd_clothing, apply_vpd_books：由名字可見，這兩個函數將被用於兩個不同的表，因為服裝表與圖書表所用於權限驗證的字段有著不同的名字。另外，大家可以看到這兩個將被用於vpd的函數都有兩個看似沒用的參數p1與p2，這是vpd接口的要求，第一個用於接收schema名，第二個用於接收table/vIEw/synonym名稱，我們定義函數必須符合接口的要求。當然除了p1,p2外，你可以有自己額外的參數。

一切准備完畢，開始調用Oracle提供的dbms_rls包應用vpd策略，在調用之前，需要賦予用戶相應權限：grant execute on dbms_rls to user1;

其中policy_name可以自定義。

隨後我們啟用vpd：（需要說明的是，DBMS_RLS包本身有ENABLE_POLICY()方法用於啟用或禁用一個vpd策略，但它只能一次啟用/禁用一張表上的一個vpd策略，為了一次性啟用/禁用所有表上的vpd策略，可以采取類似上面的做法。）並設置context：隨後我們查詢vpdsample_clothing表，只返回了ID為10002的服裝信息；查詢vpdsample_books也類似，只返回了ID為10005的圖書信息。可以更換用戶，刪除vpd策略則使用：

begin  
dbms_rls.drop_policy(object_name=>'VPDSAMPLE_CLOTHING',policy_name=>'POL_CLOTHING');  
dbms_rls.drop_policy(object_name=>'VPDSAMPLE_BOOKS',policy_name=>'pol_books');  
end;  
 
begin  
pkg_vpdsample.set_context('Rose');  
end;  
 
begin  
pkg_vpdsample.set_context('Jack');  
end;  
 
begin  
pkg_vpdsample.enable_vpd;  
end;