oracle虚拟专用数据库的示例分析

58次阅读

共计 8164 个字符，预计需要花费 21 分钟才能阅读完成。

这篇文章给大家分享的是有关 oracle 虚拟专用数据库的示例分析的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

所谓虚拟专用数据库（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）发出下面的查询语句：

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 , 
 4 OBJECT_NAME= sales_list , 
 5 POLICY_NAME= oe_sales_list_fgac , 
 6 FUNCTION_SCHEMA= oe , 
 7 POLICY_FUNCTION= sales_app_pkg.where_condition , 
 8 STATEMENT_TYPES= select,update , 
 9 UPDATE_CHECK= true, 
 10 ENABLE= true); 
 11 end; 
 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 属性的值。在实际应用中，我们可以在登录界面上，当用户单击登录按钮的时候进行设置。在这里为了演示效果，我们创建一个登录触发器来设置，如下所示：

SQL  connect / as sysdba 
SQL  create or replace trigger set_seller_id_on_logon 
 2 after logon on DATABASE 
 3 begin 
 4 oe.sales_app_pkg.set_sales_context; 
 5 end; 
 6 /

现在，我们可以开始测试 FGAC 规则的效果了。

SQL  connect oe/oe 
SQL  select seller_id,count(*) from sales_list group by seller_id; 
SELLER_ID COUNT(*) 
--------- --------- 
S0010 1067 
S0030 968 
S0020 1465

以用户 OE 的身份登录以后，可以看到，三个销售人员各自的数据行数。然后以 S0010 的身份登录：

SQL  connect s0010/s0010 
SQL  select sys_context(sales_ctx , seller_id) from dual; 
SYS_CONTEXT(SALES_CTX , SELLER_ID) 
--------------------------------------- 
S0010 
SQL  select seller_id,count(*) from oe.sales_list group by seller_id; 
SELLER_ID COUNT(*) 
--------- --------- 
S0010 1067

很明显看到，我们设置的 FGAC 规则生效了。我们继续测试更新操作：

SQL  select seller_id,qty_sold from oe.sales_list where id=300; 
SELLER_ID QTY_SOLD 
--------- -------- 
S0010 1 
SQL  update oe.sales_list set seller_id= S0020  where id=300; 
update oe.sales_list set seller_id= S0020  where id=300 
 * 
ERROR at line 1: 
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 规则，则执行下面的代码：

SQL  begin 
 2 dbms_rls.drop_policy( 
 3 OBJECT_SCHEMA= oe , 
 4 OBJECT_NAME= sales_list , 
 5 POLICY_NAME= oe_sales_list_fgac  
 6 end; 
 7 /

二、基于列的 VPD

对于某些敏感列来说，比如员工的工资等，我们可以通过创建基于列的 VPD，从而屏蔽这些敏感列，只有具有权限的用户才能访问这些列。

基于列的 VPD 与前面讨论的 FGAC 一样，也是通过设置政策来实现的。设置基于列的 VPD 时，我们首先需要创建一个政策所需要用到的函数，如下所示。

SQL  connect hr/hr 
SQL  create or replace function hr_col_vpd 
 2 (p_owner in varchar2,p_obj in varchar2) 
 3 return varchar2 
 4 is 
 5 l_ret varchar2(2000); 
 6 begin 
 7 if (p_owner = USER) then 
 8 l_ret := NULL; 
 9 else 
 10 l_ret :=  1=2  
 11 end if; 
 12 return l_ret; 
 13 end; 
 14 /

这里，我们创建了一个规则函数。与 FGAC 规则一样，该函数必须有两个传入参数，第一个表示要限定的表所属的 schema 名称，第二个表示要限定的表的名称。在该函数中，我们定义，如果登录用户为表的属主，则可以查看所有列；否则，登录用户不是表所属的用户，则不能查看指定列。

至于具体哪些列要被屏蔽，则需要在定义政策时进行指定，如下所示：

SQL  begin 
 2 dbms_rls.add_policy(object_schema= hr , 
 3 object_name= employees , 
 4 policy_name= hr_emp_col_policy , 
 5 function_schema= hr , 
 6 policy_function= hr_col_vpd , 
 7 statement_types= select , 
 8 sec_relevant_cols= salary , 
 9 sec_relevant_cols_opt =  dbms_rls.all_rows 
 10 ); 
 11 end; 
 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 列。

SQL  connect hr/hr 
SQL  select employee_id,last_name,salary from hr.employees where rownum  
EMPLOYEE_ID LAST_NAME SALARY 
----------- ------------- ------- 
198 OConnell 2600 
199 Grant 2600 
200 Whalen 4400

可以看到所有的 salary 列都显示出来了。然后以用户 OE 的身份登录，执行下面的 SQL 语句：

SQL  connect oe/oe 
SQL  select employee_id,last_name,salary from hr.employees where rownum  
EMPLOYEE_ID LAST_NAME SALARY 
----------- ------------- ------- 
198 OConnell 
199 Grant 
200 Whalen

很明显，对于用户 OE 来说，salary 列已经被屏蔽了。

感谢各位的阅读！关于“oracle 虚拟专用数据库的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

正文完