用户权限管理

一、服务器登录帐号和用户帐号管理

1.SQL Server服务器登录管理

不管使用哪种认证模式，用户都必须先具备有效的用户登录帐号。SQL Server有三个默认的用户登录帐号：即sa、Builtin\administrators和guest。sa是系统管理员(system administrator)的简称，是一个特殊的用户，在SQL Server系统和所有数据库中拥有所有的权限。SQL Server还为每一个Windows NT系统管理员提供了一个默认的用户帐号Builtin\administrators。这个帐号在系统和所有数据库中也拥有所有的权限。而guest帐号为默认访问系统用户帐号。利用企业管理器可以创建、管理SQL Server登录帐号。其具体步骤如下：

(1)打开企业器，单击服务器的“+”，展开安全性文件夹；

(2)右击登录图标，选择“新建登录”；

(3)在“名称”文本框中输入登录名，选择新建的用户帐号的认证模式；并选择默认的数据库和语言。

(4)选择“服务器角色”，在列出的固定服务器角色的左端复选框中选择相应的服务器角色；

(5)选择“数据库访问”，单击数据库左边的复选框，表示该用户可以访问相应的数据库以及该帐号在数据库中的用户名。下部的列表框列出了相应的数据库中的数据库角色清单，可以指定该帐号所属的数据库角色；

(6)单击“确定”即可完成登录帐号的创建。

此外，还可以使用创建登录向导工具创建登录帐号。具体过程如下：

(1)打开企业管理器，选择工具中的“向导--创建登录向导”；

(2)选择身份验证模式；

(3)

(4)

(5)

(6)

2.用户帐号管理

在数据库中，一个用户或工作组取得合法的登录帐号，只表明该帐号通过了Windows NT认证或者SQL Server认证，但不能表明其可以对数据库数据和对象进行某种或者某些操作，只有当他同时拥有了用户帐号后，才能够访问数据库。在一个数据库中，用户帐号{wy}标识一个用户，用户对数据库的访问权限以及对数据库对象的所有关系都是通过帐号来控制的。用户帐号总是基于数据库的，即两个不同的数据库可以有两个相同的用户帐号，并且一个登录帐号也总是与一个或多个数据库用户帐号相对应的。如，登录帐号sa自动与每一个数据库用户dbo相关联。通常而言，数据库用户帐号总是与某一登录帐号相关联，但有一个例外，那就是guest用户。在安装SQL Server系统时，guest用户被加入到master，pubs，tempdb和northwind数据库中，该用户主要是让那些没有属于自己的用户帐号的SQL Server登录者作为其默认的用户，从而使该登录者能够访问具有guest用户的数据库。

利用企业管理器可以授予SQL Server登录访问数据库的许可权限。使用它创建一个新数据库用户帐号的过程如下：打开企业管理器，展开要登录的服务器和数据库文件夹，然后展开要创建用户的数据库，右击用户图标，从弹出的菜单中选择“新建数据库用户”选项，则出现数据库用户属性--新建用户对话框。

在登录名选择框内选择已经创建的登录帐号，在用户名选择框内输入数据库用户名称，然后在下面的数据库角色成员选择框中为该用户选择数据库角色，{zh1}单击“确定”按钮即可完成数据库用户的创建。

其实在创建一个SQL Server登录帐号时，可以先为该登录帐号定出其在不同的数据库中所使用的用户名称，这实际上就完成了创建新的数据库用户这一任务。其具体操作过程请参见上一节，在打开的SQL Server属性--新建登录对话框中选择数据库访问页框。

同样，在SQL Server企业管理器中,，也可以查看或者删除数据库用户，方法是：展开某一数据库，选中用户图标，则在右面的页框中显示当前的数据库的所有用户，要删除数据库用户，则在右面的页框中右击所要删除的数据库用户，从弹出的菜单中选择delete选项即可。

二、许可(权限)管理

许可用来指定授权用户可以使用的数据库对象和这些授权用户可以对这些数据库对象执行的操作。用户在登录到SQL Server之后，其用户帐号所归属的NT组或角色所被赋予的许可（权限）决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。在每个数据库中用户的许可独立于用户帐号和用户在数据库中的角色，每个数据库都有自己独立的许可系统，在SQL Server中包括三种类型的许可：即对象许可，语句许可和预定义许可。

