程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 在 DB2 9.5 中實現新的安全功能，第 1 部分: 理解 DB2 9.5 中的角色

在 DB2 9.5 中實現新的安全功能，第 1 部分: 理解 DB2 9.5 中的角色

編輯：DB2教程

開始之前

關於本教程

本教程將帶您經歷一系列的練習，以熟悉 DB2 9.5 中的一個新特性：角色。本教程面向的讀者是 DB2 技術專家、數據庫管理員和程序員。您應該對 DB2 on Linux, UNIX, and Windows（以下稱為 DB2 LUW）有良好的理解。您還應該熟悉 DB2 命令窗口和運行 DB2 腳本。也可以使用新的 IBM Data Studio 創建和管理角色。但是，它們在 DB2 Control Center 中不受支持。

目標

本教程將幫助您熟悉 DB2 9.5 中角色的概念和特性。在這些練習中，您將學習：

角色的基本概念

如何創建和管理角色

如何使用不同的 SQL 查詢和工具分析角色的使用

系統需求

要運行本教程中的例子，需要：

DB2 9.5 Express-C

Microsoft Windows 2003、XP 或 Linux（Validated Environment）

Java® Runtime Environment 1.4.2 或更高版本

此外，還應確保硬件滿足 DB2 9.5 的需求。

DB2 9.5 Express C 可通過以上鏈接獲得。DB2 9.5 是一個完整的安裝程序，而不是一個升級補丁包。默認情況下，除非明確要求不自動啟動，否則 DB2 將在安裝後自動啟動。

請使用本教程附帶的 zip 文件中提供的示例腳本和數據來演示本教程中的概念。將 zip 文件解壓到一個名為 DB2Roles 的子目錄中（C:\DB2Roles 或 home/userid/DB2Roles）。本教程通篇將該目錄簡稱為 DB2Roles。本教程假設您將 DB2 安裝到默認目錄中。本教程要求創建很多用戶 id，所有練習都將使用這些創建的 id。

角色的定義

角色是一個數據庫對象，可以對其授予或撤銷一個或多個 DB2 特權、權限或其他角色。角色沒有所有者，它只能由安全管理員（SECADM）創建或刪除。

通過將一個角色與一個用戶相關聯，該用戶除了已有的特權以外，還將繼承該角色所具有的所有特權。

數據庫角色的主要優點是簡化了對數據庫中的特權的管理。例如：

安全管理員可以在接近於組織結構的抽象層次上控制對數據庫的訪問。例如，如果公司有 12 個分公司，每個分公司中的每個人都有一組相同的特權，那麼 SECADM 可以設置 12 種角色，然後根據用戶的位置為用戶授予成員資格。

為用戶授予角色中的成員資格時，以用戶的工作職責為依據。當用戶的工作職責變化時（這種情況在大型組織中經常發生），很容易授予和撤銷用戶的角色成員資格。例如，如果用戶從 New York 分公司調到 Boston 分公司，那麼 SECADM 只需撤銷他的 New York 分公司的角色，並為他授予 Boston 分公司的角色。

特權的分配也很簡單。管理員無需將同一組特權授給具有同一職能的每個用戶，而只需將這組特權授給一個表示該職能的角色，然後將這個角色授給具有該職能的用戶。例如，一個任務常常會要求一個用戶有很多不同的特權。但是，如果將特權授給一個角色，那麼就很容易授予或撤銷特權，而不必為每個任務維護大量的腳本。如果 SECADM 需要修改一個角色的特權，他可以只在一個地方進行修改，而不必為所有用戶重復這個過程。

通過更新角色，可以避免逐個地為每個用戶更新特權。例如，如果 SECADM 需要修改 New York 分公司的特權，那麼她可以修改角色的定義，而不必為所有用戶重復該過程。

除了 SECADM 之外，數據庫中所有可以被授予的 DB2 特權和權限都可以被授給一個角色。通過只將特權和權限授給角色，並在角色中建立用戶成員，可以大大簡化數據庫中特權的管理。

構建環境

本節介紹一些示例代碼（這些代碼可從本教程下載），以描述角色的行為。這些示例代碼基於可添加到 SAMPLE 數據庫中的數據庫對象。填充這些表所需的數據位於一個有分隔符的 ASCII 文件中，該文件的文件名為 data.del，它位於 DB2Roles 子目錄中。所有腳本都在子目錄 Section2 中。

要在 DB2 命令窗口中運行腳本，使用：

db2　–tvf　scriptname.sql

批處理腳本（後綴為 .bat）可以通過輸入名稱來運行。

說明

首先，使用 administrator/root 用戶 id 登錄到系統，然後創建以下用戶 id：

用於 SECADM 的 DB2SEC

將用於管理小組的 MARK、ALLY 和 SAUL

ADAM、DEBS、PETE、YANG、MARY 和 ANNE

ROSE、STAN、ALAN、LORI、EVAN 和 KLEM

這些用戶 id 不需要任何特殊的權限，因為它們將只用於 DB2 數據庫。所有需要的特權和權限將使用 SQL 發出。另外還需要一個名為 Pension_gp 的組。將 ROSE 放進這個組。除此之外不需要設置其他的組權限。如果使用 Windows，那麼應確保用戶不在 Administrators 組中。

下面的圖展示了公司和練習中將用到的角色的層次結構：

圖 1. 角色結構
在 DB2 9.5 中實現新的安全功能，第 1 部分: 理解 DB2 9.5 中的角色

