《SQL Server数据库系统基础》课件010

第10章SQLServer安全管理10.1SQLServer的验证模式10.2数据库用户10.3权限及设置10.4服务器角色10.5数据库角色10.6权限管理10.7实战训练小结

思考题

数据库的安全是一个十分重要的问题。对于进入数据库的用户身份需要验证，用户的操作权限需要规定，只有防止非法用户进入和拒绝非法操作，才有可能管好数据库。本章任务：学习SQLServer2000数据库的安全管理中的身份验证与登录帐户，数据库用户的创建，用户权限和角色权限的设置与验证。10.1SQLServer的验证模式10.1.1SQLServer2000登录身份验证模式用户要使用SQL数据库服务器，首先必须输入登录帐户和密码，通过SQLServer的登录身份验证。由数据库服务器可判断是否具有联接SQLServer的权限。SQLServer2000提供两种确认用户的验证模式：SQLServer身份验证和Windows身份验证。

1. SQLServer身份验证

SQLServer身份验证又称为SQLServer标准登录模式验证。使用SQLServer标准登录模式验证时，系统管理员需事先创建一个登录帐户和密码，并把它们存储在SQLServer系统数据库中。当用户想要联接到SQLServer上时，必须提供一个数据库服务器中已存在的登录帐户和密码。

当SQLServer安装完成后，SQLServer就自动建立了一个特殊的帐户 sa(SystemAdministrator)。sa帐户拥有服务器和所有的数据库，即sa拥有最高的管理权限，可以执行服务器范围内的所有操作。学习时sa可以是空密码，正式使用数据库时，为了安全，sa必须设置密码。sa帐户无法删除。

2. Windows身份验证使用Windows身份验证时，被授权联接SQLServer的WindowsNT/2000用户帐户或组帐户在联接SQLServer时不需要提供登录帐户和密码，即SQLServer认为WindowsNT/2000已对该用户作了身份验证。

通常，针对Windows用户推荐使用Windows身份验证，主要原因有三个。第一，可以提高管理效率。可以将需要访问SQLServer的用户的WindowsNT/2000用户帐户加入WindowsNT/2000组帐户中，然后将WindowsNT/2000组帐户映射成SQLServer中的一个用户，从而使整个组的每个成员都有权访问SQLServer数据库。第二，可以加快登录速度。WindowsNT/2000用户在登录SQLServer时不需要再次验证密码。第三，可以增强安全性。使用WindowsNT/2000用户帐户的密码更加安全，可以要求密码定期更换、增强复杂度和重复间隔等，防止密码长期固定不变、密码过于简单或交替使用少数密码等现象。

注意：如果将SQLServer安装在Windows9x操作系统上，则将只能使用SQLServer身份验证模式，因为Windows9x不支持WindowsNT/2000帐户验证方式。如前所述，SQLServer2000登录身份验证用来验证用户是否具有联接SQLServer的权限。和SQLServer2000提供的两种确认用户的验证模式相对应，在SQLServer2000中存在两类登录帐户，即Windows用户帐户和SQLServer帐户。在SQLServer2000企业管理器的树型目录中展开服务器下的“安全性”，选择“登录”，可以查看当前服务器所有的登录帐户信息，如图10-1所示。图10-1在企业管理器中查看服务器的登录帐户信息

在图10-1中，对于每一个服务器登录帐户，列出了该帐户的“名称”、“类型”、“服务器访问”、“默认数据库”以及“默认语言”。帐户的类型有三种，分别是“Windows用户”帐户、“Windows组”帐户和“标准”帐户。对于SQLServer帐户，“名称”列直接显示帐户名称，显示的图标如图10-1中SQLServer帐户sa的图标所示。对于WindowsNT/2000用户帐户，帐户名采用“计算机名称(或域名)\用户名(或组名)”的形式表示。例如，计算机xjsb6031上的用户stu使用“xjsb6031\stu”来表示，如图10-2所示。计算机xjsb6031上的用户组帐户dbAdmin使用“xjsb6031\dbAdmin”来表示。图10-2在计算机管理中创建的用户stu

对于WindowsNT/2000内建本地组，如Administrators、Users、Guests，其帐户名中使用BUILTIN代替计算机名(或域名)。例如，WindowsNT/2000内建本地组Administrators，其帐户名为“BUILTIN\Administrators”，如图10-1所示。“服务器访问”栏显示了是否允许该用户登录服务器，取值可以是“许可”或“拒绝”。“默认数据库”是指用户登录SQLServer后首先要联接的数据库，默认为master数据库。“默认语言”定义了SQLServer使用何种语言向用户显示信息，如SimplifiedChinese(中文简体)、English(英语)等。10.1.2设置身份验证模式可以使用企业管理器设置SQLServer身份验证模式。其步骤如下：

(1)在企业管理器中，展开SQLServer服务器组。

(2)右击要设置身份验证模式的服务器，并在弹出的快捷菜单中选择“属性”。

(3)选择“安全性”选项卡。

(4)选择“仅Windows”单选按钮(使用WindowsNT/2000身份验证模式)或“SQLServer和Windows”单选按钮，后者表示使用混合身份验证模式，如图10-3所示。

注意：改变设置后，必须停止并重新启动SQLServer服务器，设置才会生效。图10-3设置SQLServer身份验证模式10.1.3添加SQLServer身份验证登录帐户

