弹冠相庆

October 5, 2008

Oracle Virtual Private Database

Filed under: Uncategorized — Corey @ 17:51
Tags:
Oracle VPD(虚拟专有数据库)是从Oracle 8i (8.1.5)引入的一个新特性,为数据库提供了行级别
的安全性 (Row Level Security)或者说细粒度存取控制 (Fine Grained Access Control).
VPD的主要思想就是将访问限定到表中特定的行,这样每个用户将看到完全不同的数据集,
只能对这些被授权的数据进行增删查改。
Oracle 10g中VPD又有进一步完善,使得select操作可以基于列级别的控制(Column-Level Privacy).

关键词: VPD, FGAC,  Row Level Security, Column-Level Privacy, dbms_rls, add_policy

下面是个简单例子 (翻译自http://www.adp-gmbh.ch/ora/security/vpd/index.html)。

在这个例子里,假设一个公司由不同的部门组成(每个部门有一条记录在department表)。一个员
工只能属于一个部门,一个部门可以有一些机密信息记录在department_secrets表。

create table department (
  dep_id int primary key,
  name    varchar2(30)
);

create table employee (
  dep_id references department,
  name    varchar2(30)
);

create table department_secrets (
  dep_id references department,
  secret varchar2(30)
);

插入一些机密信息:

insert into department values (1, ‘Research and Development’);
insert into department values (2, ‘Sales’                   );
insert into department values (3, ‘Human Resources’         );

insert into employee values (2, ‘Peter’);
insert into employee values (3, ‘Julia’);
insert into employee values (3, ‘Sandy’);
insert into employee values (1, ‘Frank’);
insert into employee values (2, ‘Eric’ );
insert into employee values (1, ‘Joel’ );

insert into department_secrets values (1, ‘R+D Secret #1’  );
insert into department_secrets values (1, ‘R+D Secret #2’  );
insert into department_secrets values (2, ‘Sales Secret #1’);
insert into department_secrets values (2, ‘Sales Secret #2’);
insert into department_secrets values (3, ‘HR Secret #1’   );
insert into department_secrets values (3, ‘HR Secret #2’   );

对于任何一个员工,只能看到本部门的机密信息,而不能看见别的部门的机密信息。
为了在Oracle中实现这个功能,我们需要创建一个包,一个触发器以及设置一个策略。
首先创建一个包。

create or replace package pck_vpd

as
  p_dep_id department.dep_id%type;

  procedure set_dep_id(v_dep_id department.dep_id%type);

  function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/

create or replace package body pck_vpd as
 
  procedure set_dep_id(v_dep_id department.dep_id%type) is
  begin
    p_dep_id := v_dep_id;
  end set_dep_id;

  function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
  begin
    return ‘dep_id = ‘ || p_dep_id;
  end predicate;
 
end pck_vpd;
/

然后定义一个触发器,当用户登陆数据库时,这个触发器将被触发。它找到用户的部门id(dep_id)
并调用包里的set_dep_id存储过程。

create or replace trigger trg_vpd
  after logon on database
declare
  v_dep_id department.dep_id%type;
begin
  select dep_id into v_dep_id
  from employee where upper(name) = user;

  pck_vpd.set_dep_id(v_dep_id);
end;
/

最后,定义一条策略。这条策略表明了如果用户执行一条select语句(注:实际上不止是select,
取决于policy的定义,本例中是select, update, delete),哪个存储过程返回的结果会被用来添加
到where子句中 (注:本例中是’dep_id = ‘ || p_dep_id, 其中p_dept_id会用实际值代替)。

begin
dbms_rls.add_policy  (
  user,
  ‘department_secrets’,
  ‘choosable policy name’,
  user,
  ‘pck_vpd.predicate’,
  ‘select,update,delete’);
end;
/

为了测试上述设置,创建一些用户。

create user frank identified by frank default tablespace users temporary tablespace temp;
create user peter identified by peter default tablespace users temporary tablespace temp;
create user julia identified by julia default tablespace users temporary tablespace temp;

授予必要的权限。

grant all on department_secrets to frank;
grant all on department_secrets to peter;
grant all on department_secrets to julia;

grant create session to frank;
grant create session to peter;
grant create session to julia;

创建一个共有的别名。

create public synonym department_secrets for department_secrets;

Frank (属于R+D部门) 执行一条查询….

connect frank/frank;

select * from department_secrets;

    DEP_ID SECRET
———- ——————————
         1 R+D Secret #1
         1 R+D Secret #2

Peter (属于Sales部门) 执行一条查询….

connect peter/peter;

select * from department_secrets;

    DEP_ID SECRET
———- ——————————
         2 Sales Secret #1
         2 Sales Secret #2

delete, update的情况与select类似.

补充:如果要实现select时对Column-Level的控制,比如说,当查询中包含secret这个字段时所添加的
策略才生效,可以把上面的
begin
dbms_rls.add_policy  (
  user,
  ‘department_secrets’,
  ‘choosable policy name’,
  user,
  ‘pck_vpd.predicate’,
  ‘select,update,delete’);
end;
/
改为
begin
dbms_rls.add_policy  (
  user,
  ‘department_secrets’,
  ‘choosable policy name’,
  user,
  ‘pck_vpd.predicate’,
  ‘select,update,delete’),
  sec_relevant_cols=>’secret’;
end;
/

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: