oracle虛擬專用數(shù)據(jù)庫的示例分析

169次閱讀

共計(jì) 8164 個(gè)字符，預(yù)計(jì)需要花費(fèi) 21 分鐘才能閱讀完成。

這篇文章給大家分享的是有關(guān) oracle 虛擬專用數(shù)據(jù)庫的示例分析的內(nèi)容。丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨丸趣 TV 小編過來看看吧。

所謂虛擬專用數(shù)據(jù)庫（VPD）指的是，通過在數(shù)據(jù)庫里進(jìn)行配置，從而讓不同的用戶只能查看某個(gè)表里的部分?jǐn)?shù)據(jù)。VPD 分為以下兩個(gè)級(jí)別。

行級(jí)別：在該級(jí)別下，可以控制某些用戶只能查看到某些數(shù)據(jù)行。比如，對(duì)于銷售數(shù)據(jù)表 sales 來說，每個(gè)銷售人員只能檢索出他自己的銷售數(shù)據(jù)，不能查詢其他銷售人員的銷售數(shù)據(jù)。

列級(jí)別：在該級(jí)別下，可以控制某些用戶不能檢索某個(gè)表的某個(gè)列的值。比如用戶 HR 下的 employees 表中，含有工資（salary）列，由于該列比較敏感，因此不讓其他用戶查詢?cè)摿械闹怠Ｆ渌脩魴z索該列時(shí)，會(huì)發(fā)現(xiàn)其值全都為空（null）。

一、基于行的 VPD

基于行的 VPD 也叫作 Fine-Grained Access Control，簡(jiǎn)稱 FGAC。FGAC 通過定義規(guī)則實(shí)現(xiàn)，規(guī)則的集合叫做 FGAC 政策（policy）。如果對(duì)某個(gè)表設(shè)置了 FGAC，則當(dāng)用戶對(duì)該表發(fā)出查詢或者 DML 語句時(shí)，Oracle 都會(huì)根據(jù)定義的 FGAC 政策，而自動(dòng)改寫這些 SQL 語句。其改寫方式為自動(dòng)在 SQL 語句后面添加 where 條件。

比如，我們?cè)?OE 用戶下有一個(gè)表 sales_list，存放了所有的銷售記錄。每個(gè)銷售人員只能查詢他自己的銷售記錄。于是，我們?cè)?sales 表上設(shè)置 FGAC 政策來實(shí)現(xiàn)這個(gè)業(yè)務(wù)需求。如果某個(gè)銷售人員（假設(shè)其登錄的用戶名為 S0020）發(fā)出下面的查詢語句：

Select * from sales_list ;

當(dāng) Oracle 在執(zhí)行該語句時(shí)，如果發(fā)現(xiàn) sales_list 表上存在 FGAC 政策，于是就會(huì)根據(jù) FGAC 政策，按照如下方式改寫該 SQL 語句：

Select * from sales_list where seller_id= S0020

對(duì)用戶來說，這個(gè)添加 where 條件的過程是完全透明的，用戶并不知道 Oracle 已經(jīng)改寫了他發(fā)出的 SQL 語句，從而過濾了查詢結(jié)果。當(dāng)然，如果該銷售人員發(fā)出的語句為：

Select * from sales_list where values 1000 ;

那么，當(dāng) Oracle 在改寫該 SQL 語句時(shí)，則會(huì)改寫為如下形式：

Select * from sales_list where qty_sold 1000 and seller_id= S0020

使用 FGAC 政策來限定返回記錄的方式具有許多優(yōu)點(diǎn)。比如，不需要改寫應(yīng)用程序、對(duì)用戶完全透明、集中設(shè)置、便于管理等。

在使用 FGAC 時(shí)，會(huì)涉及應(yīng)用程序上下文（Application Context）的概念，使用應(yīng)用程序上下文可以簡(jiǎn)化 FGAC 的實(shí)現(xiàn)。應(yīng)用程序上下文是一個(gè)數(shù)據(jù)庫對(duì)象，可以把它理解為數(shù)據(jù)庫里的每個(gè) session 的全局環(huán)境變量。一旦用戶登錄到數(shù)據(jù)庫，從而創(chuàng)建出 session 以后，應(yīng)用程序上下文就在整個(gè) session 的生命周期里可用。在應(yīng)用程序上下文里可以定義多個(gè)屬性，并為這些屬性設(shè)置具體的值。而用戶不能直接修改屬性的值，只能通過程序包來修改屬性值。應(yīng)用程序上下文總是由用戶 sys 擁有。