SQLServer身份验证的登录帐户既适合于Windows平台的用户，也适合于非Windows平台的用户。

1.使用企业管理器添加登录帐户使用企业管理器添加SQLServer身份验证登录帐户的步骤如下：

(1)在企业管理器中展开SQLServer服务器组中的相应服务器。

(2)右击“安全性”→“登录”→“新建登录”。

(3)选中“SQLServer身份验证”单选按钮。

(4)在“名称”文本框中输入一个用户名，并在“密码”文本框中输入密码，如图10-4所示。(5)在“数据库”下拉列表框中选择默认数据库，在“语言”下拉列表框中选择默认语言。如图10-4所示，选择student数据库作为默认数据库，选择SimplifiedChinese(中文简体)作为默认语言。

(6)单击“确定”按钮。

(7)在随后出现的“确认密码”对话框中再次输入密码并单击“确定”按钮。

注意：需要确保新添加的帐户拥有对默认数据库的访问权限，其设置方法以后介绍。图l0-4添加SQLServer身份验证登录帐户2.使用Transact-SQL语句添加登录帐户除了使用企业管理器外，还可以使用Transact-SQL语句添加SQLServer身份验证登录帐户，这需要使用系统存储过程sp_addlogin。

sp_addlogin的参数可以有帐户名、密码、默认数据库、默认语言等。其中，帐户名必须指定(必选参数)，其他参数可以省略(使用默认值)。

【例10-1】为用户stu创建一个SQLServer登录帐户，没有指定密码或默认数据库。程序清单如下：

EXECsp_addlogin'stu'

在查询分析器中执行上面程序的结果是“已创建新登录”。在企业管理器中，刷新后的结果如图10-5所示，可以确认用户帐户stu已成功添加。图10-5在企业管理器中显示添加用户帐户后的结果【例10-2】在查询分析器中为用户stu1创建一个SQLServer登录帐户，并指定密码pwd以及名为College的默认数据库。程序清单如下：

EXECsp_addlogin'stu1','pwd','College'

运行上述代码后，我们可以用“stu1”这个帐户登录，成功登录后，在数据库框会显示“College”。但此时，“stu1”只是指向“College”数据库，还不具有访问该数据库的权限。10.1.4授权Windows身份验证登录帐户授权Windows身份验证登录帐户，实际上是为已经存在的WindowsNT/2000用户帐户或组帐户授权，准许该帐户进入SQLServer服务器时不输入密码就可以登录。1.使用企业管理器授权Windows帐户登录SQL服务器

(1)在企业管理器中，展开SQLServer服务器组中的相应服务器。

(2)右击“安全性”→“登录”，选择“新建登录”，打开“新建登录”对话框。

(3)选中“Windows身份验证”单选按钮。

(4)在“名称”框中输入“计算机名称(或域名)\用户名(或组名)”形式的用户帐户名，例如“xjsb6031\stu”，如图10-6所示，也可以单击“名称”框右侧的按钮，并在随后弹出的对话框中选择用户，如图10-7所示。图10-6使用企业管理器添加登录帐户图10-7选择用户对话框(5)在“数据库”下拉列表框中选择默认数据库，在“语言”下拉列表框中选择默认语言。如图10-6所示，选择student数据库作为默认数据库，选择SimplifiedChinese(中文简体)作为默认语言。

(6)单击“确定”按钮。注意：需要确保新添加的帐户是已存在的WindowsNT/2000用户帐户或组帐户。可以使用WindowsNT/2000的“计算机管理”或“域用户管理器”预先添加需要的用户帐户或组帐户。2.使用Transact-SQL语句授权Windows帐户登录SQL服务器除了上面的方法外，还可以使用Transact-SQL语句添加Windows身份验证登录帐户，这需要使用系统存储过程sp_grantlogin。

sp_grantlogin只有一个参数，即“计算机名称(或域名)\用户名(或组名)”形式的用户帐户名。

【例10-3】准许WindowsNT/2000用户“xjsb6031\stu”联接到SQLServer。程序清单如下：

EXECsp_grantlogin'xjsb6031\stu'

在查询分析器中运行上述语句，即可实现WindowsNT/2000用户xjsb6031\stu联接到SQLServer。10.1.5修改登录帐户属性在添加了新的登录帐户之后可以修改其属性，如默认数据库、默认语言等。在企业管理器中修改登录帐户属性，只需双击要修改属性的登录帐户，并在属性对话框中进行修改即可。注意：对于SQLServer的标准帐户，可以在SQL中修改其密码。对于WindowsNT/2000帐户，只能使用WindowsNT/2000的“计算机管理器”或“域用户管理器”修改帐户密码。使用Transact-SQL语句修改帐户属性涉及以下系统存储过程。

(1) sp_password：用于修改帐户密码。

(2) sp_defaultdb：用于修改默认数据库。

(3) sp_defaultlanguage：用于修改默认语言。【例10-4】将登录帐户stu的密码更改为“12345678”。程序清单如下：

EXECsp_passwordnull,'12345678','stu'

上面语句中的“null”位置上可以是原密码，此处的“null”表示不管原密码是什么，直接修改成新密码。

【例10-5】将master设置为用户stu的默认数据库。程序清单如下：