注意，有些名稱出現在兩個框中，這是因為有些人身兼兩個部門的工作，因此需要處於不止一個角色中。 KLEM 是一個新員工，因此沒有被分配任何角色。

在後面的練習中，DB2 數據庫管理員為 DB2inst1，數據庫為 SAMPLE 數據庫。所有腳本都使用用戶 id DB2inst1 和密碼 “passWord”。如果您使用不同的數據庫或數據庫管理 id（DBA），那麼必須編輯這些腳本，使之與您自己的系統相符。注意確認 “Sample” 或其他可使用的數據庫已經存在。如果不存在，請使用 DB2SAMPL 命令創建 SAMPLE 數據庫。

現在，在這組練習中，將新用戶和表添加到該數據庫中：

將用戶添加到數據庫中。

以 DBA 身份連接到數據庫，並授予以下特權：

清單 1. 將特權授給數據庫用戶
GRANT　CONNECT　ON　DATABASE　TO　USER　MARK; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　ALLY; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　ADAM; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　DEBS; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　PETE; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　YANG; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　SAUL; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　MARY; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　ANNE; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　ROSE; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　STAN; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　ALAN; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　LORI; 　 GRANT　CONNECT　ON　DATABASE　TO　USER　EVAN; 　 GRANT　SECADM　ON　DATABASE　TO　USER　DB2SEC;
或者使用 AddUsers.sql 腳本添加這些新用戶。

使用 luwebase.sql 腳本為 SAMPLE 數據庫創建新表。這裡假設將表放在 USERSPACE1 中。
下面添加基本表：

清單 2. 添加基本表
LE_DEPARTMENT　 LE_EMPLOYEE　 LE_EMP_PHOTO　 LE_EMP_RESUME　 LE_ORG　 LE_SALES　 LE_PENSIONS

使用 luweload.sql 腳本將數據裝載到新表中。如果使用 Linux，或者改動了裝載文件的位置，那麼必須編輯該腳本。

接著，運行 luweplus.sql 腳本，以創建其他的視圖、索引等數據庫對象。

在運行該 SQL 腳本時，注意檢查錯誤和警告。可能還需要使用 Control Center 或其他工具查看這些對象，以確認這些表和數據已經存在。

設置和配置角色

在本節中，您將學習如何：

創建新角色

將特權分配給角色

將成員資格授給角色

管理角色中的成員資格的權限屬於 SECADM。SECADM 使用標准的 DML 命令管理角色。

用於角色的所有 DML 命令都可以嵌入到應用程序中，或者通過使用動態的 SQL 語句發出。它是一條可執行語句，只有當 DYNAMICRULES 運行行為對包有效時，才可以動態地預置該語句。

CREATE ROLE 語句在當前服務器上定義一個角色。角色必須有一個惟一的標識名稱：

CREATE　ROLE　*role-name*

DROP ROLE 語句刪除當前服務器上的一個角色。該角色必須已經存在於當前服務器上，而且，如果角色當前正被用於作為一個連接屬性（SESSION_USER）或可信上下文，那麼不能被刪除。只有 SECADM 可以刪除角色。

DROP　ROLE　*role-name*

GRANT 和 REVOKE ROLE 語句用於向用戶、組或其他角色授予或撤銷角色。它們還可以與 WITH ADMIN OPTION 一起用於授予權限。WITH ADMIN OPTION 允許指定的授權 ID 向其他授權 ID 授予或撤銷角色。例如：

清單 3. 向其他授權 ID 授予或撤銷角色
GRANT　ROLE　*role-name*　TO　*authorization-name*　　 GRANT　ROLE　*role-name*　TO　*authorization-name*　　　WITH　ADMIN　OPTION　　 REVOKE　ROLE　*role-name*　FROM　*authorization-name*　　 REVOKE　ADMIN　OPTION　FOR　ROLE　*role-name*　　　FROM　*authorization-name*　

COMMENT ON 語句可用於在系統編目中提供對 ROLE 的描述。例如：

COMMENT　ON　ROLE　*role-name*　IS　*'comment'*　

本節所需的腳本在子目錄 Section3 中。

所有包含關於用戶或組的詳細信息的系統編目視圖都已經被更新，以包括新的角色。在 DB2 9.5 中，有兩個新的視圖顯示角色的詳細信息：

SYSCAT.ROLES：該視圖對於每個定義的角色有一行記錄，其中包含角色的標識符，另外還有創建日期和相關的審計策略信息。

SYSCAT.ROLEAUTH：對於每個已被授予角色的用戶、組或角色，無論角色上是否授予了權限，該視圖都有一行記錄。

為了簡化角色的管理，DB2 9.5 提供了一個名為 AUTH_LIST_ROLES_FOR_AUTHID 的系統函數，該函數使管理員可以確認哪些角色被授給了一個用戶、組或角色。（該函數將在下一節 “維護和管理角色” 中描述和演示。）

角色本身也是一個數據庫對象，它可以用於定義其他數據庫對象（例如表、視圖、觸發器和可信上下文）上的權限。可以將角色指定給用戶，使之獲得訪問數據庫中的數據的權限。在本教程中，學習創建一個角色層次結構，以標識圖 1 展示的小公司的結構。

您將創建以下基本角色：

Sales: 用於銷售部門

Mktg: 用於市場營銷部門

Admin: 用於管理部門

Payroll: 用於薪資部門

Pension: 用於 Yang，他是津貼管理員

SalesMgr: 包括角色 Sales 和 Mktg