比如，對(duì)于前面 sales_list 表的例子來說。我們可以創(chuàng)建一個(gè)應(yīng)用程序上下文，當(dāng)用戶登錄時(shí)，將該用戶的 ID 號(hào)作為一個(gè)屬性值放入該應(yīng)用程序上下文中。然后在定義 FGAC 政策的時(shí)候，將該用戶 ID 號(hào)取出，并作為限定條件短語（也就是 where 條件語句）返回給 Oracle，從而實(shí)現(xiàn) FGAC。

在 Oracle 數(shù)據(jù)庫里，已經(jīng)為每個(gè) session 都預(yù)先建立了一個(gè)應(yīng)用程序上下文：userenv。一旦建立了 session，該 session 就可以使用這個(gè)應(yīng)用程序上下文。在 userenv 中已經(jīng)預(yù)先定義了一些屬性，比如 ip_address、session_user 和 db_name 等。在獲取應(yīng)用程序上下文里的屬性值時(shí)，我們使用 sys_context 函數(shù)。該函數(shù)包含兩個(gè)參數(shù)，第一個(gè)參數(shù)表示應(yīng)用程序上下文的名稱，第二個(gè)參數(shù)表示要顯示的屬性名稱。如下所示：

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

我們也可以創(chuàng)建自己的應(yīng)用程序上下文，如下所示：

SQL  create or replace context sales_ctx using oe.sales_app_pkg;

在這里，sales_ctx 是應(yīng)用程序上下文的名稱，而 sales_app_pkg 則是用來設(shè)置 sales_ctx 里屬性的程序包。在創(chuàng)建應(yīng)用程序上下文時(shí)，指定的、用來設(shè)置其中屬性的程序包可以不必事先存在。但是在為應(yīng)用程序上下文里設(shè)定屬性值時(shí)，該程序包必須存在，否則報(bào)錯(cuò)。如果要?jiǎng)h除應(yīng)用程序上下文，則使用下面的命令：

SQL  drop context sales _ctx;

創(chuàng)建了應(yīng)用程序上下文以后，我們就可以在其中設(shè)置屬性了。在設(shè)置具體的應(yīng)用程序上下文屬性時(shí)，必須使用 Oracle 提供的程序包 dbms_session.set_context 來設(shè)置其屬性。其使用格式為：

dbms_session.set_context (context_name ,  attribute_name ,  attribute_value)

我們只能在程序包里使用 dbms_session.set_context，而不能直接在 SQL*Plus 里調(diào)用。如下所示：

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

我們創(chuàng)建 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;

把執(zhí)行 oe.sales_app_pkg 程序包的權(quán)限賦給所有用戶以后，我們可以測(cè)試應(yīng)用程序上下文是否生效了。

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

可以看到，應(yīng)用程序上下文生效了。接下來，我們創(chuàng)建用于 FGAC 規(guī)則的函數(shù)。

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 /

在這里，我們主要關(guān)注 where_condition 函數(shù)，該函數(shù)會(huì)為 FGAC 規(guī)則返回限定條件。這種 FGAC 規(guī)則函數(shù)必須具有兩個(gè)傳入?yún)?shù)，第一個(gè)參數(shù)表示 schema 名稱，第二個(gè)參數(shù)表示表的名稱。表示對(duì)哪個(gè) schema 下的哪個(gè)表添加 FGAC 規(guī)則。同時(shí)必須返回字符型的值，該返回值會(huì)被 Oracle 自動(dòng)添加到 SQL 語句中的 where 條件部分。不過函數(shù)名稱和參數(shù)名稱可以按照需要進(jìn)行指定。從這里定義的函數(shù) 體中可以看出，如果登錄的用戶名以 S 開頭，則會(huì)受到 FGAC 規(guī)則的限制，where 條件里會(huì)添加 seller_id= Sxxxx，Sxxxx 表示登錄的用戶名。否則，如果以其他用戶的身份登錄，則不會(huì)受到 FGAC 規(guī)則的限制。

