Oracle handbook系列之虛擬專用數(shù)據(jù)庫VPD的使用詳解
Oracle handbook系列之虛擬專用數(shù)據(jù)庫VPD的使用是本文我們主要要介紹的內(nèi)容，VPD，Oracle Virtual Private Database，即Oracle虛擬專用數(shù)據(jù)庫，是指通過應(yīng)用一些策略，使得用戶只能訪問被允許訪問的那部分?jǐn)?shù)據(jù)。其原理相對(duì)簡(jiǎn)單，Oracle根據(jù)策略自動(dòng)為相應(yīng)用戶提交的語句添加Where句，從而控制用戶可以訪問和操作的數(shù)據(jù)。
首先我們準(zhǔn)備演示用的幾張表，并插入少量的測(cè)試數(shù)據(jù)：
- 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;
（以上兩個(gè)表模擬一個(gè)簡(jiǎn)單的庫存情況，庫中有兩類物品，服裝、圖書。這里我們需要滿足兩個(gè)表中的ID的唯一性（可以通過sequence來實(shí)現(xiàn)）。）
- CREATETABLEvpdsample_users(
- user_nameVARCHAR2(20),
- user_privilegeNUMBER
- );
- --
- INSERTINTOvpdsample_usersVALUES('Jack',1);
- INSERTINTOvpdsample_usersVALUES('Rose',2);
- COMMIT;
（這個(gè)表存儲(chǔ)用戶的權(quán)限信息，其中的權(quán)限即后表vpdsample_privileges中的權(quán)限ID字段。）
（這個(gè)表存儲(chǔ)每個(gè)權(quán)限ID對(duì)應(yīng)的權(quán)限信息，即對(duì)哪些對(duì)象（服裝或圖書）有權(quán)限。）
第二步，我們要?jiǎng)?chuàng)建一個(gè)context（實(shí)際上是【context名稱空間】）。可以簡(jiǎn)單地把context理解為一個(gè)定義在內(nèi)存中的容器，在此容器中我們可以定義若干個(gè)鍵值對(duì)，這些鍵值對(duì)可以在一定的范圍內(nèi)被共享（比如同一個(gè)session中，或者同一個(gè)Oracle實(shí)例中）
首先，使用system用戶登錄，賦予創(chuàng)建者相應(yīng)的權(quán)限：grant create any context to user1;然后通過：CREATE OR REPLACE CONTEXT VPD USING pkg_vpdsample ACCESSED GLOBALLY;這里我們創(chuàng)建了一個(gè)叫’vpd’的context，’using’后面的是一個(gè)PLSQL package的名字，出于安全性考慮，Oracle需要你在創(chuàng)建context時(shí)指定一個(gè)包名，表示后續(xù)對(duì)些context的修改只能通過此包中的存儲(chǔ)過程進(jìn)行修改，不能通過dbms_session.set_context()直接進(jìn)行修改。創(chuàng)建context時(shí)，package不存在并不會(huì)導(dǎo)致編譯錯(cuò)誤。
最后’accessed globally’是一個(gè)可選項(xiàng)，如果未添加此項(xiàng)，表示此context使用范圍是某一session；如果指定了此項(xiàng)，則表示該context可以在整個(gè)數(shù)據(jù)庫實(shí)例范圍內(nèi)共享。
欲刪除context同樣需要賦予相應(yīng)的權(quán)限：
- grant drop any context to user1;
- drop context VPD;
第三步，建立一個(gè)package（即上面的pkg_vpdsample），包中的各個(gè)函數(shù)及存儲(chǔ)過程的作用會(huì)隨后逐一給出：
- 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：這兩個(gè)存儲(chǔ)過程用于設(shè)置context vpd下的一個(gè)自定義屬性’enable’，1表是啟用vpd，0表禁用vpd，由于我們?cè)趧?chuàng)建此context時(shí)指定了accessed globally，所以這些屬性是可以被跨session訪問的。這兩個(gè)存儲(chǔ)過程調(diào)用了dbms_session，因此需要被賦予相應(yīng)的權(quán)限：grant execute on dbms_session to user1;
set_context：上面我們提到了context，并且在enable_vpd與disable_vpd中使用了dbms_session.set_context來設(shè)置自定義的屬性，其實(shí)除了這樣自定義的context外，Oracle還提供了預(yù)定義的context ’userenv’，該名稱空間下有若干預(yù)定義的屬性，比如’client_identifier’，設(shè)置此屬性我們不使用set_context，而是使用dbms_session.set_identifier()。
本例中此存儲(chǔ)過程根據(jù)傳入的用戶名，查找到該用戶的權(quán)限ID，并將此ID作為client_identifier保存到context ’userenv’中，以便后續(xù)使用。在實(shí)際應(yīng)用中，此存儲(chǔ)過程應(yīng)該是由外部的應(yīng)用程序調(diào)用的，應(yīng)用程序可以在登錄驗(yàn)證完成后，調(diào)用此存儲(chǔ)過程寫入context。
gen_vpd_predicate：根據(jù)傳入的字段名稱生成一個(gè)語法正確的where子句。這里首先判斷了是否啟用了vpd，未設(shè)置vpd.enable屬性的也認(rèn)為的已經(jīng)啟用了。隨后判斷是否已設(shè)置userenv.client_identifier屬性，如果未設(shè)置，則返回一個(gè)始終為false的where子句以防止用戶查看數(shù)據(jù)。最后生成的語句中，根據(jù)取到的client_identifier（即用戶的權(quán)限ID）查找權(quán)限表vpdsample_privileges得到該用戶有權(quán)限的所有對(duì)象ID。
apply_vpd_clothing, apply_vpd_books：由名字可見，這兩個(gè)函數(shù)將被用于兩個(gè)不同的表，因?yàn)榉b表與圖書表所用于權(quán)限驗(yàn)證的字段有著不同的名字。另外，大家可以看到這兩個(gè)將被用于vpd的函數(shù)都有兩個(gè)看似沒用的參數(shù)p1與p2，這是vpd接口的要求，第一個(gè)用于接收schema名，第二個(gè)用于接收table/view/synonym名稱，我們定義函數(shù)必須符合接口的要求。當(dāng)然除了p1,p2外，你可以有自己額外的參數(shù)。
一切準(zhǔn)備完畢，開始調(diào)用Oracle提供的dbms_rls包應(yīng)用vpd策略，在調(diào)用之前，需要賦予用戶相應(yīng)權(quán)限：grant execute on dbms_rls to user1;
其中policy_name可以自定義。
隨后我們啟用vpd：（需要說明的是，DBMS_RLS包本身有ENABLE_POLICY()方法用于啟用或禁用一個(gè)vpd策略，但它只能一次啟用/禁用一張表上的一個(gè)vpd策略，為了一次性啟用/禁用所有表上的vpd策略，可以采取類似上面的做法。）并設(shè)置context：隨后我們查詢vpdsample_clothing表，只返回了ID為10002的服裝信息；查詢vpdsample_books也類似，只返回了ID為10005的圖書信息?？梢愿鼡Q用戶，刪除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;
關(guān)于Oracle handbook系列之虛擬專用數(shù)據(jù)庫VPD的相關(guān)知識(shí)就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
【編輯推薦】
分享到微信 
分享到微博