AdminMgr: 包括角色 Admin 和 Payroll

CEO: 包括角色 AdminMgr 和 SalesMgr

說明

使用您在上一節創建的安全管理員用戶 id 和密碼登錄到系統。將當前目錄更改為 Section3。

使用以下代碼創建基本角色：

清單 4. 創建基本角色
CREATE　ROLE　Sales; 　 CREATE　ROLE　Mktg; 　 CREATE　ROLE　Admin; 　 CREATE　ROLE　Payroll; 　 CREATE　ROLE　Pension; 　 CREATE　ROLE　SalesMgr; 　 CREATE　ROLE　AdminMgr; 　 CREATE　ROLE　CEO;

或者使用 CreateRoles.sql 腳本創建角色。

下面的特權表顯示了被授給這些角色的特權：

表 1. 特權角色特權表　 SELECT LE_ORG、LE_SALES Sales UPDATE、INSERT LE_SALES Mktg SELECT LE_ORG、LE_SALES 　 SELECT LE_DEPARTMENT、LE_EMPLOYEE、LE_ORG、LE_SALES Admin UPDATE LE_EMPLOYEE、LE_DEPARTMENT 　 SELECT LE_DEPARTMENT、LE_EMPLOYEE、LE_EMP_PHOTO、LE_EMP_RESUME、 LE_PENSIONS Payroll UPDATE、INSERT、DELETE LE_EMPLOYEE Pension SELECT、UPDATE、INSERT、DELETE、ALTER LE_PENSIONS

使用以下命令將特權授給角色：

清單 5. 將特權授給角色
GRANT　SELECT　ON　TABLE　db2inst1.le_org　TO　ROLE　Sales;　 GRANT　SELECT,　UPDATE,　INSERT　ON　TABLE　db2inst1.le_sales　TO　ROLE　Sales;　 GRANT　SELECT　ON　TABLE　db2inst1.le_org　TO　ROLE　Mktg;　 GRANT　SELECT　ON　TABLE　db2inst1.le_sales　TO　ROLE　Mktg;　 GRANT　SELECT　ON　TABLE　db2inst1.le_department　TO　ROLE　Admin;　 GRANT　SELECT　ON　TABLE　db2inst1.le_employee　TO　ROLE　Admin;　 GRANT　SELECT　ON　TABLE　db2inst1.le_org　TO　ROLE　Admin;　 GRANT　SELECT　ON　TABLE　db2inst1.le_sales　TO　ROLE　Admin;　 GRANT　UPDATE　ON　TABLE　db2inst1.le_department　TO　ROLE　Admin;　 GRANT　UPDATE　ON　TABLE　db2inst1.le_employee　TO　ROLE　Admin;　 GRANT　SELECT　ON　TABLE　db2inst1.le_department　TO　ROLE　Payroll;　 GRANT　SELECT　ON　TABLE　db2inst1.le_emp_photo　TO　ROLE　Payroll;　 GRANT　SELECT　ON　TABLE　db2inst1.le_emp_resume　TO　ROLE　Payroll;　 GRANT　SELECT　ON　TABLE　db2inst1.le_pensions　TO　ROLE　Payroll;　 GRANT　UPDATE,　INSERT,　DELETE　ON　TABLE　db2inst1.le_employee　TO　ROLE　Payroll;　 GRANT　SELECT,　UPDATE,　INSERT,　DELETE,　ALTER　ON　TABLE　db2inst1.le_pensions　 TO　ROLE　Payroll;

或者使用 AssignRoles.sql 腳本腳本創建角色。

現在需要將個人放到他們各自的角色中。

使用以下命令授予角色的成員資格：

清單 6. 授予角色的成員資格
GRANT　ROLE　SalesMgr,　AdminMgr　to　ROLE　CEO;　 GRANT　ROLE　CEO　TO　USER　Mark; 　 GRANT　ROLE　Admin,　Payroll　to　ROLE　AdminMgr;　 GRANT　ROLE　AdminMgr　TO　USER　Ally; 　 GRANT　ROLE　Sales,　Mktg　to　ROLE　SalesMgr;　 GRANT　ROLE　SalesMgr　TO　USER　Saul; 　 GRANT　ROLE　Admin　TO　USER　Adam,　Debs,　Mary; 　 GRANT　ROLE　Mktg　TO　USER　Mary,　Anne,　Rose; 　 GRANT　ROLE　Payroll　TO　USER　Adam,　Pete,　Yang; 　 GRANT　ROLE　Pension　TO　USER　Yang; 　 GRANT　ROLE　Sales　TO　USER　Stan,　Alan,　Lori,　Evan;

或者使用 AssignUsers.sql 腳本授予角色的成員資格。

現在有了基本結構，接下來可以運行一些查詢，以驗證屬於用戶的特權和權限。

以 Anne 的身份連接到 SAMPLE 數據庫，並運行兩個查詢：

CONNECT　TO　SAMPLE　USER　Anne　USING　passWord 　 SELECT　*　FROM　db2inst1.le_org　　 SELECT　*　FROM　db2inst1.le_employee　

或者使用 AnneQuerIEs.sql 腳本運行查詢。將會執行 ORG 表的 SELECT，但是 Anne 不能訪問 EMPLOYEE 表，因為 Anne 只屬於角色 MKTG。輸出應該如下所示：

清單 7. 查詢輸出
CONNECT　TO　SAMPLE　USER　Anne　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　ANNE　　Local　database　alias　　=　SAMPLE　　　 SELECT　*　FROM　db2inst1.le_org　　 DEPTNUMB　DEPTNAME　　　　MANAGER　DIVISION　　LOCATION　 --------　--------------　-------　----------　-------------　 C21　　　Head　Office　　C01　　　Corporate　London　 C21　　　Eastern　　　　C01　　　Finance　　VIEnna　 C21　　　Western　　　　C01　　　Finance　　Paris　 .　 .　 .　　　9　record(s)　selected.　　　 SELECT　*　FROM　db2inst1.le_employee　 SQL0551N　"ANNE"　does　not　have　the　privilege　to　perform　Operation　 "SELECT"　on　object　"DB2INST1.LE_EMPLOYEE".　SQLSTATE=42501

將用戶改為 Mary，重復上述查詢：

CONNECT　TO　SAMPLE　USER　Mary　USING　passWord 　 SELECT　*　FROM　db2inst1.le_org　　 SELECT　*　FROM　db2inst1.le_employee　

對於 Mary，這兩個查詢都可以執行，因為她同時屬於 MKTG 和 ADMIN 角色。因此，您可以看到 le_employee 表的內容（參見清單 8）。

以 Debs 的身份連接到 SAMPLE 數據庫，並運行兩個查詢： CONNECT　TO　SAMPLE　USER　Debs　USING　passWord　 SELECT　*　FROM　db2inst1.le_employee　 SELECT　*　FROM　db2inst1.le_pensions　

或者使用 DebsQuerIEs.sql 腳本運行查詢。將會執行 EMPLOYEE 表的 SELECT，但是 Debs 不能訪問 PENSIONS 表，因為 Debs 只屬於角色 Admin（參見清單 9）。

將用戶改為 Adam，重復上述查詢： CONNECT　TO　SAMPLE　USER　Adam　USING　passWord　 SELECT　*　FROM　db2inst1.le_employee　 SELECT　*　FROM　db2inst1.le_pensions　

對於 Adam，這兩個查詢都可以執行，因為他同時屬於 PAYROLL 和 ADMIN 角色。因此，可以看到 le_pensions 表的內容（參見清單 10）。

可選地，將用戶改為 Mark（CEO 角色）。他可以運行所有查詢。

維護和管理角色

僅從本教程還難以看出角色為數據庫管理帶來的好處。但是，如果考慮一個有數千名雇員、數百種角色的大型公司，那麼這種好處就很明顯了。例如，如果該公司平均每個分公司有 20 個銷售代表，每個地區或州有 10 個分公司，銷售網絡中一共有 50 個地區，那麼總共就有 10,000 名銷售代表，而且很可能還有 1,000 名相關的雇員。在之前版本的 DB2 中，必須將特權授給每個人，或者在數據訪問的安全性方面有所妥協。假設每個分公司、地區和支持角色都有不同的角色；您仍然有超過 500 種角色。然而，當有人加入或離開公司，或者調動位置時，只需授予或撤銷一個角色。假設耗損率為 5%，那麼一年中仍然有超過 500 次變化。但是，如果將這項管理任務委派到地區或分公司這兩級，那麼這兩級上的管理任務就容易得多了。

在本節中，您將看到：

WITH ADMIN OPTION

撤銷角色權限的效果

查詢成員資格權限

說明

使用您在本教程 “構建環境” 小節中創建的安全管理員用戶 id 和密碼登錄到系統。將當前目錄改為 Section4。

首先，我們來探索撤銷角色成員資格的一些效果：

使用 Mary 身份連接到 SAMPLE 數據庫。運行 SALES 與 EMPLOYEE 表之間的一個連接，然後創建這兩個表上的一個視圖：

清單 11. 測試 Mary 的權限
CONNECT　TO　SAMPLE　USER　Mary　USING　passWord　 SELECT　e.firstnme,　e.lastname,　SUM(s.sales)　　FROM　db2inst1.le_employee　e,　db2inst1.le_sales　s　　WHERE　e.firstnme=s.sales_person　　GROUP　BY　e.firstnme,　e.lastname　 CREATE　VIEW　total_sales　(fname,　lname,　sales)　　AS　SELECT　e.firstnme,　e.lastname,　SUM(s.sales)　　FROM　db2inst1.le_employee　e,　db2inst1.le_sales　s　　WHERE　e.firstnme=s.sales_person　　GROUP　BY　e.firstnme,　e.lastname

或者使用 Mary01.sql 腳本運行 SQL。 SELECT 應該產生一個銷售人員銷售列表，create vIEw 語句將該數據復制為一個視圖。這些命令可以執行，因為 Mary 屬於 Admin 和 Sales 角色。

清單 12. Mary 測試的結果
CONNECT　TO　SAMPLE　USER　Mary　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　MARY　　Local　database　alias　　=　SAMPLE　　　 SELECT　e.firstnme,　e.lastname,　　 SUM(s.sales)　as　Total_Sales　 FROM　db2inst1.le_employee　e,　db2inst1.le_sales　s　WHERE　e.firstnme=s.sales_person　　 GROUP　BY　e.firstnme,　e.lastname　　 FIRSTNME　　　LASTNAME　　　　TOTAL_SALES　 ------------　---------------　-----------　 ALAN　　　　　OMAHONY　　　　　　　　　　8　 EVAN　　　　　WELSH　　　　　　　　　　34　 LORI　　　　　VERLOAD　　　　　　　　　57　 STAN　　　　　DELIVER　　　　　　　　　64　　　4　record(s)　selected.　　　 CREATE　VIEW　total_sales　(fname,　lname,　sales)　AS　SELECT　e.firstnme,　e.lastname,　 SUM(s.sales)　 FROM　db2inst1.le_employee　e,　db2inst1.le_sales　s　WHERE　e.firstnme=s.sales_person　 GROUP　BY　e.firstnme,　e.lastname　 DB20000I　The　SQL　command　completed　successfully.

通過角色成員資格繼承的權限或特權與直接授予的權限或特權完全一樣。

現在，從 Admin 角色中刪除 Mary，並嘗試通過 SELECT 語句使用 Mary 創建的視圖：

清單 13. 撤銷 Mary 的 ADMIN 角色
CONNECT　TO　SAMPLE　USER　db2sec　USING　passWord 　 REVOKE　ROLE　admin　FROM　Mary 　 CONNECT　TO　SAMPLE　USER　Mary　USING　passWord 　 SELECT　e.firstnme,　e.lastname,　SUM(s.sales)　　FROM　db2inst1.le_employee　e,　db2inst1.le_sales　s　　WHERE　e.firstnme=s.sales_person　　GROUP　BY　e.firstnme,　e.lastname 　 SELECT　*　FROM　total_sales

或者使用 Mary02.sql 腳本運行 SQL。 SELECT 失敗了，因為 Mary 已經不屬於 Admin 角色，因此不再具有 LE_EMPLOYEE 上的選擇權限。第二個 SELECT 也失敗了，因為該視圖被標記為無效。

清單 14. Mary 不再能夠訪問 ADMIN 表
CONNECT　TO　SAMPLE　USER　DB2SEC　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　DB2SEC　　Local　database　alias　　=　SAMPLE　　　 REVOKE　ROLE　Admin　FROM　Mary　 DB20000I　The　SQL　command　completed　successfully.　　 CONNECT　TO　SAMPLE　USER　Mary　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　MARY　　Local　database　alias　　=　SAMPLE　　　 SELECT　e.firstnme,　e.lastname,　SUM(s.sales)　as　Total_Sales　 FROM　db2inst1.le_employee　e,　db2inst1.le_sales　s　 WHERE　e.firstnme=s.sales_person　GROUP　BY　e.firstnme,　e.lastname　 SQL0551N　"MARY"　does　not　have　the　privilege　to　perform　Operation　"SELECT"　 on　object　"DB2INST1.LE_EMPLOYEE".　SQLSTATE=42501　　 SELECT　*　FROM　total_sales　 SQL0575N　VIEw　or　materialized　query　table　"MARY.TOTAL_SALES"　cannot　be　used　 because　it　has　been　marked　inOperative.　SQLSTATE=51024　

要委托 GRANT/REVOKE 權限，可以使用 WITH ADMIN OPTION。

SECADM 為 Stan 授予 SALES 角色的 ADMIN OPTION，使他可以處理所有新的 Salespeople。Stan 將訪問權授給新的銷售員 Klem。測試 Klem 的訪問權。

清單 15. 委托 ADMIN OPTION
CONNECT　TO　SAMPLE　USER　db2sec　USING　passWord 　 GRANT　ROLE　sales　TO　Stan　WITH　ADMIN　OPTION 　 CONNECT　TO　SAMPLE　USER　Stan　USING　passWord 　 GRANT　ROLE　sales　TO　Klem　WITH　ADMIN　OPTION

或者使用 Stan01.sql 腳本運行 SQL。第一個 grant 成功，但是 Stan 不能將該權限傳遞給其他用戶。因此，第二個 grant 失敗。所以只是將普通訪問權授給 Klem。

清單 16. 只有 SECADM 可以委托 ADMIN OPTION
CONNECT　TO　SAMPLE　USER　DB2SEC　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　DB2SEC　　Local　database　alias　　=　SAMPLE　　　 GRANT　ROLE　sales　TO　Stan　WITH　ADMIN　OPTION　 DB20000I　The　SQL　command　completed　successfully.　　 CONNECT　TO　SAMPLE　USER　Stan　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　STAN　　Local　database　alias　　=　SAMPLE　　　 GRANT　ROLE　sales　TO　Klem　WITH　ADMIN　OPTION　 DB21034E　The　command　was　processed　as　an　SQL　statement　because　it　was　not　a　 valid　Command　Line　Processor　command.　　 During　SQL　processing　it　returned:　 SQL0551N　"STAN"　does　not　have　the　privilege　to　perform　Operation　"GRANT"　on　 object　"SALES".　SQLSTATE=42501　

將普通訪問權授給 Klem：

清單 17. 測試委托的權力
CONNECT　TO　SAMPLE　USER　Stan　USING　passWord 　 GRANT　ROLE　sales　TO　Klem 　 CONNECT　TO　SAMPLE　USER　Klem　USING　passWord 　 SELECT　*　FROM　db2inst1.le_sales

或者使用 Stan02.sql 腳本運行 SQL。Klem 現在是 Sales 角色的成員，他擁有作為銷售員的新角色的所有必要的權力。

清單 18. 權限委托成功
CONNECT　TO　SAMPLE　USER　Stan　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　STAN　　Local　database　alias　　=　SAMPLE　　　 GRANT　ROLE　sales　TO　Klem　 DB20000I　The　SQL　command　completed　successfully.　　 CONNECT　TO　SAMPLE　USER　Klem　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　KLEM　　Local　database　alias　　=　SAMPLE　　　 SELECT　*　FROM　db2inst1.le_sales　　 SALES_DATE　SALES_PERSON　　REGION　　　　　SALES　 ----------　---------------　---------------　-----------　 31/12/2005　STAN　　　　　　London　　　　　　　　　　7　 29/03/2006　STAN　　　　　　London　　　　　　　　　　3　 30/03/2006　STAN　　　　　　London　　　　　　　　　　8　 .　 .　 .　 .　 .

如果 Stan 退休了，對 Klem 有什麼影響？將 Stan 從數據庫 SAMPLE 中刪除，並測試 Klem 的訪問權：

清單 19. 撤銷 Stan 的訪問權
CONNECT　TO　SAMPLE　USER　db2sec　USING　passWord 　 REVOKE　ROLE　sales　FROM　Stan 　 CONNECT　TO　SAMPLE　USER　Klem　USING　passWord 　 SELECT　*　FROM　db2inst1.le_sales

或者使用 Stan03.sql 腳本運行 SQL。 Stan 不再具有 Sales 角色的成員資格，Klem 仍保留所有必要的權限。

清單 20. Klem 仍然擁有完整訪問權
CONNECT　TO　SAMPLE　USER　DB2SEC　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　DB2SEC　　Local　database　alias　　=　SAMPLE　　　 REVOKE　ROLE　sales　FROM　Stan　 DB20000I　The　SQL　command　completed　successfully.　　 CONNECT　TO　SAMPLE　USER　Klem　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　KLEM　　Local　database　alias　　=　SAMPLE　　　 SELECT　*　FROM　db2inst1.le_sales　　 SALES_DATE　SALES_PERSON　　REGION　　　　　SALES　 ----------　---------------　---------------　-----------　 31/12/2005　STAN　　　　　　London　　　　　　　　　　7　 29/03/2006　STAN　　　　　　London　　　　　　　　　　3　 30/03/2006　STAN　　　　　　London　　　　　　　　　　8　 .　 .　 .

如果管理員需要查詢角色，那麼他可以查詢編目 SYSCAT.ROLES 和 SYSCAT.ROLEAUTH 中的視圖。但是，DB2 9.5 提供了一個名為 AUTH_LIST_ROLES_FOR_AUTHID 的函數，以便查詢角色的使用情況。該函數有兩個參數 AUTHID 和 TYPE。本練習使用 AUTH_LIST_ROLES_FOR_AUTHID 函數找出授給用戶（TYPE）Ally（AUTHID）的角色以及授給角色（TYPE）CEO（AUTHID）的角色：

清單 21. 查詢 ROLES
CONNECT　TO　SAMPLE　USER　db2sec　USING　passWord 　 SELECT　GRANTOR,　GRANTORTYPE,　GRANTEE,　GRANTEETYPE,　ROLENAME,　CREATE_TIME,　ADMIN　　FROM　TABLE　(SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID　('ALLY',　'U')　)　AS　T 　 SELECT　GRANTOR,　GRANTORTYPE,　GRANTEE,　GRANTEETYPE,　ROLENAME,　CREATE_TIME,　ADMIN　　FROM　TABLE　(SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID　('CEO',　'R')　)　AS　T

或者使用 Admin01.sql 腳本運行 SQL。另外還有一種 TYPE 是 ‘G’，它表示組。

清單 22. 用戶 Ally 和角色 CEO 的權限
Roles　For　Ally　 DB2SEC　U　ADMINMGR　R　ADMIN　2007-11-29-17.14.54.765001　N　 DB2SEC　U　ADMINMGR　R　PAYROLL　2007-11-29-17.14.54.781002　N　 DB2SEC　U　ALLY　U　ADMINMGR2007-11-29-17.14.54.828002　N　　 Roles　For　CEO　 DB2SEC　U　SALESMGR　R　SALES　2007-11-29-17.14.54.718000　N　 DB2SEC　U　SALESMGR　R　MKTG　2007-11-29-17.14.54.734002　N　 DB2SEC　U　ADMINMGR　R　ADMIN　2007-11-29-17.14.54.765001　N　 DB2SEC　U　ADMINMGR　R　PAYROLL　2007-11-29-17.14.54.781002　N　 DB2SEC　U　CEO　　　　　R　SALESMGR2007-11-29-17.14.54.812002　N　 DB2SEC　U　CEO　　　　　R　ADMINMGR2007-11-29-17.14.54.828002　N

（可選）比較角色和組

在本節中，探索角色相對於組的優點。與組相比，在 DB2 中使用角色的主要優點是，角色是由數據庫管理的。因此，DB2 知道角色中的成員何時變化，並且可以考慮對於所有操作的角色。而組卻不是這樣，因為組是在數據庫之外管理的，受操作系統或 LDAP 服務器（包括 Microsoft Active Directory）的控制。因此，在執行權限檢查時，DB2 對於何時考慮授給組的特權和權限有所限制。這些限制包括，在創建以下對象時不考慮組：

視圖

觸發器

物化查詢表（Materialized Query Table，MQT）

包含靜態 SQL 的包

SQL 例程

我們通過一個視圖和觸發器來驗證一下。

用於填充這些表的腳本放在一個帶分隔符的 ASCII 文件中，該文件的文件名為 data.del，位於 DB2Roles 子目錄中。

說明

使用您在 “構建環境” 中創建的安全管理員用戶 id 和密碼登錄到系統。將當前目錄更改為 Section5。

使用以下命令創建表 LE_PEN_DELETES：