創(chuàng)建了用于 FGAC 規(guī)則的函數(shù)以后，我們開始定義 FGAC 規(guī)則。

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 程序包來創(chuàng)建 FGAC 規(guī)則。我們?yōu)橛脩?OE 下的 sales_list 表創(chuàng)建了規(guī)則；該規(guī)則利用用戶 OE 下的 sales_app_pkg.where_condition 函數(shù)返回 where 條件；該規(guī)則作用的 SQL 語句類型為 select 和 update；update_check 參數(shù)說明是否對(duì)更新以后的結(jié)果判斷是否滿足 FGAC 規(guī)則；在創(chuàng)建規(guī)則的同時(shí)，我們也啟用該規(guī)則（enable 設(shè)置為 true）。

創(chuàng)建了 FGAC 規(guī)則以后，我們需要在用戶登錄到應(yīng)用程序的時(shí)候，調(diào)用 sales_app_pkg 程序包里的 set_sales_context 存儲(chǔ)過程來設(shè)置該用戶的應(yīng)用程序上下文里的 seller_id 屬性的值。在實(shí)際應(yīng)用中，我們可以在登錄界面上，當(dāng)用戶單擊登錄按鈕的時(shí)候進(jìn)行設(shè)置。在這里為了演示效果，我們創(chuàng)建一個(gè) 登錄觸發(fā)器來設(shè)置，如下所示：

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 /

現(xiàn)在，我們可以開始測(cè)試 FGAC 規(guī)則的效果了。

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 的身份登錄以后，可以看到，三個(gè)銷售人員各自的數(shù)據(jù)行數(shù)。然后以 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

很明顯看到，我們?cè)O(shè)置的 FGAC 規(guī)則生效了。我們繼續(xù)測(cè)試更新操作：

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

由于我們?cè)趧?chuàng)建 FGAC 規(guī)則時(shí)，指定了 update_check 為 true，當(dāng)用戶 S0010 登錄以后更新 sales_list 表，將 seller_id 從 S0010 更新為 S0020 時(shí)報(bào)錯(cuò)，因?yàn)?S0010 無權(quán)查詢和修改不屬于他的銷售數(shù)據(jù)。如果指定 update_check 為 false，則允許這樣的 update 語句成功。

FGAC 規(guī)則的使用是非常靈活的，其關(guān)鍵就在于 where_condition 函數(shù)的寫法。如果要?jiǎng)h除 FGAC 規(guī)則，則執(zhí)行下面的代碼：

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

對(duì)于某些敏感列來說，比如員工的工資等，我們可以通過創(chuàng)建基于列的 VPD，從而屏蔽這些敏感列，只有具有權(quán)限的用戶才能訪問這些列。

基于列的 VPD 與前面討論的 FGAC 一樣，也是通過設(shè)置政策來實(shí)現(xiàn)的。設(shè)置基于列的 VPD 時(shí)，我們首先需要?jiǎng)?chuàng)建一個(gè)政策所需要用到的函數(shù)，如下所示。

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 /

這里，我們創(chuàng)建了一個(gè)規(guī)則函數(shù)。與 FGAC 規(guī)則一樣，該函數(shù)必須有兩個(gè)傳入?yún)?shù)，第一個(gè)表示要限定的表所屬的 schema 名稱，第二個(gè)表示要限定的表的名稱。在該函數(shù)中，我們定義，如果登錄用戶為表的屬主，則可以查看所有列；否則，登錄用戶不是表所屬的用戶，則不能查看指定列。

至于具體哪些列要被屏蔽，則需要在定義政策時(shí)進(jìn)行指定，如下所示：

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 /

創(chuàng)建基于列 VPD 與創(chuàng)建 FGAC 政策一樣，也是使用 dbms_rls 程序包里的 add_policy 存儲(chǔ)過程。在這里，我們定義了一個(gè)名為 hr_emp_col_policy 的政策。該政策作用在用戶 HR 下的 employees 表上；采用的政策函數(shù)為用戶 HR 下的 hr_col_vpd。
與 FGAC 政策不同的是，我們需要指定另外兩個(gè)參數(shù)：sec_relevant_cols 表示要屏蔽的列的名稱，可以指定多個(gè)列，列與列之間用逗號(hào)隔開；sec_relevant_cols_opt 設(shè)置為 all_rows，則說明對(duì) 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 的身份登錄，執(zhí)行下面的 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

很明顯，對(duì)于用戶 OE 來說，salary 列已經(jīng)被屏蔽了。

感謝各位的閱讀！關(guān)于“oracle 虛擬專用數(shù)據(jù)庫的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！

正文完