Oracle VPD技术在中联his系统中的应用

前言：
VPD技术即虚拟专用数据库（应该是virtual private database），通过它可以对指定的用户隐藏你不想让他看到的数据。类似于，在别人访问这个表的时候，你自动在后面追加上你隐含添加的where条件。比如，select * from test用户请求的这个sql直接透明转换成select * from test where id=?这种形式然后把结果返还给用户。这种方式可以不用修改应用程序，达到访问数据权限的控制，便于整体管理,现在渝中社区环境中已经实际运用，效果很不错。
环境：
数据库环境：OS:windows2003企业版 数据库：oracle 10gr2 实例名：ORCL
业务数据环境：医院有3个门诊收费室，分别为：收费室一，收费室二，收费室三，上级部门为财务科
实现目标
    通过数据库的VPD技术，不用修改程序的情况下，达到权限控制，每个收费室的人员在病人收费管理中，只能查询，操作本收费室的单据，上级部门（财务科）的人员能够查询所有收费室的单据
技术操作
创建一个应用程序上下文,context对象始终属于sys用户，指定了操作此上下文的包
CREATE CONTEXT sec_zlhis_context USING pkg_sec_zlhis
创建上下文包头
CREATE OR REPLACE PACKAGE pkg_sec_zlhis IS
PROCEDURE Set_DeptID;
END;
 
2．创建上下文包体
CREATE OR REPLACE Package Body pkg_sec_zlhis Is
    Procedure Set_DeptID Is
        DeptID Number;
    Begin
--取得用户的缺省部门
Select 部门id Into DeptID
From 部门人员
Where 人员id = (Select 人员id From 上机人员表 Where 用户名 = Sys_Context('USERENV', 'SESSION_USER')) And 缺省 = 1;
----设置上下文属性
Dbms_Session.Set_Context('sec_zlhis_context', 'DeptID', DeptID);
Exception
When Others Then
Raise_application_error(-20011, '[安全检查失败]:无法设置用户缺省部门ID');
    End;
End;
 
3．创建公共同义词，授予用户的上下文包的执行权限
create public synonym sec_zlhis_context for sec_zlhis_context
create public synonym pkg_sec_zlhis for pkg_sec_zlhis
Grant Execute On pkg_sec_zlhis To public
 
4．创建策略包
CREATE OR REPLACE Package pkg_sec_zlhis_policy As
    Function Dept_Sec
    (
        D1 Varchar2,
        D2 Varchar2
    ) Return Varchar2;
End pkg_sec_zlhis_policy;
 
5．创建策略包体,函数用于返回查询语句的where条件
    CREATE OR REPLACE Package Body pkg_sec_zlhis_policy As
    Function Dept_Sec
    (
        D1 Varchar2,
        D2 Varchar2
    ) Return Varchar2 Is
        d_Predicate Varchar2(2000);
v_DeptID Number;
    Begin
--从用户上下文中取得属性(部门id)
v_DeptID:=SYS_CONTEXT('sec_zlhis_context', 'DeptID');
        
If v_DeptID Is Null Then
        d_Predicate:='1=1';
        Else
        For Temp_Cur In ( Select 姓名 From 人员表 Where Id In (Select 人员id From 部门人员 Where 部门Id=v_DeptID )) Loop
        d_Predicate:= d_Predicate||''''||Temp_Cur.姓名||''''||',';
        End Loop;
        
        d_Predicate:= Replace( '('||d_Predicate||')',',)',')');
--生成Where条件
d_Predicate := '操作员姓名 in '||d_Predicate;
        End If;
        
Return d_Predicate;
End Dept_Sec;
End pkg_sec_zlhis_policy;
 
6．测试策略包
Begin
dbms_output.put_line(pkg_sec_zlhis_policy.Dept_Sec('','') );
End;
 
7．创建策略,这里只处理了病人费用记录
Begin
    Dbms_Rls.Add_Policy('ZLHIS', '病人费用记录', '病人费用记录_policy', 'ZLHIS',
'pkg_sec_zlhis_policy.Dept_Sec', 'select');
End;
 
8．用收费室用户登陆，策略测试
Begin
ZLHIS.pkg_sec_zlhis.Set_DeptID();
End;
 
Select * From ZLHIS.病人费用记录 Where 登记时间 Between Sysdate -200 And Sysdate – 195
 
9．创建事件触发器，用户登录时，设置上下文属性
Create Or Replace Trigger Tri_Sec_Zlhis
    After Logon On Database
Declare
v_UserCount Number;
Begin
--只处理ZLHIS用户
Select Count(*) Into v_UserCount From 上机人员表
Where 用户名=Sys_Context('USERENV', 'SESSION_USER')
And 用户名<>'ZLHIS';
If (v_UserCount<>0) Then
     zlhis.pkg_sec_zlhis.Set_DeptID();
End If;
End Tri_Sec_Zlhis;
 
总结：
至此，一个简单的vpd数据库就建成了，当然，实际工作环境中不会这么简单，条件也比较复杂，不会这么简单，而且前期的测试工作也很很重要，不过，只要大家多练习，一定会有意想不到的收获。