CREATE　TABLE　db2inst1.le_pen_deletes　　(X　int,　　D_DATE　date)　

或者使用 Yang01.sql 腳本運行 SQL。

清單 23. 創建的 le_pen_deletes 表
CONNECT　TO　SAMPLE　USER　DB2INST1　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　DB2INST1　　Local　database　alias　　=　SAMPLE　　　 CREATE　TABLE　db2inst1.le_pen_deletes　(User　　　VARCHAR(128),　D_DATE　　DATE)　 DB20000I　The　SQL　command　completed　successfully.

使用 SECADM 用戶 DB2SEC 將 INSERT 特權授給角色 PENSION：

GRANT　INSERT　ON　db2inst1.le_pen_deletes　TO　ROLE　Pension　

或者使用 Yang02.sql 腳本運行 SQL。

清單 24. 授予表上的 INSERT 特權
CONNECT　TO　SAMPLE　USER　DB2INST1　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　DB2INST1　　Local　database　alias　　=　SAMPLE　　　 GRANT　INSERT　ON　db2inst1.le_pen_deletes　TO　ROLE　Pension　 DB20000I　The　SQL　command　completed　successfully.

使用用戶 Yang 創建觸發器 PENS_TRG，每當 LE_PENSIONS 中有一行被刪除時，該觸發器就將一行記錄插入到 LE_PEN_DELETES 中：

清單 25. 使用 ROLE 權限創建觸發器
CREATE　TRIGGER　PENS_TRG　　AFTER　DELETE　on　db2inst1.le_pensions　　FOR　EACH　STATEMENT　　MODE　DB2SQL　　INSERT　INTO　db2inst1.le_pen_deletes　　VALUES　(1,　Current　Date)　

或者使用 Yang03.sql 腳本運行 SQL。

該觸發器應該成功完成。

清單 26. 成功創建觸發器
CONNECT　TO　SAMPLE　USER　Yang　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　YANG　　Local　database　alias　　=　SAMPLE　　　 CREATE　TRIGGER　pens_trg　　　　　AFTER　DELETE　ON　db2inst1.le_pensions　　　　　FOR　EACH　STATEMENT　　　　　MODE　DB2SQL　　　　　INSERT　INTO　db2inst1.le_pen_deletes　　　　　　　　　VALUES　(　Current　User,　Current　Date)　 DB20000I　The　SQL　command　completed　successfully.　

Test it using the following code:

DELETE　FROM　db2inst1.le_pensions　　WHERE　EMPNO="012213"　 SELECT　*　FROM　db2inst1.le_pen_deletes　

或者使用 Yang04.sql 腳本運行 SQL。

清單 27. 測試觸發器
CONNECT　TO　SAMPLE　USER　Yang　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　YANG　　Local　database　alias　　=　SAMPLE　　　 DELETE　FROM　db2inst1.le_pensions　WHERE　EMPNO='012213'　 DB20000I　The　SQL　command　completed　successfully.　　　 CONNECT　TO　SAMPLE　USER　db2inst1　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　DB2INST1　　Local　database　alias　　=　SAMPLE　　　 SELECT　*　FROM　db2inst1.le_pen_deletes　　 USER　D_DATE　 -------　----------　 YANG　30/11/2007

接下來試著使用組來重復上述工作。在 “構建環境” 小節中，您應該創建了一個名為 PENSION_GP 的組，而且 ROSE 應該是這個組的一個成員。Rose 屬於角色 MKTG，因此不能訪問任何與 Pension 角色相關的數據庫對象。

將權限授給組 PENSION_GP，使這個組可以操縱 pension 表：

清單 28. 為組授予權限
GRANT　CONNECT　ON　DATABASE　TO　GROUP　Pension_gp　 GRANT　INSERT,　UPDATE,　ALTER,　DELETE,　SELECT　ON　db2inst1.le_pen_deletes　 TO　GROUP　Pension-gp　 GRANT　DELETE　ON　db2inst1.le_pensions　TO　GROUP　Pension_gp　

或者使用 Rose01.sql 腳本運行 SQL。

清單 29. 授予的組權限
CONNECT　TO　SAMPLE　USER　db2inst1　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　DB2INST1　　Local　database　alias　　=　SAMPLE　　　 GRANT　CONNECT　ON　DATABASE　TO　GROUP　Pensions_gp　 DB20000I　The　SQL　command　completed　successfully.　　 GRANT　INSERT　ON　db2inst1.le_pen_deletes　TO　GROUP　Pension_gp　 DB20000I　The　SQL　command　completed　successfully.　　 GRANT　INSERT,　UPDATE,　ALTER,　DELETE,　SELECT　ON　db2inst1.le_pen_deletes　 TO　GROUP　Pension_gp　 DB20000I　The　SQL　command　completed　successfully.

使用用戶 Rose 創建觸發器 PENS_TRG_F，每當 LE_PENSIONS 中有一行被刪除時，該觸發器就將一行記錄插入到 LE_PEN_DELETES 中：

清單 30. 使用 GROUP 權限創建觸發器
CREATE　TRIGGER　PENS_TRG_F　　AFTER　DELETE　on　db2inst1.le_pensions　　FOR　EACH　STATEMENT　　MODE　DB2SQL　　INSERT　INTO　db2inst1.le_pen_deletes　　VALUES　(1,　Current　Date)

或者使用 Rose02.sql 腳本運行 SQL。

這條語句應該生成以下錯誤消息：

