首页 > 代码库 > [精]Oracle VPD详解(虚拟专用数据库)

[精]Oracle VPD详解(虚拟专用数据库)

所谓虚拟专用数据库(VPD)指的是,通过在数据库里进行配置,从而让不同的用户只能查看某 个表里的部分数据。VPD分为以下两个级别。 

  • 行级别:在该级别下,可以控制某些用户只能查看到某些数据行。比如,对于销售数据表sales 来说,每个销售人员只能检索出他自己的销售数据,不能查询其他销售人员的销售数据。 

  • 列级别:在该级别下,可以控制某些用户不能检索某个表的某个列的值。比如用户HR 下的 employees 表中,含有工资(salary)列,由于该列比较敏感,因此不让其他用户查询该列的值。 其他用户检索该列时,会发现其值全都为空(null )。 

一、基于行的VPD  

基于行的VPD 也叫作Fine-Grained Access Control ,简称 FGAC 。FGAC 通过定义规则实现,规则 的集合叫做FGAC 政策(policy)。如果对某个表设置了 FGAC ,则当用户对该表发出查询或者DML 语句时,Oracle 都会根据定义的 FGAC 政策,而自动改写这些SQL 语句。其改写方式为自动在SQL 语句后面添加where条件。 
比如,我们在OE用户下有一个表sales_list ,存放了所有的销售记录。每个销售人员只能查询他 自己的销售记录。于是,我们在sales 表上设置FGAC 政策来实现这个业务需求。如果某个销售人员 (假设其登录的用户名为 S0020 )发出下面的查询语句: 
  1. Select * from sales_list ;
当Oracle 在执行该语句时,如果发现 sales_list 表上存在FGAC 政策,于是就会根据 FGAC 政策,按照如下方式改写该SQL 语句: 
  1. Select * from sales_list where seller_id=‘S0020‘;
对用户来说,这个添加 where条件的过程是完全透明的,用户并不知道 Oracle 已经改写了他发出的SQL 语句,从而过滤了查询结果。当然,如果该销售人员发出的语句为: 
  1. Select * from sales_list where values>1000 ;
那么,当Oracle 在改写该 SQL 语句时,则会改写为如下形式: 
  1. Select * from sales_list where qty_sold>1000 and seller_id=‘S0020‘;
使用FGAC 政策来限定返回记录的方式具有许多优点。比如,不需要改写应用程序、对用户完全透明、集中设置、便于管理等。 
在使用FGAC 时,会涉及应用程序上下文(Application Context)的概念,使用应用程序上下文可 以简化FGAC 的实现。应用程序上下文是一个数据库对象,可以把它理解为数据库里的每个 session 的全局环境变量。一旦用户登录到数据库,从而创建出session 以后,应用程序上下文就在整个 session 的生命周期里可用。在应用程序上下文里可以定义多个属性,并为这些属性设置具体的值。而用户不 能直接修改属性的值,只能通过程序包来修改属性值。应用程序上下文总是由用户sys 拥有。 
比如,对于前面 sales_list 表的例子来说。我们可以创建一个应用程序上下文,当用户登录时,将 该用户的ID 号作为一个属性值放入该应用程序上下文中。然后在定义FGAC 政策的时候,将该用户 ID号取出,并作为限定条件短语(也就是where条件语句)返回给 Oracle,从而实现FGAC 。 
在Oracle 数据库里,已经为每个 session 都预先建立了一个应用程序上下文:userenv。一旦建立了session ,该 session 就可以使用这个应用程序上下文。在 userenv中已经预先定义了一些属性,比如 ip_address、session_user和db_name 等。在获取应用程序上下文里的属性值时,我们使用sys_context 函数。该函数包含两个参数,第一个参数表示应用程序上下文的名称,第二个参数表示要显示的属性 名称。如下所示: 
  1. SQL> select sys_context(‘userenv‘,‘ip_address‘) "IP",
  2. sys_context(‘userenv‘,‘db_name‘) "DB" from dual;
  3. IP DB
  4. --------------- ---------
  5. 152.68.32.60 ora10g