EXECsp_defaultdb'stu','master'【例10-6】将登录用户stu的默认语言设置为法语。程序清单如下：

EXECsp_defaultlanguage'stu','french'10.1.6拒绝登录帐户有时可能需要暂时禁止一个登录帐户联接到SQLServer，过一段时间后再恢复，这可以通过企业管理器或Transact-SQL语句来实现。

1.使用企业管理器拒绝登录帐户使用企业管理器拒绝登录帐户的步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。

(2)选择“安全性”→“登录”，在右窗格的登录帐户列表中双击要拒绝访问的WindowsNT/2000帐户，打开帐户属性对话框。

(3)在帐户属性对话框中，选中“拒绝访问”单选按钮，如图10-8所示。

(4)单击“确定”按钮。图10-8拒绝登录帐户2.使用Transact-SQL语句拒绝登录帐户利用系统存储过程sp_denyaccess可以暂时禁止一个WindowsNT/2000帐户的登录权限。

【例10-7】阻止用户“xjsb6031\stu”登录到SQLServer。程序清单如下：

EXECsp_denylogin'xjsb6031\stu'

当需要恢复被拒绝登录帐户的登录权限时，可以使用企业管理器，也可以使用系统存储过程sp_grantlogin。

注意：只能拒绝WindowsNT/2000帐户登录，对于SQLServer登录帐户，只能通过删除该登录帐户来禁止该帐户联接到SQLServer。10.1.7删除登录帐户如果需要永远禁止某帐户访问SQLServer，则可以将该帐户删除。

1.使用企业管理器删除登录帐户使用企业管理器删除登录帐户的步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。

(2)选择“安全性”→“登录”，右击右窗格登录帐户列表中要删除的帐户，从弹出的快捷菜单中选择“删除”选项，如图10-9所示。

(3)在随后弹出的提示框中单击“是”按钮。图10-9删除登录帐户2.使用Transact-SQL语句删除登录帐户使用系统存储过程删除登录帐户时，要根据不同类型的登录帐户使用不同的系统存储过程。对于WindowsNT/2000帐户，需要使用系统存储过程sp_revokelogin，而对于SQLServer帐户，则需使用系统存储过程sp_droplogin。【例10-8】撤消WindowsNT/2000帐户“xjsb6031\stu”访问数据库(Windows帐户仍在)。程序如下：

EXECsp_revokelogin'xjsb6031\stu'【例10-9】删除SQLServer帐户stu1。程序如下：

EXECsp_droplogin'stu1'

删除某登录帐户后，该帐户将在企业管理器窗口的登录帐户列表中消失。10.2数 据 库 用 户10.2.1管理数据库用户在数据库中，一个用户或工作组取得合法的登录帐户，只表明该帐户可以通过Windows认证或者SQLServer认证，并不表明可以对数据库数据和数据库对象进行某种或者某些操作，管理员必须在数据库中为用户建立一个用户帐户，才能使该用户访问该数据库。每个数据库中都有一个独立的数据库用户列表。每个数据库用户与服务器登录帐户间都存在着一种映射关系。系统管理员可以将一个服务器登录帐户映射到用户需要访问的每一个数据库中的一个用户帐户或角色上。一个登录帐户在不同的数据库中可以映射成不同的用户，从而拥有不同的权限。

在SQLServer中，每个数据库一般都有两个默认的用户：dbo和guest。dbo代表数据库的拥有者。例如，sysadmin服务器角色的成员被自动映射成dbo。在安装SQLServer系统时，guest用户被加入到master、pubs、tempdb和northwind数据库中，该用户主要是让那些没有自己的用户帐户的SQLServer登录者作为默认用户，访问具有guest用户的数据库。使用企业管理器可以管理数据库的用户。在企业管理器中，展开SQLServer服务器组中相应的服务器，选择“数据库”→“数据库名”→“用户”，可以在右窗格中看到该数据库的所有用户。图10-10显示了系统数据库master中的所有用户列表。图10-10显示系统数据库master中的所有用户列表10.2.2添加数据库用户数据库用户是用户进入数据库服务器后的又一道关卡。登录帐户相当于进入大楼前给门卫看的通行证，而数据库用户则是进入房间的钥匙。不同的房间需要不同的钥匙，一个人也可能有多个房间的钥匙。可以为一个登录帐户在一个或多个数据库中添加用户。数据库用户的名字可以与登录帐户相同，也可以不同。

1.使用企业管理器添加数据库用户使用企业管理器添加数据库用户的步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。(2)展开“数据库”，再展开待添加用户的数据库，右击“用户”，在弹出的快捷菜单中选择“新建数据库用户”选项，如图10-11所示，将弹出“数据库用户属性”对话框，如图10-12所示。

(3)在“登录名”下拉列表框中选择一个登录帐户(这里选中的是Windows登录帐户，也可以选SQL标准登录帐户)。

(4)在“用户名”框中输入映射后的用户名(如果选SQL标准登录帐户，则数据库用户名默认和登录名相同)，如图10-13所示。图10-11“新建数据库用户”选项图10-12“数据库用户属性”对话框图10-13选择登录帐户并输入用户名(5)如果需要，则还可以在“数据库角色成员”列表中选择除public以外的其他数据库角色。关于数据库角色的内容将在后面的章节中讲述。在图10-13中，将登录帐户“xjsb6031\stu”映射成college数据库的用户stuAdmin。这样，登录帐户“xjsb6031\stu”就可以用“stuAdmin”这个用户名进入college数据库了。

