基於VPD的資料管理

VPD這東西，ORACLE8i就已經有了，以前我們的系統主要是面向醫院使用，更多的是一個OLTP 系統，雖然VPD有用，但不值得冒這個險使用這個技術。但如果使用在一個彙總的資料庫中，尤其對於安全特性考慮比較多的地方，還是值得試一試。

前段時間開發了一個科訓系統，本意是給科訓局做統計上報。這個程式能否推下去，目前還不明朗，但可以拿其中的資料做些測試。

以論文(表paper)為例（個人認為，這個表的名稱應該為複數更合適），其中有個欄位organ_code 表示該論文屬於哪個單位的，單位編碼是按級別編寫，例如第二軍醫大學為D2， 長征醫院為D22 ，如果不同單位的人想訪問這個表，則希望只能訪問其本單位或者下屬單位的資料。

表users 中有個欄位organ_code 表示該使用者屬於哪個單位。

以SYSTEM使用者建立包：pck_vpd

create or replace package pck_vpd

as

  p_organ_code users.organ_code%type;

  procedure set_organ_code(v_organ_code users.organ_code%type);

  function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;

end pck_vpd;

/

create or replace package body pck_vpd as

  procedure set_organ_code(v_organ_code users.organ_code%type) is

  begin

    p_organ_code := v_organ_code;

  end set_organ_code;

  function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is

  begin

    return  'organ_code like  ''' || p_organ_code||'%''';

  end predicate;

end pck_vpd;

/

建立觸發器：

create or replace trigger trg_vpd

  after logon on database

declare

  v_organ_code  users.organ_code%type;

begin

 begin

  select organ_code into v_organ_code

  from users where upper(db_user) = user;

  exception when NO_DATA_FOUND then

           v_organ_code :='';

  end;         

  pck_vpd.set_organ_code(v_organ_code);

end;

/

新增策略：

begin

dbms_rls.add_policy  (

  'TRAIN',

  'PAPER',

  'paper policy name',

  user,

  'pck_vpd.predicate',

  'select,update,delete');

end;

/

以使用者LGB（我們一個同事的使用者名稱，我們稱之為老幹部）為例:

update  users set  organ_code='E' where db_user='LGB';

commit;

SQL> conn lgb/lgb@dbserver

Connected.

SQL> select distinct organ_code from paper ;

ORGAN_COD

---------

E

E0845

修改下使用者的organ_code

update  users set  organ_code='D' where db_user='LGB';

commit;

SQL>  conn lgb/lgb@dbserver

Connected.

SQL> select distinct organ_code from paper ;

ORGAN_COD

---------

D

D30

D31

D40

D4061

D4062

D41

D45

8 rows selected.