我们也可以创建自己的应用程序上下文,如下所示: 
  1. SQL> create or replace context sales_ctx using oe.sales_app_pkg;
在这里,sales_ctx 是应用程序上下文的名称,而 sales_app_pkg 则是用来设置sales_ctx 里属性的程序包。在创建应用程序上下文时,指定的、用来设置其中属性的程序包可以不必事先存在。但是在为应用程序上下文里设定属性值时,该程序包必须存在,否则报错。如果要删除应用程序上下文,则使用下面的命令: 
  1. SQL> drop context sales _ctx;
创建了应用程序上下文以后,我们就可以在其中设置属性了。在设置具体的应用程序上下文属性时,必须使用Oracle 提供的程序包 dbms_session.set_context 来设置其属性。其使用格式为: 
  1. dbms_session.set_context (‘context_name‘, ‘attribute_name‘, ‘attribute_value‘)
我们只能在程序包里使用dbms_session.set_context,而不能直接在SQL*Plus里调用。如下所示: 
  1. SQL> show user
  2. USER is "SYS"
  3. SQL> exec dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,‘S0020‘);
  4. BEGIN dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,‘S0020‘); END;
  5. *
  6. ERROR at line 1:
  7. ORA-01031: insufficient privileges
  8. ORA-06512: at "SYS.DBMS_SESSION", line 90
  9. ORA-06512: at line 1
我们创建oe.sales_app_pkg包,如下所示: 
  1. SQL> connect oe/oe
  2. SQL> create or replace package sales_app_pkg is
  3. 2 procedure set_sales_context;
  4. 3 end;
  5. 4 /
  6. SQL> create or replace package body sales_app_pkg is
  7. 2 procedure set_sales_context is
  8. 3 begin
  9. 4 dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,user);
  10. 5 end;
  11. 6 end;
  12. 7 /
  13. SQL> grant select on sales_list to public;
  14. SQL> grant update on sales_list to public;
  15. SQL> grant execute on sales_app_pkg to public;
把执行oe.sales_app_pkg 程序包的权限赋给所有用户以后,我们可以测试应用程序上下文是否生效了。 
  1. SQL> connect hr/hr
  2. SQL> exec oe.sales_app_pkg.set_sales_context;
  3. SQL> select sys_context(‘sales_ctx‘,‘seller_id‘) from dual;
  4. SYS_CONTEXT(‘SALES_CTX‘,‘SELLER_ID‘)
  5. --------------------------------------------------------------------------------
  6. HR
可以看到,应用程序上下文生效了。接下来,我们创建用于FGAC 规则的函数。 
  1. SQL> create or replace package sales_app_pkg is
  2. 2 procedure set_sales_context;
  3. 3 function where_condition
  4. 4 (p_schema_name varchar2,p_tab_name varchar2)
  5. 5 return varchar2;
  6. 6 end;
  7. 7 /
  8. SQL> create or replace package body sales_app_pkg is
  9. 2 procedure set_sales_context is
  10. 3 v_user varchar2(30);
  11. 4 begin
  12. 5 dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,user);
  13. 6 end;
  14. 7
  15. 8 function where_condition
  16. 9 (p_schema_name varchar2,p_tab_name varchar2) return varchar2 is
  17. 10 v_seller_id varchar2(100) := upper(sys_context(‘sales_ctx‘,‘seller_id‘));
  18. 11 v_where_condition varchar2(2000);
  19. 12 begin
  20. 13 if v_seller_id like ‘S%‘ then
  21. 14 v_where_condition := ‘seller_id = ‘ || ‘‘‘‘ || v_seller_id || ‘‘‘‘;
  22. 15 else
  23. 16 v_where_condition := null;
  24. 17 end if;
  25. 18 return v_where_condition;
  26. 19 end;
  27. 20 end;
  28. 21 /
