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

　2009-11-20 00:00:00　来源：WEB开发网　　　

核心提示：开始之前关于本教程本教程将带您经历一系列的练习，以熟悉 DB2 9.5 中的一个新特性：角色，在 DB2 9.5 中实现新的安全功能，第 1 部分: 理解 DB2 9.5 中的角色，本教程面向的读者是 DB2 技术专家、数据库管理员和程序员，您应该对 DB2 on Linux, UNIX, and Windows（以下

开始之前

关于本教程

本教程将带您经历一系列的练习，以熟悉 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　