浅谈SQL Server 2005数据库系统的权限管理

苏文萍

南京市科技信息研究所 江苏 210018

0 引言

对于一个数据库管理员来说，安全性就意味着必须保证那些具有特殊数据访问权限的用户能够登录到SQL Server，并且能够访问数据以及对数据库对象实施各种权限范围内的操作；同时，他还要防止所有的非授权用户的非法操作。

SQL Server2005提供的安全管理模式建立在安全身份验证和访问许可两种机制上。SQL Server2005中权限管理的主要对象包括服务器登录名、服务器角色、数据库用户、数据库角色，搞清楚这些对象间的关系，对于理解SQL Server2005的权限管理有很大帮助。比如为大型企业设计数据库的时候必然考虑到角色划分、权限划分的问题。

1 许可(权限)管理

1.1 概述

许可用来指定授权用户可以使用的数据库对象和这些授权用户可以对这些数据库对象执行的操作。用户在登录到SQL Server之后，其用户账号所归属的Windows组或角色所被赋予的许可(权限)决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。在每个数据库中用户的许可独立于用户账号和用户在数据库中的角色，每个数据库都有自己独立的许可系统。

在SQL Server中包括三种类型的许可：

(1) 对象许可：表示对特定的数据库对象(即表、视图、字段和存储过程)的操作许可，它决定了能对表、视图等数据库对象执行哪些操作。

(2) 语句许可：表示对数据库的操作许可，也就是说，创建数据库或者创建数据库中的其他内容所需要的许可类型称为语句许可。

(3) 预定义许可：指系统安装以后有些用户和角色不必授权就有的许可。

许可的管理包括对许可的授权、否定和收回。在 SQL Server中，可以使用SQL Server管理平台和Transaction_SQL语句两种方式来管理许可。

1.2 使用SQL Server 管理平台管理许可

SQL Server 可通过两种途径实现对用户许可的设定：

(1) 面向单一用户

在数据库用户常规选项页中，如果在“数据库角色成员身份”选项栏中选择一个数据库角色，实际上就完成了数据库用户语句许可的设置。因为对于这些数据库固定角色，SQL Server已经定义了其具有哪些语句许可。

(2) 面向数据库对象的许可设置

在SQL Server 管理平台中，展开服务器和数据库，然后选择需要设置的用户对象，即表、视图、存储过程等，在右面的页框中选择要进行许可设置的对象，右击该对象，从弹出的快捷菜单中选择“属性”选项，出现对象属性对话框，在该对话框中选择“权限”页框，单击“添加”按钮设置好相应的对象许可后，单击“确定”按钮即可完成数据库对象的许可设置。

1.3 使用Transaction_SQL 语句管理许可

Transaction-SQL 语句中的数据控制语言(DCL)用来设置或更改数据库用户或角色权限的语句，包括 GRANT、REVOKE和DENY三种命令。默认状态下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人员才有权力执行数据控制语言。

1.3.1 GRANT语句

(1) 语句权限与角色的授予，语法如下：

GRANT ＜语句权限>|＜角色> [,＜语句权限>|＜角色>]…TO＜ 用 户 名 >|＜ 角 色 >|PUBLIC[,＜ 用 户 名 >|＜ 角色>]…[WITH ADMIN OPTION]

例如：给用户 Mary 和 John 以及 Windows NT 组CorporateBobJ 授予多个语句权限，语句如下：

GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, [CorporateBobJ]

(2) 对象权限与角色的授予

数据库管理员拥有系统权限，而作为数据库的普通用户，只对自己创建的基本表、视图等数据库对象拥有对象权限。如果要共享其他的数据库对象，则必须授予他一定的对象权限。

同语句权限的授予类似，SQL语言使用GRANT语句为用户授予对象权限，其语法格式为：

GRANT ALL|＜对象权限>[(列名[,列名]…)][,＜对象权限>]…ON ＜对象名>

TO ＜用户名>|＜角色>|PUBLIC[,＜用户名>|＜角色>]…[WITH ADMIN OPTION]

其语义为：将指定的操作对象的对象权限授予指定的用户或角色。

例如：在权限层次中授予对象权限。首先，给所有用户授予 SELECT 权限，然后，将特定的权限授予用户 Mary，John 和 Tom，语句实现如下：

GRANT SELECT

ON s

TO public

GO

GRANT INSERT, UPDATE, DELETE

ON s

TO Mary, John, Tom

GO

1.3.2 REVOKE语句

REVOKE语句是与GRANT语句相反的语句，它能够将以前在当前数据库内的用户或者角色上授予或拒绝的权限删除，但是该语句并不影响用户或者角色从其他角色中作为成员继承过来的权限。

(1) 语句权限与角色的收回

数据库管理员可以使用 REVOKE语句收回语句权限，其语法格式为：

