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

• 列级别：在该级别下，可以控制某些用户不能检索某个表的某个列的值。比如用户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 ）发出下面的查询语句： 

Select * from sales_list ; 

当Oracle 在执行该语句时，如果发现 sales_list 表上存在FGAC 政策，于是就会根据 FGAC 政策，按照如下方式改写该SQL 语句： 

Select * from sales_list where seller_id=‘S0020‘; 

对用户来说，这个添加 where条件的过程是完全透明的，用户并不知道 Oracle 已经改写了他发出的SQL 语句，从而过滤了查询结果。当然，如果该销售人员发出的语句为： 

Select * from sales_list where values>1000 ; 

那么，当Oracle 在改写该 SQL 语句时，则会改写为如下形式： 

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 函数。该函数包含两个参数，第一个参数表示应用程序上下文的名称，第二个参数表示要显示的属性 名称。如下所示： 

SQL> select sys_context(‘userenv‘,‘ip_address‘) "IP", 
     sys_context(‘userenv‘,‘db_name‘) "DB" from dual; 
IP                     DB 
---------------     --------- 
152.68.32.60         ora10g 

我们也可以创建自己的应用程序上下文，如下所示： 

SQL> create or replace context sales_ctx using oe.sales_app_pkg; 

在这里，sales_ctx 是应用程序上下文的名称，而 sales_app_pkg 则是用来设置sales_ctx 里属性的程序包。在创建应用程序上下文时，指定的、用来设置其中属性的程序包可以不必事先存在。但是在为应用程序上下文里设定属性值时，该程序包必须存在，否则报错。如果要删除应用程序上下文，则使用下面的命令： 

SQL> drop context sales _ctx; 

创建了应用程序上下文以后，我们就可以在其中设置属性了。在设置具体的应用程序上下文属性时，必须使用Oracle 提供的程序包 dbms_session.set_context 来设置其属性。其使用格式为： 

dbms_session.set_context (‘context_name‘, ‘attribute_name‘, ‘attribute_value‘) 

我们只能在程序包里使用dbms_session.set_context，而不能直接在SQL*Plus里调用。如下所示： 

SQL> show user 
USER is "SYS" 
SQL> exec dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,‘S0020‘); 
BEGIN dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,‘S0020‘); END; 
* 
ERROR at line 1: 
ORA-01031: insufficient privileges 
ORA-06512: at "SYS.DBMS_SESSION", line 90 
ORA-06512: at line 1 

我们创建oe.sales_app_pkg包，如下所示： 

SQL> connect oe/oe 
SQL> create or replace package sales_app_pkg is 
  2   procedure set_sales_context; 
  3  end;   
  4  / 
SQL> create or replace package body sales_app_pkg is 
  2   procedure set_sales_context is 
  3   begin 
  4    dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,user); 
  5   end; 
  6  end; 
  7  / 
SQL> grant select on sales_list to public; 
SQL> grant update on sales_list to public; 
SQL> grant execute on sales_app_pkg to public; 

把执行oe.sales_app_pkg 程序包的权限赋给所有用户以后，我们可以测试应用程序上下文是否生效了。 

SQL> connect hr/hr 
SQL> exec oe.sales_app_pkg.set_sales_context; 
SQL> select sys_context(‘sales_ctx‘,‘seller_id‘) from dual; 
SYS_CONTEXT(‘SALES_CTX‘,‘SELLER_ID‘) 
-------------------------------------------------------------------------------- 
HR 

可以看到，应用程序上下文生效了。接下来，我们创建用于FGAC 规则的函数。 

SQL> create or replace package sales_app_pkg is 
  2   procedure set_sales_context; 
  3   function where_condition 
  4    (p_schema_name varchar2,p_tab_name varchar2) 
  5     return varchar2; 
  6  end; 
  7  /   
SQL> create or replace package body sales_app_pkg is 
  2  procedure set_sales_context is 
  3      v_user varchar2(30); 
  4  begin 
  5    dbms_session.set_context(‘sales_ctx‘,‘seller_id‘,user); 
  6  end; 
  7   
  8  function where_condition 
  9  (p_schema_name varchar2,p_tab_name varchar2) return varchar2 is 
 10    v_seller_id varchar2(100) := upper(sys_context(‘sales_ctx‘,‘seller_id‘)); 
 11    v_where_condition varchar2(2000); 
 12  begin 
 13    if v_seller_id like ‘S%‘ then 
 14      v_where_condition :=  ‘seller_id = ‘ || ‘‘‘‘ || v_seller_id || ‘‘‘‘; 
 15    else 
 16      v_where_condition := null; 
 17    end if; 
 18    return v_where_condition; 
 19  end; 
 20  end; 
 21  / 

在这里，我们主要关注 where_condition 函数，该函数会为 FGAC 规则返回限定条件。这种 FGAC 规则函数必须具有两个传入参数，第一个参数表示 schema 名称，第二个参数表示表的名称。表示对哪 个schema 下的哪个表添加FGAC 规则。同时必须返回字符型的值，该返回值会被Oracle 自动添加到 SQL 语句中的where条件部分。不过函数名称和参数名称可以按照需要进行指定。从这里定义的函数 体中可以看出，如果登录的用户名以S 开头，则会受到FGAC 规则的限制，where 条件里会添加 seller_id=‘Sxxxx‘ ，Sxxxx 表示登录的用户名。否则，如果以其他用户的身份登录，则不会受到FGAC规则的限制。 
创建了用于FGAC 规则的函数以后，我们开始定义FGAC 规则。 

SQL> connect / as sysdba 
SQL> begin 
  2    dbms_rls.add_policy( 
  3       OBJECT_SCHEMA=>‘oe‘
                        

                            

                                
< 上一篇
常用sql语句及案例（oracle）
                            
                            

                                 
下一篇 >
mysql常用命令