Oraclehandbook系列之虚拟专用数据库VPD的使用详解

1、VPD Oracle Virtual Private Database，即Oracle虚拟专用数据库，是指通过应用一些策略，使得用户只能访问被允许访问的那部分数据。其原理相对简单,Oracle根据策略8.对可以在一定的范围内被共享（比如同一个session中，或者同一个 Oracle实例中）自动为相应用户提交的语句添加Where句，从而控制用户可以访问和操作的数据。首先我们准备演示用的几张表，并插入少量的测试数据:1.2.3.CREATETABLEv pdsa mp le_clothing(clothing_idNUMBER,4.5.6.typ eVARCHAR2(30),brandVARCH

2、AR2(30),descri ptonVARCHAR2(100);7.8.INSERTINTOv pdsa mp le_clothingVALUES(10002,jacket,ABC,autumnstyle);9.10.11.INSERTINTOv pdsa mp le_clothingVALUES(10003,t-shirr,X YZ, summerstyle); commit;CREATETABLEv pdsam pl e_books(12.13.book_idNUMBER,nameVARCHAR2(30),14.15.16.authorVARCHAR2(20);17.18.INSERTI

3、NTOv pdsa mp le_booksVALUES(10005,CountryDriving, P eterHessler);INSERTINTOv pdsam pl e_booksVALUES(10006,Lifewithoutlimits,NickVujicic);19.commit;（以上两个表模拟一个简单的库存情况，库中有两类物品，服装、图书。这里我们需要满足两个表中的ID的唯一性（可以通过 sequenee来实现）。）1.2.CREATETABLEv pdsa mp le_users(user_nameVARCHAR2(20),3.4.user_ privilegeNUMBER)

4、;5.6.7.INSERTINTOv pdsa mp le_usersVALUES(Jack,1);INSERTINTOv pdsam pl e_usersVALUES(Rose,2);COMMIT;（这个表存储用户的权限信息，其中的权限即后表vpdsample_privileges中的权限 ID 字段。)（这个表存储每个权限ID对应的权限信息，即对哪些对象（服装或图书）有权限。）第二步，我们要创建一个con text （实际上是【co ntext名称空间】）。可以简单地把con text理解为一个定义在内存中的容器，在此容器中我们可以定义若干个键值对，这些键值首先，使用system用户登录，

5、赋予创建者相应的权限:grant create any con text to user1;然后通过：CREATEORRE PLACECONTEXV PDUSING pkg_vpdsam pie ACCESSEGLOBALLY;这里我们创建了一个叫vpd 的con text ， using 后面的是一个 P LSQL package的名字，出于安全性考虑，Oracle需要你在创建con text时指定一个包名，表示后续对些con text的修改只能通过此包中的存储过程进行修改，不能通过dbms_sessio n.set_co ntext()直接进行修改。创建 con text时，package

6、不存在并不会导致编译错误。最后accessed globally是一个可选项，如果未添加此项，表示此con text使用范围是某一 session ;如果指定了此项，则表示该con text可以在整个数据库实例范围内共享。欲删除con text同样需要赋予相应的权限:1.2.grant drop any context to userl;drop context VPD;第三步，建立一个 package (即上面的pkg_vpdsample )，包中的各个函数及存储过程的作用会随后逐一给出:1.CREATEORRE PLACE PACKAGE pkg_v pdsa mple2.3.4.ISP

7、ROCEDUREenable_v pd;P ROCEDUREdisable_v pd;5.6.P R0CEDUREset_context(p_user_namelNVARCHAR2);FUNCTIONgen_v pd_p redicate(p_column_nameINVARCHAR2)RETURNVARCHAR2;7.8.9.FUNCTIONa pp ly_v pd_clothing( p1invarcha r2,p 2invarchar2)RETURNVARCHAR2;FUNCTIONa pp ly_v pd_books( p1invarcha r2,p 2invarchar2)RETUR

8、NVARCHAR2;END;10.11.CREATEORRE PLACE PACKAGEBOD Yp kg_v pdsa mp lelS12.13.14.P ROCEDUREenable_v pdlSBEGINDBMS_SESSION.set_context(names pace =V PD,15.16.attribute =ENABLE,value =1);17.18.19.END;/*/20.21.P ROCEDUREdisable_v pdlSBEGIN22.23.24.DBMS_SESSION.set_context(attribute =ENABLE,value =O);names

9、pace =V PD,25.26.END;27.28./*/P ROCEDUREset_context(p_user_nameINVARCHAR2)IS29.30.31.l_p rivilegeVARCHAR2(10);BEGINSELECTuser_ privilege32.INTOl_ privilege6.37.38.FROMv pdsa mpl e_usersWHEREuser_name = p_user_name DBMS_SESSION.set_identifier(END;/*/client_id = l_p rivilege);该是由外部的应用程序调用的，应

10、用程序可以在登录验证完成后，调用此存储过程写入con text 。8.BEGINl_vp d_flag:IFl_v pd_flag RETURNNULL;ELSE=NVL(SYS_CONTEXT(V PD,ENABLE),O);=0THENl_p rivilege:IFI_p rivilegeISNULLTHEN= SYS_CONTEXT(USERENV,CLIENT_IDENTIFIER);49.50.51.RETURN 1 =2;ELSERETURNp_column_name|IN(SELECTobjectidFROMv pdsa m

11、p le_ privilegesWHERE privilege_id=|l_ privilege);FUNCTIONgen_v pd_p redicate(p_column_namelNVARCHAR2)RETURNVARCHAR2ISI_vp d_flagVARCHAR2(1); l_p rivilegeVARCHAR2(10);8.ENDIF;ENDIF;END;*/*：FUNCTIONa pply_vp d_clothing( p1invarcha r2,p 2invarchar2)RETURNVARCHAR2ISBEGIN2.6

12、3.64.65.RETURNgen_v pd_p redicate(clothing_id);END;/*/FUNCTIONa pply_vp d_books( p1invarcha r2,p 2invarchar2)RETURNVARCHAR2ISBEGINRETURNgen_v pd_p redicate(book_id);66.67.END;END;en able_vpd,disable_vpd:这两个存储过程用于设置con text vpd下的一个自定义属性en able ， 1表是启用 vpd，0表禁用vpd ,由于我们在创建此 con text时指定了 accessedglobal

13、ly ，所以这些属性是可以被跨session访问的。这两个存储过程调用了dbms_session，因此需要被赋予相应的权限:grant execute on dbms_sessi on to user1;set_co ntext :上面我们提到了 con text ，并且在en able_vpd与disable_vpd 中使用了 dbms_sessio n. set_co ntext来设置自定义的属性，其实除了这样自定义的con text外，Oracle还提供了预定义的con text usere nv ，该名称空间下有若干预定义的属性， 比如clie nt_ide ntifier设置此属性我

14、们不使用set_context ，而是使用dbms_session.set_identifier()本例中此存储过程根据传入的用户名，查找到该用户的权限ID，并将此 ID 作为 client_identifier保存到con text usere nv 中，以便后续使用。在实际应用中，此存储过程应gen_vpd_predicate :根据传入的字段名称生成一个语法正确的where子句。这里首先判断了是否启用了vpd ,未设置vpd.enable属性的也认为的已经启用了。随后判断是否已设置 userenv.client identifier属性，如果未设置，则返回一个始终为false的where

15、子句以防止用户查看数据。最后生成的语句中，根据取到的die nt ide ntifier（即用户的权限ID）查找权限表vp dsam pl e_privileges得到该用户有权限的所有对象ID。appiy_vpd_clothing,apply_vpd_books :由名字可见，这两个函数将被用于两个不同的表，因为服装表与图书表所用于权限验证的字段有着不同的名字。另外，大家可以看到这两个将被用于vpd的函数都有两个看似没用的参数p1与p2,这是vpd接口的要求，第一个用于接收schema名，第二个用于接收table/view/synonym名称，我们定义函数必须符合接口的要求。当然除了 p1,

16、p2外，你可以有自己额外的参数。一切准备完毕，开始调用Oracle提供的dbms_rls包应用vpd策略，在调用之前，需要赋予用户相应权限:grant execute on dbms_rls to userl;其中policy_name 可以自定义。随后我们启用vpd:（需要说明的是，DBMS_RL包本身有ENABLEPOLICY（方法用于启用或禁用一个vpd策略，但它只能一次启用/禁用一张表上的一个vpd策略，为了一次性启用/禁用所有表上的vpd策略，可以采取类似上面的做法。）并设置con text :随后我们查询 vp dsam pl e_clothi ng表，只返回了 ID为10002的服装信息；查询vpdsample_books 也类似,..5.16.object_name =V PDSA MP LE_CLOTHING,p olicy_name=PO L_CLOTHING);object_name =V PDSA MP LE_BOOKS, p