REVOKE ＜语句权限>|＜角色> [,＜语句权限>|＜角色>]…

FROM ＜用户名>|＜角色>|PUBLIC[,＜用户名>|＜角色>]…

例如：收回用户 ZHANGYILIN所拥有的 CREATE TABLE的语句权限，实现如下：

REVOKE CREATE TABLE

FROM ZHANGYILIN

(2) 对象权限与角色的收回

所有授予出去的权力在必要时都可以由数据库管理员和授权者收回，收回对象权限仍然使用 REVOKE语句，其语法格式为：

REVOKE ＜对象权限>|＜角色> [,＜对象权限>|＜角色>]…

FROM ＜用户名>|＜角色>|PUBLIC[,＜用户名>|＜角色>]…

例如：从public 角色中收回 SELECT 权限，然后，收回用户 Mary，John 和 Tom 的特定权限：

USE pubs

GO

REVOKE SELECT ON s FROM public

GO

REVOKE INSERT, UPDATE, DELETE

ON s

FROM Mary, John, Tom

1.3.3 DENY语句

DENY语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。

否定语句权限的语法形式为：

DENY ALL|＜语句权限>|＜角色> [,＜语句权限>|＜角色>]…

TO ＜用户名>|＜角色>|PUBLIC[,＜用户名>|＜角色>]…

否定对象权限的语法形式为：

DENY ALL|＜对象权限>[(列名[,列名]…)][,＜对象权限>]…ON ＜对象名>

TO ＜用户名>|＜角色>|PUBLIC[,＜用户名>|＜角色>]…

例如：给 public 角色授予 SELECT 权限，然后，拒绝用户 Mary，John 和 Tom 的特定权限：

USE pubs

GO

GRANT SELECT

ON s

TO public

GO

DENY SELECT, INSERT, UPDATE, DELETE

ON s

TO Mary,John,Tom

2 角色管理

2.1 概述

角色定义了常规的 SQL Server 用户类别。每种角色将该类别的用户与其使用 SQL Server时执行的任务集以及成功完成这些任务所需的知识相关联。利用角色，SQL Server管理者可以将某些用户设置为某一角色，这样只要对角色进行权限设置便可以实现对所有用户权限的设置。

SQL Server提供了用户通常管理工作的预定义服务器角色和数据库角色。用户还可以创建自己的数据库角色，以便表示某一类进行同样操作的用户。当用户需要执行不同的操作时，只需将该用户加入不同的角色中即可，而不必对该用户反复授权许可和收回许可。

2.2 服务器角色

服务器角色主要是控制服务器端对请求数据库资源的访问权限，他允许或拒绝服务器登录名的访问操作。SQL Server2005提供一组固定的服务器角色，比如sysadmin、securityadmin等，通过将这些固定的服务器角色赋予不同的服务器登录名对象，可以是实现服务器级别的权限管理。将服务器角色赋予登录名对象的语句如下： exec sp_addsrvrolemember Kelvin, sysadmin ;exec sp_dropsrvrolemember Kelvin, sysadmin; 一个服务器登录名对象可以拥有多个固定服务器角色权限，但是固定服务器角色不能被修改、添加和删除。

服务器角色是指根据SQL Server的管理任务，以及这些任务相对的重要性等级来把具有SQL Server管理职能的用户划分为不同的用户组，每一组所具有的管理SQL Server的权限都是SQL Server内置的。服务器角色存在于各个数据库之中，要想加入用户，该用户必须有登录账号以便加入到角色中。

对于拥有服务器角色sysadmin的登录名对象，它可以SQL Server2005中做任何操作(由此可见服务器角色权限粒度之大)。但对那些没有sysadmin角色的登录名对象，它需要拥有一个能访问特定数据库实例的数据库用户名(User)以实现对该数据库的操作。比如说为登录名Kelvin想要访问AdventureWorks数据库，但不具备sysadmin的服务器角色。那么则在AdventureWorks数据库中中创建一个名为user_Kelvin的数据库用户。TSQL：USE AdventureWorks; GO; create user user_Kelvin for login Kelvin; 执行之后，用 Kelvin登录名登录的用户与AdventureWorks下的user_Kelvin用户建立起了关联。

2.3 数据库角色

数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。

SQL Server提供了两种类型的数据库角色：

(1) 固定的数据库角色

固定数据库角色是系统默认用于组织数据库用户权限的角色，包括db_datareader、db_ddladmin、db_owner等，这些角色都包含与其名称相同的数据库架构(Schema)，比如db_datareader就默认只拥有名为db_datareader的架构。用户可以创建角色，并让角色获取相应的架构，最后将角色与数据库用户建立起联系即可。 比如： Use AdventureWorks; exec sp_addrolemember db_datareader, user_Kelvin; 执行后user_Kelvin就拥有了读取数据的权限(该权限为db_datareader角色拥有)。