对象许可表示对特定的数据库对象（即表、视图、字段和存储过程）的操作许可，它决定了能对表、视图等数据库对象执行哪些操作。如果用户想要对某一对象进行操作，其必须具有相应的操作的权限。表和视图许可用来控制用户在表和视图上执行SELECT，UPDATE和REFERENCES操作的能力。存储过程许可用来控制用户执行EXECUTE语句的能力。

语句许可表示对数据库的操作许可，也就是说，创建数据库或者创建数据库中的其它内容所需要的许可类型称为语句许可。这些语句通常是一些具有管理性的操作，如创建数据库、表和存储过程等。这种语句虽然仍包含有操作的对象，但这些对象在执行该语句之前并不存在于数据库中。因此，语句许可针对的是某个SQL语句，而不是数据库中已经创建的特定的数据库对象。只有sysadmin,db-owner和db-securityadmin角色的成员才能授予语句许可，可用于语句许可的Transaction-SQL语句及其含义如下：

• Create database:创建数据库；
• Create table:创建表；
• Create view:创建视图；
• Create rule:创建默认；
• Create procedure:创建存储过程；
• Create index:创建索引；
• Backup log:备份事务日志。

预定义许可是指系统安装以后有些用户和角色不必授权就有的许可。其中的角色包括固定服务器角色和固定数据库角色，用户包括数据库对象所有者。只有固定角色或者数据库对象所有者的成员才可以执行某些操作。执行这些操作的许可就称为预定义许可。

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

1.使用SQL Server企业管理器管理许可

SQL Server可通过两种途径：即面向单一用户和面向数据库对象的许可设置，来实现对语句许可和对象许可的管理，从而实现对用户许可的设定。

(1)面向单一用户的许可设置。

展开指定的数据库，单击用户图标，右击进行设置许可的用户，选择“属性”，单击“权限”进行设置即可。在“数据库角色成员”中选择任何一个数据库角色实际上就完成了用户语句许可的设置。

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

展开指定的数据库，选择需要设置的对象，右击该对象，选择“all tasks-管理权限”，设置相应的对象许可后，单击“确定”即可。

可以看出，对象属性对话框与前面的数据库用户属性对话框极为类似，在数据库用户属性对话框中，是为某一用户设置其对当前数据库的所有对象的访问许可，而在对象属性对话框中，是为某一数据库对象设置当前数据库所有用户对其的访问许可。

2.使用Transaction-SQL语句

transaction-SQL语句使用grant、revoke和deny三种命令来管理权限。 Grant语句用把许可授予某一用户，以允许该用户执行针对该对象的操作或允许其运行某些语句，语法形式如下：

(1)对语句的许可：

Grant {all | statement[,...n]} to security_accout[,...n]

(2)对对象的许可：