2.使用Transact-SQL语句添加数据库用户除上述方法外，还可以使用系统存储过程sp_grantdbaccess添加数据库用户。

【例10-10】在college数据库中为WindowsNT/2000用户“xjsb6031\teacher”添加数据库用户，并取名为teachAdmin。程序清单如下：

USEcollege--选中college数据库

EXECsp_grantdbaccess'xjsb6031\teacher','teachAdmin'

这样，登录帐户“xjsb6031\teacher”就可以用“teachAdmin”这个用户名进入college数据库了。如果选中其他数据库运行上述语句，则可为登录帐户“xjsb6031\teacher”在多个数据库中创建用户，这个用户可同时访问多个数据库。10.2.3删除数据库用户当我们要取消某个登录帐户对数据库访问时，就要删除数据库的用户。删除数据库的用户实际就是删除一个登录帐户与一个数据库用户的映射，删除之后在数据库用户列表中就少了这个用户，与这个数据库用户对应的登录帐户就失去了这个数据库的访问权，这时，该登录帐户还在，该登录帐户对其他数据库的访问权并不受影响。在企业管理器中，删除一个数据库用户时可以用鼠标右击欲删除的用户，从弹出的快捷菜单中选择“删除”选项，在随后弹出的确认对话框中单击“是”按钮，即可将该用户从数据库中删除，如图10-14所示。这时该用户所对应的登录帐户仍然存在。图10-14删除数据库用户

利用Transact-SQL语句同样可以删除数据库用户，下面是利用系统存储过程sp_revokedbaccess删除用户的例子。

【例10-11】从college数据库中删除用户teachAdmin。程序如下：

USEcollegeEXECsp_revokedbaccess'teachAdmin'

运行上述语句后，登录帐户“xjsb6031\teacher”就失去了对college数据库的访问权。10.3权 限 及 设 置权限用来指定授权数据库用户在数据库中可以执行的操作。用户在登录到SQLServer之后，其用户帐户归属的WindowsNT/2000组或角色被赋予的权限决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。在每个数据库中，用户的权限独立于用户帐户和用户在数据库中的角色，每个数据库都有自己独立的权限系统。10.3.1数据库权限

SQLServer中包括三种类型的权限：对象权限、语句权限和预定义权限。1.对象权限对象权限表示数据库用户对特定的数据库对象(即表、视图、存储过程等)的操作权限，它决定了能对表、视图等数据库对象执行哪些操作。进入数据库的用户想对某一对象进行操作，必须具有相应的操作权限，具体包括：●对于表和视图的权限：该权限用来控制用户在表和视图上执行SELECT、INSERT、UPDATE和DELETE语句的能力。●对于表和视图的列的权限：该权限用来控制用户在单个字段上执行SELECT、UPDATE和REFERENCES操作的能力。●对于存储过程的权限：该权限用来控制用户执行EXECUTE语句的能力。2.语句权限语句权限表示数据库用户对数据库的操作权限，即创建数据库或者创建数据库中其他内容所需要的权限类型。这些语句通常是一些具有管理性的操作，如创建数据库、表和存储过程等。这种语句虽然仍包含有操作的对象，但这些对象在执行该语句之前并不存在于数据库中。因此，语句权限针对的是某个SQL语句，而不是数据库中已经创建的特定的数据库对象。只有sysadmin、db_owner和db_securityadmin角色的成员才能授予语句权限。可用于语句权限的Transact-SQL语句及其含义如下：● Createdatabase：用于创建数据库。● Createtable：用于创建表。● Createview：用于创建视图。● Createrule：用于创建规则。● Createdefault：用于创建默认。● Createprocedure：用于创建存储过程。● Createindex：用于创建索引。● Backupdatabase：用于备份数据库。● Backuplog：用于备份事务日志。

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

注意：预定义权限不能明确地赋予和撤消。10.3.2使用企业管理器设置权限权限的设置包括对权限的授权(grant)、拒绝(deny)和撤消(revoke)。在SQLServer中，可以使用SQLServer企业管理器来设置权限，但对于语句权限和对象权限使用的方法是不同的。

1.使用企业管理器设置语句权限使用企业管理器管理语句权限的步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。

(2)展开“数据库”，右击指定的服务器，在弹出的快捷菜单中选择“属性”选项，将弹出数据库属性对话框，如图10-15所示。图10-15数据库属性对话框(3)单击“权限”选项卡，打开如图10-16所示的“权限”选项卡。

(4)在如图10-17所示的“权限”选项卡中列出了该数据库中所有的用户和角色，以及所有的语句权限。可以单击“用户/角色”与权限交叉点上的方框改变权限的状态。其中，“√”表示用户被授予权限(简称授权)；“×”表示用户被禁止权限(简称禁止)；“□表示没有给该用户授予权限或授予(或禁止)后又被收回(简称撤消)。图10-17表示用户xjsb6031\stu被授予了创建表的权限，同时被禁止了创建视图的权限。