SQL Server管理者不能对固定的数据库角色所具有的权限进行任何修改。SQL Server的每一个数据库中都有一组固定的数据库角色，可以将不同级别的数据库管理工作分给不同的角色，从而有效地实现工作权限的传递。

(2) 用户自定义的数据库角色

如果一组用户需要执行在SQL Server中指定的一组操作并且不存在对应的Windows组，或者没有管理Windows用户账号的许可，就可以在数据库中建立一个用户自定义的数据库角色。用户自定义的数据库角色有两种类型：即标准角色和应用程序角色。

标准角色通过对用户权限等级的认定而将用户划分为不用的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。

应用程序角色使得某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据。通过应用程序角色，能够以可控制方式来限定用户的语句或者对象许可。

2.4 使用SQL Server管理平台管理角色

(1) 管理服务器角色

打开SQL Server管理平台，展开指定的服务器，单击安全性文件夹，然后单击服务器角色图标，在右边的页框中右击所要的角色，从弹出的快捷菜单中选择“属性”选项，则出现服务器角色属性对话框。在该对话框中我们可以看到属于该角色的成员。单击“添加”按钮则弹出添加成员对话框，其中可以选择添加新的登录账号作为该服务器角色成员，单击删除按钮则可以从服务器角色中“删除”选定的账号。

(2) 管理数据库角色

在SQL Server管理平台中，展开指定的服务器以及指定的数据库，然后展开安全性文件夹，右击数据库角色图标，从弹出的快捷菜单中选择“新建数据库角色”选项，则出现新建数据库角色对话框。在名称文本框中输入该数据库角色的名称；点击架构前的复选框，可设定此角色拥有的架构；单击“添加”按钮，可将数据库用户增加到新建的数据库角色中；最后单击“确定”按钮即可完成新的数据库角色的创建。

2.5 使用存储过程管理角色

(1) 管理服务器角色

在SQL Server中，管理服务器角色的存储过程主要有两个：sp_addsrvrolemember和sp_dropsrvrolemember。

系统存储过程 sp_addsrvrolemember可以将某一登录账号加入到服务器角色中，使其成为该服务器角色的成员。其语法形式如下：

sp_addsrvrolemember login，role

系统存储过程sp_dropsrvrolemember可以将某一登录者从某一服务器角色中删除，当该成员从服务器角色中被删除后，便不再具有该服务器角色所设置的权限。其语法形式如下：

sp_dropsrvrolemember [@loginame=]’login’，[@rolename=]’role’

其中，@loginame为登录者名称；@rolename为服务器角色。

(2) 管理数据库角色

在SQL Server中，支持数据库管理的存储过程主要有六种：

create role：用来创建一个新的数据库角色，create role语法形式如下：

create role role_name [ AUTHORIZATION owner_name ]

其中：role_name 为待创建角色的名称；AUTHORIZATION owner_name为拥有新角色的数据库用户或角色。如果未指定用户，则执行 CREATE ROLE 的用户将拥有该角色。

droprole：用于从当前数据库角色中删除一个数据库角色，Droprole的语法形式如下：

Drop role role_name

sp_helprole：用来显示当前数据库中所有数据库角色的全部信息。其语法形式如下：

sp_helprole [‘role’]

sp_addrolemember：用来向数据库某一角色中添加数据库用户，这些角色可以是用户自定义的标准角色，也可以是固定的数据库角色，但不能是应用程序角色。其语法形式如下：

sp_addrolemember role，security_account

sp_droprolemember：用来删除某一角色的用户。其语法形式如下：

sp_droprolemember role，security_account

sp_helprolemember：用于显示某一数据库角色的所有成员。其语法形式如下：

sp_helprolemember [‘role’]

3 结束语

SQL Server2005中权限管理的主要对象包括服务器登录名、服务器角色、数据库用户、数据库角色。服务器登录名对象分别与服务器角色和数据库用户直接关联，而数据库用户与数据库角色和数据库架构直接联系，从而形成了整个数据库结构的权限管理。搞清楚这些对象间的关系，对于理解SQL Server2005的权限管理有很大帮助。

[1] 刘世生,韩兵.基于RBAC的SQL Server 2005数据库权限管理[J].硅谷.2009.

[2] 陈伟.SQL Server 2005数据库应用与开发教程[M].北京:清华大学出版社.2007.

[3] 邢红刚.关于SQL Server数据库的安全问题的思考们.科技资讯.2008.

[4] 宋敏,程凤娟. SQL Server 2005安全机制研究[J].电脑与信息技术.2008.

[5] 郭煦.基于角色的访问控制的应用研究[J].上海电机学院学报.2007.