管理SQL Server 2008 数据库角色

• 2020 年 4 月 2 日
• 筆記

角色是SQL Server 2008用来集中管理数据库或者服务器的权限。数据库管理员将操作数据库的权限赋予角色。然后，数据库管理员再将角色赋给数据库用户或者登录账户，从而使数据库用户或者登录账户拥有了相应的权限。 

1  固定服务器角色

为便于管理服务器上的权限，SQL Server提供了若干“角色”，这些角色是用于分组其他主体的安全主体。“角色”类似于Microsoft Windows操作系统中的“组”。

服务器级角色也称为“固定服务器角色”，因为不能创建新的服务器级角色。服务器级角色的权限作用域为服务器范围。可以向服务器级角色中添加SQL Server登录名、Windows账户和Windows组。固定服务器角色的每个成员都可以向其所属角色添加其他登录名。

用户可以指派给这8个服务器角色之中的任意一个角色。下面将分别介绍这8个服务器角色：

　　　　sysadmin  这个服务器角色的成员有权在SQL Server 2008中执行任何任务。不熟悉SQL Server 2008的用户可能会意外地造成严重问题，所以给这个角色批派用户时应该特别小心。通常情况下，这个角色仅适合数据库管理员（DBA）。

　　　　securityadmin  这个服务器角色的成员将管理登录名及其属性。他们可以GRANT、DENY和REVOKE服务器级权限。也可以GRANT、DENY和REVOKE数据库级权限。另外，他们可以重置SQL Server 2008登录名的密码。

　　　　serveradmin  这个服务器角色的成员可以更改服务器范围的配置选项和关闭服务器。比如SQL Server 2008可以使用多大内存或者关闭服务器，这个角色可以减轻管理员的一些管理负担。

　　　　setupadmin  这个服务器角色的成员可以添加和删除链接服务器，并且也可以执行某些系统存储过程。

　　　　processadmin  SQL Server 2008能够多任务化，也就是说，他可以通过执行多个进程做多件事件。例如，SQL Server 2008可以生成一个进程用于向高速缓存写数据，同时生成另一个进程用于从高速缓存中读取数据。这个角色的成员可以结束（在SQL Server 2008中称为删除）进程。

　　　　diskadmin  这个服务器角色用于管理磁盘文件，比台镜像数据库和添加备份设备。这适合于助理DBA。

　　　　dbcreator  这个服务器角色的成员可以创建、更改、删除和还原任何数据库。这不仅是适合助理DBA的角色，也可能是个适合开发人员的角色。

　　　　bulkadmin  这个服务器角色的成员可以运行BULK INSERT语句。这条语句允许他们从文本文件中将数据导入到SQL Server 2008数据库中。

在SQL Server 2008中可以使用系统存储过程对固定服务器角色进行相应的操作，表9-3就列出了可以对服务器角色进行操作的各个存储过程。

表3  使用服务器角色的操作

例如，想要查看所有的固定服务器角色，就可以使用系统存储过程sp_helpsrvrole，具体的执行过程及结果如图16所示。

16  查看固定服务器角色

下面将运用上面介绍的知识，将一些用户指派给固定服务器角色，进而分配给他们相应的管理权限。具体步骤如下所示：

（1）打开SQL Server Management Studio，在【对象资源管理器】窗口，展开【安全性】节点，然后再展开【服务器角色】节点。

（2）双击sysadmin节点，打开【服务器角色属性】节点，然后单击【添加】按钮，打开【选择登录名】窗口。

（3）单击【浏览】按钮，打开【查找对象】对话框，启用shop_Manage选项旁边的复选框，如图17所示。

17  添加登录名

（4）单击【确定】按钮返回到【选择登录名】对话框，就可以看到刚刚添加的登录名shop_Manage，如图18所示。

18  【选择登录名】对话框

（5）单击【确定】按钮返回【服务器角色属性】窗口，在角色成员列表中，就可以看到服务器角色sysadmin的所有成员，其中包括刚刚添加的shop_Manage，如图19所示。

19  【服务器角色属性】窗口

（6）用户可以再次通过【添加】按钮添加新的登录名，也可以通过【删除】按钮删除某些不需要的登录名。

（7）添加完成后，单击【确定】按钮关闭【服务器角色属性】窗口。

2  固定数据库角色

固定数据库角色存在于每个数据库中，在数据库级别提供管理特权分组。管理员可将任何有效的数据库用户添加为固定数据库角色成员。每个成员都获得应用于固定数据库角色的权限。用户不能增加、修改和删除固定数据库角色。

SQL Server 2008在数据库级设置了固定数据库角色来提供最基本的数据库权限的综合管理。在数据库创建时，系统默认创建了10个固定数据库角色，下面将分别介绍这几个固定数据库角色：

　　　　db_owner  进行所有数据库角色的活动，以及数据库中的其他维护和配置活动。该角色的权限跨越所有其他的固定数据库角色。

　　　　db_accessadmin  这些用户有权通过添加或者删除用户来指定谁可以访问数据库。

　　　　db_securityadmin  这个数据库角色的成员可以修改角色成员身份和管理权限。

　　　　db_ddladmin  这个数据库角色的成员可以在数据库中运行任何数据定义语言(DDL)命令。这个角色允许他们创建、修改或者删除数据库对象，而不必浏览里面的数据。

　　　　db_backupoperator  这个数据库角色的成员可以备份该数据库。