(5)设置完成后，单击“确定”按钮，使设置生效并返回企业管理器。图10-16数据库属性中的“权限”选项卡图10-17修改用户/角色的权限2.使用企业管理器设置对象权限管理对象权限有以下两种方式：●从用户/角色的角度来管理，即设置一个用户/角色能对哪些对象执行哪些操作。●从对象的角度来管理，即设置一个数据库对象能被哪些用户/角色执行哪些操作。例如，要为stu赋予查询学生表的权限，可以按以下步骤进行：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。

(2)展开“数据库”，再展开指定的college数据库，选择“用户”，在右窗格的用户列表中双击stu用户，打开“数据库用户属性”对话框，如图10-18所示。图10-18“数据库用户属性”对话框(3)单击“权限”按钮，打开“权限”选项卡，给stu用户授予对学生表执行SELECT的权限，如图10-19所示。

(4)若单击图10-19中的“列”按钮，则可以指定对列操作的权限，如图10-20所示。

(5)设置完成后单击“确定”按钮。上述过程是先选中用户，再对该用户对应的所有对象设置权限；也可以从某一对象(比如学生表)出发，设置所有用户对该对象的权限。下面从对象的角度来完成相同的工作，即针对学生表，设置用户stu拥有的查询该表的权限。具体的设置步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。图10-19“权限”选项卡图10-20设置列操作的权限(2)展开“数据库”，再展开指定的college数据库，选择“表”，在右边窗口的表用户列表中双击学生表，打开“表属性”对话框，如图10-21所示。

(3)单击对话框中的“权限”按钮，打开“权限”选项卡，其中列出了该数据库中所有的用户和角色，以及所有的语句权限。给stu用户授予对学生表执行UPDATE的权限，如图10-22所示。

(4)若单击“列”按钮，则可以指定对列操作的权限。

(5)设置完成后单击“确定”按钮。在图10-22中，college数据库只有一个用户。如果有很多用户，则会同时排列出来，可同时授权。图10-21“表属性”对话框图10-22指定权限对话框3.验证用户权限

【例10-12】使用stu用户登录，查询和修改“学生表”，验证stu的SELECT和UPDATE权限，如图10-23所示。程序清单如下：

select*from学生表where学号='05209001'update学生表set电话='66666666'where学号='05209001'select*from学生表where学号='05209001'图10-23验证stu的权限10.3.3使用Transact-SQL语句设置权限

Transact-SQL语句中有GRANT、DENY、REVOKE三种命令可以设置权限。

(1)GRANT命令：用于把权限授予某一用户，以允许该用户执行针对该对象的操作或允许其运行某些语句。

(2)DENY命令：用来禁止用户对某一对象或语句的权限，它不允许该用户执行针对数据库对象的某些操作或不允许其运行某些语句。

(3) REVOKE命令：用来撤消用户对某一对象或语句的权限，使其不能执行操作。(如果该用户是角色成员，且角色被授权，则另当别论，这种情况将在10.5.3节中讨论。)

用上述命令管理对象权限的语法如下：

GRANT|DENY|REVOKEa11|权限名ON表名|其他对象名TO用户|角色角色可以理解为用户组。

【例10-13】将数据库college中名为“学生表”的SELECT权限赋予stu用户。程序清单如下：

USEcollegeGRANTSELECTON学生表TOstu

数据库用户stu因此拥有了对学生表读的权限。如果要一次授予增、删、改、读多种权限，则可在GRANT后面写上INSERT、DELETE、UPDATE、SELECT中的任意几个，用“，”分开即可。

【例10-14】下面是对象权限的示例，本例将禁止stu对学生表的SELECT权限。程序清单如下：

USEcollegeDENYSELECTON学生表TOstu

在数据库管理员执行了上述语句后，如果用户stu再次对学生表执行SELECT语句，则系统将提示没有权限，操作遭到拒绝，如图10-24所示。

【例10-15】授予用户stu在college数据库中创建表的语句权限。程序清单如下：

USEcollegeGRANTCREATETABLETOstu

运行上述语句后，可以得到如图10-17所示的“√”，也就是用户stu获得了创建表的权限。图10-24出错信息示例10.4服 务 器 角 色角色是从SQLServer7.0开始引进的新概念，它代替了以前版本中组的概念。利用角色，SQLServer管理者可以将某一组用户设置为某一角色，这样只要对角色进行权限设置便可以实现对所有用户权限的设置，大大减少了数据库管理员的工作量。SQLServer提供了通常管理工作的预定义服务器角色和数据库角色。用户还可以创建自己的数据库角色，以表示某一类用户可以进行同样的操作。当用户需要执行不同的操作时，只需将该用户加入到不同的角色中即可，从而避免了对每个类似的用户反复进行同样的授权许可。

服务器角色是指根据SQLServer的管理任务以及这些任务相对的重要性等级，把具有SQLServer管理职能的用户划分为不同的用户组，每一组所具有的管理SQLServer的权限都是SQLServer内置的，不能增加或减少服务器角色，也不能修改服务器角色的权限，只能向其中加入用户或者其他角色。要加入服务器角色，必须要有登录帐户。10.4.1固定的服务器角色