清單 31. 觸發器創建失敗
CONNECT　TO　SAMPLE　USER　Rose　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　ROSE　　Local　database　alias　　=　SAMPLE　　　 CREATE　TRIGGER　pens_trg_f　　　　　AFTER　DELETE　ON　db2inst1.le_pensions　　　　　FOR　EACH　STATEMENT　　　　　MODE　DB2SQL　　　　　INSERT　INTO　db2inst1.le_pen_deletes　　　　　　　　　VALUES　(　Current　User,　Current　Date)　 DB21034E　The　command　was　processed　as　an　SQL　statement　because　it　was　not　a　 valid　Command　Line　Processor　command.　During　SQL　processing　it　returned:　 SQL0551N　"ROSE"　does　not　have　the　privilege　to　perform　Operation　"ALTER　TABLE"　 on　object　"DB2INST1.LE_PENSIONS".　LINE　NUMBER=2.　SQLSTATE=42501　

雖然組 Pension_gp 擁有特權 “ALTER”，而且 Rose 也在這個組中，但是她不能創建觸發器，因為組權限不被考慮。

現在，使用用戶 Yang 創建視圖 PENS_VIEW，以查看 LE_PENSIONS 表中被選中的信息，並使用該視圖查詢數據：

清單 32. 使用 ROLE 權限創建視圖
CREATE　VIEW　PENS_VIEW　AS　　　(SELECT　DISTINCT(job),　SUM(base),　SUM(avc)　　FROM　db2inst1.le_pensions　　GROUP　BY　JOB) 　 SELECT　*　FROM　PENS_VIEW

或者使用 Yang05.sql 腳本運行 SQL。

該視圖應該成功完成，您可以從這個新視圖中選擇數據。

清單 33. 視圖創建成功
CONNECT　TO　SAMPLE　USER　Yang　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　YANG　　Local　database　alias　　=　SAMPLE　　　 CREATE　VIEW　pens_vIEw　(career,　tot_pen,　tot_avc)　AS　SELECT　job,　SUM(base),　 SUM(AVC)　 FROM　db2inst1.le_pensions　GROUP　BY　job　 DB20000I　The　SQL　command　completed　successfully.　　 SELECT　*　FROM　pens_vIEw　　 CAREER　　TOT_PEN　　　　　　　　　　　　　　TOT_AVC　 --------　---------------------------------　---------------------------------　 ANALYST　　　　　　　　　　　　　265750.00　　　　　　　　　　　　　146060.00　 CEO　　　　　　　　　　　　　　　1527500.00　　　　　　　　　　　　　　4220.00　 CLERK　　　　　　　　　　　　　　266400.00　　　　　　　　　　　　　28130.00　 MANAGER　　　　　　　　　　　　　811624.00　　　　　　　　　　　　　16360.00　 P　LEADER　　　　　　　　　　　　　821895.00　　　　　　　　　　　　　186391.00　 SALESREP　　　　　　　　　　　　　220670.00　　　　　　　　　　　　　23054.00

現在，試著使用用戶 Rose 創建視圖 PENS_VIEW_F，以查看 LE_PENSIONS 表中被選中的信息，並試著直接選擇數據：

清單 34. 使用 GROUP 權限創建視圖
CREATE　VIEW　PENS_VIEW_F　AS　　　(SELECT　DISTINCT(job),　SUM(base),　SUM(avc)　　FROM　db2inst1.le_pensions　　GROUP　BY　JOB) 　 SELECT　DISTINCT(job),　SUM(base),　SUM(avc)　　FROM　db2inst1.le_pensions　　GROUP　BY　JOB　

或者使用 Rose03.sql 腳本運行 SQL。

由於對組的限制，create vIEw 語句應該會生成錯誤消息，但是直接選擇卻可以成功。

清單 35. 視圖創建失敗
CONNECT　TO　SAMPLE　USER　Rose　USING　　　　Database　Connection　Information　　　Database　server　　　　=　DB2/NT　9.5.0　　SQL　authorization　ID　　=　ROSE　　Local　database　alias　　=　SAMPLE　　　 CREATE　VIEW　pens_vIEw_f　(career,　tot_pen,　tot_avc)　AS　SELECT　job,　SUM(base),　 SUM(AVC)　 FROM　db2inst1.le_pensions　GROUP　BY　job　 DB21034E　The　command　was　processed　as　an　SQL　statement　because　it　was　not　a　 valid　Command　Line　Processor　command.　During　SQL　processing　it　returned:　 SQL0551N　"ROSE"　does　not　have　the　privilege　to　perform　Operation　"SELECT"　on　 object　"DB2INST1.LE_PENSIONS".　SQLSTATE=42501　　 SELECT　job,　SUM(base),　SUM(AVC)　FROM　db2inst1.le_pensions　GROUP　BY　job　　 JOB　　　2　　　　　　　　　　　　　　　　　3　 --------　---------------------------------　---------------------------------　 ANALYST　　　　　　　　　　　　　265750.00　　　　　　　　　　　　　146060.00　 CEO　　　　　　　　　　　　　　　1527500.00　　　　　　　　　　　　　　4220.00　 CLERK　　　　　　　　　　　　　　266400.00　　　　　　　　　　　　　28130.00　 MANAGER　　　　　　　　　　　　　811624.00　　　　　　　　　　　　　16360.00　 P　LEADER　　　　　　　　　　　　　821895.00　　　　　　　　　　　　　186391.00　 SALESREP　　　　　　　　　　　　　220670.00　　　　　　　　　　　　　23054.00　