在这里,我们主要关注 where_condition 函数,该函数会为 FGAC 规则返回限定条件。这种 FGAC 规则函数必须具有两个传入参数,第一个参数表示 schema 名称,第二个参数表示表的名称。表示对哪 个schema 下的哪个表添加FGAC 规则。同时必须返回字符型的值,该返回值会被Oracle 自动添加到 SQL 语句中的where条件部分。不过函数名称和参数名称可以按照需要进行指定。从这里定义的函数 体中可以看出,如果登录的用户名以S 开头,则会受到FGAC 规则的限制,where 条件里会添加 seller_id=‘Sxxxx‘ ,Sxxxx 表示登录的用户名。否则,如果以其他用户的身份登录,则不会受到FGAC规则的限制。 
创建了用于FGAC 规则的函数以后,我们开始定义FGAC 规则。 
  1. SQL> connect / as sysdba
  2. SQL> begin
  3. 2 dbms_rls.add_policy(
  4. 3 OBJECT_SCHEMA=>‘oe‘,
  5. 4 OBJECT_NAME=>‘sales_list‘,
  6. 5 POLICY_NAME=>‘oe_sales_list_fgac‘,
  7. 6 FUNCTION_SCHEMA=>‘oe‘,
  8. 7 POLICY_FUNCTION=>‘sales_app_pkg.where_condition‘,
  9. 8 STATEMENT_TYPES=>‘select,update‘,
  10. 9 UPDATE_CHECK=>true,
  11. 10 ENABLE=>true);
  12. 11 end;
  13. 12 /
如上所示,我们使用 dbms_rls 程序包来创建 FGAC 规则。我们为用户 OE下的sales_list 表创建了 规则;该规则利用用户 OE下的sales_app_pkg.where_condition 函数返回 where条件;该规则作用的 SQL 语句类型为select 和update ;update_check 参数说明是否对更新以后的结果判断是否满足 FGAC 规则; 在创建规则的同时,我们也启用该规则(enable 设置为true )。 
创建了FGAC 规则以后,我们需要在用户登录到应用程序的时候,调用 sales_app_pkg  程序包里 的set_sales_context 存储过程来设置该用户的应用程序上下文里的 seller_id 属性的值。在实际应用中, 我们可以在登录界面上,当用户单击登录按钮的时候进行设置。在这里为了演示效果,我们创建一个 登录触发器来设置,如下所示: 
  1. SQL> connect / as sysdba
  2. SQL> create or replace trigger set_seller_id_on_logon
  3. 2 after logon on DATABASE
  4. 3 begin
  5. 4 oe.sales_app_pkg.set_sales_context;
  6. 5 end;
  7. 6 /
现在,我们可以开始测试FGAC 规则的效果了。 
  1. SQL> connect oe/oe
  2. SQL> select seller_id,count(*) from sales_list group by seller_id;
  3. SELLER_ID COUNT(*)
  4. --------- ---------
  5. S0010 1067
  6. S0030 968
  7. S0020 1465
以用户OE的身份登录以后,可以看到,三个销售人员各自的数据行数。然后以S0010 的身份登录: 
  1. SQL> connect s0010/s0010
  2. SQL> select sys_context(‘sales_ctx‘,‘seller_id‘) from dual;
  3. SYS_CONTEXT(‘SALES_CTX‘,‘SELLER_ID‘)
  4. ---------------------------------------
  5. S0010
  6. SQL> select seller_id,count(*) from oe.sales_list group by seller_id;
  7. SELLER_ID COUNT(*)
  8. --------- ---------
  9. S0010 1067
很明显看到,我们设置的FGAC 规则生效了。我们继续测试更新操作: 
  1. SQL> select seller_id,qty_sold from oe.sales_list where id=300;
  2. SELLER_ID QTY_SOLD
  3. --------- --------
  4. S0010 1
  5. SQL> update oe.sales_list set seller_id=‘S0020‘ where id=300;
  6. update oe.sales_list set seller_id=‘S0020‘ where id=300
  7. *
  8. ERROR at line 1:
  9. ORA-28115: policy with check option violation