SQLServer提供的固定服务器角色如下：●系统管理员(sysadmin)：拥有SQLServer所有的权限许可。●服务器管理员(serveradmin)：管理SQLServer服务器端的设置。●磁盘管理员(diskadmin)：管理磁盘文件。●进程管理员(processadmin)：管理SQLServer系统进程。●安全管理员(securityadmin)：管理和审核SQLServer系统登录。●安装管理员(setupadmin)：增加、删除联接服务器，建立数据库复制以及管理扩展存储过程。●数据库创建者(dbcreator)：创建数据库并对数据库进行修改。●大容量插入操作管理者(bulkadmin)：可以执行大容量插入操作。在企业管理器中，展开SQLServer服务器组中相应的服务器，选择“安全性”→“服务器角色”，在右侧窗格中将显示所有的服务器角色及其描述，如图10-25所示。图10-25在企业管理器中显示服务器角色及其描述10.4.2固定服务器角色的应用如果将一个登录帐户加入到一个服务器角色中，作为该角色的一个成员，则可以使该登录帐户自动拥有该服务器角色预定义的权限。一个登录帐户可以同时属于多个角色，也可以不属于任何角色。默认情况下，只有sysadmin服务器角色含有预定成员，包括WindowsNT/2000系统管理员组帐户(BUILTIN/Administrators)、系统管理员帐户(计算机名称/Administrator)、SQLServer帐户sa。可以使用企业管理器或Transact-SQL语句更改服务器角色成员。1.使用企业管理器更改服务器角色成员使用企业管理器更改服务器角色成员的步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。

(2)选择“安全性”→“服务器角色”，双击右窗格服务器角色列表中要更改的服务器角色，将弹出“服务器角色属性”对话框，其中显示出当前服务器角色成员列表，如图10-26所示。

(3)选中一个服务器角色成员并单击“删除”按钮，即可从服务器角色成员列表中删除该服务器角色成员。图10-26“服务器角色属性”对话框(4)单击“添加”按钮，将弹出“添加成员”对话框，如图10-27所示。选择待添加成员并单击“确定”按钮，可以将选中的一个或多个登录帐户添加到服务器角色成员列表中。

(5)单击“服务器角色属性”对话框的“权限”选项卡，可以查看该服务器角色所拥有的权限列表，如图10-28所示。

(6)单击“确定”按钮，确认修改。图10-27“添加成员”对话框图10-28“服务器角色属性”对话框的“权限”选项卡

在企业管理器中，也可以在“登录帐户属性”对话框中改变该帐户所属的服务器成员角色，操作步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。

(2)选择“安全性”→“登录”，双击右窗格登录帐户列表中要更改服务器角色成员的登录帐户，将弹出“SQLServer登录属性”对话框。

(3)选择“SQLServer登录属性”对话框的“服务器角色”选项卡，如图10-29所示。

(4)从“服务器角色”列表中选中或取消选中，即可将该登录帐户加入服务器角色或从服务器角色中删除。图10-30所示为将“xjsb6031\stu”登录帐户加入SystemAdministrators角色。

(5)单击“确定”按钮，确认修改。图10-29“服务器角色”选项卡图10-30将“xjsb6031\stu”登录帐户加入SystemAdministrators角色2.使用Transact-SQL语句更改服务器角色成员使用系统存储过程sp_addsrvrolemember、sp_dropsrvrolemeber可以添加和删除服务器角色成员。

【例10-16】将WindowsNT/2000帐户“xjsb6031\teacher”添加到sysadmin服务器角色中。程序清单如下：

EXECsp_addsrvrolemember'xjsb6031\teacher','sysadmin'

返回信息：'xjsb6031\teacher'已添加到角色'sysadmin'中。说明服务器角色成员添加成功。

注意：如果还没有对该登录帐户授予SQLServer访问权限，那么将自动对其授予访问权限。【例10-17】将WindowsNT/2000帐户“xjsb6031\teacher”从sysadmin服务器角色中删除。程序清单如下：

EXECsp_dropsrvrolemember'xjsb6031\teacher','sysadmin'

返回信息：'xjsb6031\teacher'已从角色'sysadmin'中除去。说明服务器角色成员删除成功。此外，还有以下几个和服务器角色管理相关的系统过程。● sp_helpsrvrole：返回服务器角色列表。● sp_helpsrvrolemember：返回指定服务器角色的信息。若不提供参数，则返回所有的服务器角色信息。● sp_srvrolepermission：返回一个服务器角色的权限。10.5数 据 库 角 色在前面的章节中，我们介绍了为数据库用户授权。如果有很多用户对很多表有相同的权限(比如，某单位的销售部门有50个人，部门数据库有60个表或视图)，那么设置工作量会很大，而且检查工作量也很大，因此我们要考虑能否找到一种捷径，设置一个用户组，授予相应的权限，把用户添加到用户组中，这样每个用户既拥有了相同的数据库权限，而且设置和检查都很简单。数据库角色就是这样一种用户组。比如，在生活中，有一个高尔夫球场只对俱乐部会员开放(会员具有相同的许可)，如果你参加了这个俱乐部，那么就可以进入该球场。另外，还有一个游泳馆，只对游泳俱乐部会员开放，你也可以参加游泳俱乐部，获得准许进入那个游泳馆。一个人可以同时参加多个俱乐部，获得多项娱乐准许。不同的数据库角色就像不同的俱乐部，有不同的许可；一个用户就像一个娱乐爱好者，可以经批准成为不同角色的成员。10.5.1数据库角色的分类