grant { {all [priviledges] | permission[,...n]}
[(column[,...n])] on {table view}
| on {table | view}[(column[,...n)]
| on {stored_procedure | extended_procedure} }
to security_account[,...n]
[as {group | role}]

其中，all参数表示具有所有的语句或对象权限。对于语句权限来说，只有sysadmin角色才具有所有的语句权限。对于对象权限来说，只有sysadmin和db_owner角色才具有访问某一数据库所有对象的权限。许可的授权可以累加，用户可以执行授予他们的单个操作许可，以及因属于某个角色而具有的操作许可。

一般对对象许可的类型如下：

SELECT(表、视图、列)
UPDATE(表、视图、列)
DELETE(表、视图)
INSERT(表、视图)
REFERENCES(列)
EXEC(存储过程)

例：授权对象许可

use ccr
grant select on products to df_role
GO
grant insert, update, delete on products to dd,df
GO

例：语句许可

use ccr
grant create database to dd,df,[Corporate\admi]
go
grant create table to dd,df,[Corporate\admi]
GO

Deny语句可以用来禁止用户对某一对象或语句的权限，这些权限是经过grant语句授予的，它不允许该用户执行针对数据库对象的某些操作或不允许其运行某些语句，语法形式如下：

(1)否定语句的许可：

deny {all | statement[,..n]} to security_account[,...n]

例：否认语句许可

use ccr
GO
DENY create table to dd, df
GO

(2)否定对象的许可：

deny { {all [priviledges] | permission[,...n]}
[(column[,...n])] on {table view}
| on {table | view}[(column[,...n])]
| on {stored_procedure | extended_procedure} }
to security_account[,...n]

例：否认对象许可

use ccr
GO
DENY Insert, update, delete on produecs to dd, df
GO

在默认情况下，只有sysadmin、db_owner和db_securityadmin角色的成员和数据库对象所有者，才具有否定许可的权限。

Revoke语句可以用来禁止用户对某一对象或语句的许可，明确禁止其执行某些操作或者运行某些语句，语法如下：

(1)收回语句的许可：

revoke {all | statement[,..n]} from security_account[,..n]

(2)收回对象的许可：

revoke [grant option for]
{ {all [priviledges] | permission[,...n]}
[(column[,...n])] on {table view}
| on {table | view}[(column[,...n)]
| on {stored_procedure | extended_procedure} }
from security_account[,...n]
[as {group | role}]

注：收回许可即是删除以前授予的许可和否认的许可。

例：

use ccr
revoke select,insert,update on products from dd, df
GO

revoke Create table from dd

在默认情况下，只有sysadmin、db_owner和db_securityadmin角色的成员和数据库对象所有者，才具有收回许可的权限。收回许可就是删除系统表sysprotects中的内容。

三、角色管理

角色是SQL Server7.0版本引进的新概念，它代替了以前版本中组的概念。利用角色，SQL Server管理者可以将某些用户设置为某一角色，这样只要对角色进行权限设置便可以实现对所有用户权限的设置，大大减少了管理员的工作量。SQL Server提供了用户通常管理工作的预定义服务器角色和数据库角色。用户还可以创建自己的数据库角色，以便表示某一类进行同样操作的用户。当用户需要执行不同的操作时，只需将该用户加入不同的角色中即可，而不必对该用户反复授予许可和收回许可。

1.服务器角色

服务器角色是指根据SQL Server的管理任务，以及这些任务相对的重要性等级来把具有SQL Server管理职能的用户划分为不同的用户组，每一组所具有的管理SQL Server的权限都是系统内置的，即不能对其进行添加、修改和删除，只能向其中加入用户或者其他角色。服务器角色存在于各个数据库之中，要想加入用户，该用户必须有登录帐号以便加入到角色中。SQL Server提供了七种常用的固定服务器角色，其具体含义如下：

• 系统管理员(sysadmin)：拥有SQL Server所有的权限许可；
• 服务器管理员(serveradmin)：管理SQL Server服务器端的设置；
• 磁盘管理员(diskadmin)：管理磁盘文件；
• 进程管理员(processadmin)：管理SQL Server系统进程；
• 安全管理员(securityadmin)：管理和审核SQL Server系统登录；
• 安装管理员(setupadmin)：增加、删除连接服务器，建立数据库复制以及管理扩展存储过程；
• 数据库创建者(dbcreator)：创建数据库，并对数据库进行修改。

2.数据库角色

数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。SQL Server提供了两种类型的数据库角色：系统固定角色和用户自定义角色。

(1)固定数据库角色

固定数据库角色是指SQL Server已经定义了这些角色所具有的管理、访问数据库的权限，而且SQL Server管理者不能对其所具有的权限进行任何修改。SQL Server中的每一个数据库中都有一组固定的数据库角色，在数据库中使用固定的数据库角色可以将不同级别的数据库管理工作分给不同的角色，从而有效地实现工作权限的传递。SQL Server提供了十种常用的固定数据库角色来授予组合数据库级管理员权限，这些固定的数据库角色信息存储在系统表sysusers中。其含义如下：

• public：
• db_owner：在数据库中有全部权限。
• db_accessadmin：可以添加或删除用户ID。
• db_ddladmin：可以发出ALL DDL操作的所有权。
• db_securityadmin：可以管理全部权限、对象所有权、角色和角色成员资格。
• db_backupoperator：可以发出DBCC、CHECKPOINT和BACKUP语句。
• db_datareader：可以选择数据库内任何用户表中的所有数据。
• db_datawriter：可以更改数据库内任何用户表中的所有数据。
• db_denydatareader：不能选择数据库内任何用户表中的任何数据。
• db_denydatawriter：不能更改数据库内任何用户表中的任何数据。

在固定的数据库角色中，public是一个特殊的数据库角色，数据库中的每个用户都是其成员。不能将用户、组或其他角色指定给public角色，在每一个数据库中都包含public角色，且不能删除这个角色。

(2)用户自定义角色

创建用户定义的数据库角色就是创建一组用户，这些用户具有相同的一组许可。如果一组用户需要执行在SQL Server中指定的一组操作且不存在对应的NT组，或者没有管理NT用户帐号的许可，就可以在数据库中建立一个用户自定义的数据库角色。自定义角色有两种：标准角色和应用程序角色。

标准角色通过对用户权限等级的认定而将用户划分为不同的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。所有的固定数据库角色或SQLServer管理者自定义的某一角色都是标准角色。

应用程序角色是一种比较特殊的角色。当我们打算让某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据时，就应该考虑使用应用程序角色。

当某一用户使用了应用程序角色时，他便放弃了已被赋予的所有数据库专有权限，他所拥有的只是应用程序角色被设置的角色。通过应用程序角色，能够以可控制方式来限定用户的语句或者对象许可。

标准角色是通过把用户加入到不同的角色当中而使用户具有相应的语句许可或对象许可，而应用程序角色是首先将这样或那样的权限赋予应用程序，然后将逻辑加入到某一特定的应用程序中，从而通过xx应用程序角色而实现对应用程序存取数据的可控性。只有应用程序角色被xx，角色才是有效的，用户也便可以且只可以执行应用程序角色相应的权限，而不管用户是一个sysadmin或者public标准数据库角色。

在SQL Server中，可以利用企业管理器和存储过程两种方式来管理角色。

3.使用企业管理器管理角色

(1)管理服务器角色

使用企业管理器可以添加、删除角色。

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

在服务器角色属性对话框中单击“权限”按钮，则可以查看该服务器角色所具有的所有权限。

(2)管理数据库角色

使用企业管理器可以添加、删除数据库角色。

在企业管理器中，展开指定的服务器以及指定的数据库，然后右击角色图标，从菜单中选择“新建数据库角色”，在出现的数据库角色属性----新建角色对话框中输入该数据库角色的名称，还可以选择该数据库角色的类型，即标准角色和应用程序角色。。如果选择标准角色，可单击“添加”按钮，将数据库用户添加到新建的数据库角色中；如果选择“应用程序角色”，则在密码框中输入口令。{zh1}单击“确定”按钮即可完成新的数据库角色的创建。

数据库角色创建成功后，可以通过与上面类似的步骤查看已创建的数据库角色的属性，单击“权限”按钮将出现数据库角色属性对话框，通过单击白方框可以进行角色权限的设置。

同样，要删除自定义的数据库角色，只须在指定的数据库中单击“角色”图标后，在右边的页框中选择要删除的数据库角色的图标，右击该图标，从弹出的菜单中选择“删除”即可。

4.使用存储过程管理角色

(1)管理服务器角色

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

sp_addsrvrolemember和sp_dropsrvrolemember

• sp_addsrvrolemember可以将某一登录帐号加入到服务器角色中，使其成为该服务器角色的成员。语法如下：

  sp_addsrvrolemember login , role

• sp_dropsrvrolemember可以将某一登录帐号从某一服务器角色中删除，当该成员从服务器角色中被删除后，便不再具有该服务器角色所设置的权限。语法如下：

  sp_dropsrvrolemember [@loginname=]'login',[@rolename=]'role'

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

(2)管理数据库角色

在SQL Server中，支持数据库管理的存储过程主要有六种，其具体含义和语法如下：

• sp_addrole：用于创建一个新的数据库角色；

  sp_addrole role, owner

• sp_droprole：用于从当前数据库角色中删除一个数据库角色；

  sp_droprole role

• sp_helprole：用于显示当前数据库中所有数据库角色的全部信息；

  sp_helprole ['role']

• sp_addrolemember：用于向数据库某一角色中添加数据库用户，这些角色可以是用户自定义的标准角色，也可以是固定的数据库角色，但不能是应用程序角色。

  sp_addrolemember role, security_account

• sp_droprolemember：用于删除某一角色的用户；

  sp_droprolemember role, security_account

• sp_helprolemember：用于显示某一数据库角色的所有成员。

  sp_helprolemember ['role']