由于我们在创建FGAC 规则时,指定了update_check 为true ,当用户 S0010 登录以后更新sales_list 表,将 seller_id 从S0010 更新为S0020 时报错,因为 S0010 无权查询和修改不属于他的销售数据。如 果指定update_check 为false ,则允许这样的update 语句成功。 
FGAC 规则的使用是非常灵活的,其关键就在于 where_condition 函数的写法。如果要删除 FGAC规则,则执行下面的代码: 
  1. SQL> begin
  2. 2 dbms_rls.drop_policy(
  3. 3 OBJECT_SCHEMA=>‘oe‘,
  4. 4 OBJECT_NAME=>‘sales_list‘,
  5. 5 POLICY_NAME=>‘oe_sales_list_fgac‘);
  6. 6 end;
  7. 7 /

二、基于列的VPD  

对于某些敏感列来说,比如员工的工资等,我们可以通过创建基于列的 VPD ,从而屏蔽这些敏感列,只有具有权限的用户才能访问这些列。 
基于列的VPD 与前面讨论的FGAC 一样,也是通过设置政策来实现的。设置基于列的VPD 时,我们首先需要创建一个政策所需要用到的函数,如下所示。 
  1. SQL> connect hr/hr
  2. SQL> create or replace function hr_col_vpd
  3. 2 (p_owner in varchar2,p_obj in varchar2)
  4. 3 return varchar2
  5. 4 is
  6. 5 l_ret varchar2(2000);
  7. 6 begin
  8. 7 if (p_owner = USER) then
  9. 8 l_ret := NULL;
  10. 9 else
  11. 10 l_ret := ‘1=2‘;
  12. 11 end if;
  13. 12 return l_ret;
  14. 13 end;
  15. 14 /
这里,我们创建了一个规则函数。与 FGAC 规则一样,该函数必须有两个传入参数,第一个表示 要限定的表所属的schema 名称,第二个表示要限定的表的名称。在该函数中,我们定义,如果登录用 户为表的属主,则可以查看所有列;否则,登录用户不是表所属的用户,则不能查看指定列。 
至于具体哪些列要被屏蔽,则需要在定义政策时进行指定,如下所示: 
  1. SQL> begin
  2. 2 dbms_rls.add_policy(object_schema=>‘hr‘,
  3. 3 object_name=>‘employees‘,
  4. 4 policy_name=>‘hr_emp_col_policy‘,
  5. 5 function_schema=>‘hr‘,
  6. 6 policy_function=>‘hr_col_vpd‘,
  7. 7 statement_types=>‘select‘,
  8. 8 sec_relevant_cols=>‘salary‘,
  9. 9 sec_relevant_cols_opt => dbms_rls.all_rows
  10. 10 );
  11. 11 end;
  12. 12 /
创建基于列VPD 与创建FGAC 政策一样,也是使用dbms_rls 程序包里的add_policy 存储过程。 在这里,我们定义了一个名为 hr_emp_col_policy 的政策。该政策作用在用户 HR下的employees 表上; 采用的政策函数为用户HR下的hr_col_vpd 。 
与FGAC 政策不同的是,我们需要指定另外两个参数:sec_relevant_cols表示要屏蔽的列的名称, 可以指定多个列,列与列之间用逗号隔开;sec_relevant_cols_opt 设置为all_rows,则说明对 employees 表里所有的记录都屏蔽salary 列。 
我们以用户HR的身份登录,并显示salary 列。 
  1. SQL> connect hr/hr
  2. SQL> select employee_id,last_name,salary from hr.employees where rownum<4;
  3. EMPLOYEE_ID LAST_NAME SALARY
  4. ----------- ------------- -------
  5. 198 OConnell 2600
  6. 199 Grant 2600
  7. 200 Whalen 4400
可以看到所有的salary 列都显示出来了。然后以用户OE的身份登录,执行下面的SQL 语句: 
  1. SQL> connect oe/oe
  2. SQL> select employee_id,last_name,salary from hr.employees where rownum<4;
  3. EMPLOYEE_ID LAST_NAME SALARY
  4. ----------- ------------- -------
  5. 198 OConnell
  6. 199 Grant
  7. 200 Whalen
很明显,对于用户OE来说,salary 列已经被屏蔽了。 

[精]Oracle VPD详解(虚拟专用数据库)