　　　　db_datareader  这个数据库角色的成员可以读取所有用户表中的所有数据。

　　　　db_datawriter  这个数据库角色的成员可以在所有用户表中添加、删除或者更改数据。

　　　　db_denydatareader  这个服务器角色的成员不能读取数据库内用户表中的任何数据，但可以执行架构修改（比如在表中添加列）。

　　　　db_denydatawriter  这个服务器角色的成员不能添加、修改或者删除数据库内用户表中的任何数据。

　　　　public  在SQL Server 2008中每个数据库用户都属于public数据库角色。当尚未对某个用户授予或者拒绝对安全对象的特定权限时，则该用户将继承授予该安全对象的public角色的权限。这个数据库角色不能补删除。

在SQL Server 2008中可以使用Transact-SQL语句对固定数据库角色进行相应的操作，表9-4就列出了可以对服务器角色进行操作的系统存储过程和命令等。

表4  数据库角色的操作

例如，使用系统存储过程sp_helpdbfixedrole就可以返回固定数据库角色的列表，如图20所示。

20 查看固定数据库角色

提示：由于所有数据库用户都自动成为public数据库角色的成员，因此给这个数据库角色指派权限时需要谨慎。

下面通过将用户添加到固定数据库角色中来配置他们对数据库拥有的权限，具体步骤如下所示：

（1）打开SQL Server Management Studio，在【对象资源管理器】窗口，展开【数据库】节点，然后再展开数据库【网店购物系统】节点中的【安全性】节点。

（2）接着展开【角色】节点，然后再展开【数据库角色】节点，双击db_owner节点，打开【数据库角色属性】窗口。

（3）单击【添加】按钮，打开【选择数据库用户或角色】对话框，然后单击【浏览】按钮打开【查找对象】对话框，选择数据库用户admin，如图21所示

21  添加数据库用户

（4）单击【确定】按钮返回【选择数据库用户或角色】对话框。如图22所示。

22  【选择数据库用户或角色】对话框

（5）单击【确定】按钮，返回【数据库角色属性】窗口，在这里可以看到当前角色拥有的架构以及该角色所有的成员，其中包括刚添加的数据库用户admin，如图23所示。

23  【数据库角色属性】窗口

（6）添加完成后，单击【确定】按钮关闭【数据库角色属性】窗口。

3  应用程序角色

应用程序角色是一个数据库主体，他使应用程序能够用其自身的、类似用户的特权来运行。使用应用程序角色，可以只允许通过特定应用程序连接的用户访问特定数据。与数据库角色不同的是，应用程序角色默认情况下不包含任何成员，而且不活动。应用程序角色使用两种身份验证模式，可以使用sp_setapprole来激活，并且需要密码。因为应用程序角色是数据库级别的主体，所以他们只能通过其他数据库中授予guest用户账户的权限来访问这些数据库。因此，任何已禁用guest用户账户的数据库对其他数据库中的应用程序角色都不可访问。

创建应用程序角色的过程与创建数据库角色的过程一样，图24为应用程序角色的创建窗口。

24  创建应用程序角色

应用程序角色和固定数据库角色的区别有如下4点：

　　　　应用程序角色不包含任何成员。不能将Windows组、用户和角色添加到应用程序角色。

　　　　当应用程序角色被激活以后，这次服务器连接将暂时失去所有应用于登录账户、数据库用户等的权限，而只拥有与应用程序相关的权限。在断开本次连接以后，应用程序失去作用。

　　　　默认情况下，应用程序角色非活动，需要密码激活。

　　　　应用程序角色不使用标准权限。

4  用户自定义角色

有时，固定数据库角色可能不满足需要。例如，有些用户可能只需数据库的“选择”、“修改”和“执行”权限。由于固定数据库角色之中没有一个角色能提供这组权限，所以需要创建一个自定义的数据库角色。

在创建数据库角色进，先给该角色指派权限，然后将用户指派给该角色；这样，用户将继承给这个角色指派的任何权限。这不同于固定数据库角色，因为在固定角色中不需要指派权限，只需要添加用户。创建自定义数据库角色的步骤如下所示：

（1）打开SQL Server Management Studio，在【对象资源管理器】窗口，展开【数据库】|【网店购物系统】|【安全性】|【角色】节点，右击【数据库角色】节点从弹出菜单中选择【新建数据库角色】命令，打开【数据库角色-新建】窗口。

（2）设置角色名称为TestRole，所有者选择dbo，单击【添加】按钮，选择数据库用户admin，如图25所示。

25  【数据库角色-新建】窗口

（3）选中【安全对象】选项，打开【安全对象】选项页面，通过单击【搜索】按钮，添加“商品信息”表为“安全对象”，选中【选择】后面【授予】列的复选框，如图26所示。

26  为角色分配权限

（4）单击【列权限】按钮，还可以为该数据角色配置表中每一列的具体权限，如图27所示。

27  分配列权限

（5）具体的权限分配完成后，单击【确定】按钮创建这个角色，并返回到SQL Server Management Studio。

（6）关闭所有程序，并重新登录为admin。

（7）展开【数据库】|【网店购物系统】|【表】节点，可以看到表节点下面只显示了拥有查看权限的【商品信息】表。

（8）由于在【列权限】窗口设置该角色的权限为：不允许查看【商品信息】表中的“商品价格”列，那么在查询视图中输入下列语句将出现错误，如图29所示。

29 使用SELECT语句验证权限