SQLServer提供了两种类型的数据库角色，即固定的数据库角色和用户自定义的数据库角色。用户自定义的数据库角色就是把一组用户共有的权限设置给一个对象，然后定义一组用户成为角色的成员，这些用户就具有了数据库用户所有的权限。

用户自定义的数据库角色有两种类型，即标准角色和应用程序角色。标准角色通过对用户权限等级的认定将用户划分为不同的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。所有固定的数据库角色或SQLServer管理者自定义的某一角色都是标准角色。应用程序角色是一种比较特殊的角色。当我们打算让某些用户只能通过特定的应用程序间接地存取数据库中的数据，而不是直接存取数据库数据时，就应该考虑使用应用程序角色。当某一用户使用了应用程序角色时，就放弃了本身所有的数据库权限，拥有的只是应用程序角色被设置的角色。通过应用程序角色能够以可控的方式限定用户的语句或者对象权限。

标准角色是指通过把用户加入到不同的角色中而使用户具有这样或那样的语句权限或对象权限，获得的是数据库用户和各角色权限的总和。应用程序角色首先将各种需要的权限赋予应用程序角色，然后将应用程序角色加入到某一特定的应用程序中，通过激活应用程序角色实现对应用程序存取数据的可控性。只有应用程序角色被激活，角色才有效。用户可以且只可以执行应用程序角色相应的权限，而不管用户在激活程序角色以前有多少权限。10.5.2固定的数据库角色固定的数据库角色是在每个数据库中都存在的预定义组，SQLServer已经预先定义了这些角色所具有的管理、访问数据库的权限，而且SQLServer管理者不能对其所具有的权限进行任何修改。在数据库中使用固定的数据库角色可以将不同级别的数据库管理工作分给不同的角色，从而有效地实现工作权限的传递。

SQLSenrer提供了10种常用的固定数据库角色，这些固定数据库角色信息存储在系统表sysusers中。这些固定的数据库角色的具体含义如下：● public：维护全部默认权限。● db_owner：数据库的所有者，可以对所拥有的数据库执行任何操作。● db_accessadmin：可以增加或者删除数据库用户、工作组和角色。● db_ddladmin：可以增加、删除和修改数据库中的任何对象。● db_securityadmin：管理角色、角色成员、管理对象和语句权限。● db_backupoperator：可以备份和恢复数据库。● db_datareader：能且仅能对数据库中的任何表执行SELECT操作，从而读取所有表的信息。● db_datawriter：能够增加、修改和删除表中的数据，但不能进行SELECT操作。● db_denydatareader：不能读取数据库中任何表中的数据。● db_denydatawriter：不能对数据库中的任何表执行增加、修改和删除操作。在固定的数据库角色中，public是一个特殊的数据库角色，数据库中的每个用户都是public角色中的成员。不能将用户、组或者其他角色指定给pubic角色，在每一个数据库中都包含public角色，但不能删除。一个人可以不参加任何团体，但仍然属于“大众”这个团体。public角色就是指“大众”，如果一个数据库要给每个用户某种权限，那么就应为public角色设置这种权限。

在企业管理器中，展开SQLServer服务器组中相应的服务器，展开“数据库”，再展开指定数据库，选择“角色”，在右窗格中列出数据库中已存在的角色。在未创建新角色之前，数据库中只有固定数据库角色，如图10-31所示。图10-31在企业管理器中列出数据库中已存在的角色10.5.3数据库标准角色在SQLServer中，可以利用企业管理器和系统存储过程两种方式创建数据库标准角色。

1.使用企业管理器创建数据库标准角色使用企业管理器创建数据库标准角色的步骤如下：

(1)在企业管理器中，展开SQLServer服务器组中相应的服务器。

(2)展开“数据库”，再展开指定数据库，右击“角色”，在弹出的快捷菜单中选择“新建数据库角色”选项，将弹出“数据库角色属性”对话框，如图10-32所示。

(3)在“名称”栏中输入自定义角色的名称，选中“标准角色”单选按钮。图10-32“数据库角色属性”对话框(4)单击“添加”按钮，在弹出的“添加角色成员”对话框中选中要添加的成员，如图10-33所示。单击“确定”按钮，回到“数据库角色属性”对话框。

(5)如图10-34所示，已将登录帐户stu2添加到数据库角色成员列表中。

(6)在图10-34所示的“数据库角色属性”对话框中，单击“删除”按钮也可以删除选中的角色成员。图10-33“添加角色成员”对话框图10-34将登录帐户stu2添加到数据库角色成员列表中2.使用Transact-SQL语句创建数据库标准角色使用Transact-SQL语句创建数据库标准角色通常需要用到以下系统存储过程。● sp_addrole：创建数据库角色。● sp_addrolemember：向数据库角色添加成员。

【例11-18】为college数据库创建一个名为dbAdmin的数据库角色。程序清单如下：

USEcollegeEXECsp_addrole'dbAdmin'

【例10-19】将SQLServer用户stu3添加到college数据库的dbAdmin角色中。程序清单如下：

USEcollegeEXECsp_addrolemember'dbAdmin','stu3'3.在企业管理器中删除数据库标准角色在企业管理器中，右击待删除角色，从弹出的快捷菜单中选择“删除”选项，并在随后弹出的确认对话框中选择“是”，就可以删除一个用户自定义角色。注意：首先，系统固定的数据库角色不能被删除；其次，不能删除一个有成员的角色，即必须首先删除该角色的成员，否则系统将提示应先删除其成员，如图10-35所示。图10-35删除角色前应先删除其成员4.使用Transact-SQL语句删除数据库标准角色使用Transact-SQL语句删除数据库标准角色所用到的系统存储过程如下：● sp_droprole：删除数据库角色。● sp_droprolemember：从数据库角色中删除成员。

【例10-20】将SQLServer用户stu3从college数据库的dbAdmin角色中删除。程序清单如下：

USEcollegeEXECsp_droprolemember'dbAdmin','stu3'10.5.4应用程序角色标准用户能够登录联接到SQLServer，并能运行赋予权限范围内的语句。有些情况下，人们只希望用户在某应用程序中具有相关权限，而不希望他们使用查询分析器或任何其他工具访问SQLServer。在这种情况下，就可以使用应用程序角色。应用程序角色需要激活才能使用，一般的使用方法是：由另一标准用户激活应用程序角色，这样应用程序角色就可以获得其相应的权限了。标准角色和应用程序角色间的主要差别如下：

(1)应用程序角色不能包含成员。

(2)应用程序角色默认设置为未激活状态，要由其他标准角色激活后才能使用。【例10-21】使用企业管理器在College数据库中创建名为“AppRole01”、密码为“ABC”的应用程序角色。

(1)在控制台中展开College数据库。

(2)右击“角色”，然后单击“新建数据库角色”命令。

(3)在“名称”栏中输入新的应用程序角色的名称，这里输入“AppRole01”。

(4)在“数据库角色类型”下单击“应用程序角色”，然后输入密码，这里输入“ABC”。

(5)单击“确定”按钮完成本例。

【例10-22】使用系统存储过程sp_addapprole在College数据库中添加一名为“AppRole02”，密码为“ABC”的应用程序角色。在查询分析器中运行如下命令：

USECollegeGOEXECsp_addapprole'AppRole02'，'ABC‘

【例10-22】生成应用程序角色并指定权限，演示应用程序角色如何被激活。在查询分析器中运行如下命令：

(1)增加应用程序角色。

USECollegeEXECSP_addapprole'AppRole03','ABC'(2)授权AppRole03具有班级表的SELECT权限。

GRANTSELECTON班级表TOAppRole03(3)创建登录名为“stu5”、密码为“ABC”、默认数据库为College并能联接到College数据库的用户。

EXECSp_addlogin'stu5','ABC','College'USECollegeEXECsp_grantdbaccess'stu5','stu5'(4)以stu5身份联接另一查询分析器窗口。在查询分析器主菜单上选择“文件”，再选择“联接”命令，如图10-36所示，选择“SQLServer身份认证”。登录名输入“stu5”，密码输入“ABC”，单击“确定”按钮完成联接。图10-36以stu5身份联接另一查询分析器窗口(5)在新的查询分析器窗口中运行如下命令：

SELECT*FROM班级表运行结果如图10-37所示，由于用户stu5没有执行该语句的权限，因此执行遭到拒绝(前面新建的stu5用户)。

(6)激活应用程序角色AppRole03，执行后返回，如图10-38所示。

EXECSP_SETAPPROLE'AppRole03','ABC’(7)重新执行对班级表的SELECT操作，并与应用程序角色激活前进行对比。由于应用程序角色AppRole03已激活，且有执行该语句的权限，因此下列语句可正常执行：

SELECT*FROM班级表与此同时，也证明了用户stu5原先所具有的权限丢失。图10-37执行语句遭到拒绝图10-38激活应用程序角色AppRole0310.6权限管理10.6.1权限计算权限的设置是指对对象权限和语句权限的设置。权限可以由数据库所有者和角色进行管理。权限管理的内容包括以下三个方面的内容：●授予权限(有权)：允许某个用户或角色对一个对象执行某种操作或某种语句。●禁止访问(禁止)：禁止某个用户或角色访问某个对象。即使该用户或其加入的角色在其他途径被授权访问，或者由于继承而获得访问权，结果仍然被禁止执行相应的操作。多重权限中只要一个禁止，其他授权均无效。●撤消权限(无权)：即某个用户或角色对一个对象没有执行某种操作或某种语句的许可，也没有禁止。没有许可就是无权访问，如果其他途径被授权访问或继承访问权，则可获得访问权。实际上，用户权限是对该用户授权和继承角色权限的累加，有权+禁止=禁止，有权+无权=有权，无权+禁止=禁止。如果使用应用程序角色，则一旦激活，只有应用程序角色的权限，原有权限全部作废，不能累加。

10.6.2权限管理的应用下面我们分步来计算权限。

(1)首先我们定义一个登录帐户STUD，并在College数据库中定义具有同名的数据库用户。

分析：这时如果以STUD登录，则有PUBLIC角色所具有的权限，因为每一个用户都默认是PUBLIC角色的成员。操作：

usemastergoEXECsp_addlogin'stud'USEcollege--选中college数据库

EXECsp_grantdbaccess'stud','stud'

验证：运行上列代码