第 8 章系统安全管理

第8章系统安全管理 8.1 SQL Server 2000的身份认证模式 8.2建立和管理用户账号 8.3 服务器角色与数据库角色 8.4 事务

8.1 SQL Server 2000的身份认证模式 SQL Server 2000的身份认证模式是指系统确认用户的方式。SQL Server2000有两种身份认证模式：Windows NT认证模式和SQL Server认证模式，图8.1给出了这两种方式登录SQL Server服务器的情形。图8.1两种认证方式登录SQL Server服务器的情形

8.1 SQL Server 2000的身份认证模式 1．Windows NT认证模式用户登录Windows NT时进行身份认证，登录SQL Server时不再进行身份验证。对于Windows NT认证模式登录的几点重要说明： (1) 必须将NT网络账号加入到SQL Server中，才能采用NT网络账号登录SQL Server。 (2) 如果使用NT网络账号登录到另一个网络的SQL Server，必须在NT网络中设置彼此的托管权限。 2．SQL Server认证模式在SQL Server认证模式下，SQL Server服务器要对登录的用户进行身份验证，对于Windows 9x系列的操作系统只能使用SQL Server认证模式，而当SQL Server 在Windows NT或Windows2000/2003或Windows XP上运行时，系统管理员设定登录认证模式的类型可为Windows NT认证模式和混合模式。当采用混合模式时，SQL Server系统既允许使用Windows NT账号登录，也允许使用SQL Server账号登录。

8.2.1 Windows NT认证模式登录账号的建立与取消 1. 通过企业管理器建立Windows NT认证模式的登录账号对于Windows NT或Windows 2000/2003或Windows XP操作系统，安装本地SQL Server 2000的过程中，允许选择认证模式。步骤如下（在此以Windows XP为例）：第１步创建Windows XP的用户。以管理员身份，登录到Windows XP，在桌面选择“我的电脑”“管理”，进入如图8.2所示的界面，选择“本地用户和组”。选择“用户”图标右击，出现一快捷菜单，选择菜单项“新用户”，进入如图8.3所示的界面，输入用户名、密码，选择“创建”按钮，然后选择“关闭”按钮。

8.2.1 Windows NT认证模式登录账号的建立与取消 图9.3 Windows XP 本地计算机创建新用户的界面图9.2 Windows XP本地计算机管理界面

8.2.1 Windows NT认证模式登录账号的建立与取消 第2步将NT网络账号加入到SQL Server中：以管理员身份登录到SQL Server，进入企业管理器，选择如图8.4中的“登录”图标右击，出现快捷菜单，选择“新建登录”，出现如图8.5所示的界面，点击“常规”选项卡的“浏览”按钮，可选择用户名或用户组添加到SQL Server登录用户列表中。例如：本例的用户名为：zhou。图8.4 在企业管理器中选择“登录”图标右击

8.2.1 Windows NT认证模式登录账号的建立与取消 图8.5 SQL Server信任连接设置窗口

8.2.1 Windows NT认证模式登录账号的建立与取消 2．通过SQL命令建立Windows NT认证模式的登录账号创建Windows NT、Windows 2000/2003或Windows XP的用户或组后，使用系统存储过程sp_grantlogin可将一个Windows NT或Windows2000/2003或Windows XP的用户或组的登录账号添加到SQL Server中，以便通过Windows 身份验证可连接到SQL Server。语法格式： sp_grantlogin [@loginame =] 'login' 注意：在用户定义事务内无法执行 sp_grantlogin；仅 sysadmin 或 securityadmin 固定服务器角色的成员可以执行sp_grantlogin。

8.2.1 Windows NT认证模式登录账号的建立与取消 【例8.1】将 Windows NT 域dream中的zhouym用户加入到 SQL Server中。 EXEC sp_grantlogin ' dream\zhouym ' 或 EXEC sp_grantlogin [Nanjing\chengfang] 3. Windows NT认证模式登录账号的取消通过执行系统存储过程sp_revokelogin可取消Windows NT 用户或组登录SQL Server的账号。语法格式： sp_revokelogin [ @loginame = ] 'login' 参数含义： @loginame =:常量字符串； 'login'：Windows NT 用户或组的名称，为域\用户形式返回值：0（成功）或 1（失败）。

8.2.1 Windows NT认证模式登录账号的建立与取消 说明： (1) 应注意sp_revokelogin 与sp_denylogin的区别。若执行sp_denylogin ‘dream\zhouym’，即使授权dream\Admins 的登录权限，而dream\zhouym却没有登录权限。 (2) 使用 sp_droplogin 可删除用 sp_addlogin 添加的 SQL Server 登录。 (3) 在用户定义事务内部不能执行 sp_revokelogin。 (4) 只有 sysadmin 或 securityadmin 固定服务器角色的成员才能执行 sp_revokelogin。【例8.2】取消 Windows NT 用户 dream\zhouym登录SQL Server的账号。 EXEC sp_revokelogin ‘dream\zhouym’ 或 EXEC sp_revokelogin [dream\zhouym] 通过企业管理器也可以取消登录账号，有兴趣的读者可以试一试。

8.2.2 混合认证模式下SQL Server登录账号的建立与删除在Windows NT或Windows2000/2003或Windows XP环境下，如果要使用SQL Server账号登录SQL Server如何实现？首先应将SQL Server的认证模式设置为混合模式。步骤如下：第1步在企业管理器中，选择要登录的SQL Server服务器图标右击，出现一快捷菜单，选择菜单项“属性”，出现如图8.6的SQL Server服务器属性配置窗口。

8.2.2 混合认证模式下SQL Server登录账号的建立与删除第2步选择“安全性”选项卡，如图8.7所示，选择身份验证为“SQL Server与Windows”，选择“确定”按钮。图8.6 SQL Server服务器属性配置窗口图8.7 SQL Server身份认证方式配置窗口

8.2.2 混合认证模式下SQL Server登录账号的建立与删除设置混合认证方式后，如何创建SQL Server的登录账号呢？可在企业管理器中创建，也可利用系统存储过程创建。图8.8 SQL Server登录账号新建窗口

8.2.2 混合认证模式下SQL Server登录账号的建立与删除 1．通过企业管理器创建SQL Server登录账号例如，要创建一个名为“zhang”的账号，步骤如下：第1步在企业管理器中选择“登录”图标右击，出现如图8.4的界面，选择“新建登录”菜单项，进入如图8.8的界面；第2步输入账号名、密码，选择“SQL Server身份验证”方式，选择“确定”按钮。 2. 利用系统存储过程创建SQL Server登录账号语法格式： sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ]

8.2.2 混合认证模式下SQL Server登录账号的建立与删除说明： (1) 不能从用户定义的事务中执行 sp_addlogin。 (2) 通过执行如下存储过程可根据需要修改有关参数： sp_password：更改用户密码； sp_defaultdb：更改用户的默认数据库。 (3) 只有 sysadmin 和 securityadmin 固定服务器角色的成员才可以执行sp_addlogin。

8.2.2 混合认证模式下SQL Server登录账号的建立与删除【例8.3】如下语句创建没有密码和默认数据库的登录账号。 EXEC sp_addlogin 'zhang' 【例8.4】创建登录 ID 为wang，密码为’dongdong’，并指定默认数据库为XSCJ。为用户 cheng创建一个 SQL Server 登录名，密码为‘chocolate’，默认数据库为pubs，默认语言为 us_english，SID为：0x0123456789ABCDEF0123456789ABCDEF。 EXEC sp_addlogin 'wang', 'dongdong', 'XSCJ' EXEC sp_addlogin 'cheng', 'chocolate', 'pubs', 'us_english', 0x0123456789ABCDEF0123456789ABCDEF 3. SQL Server登录账号的删除利用sp_droplogin系统存储过程可删除SQL Server登录账号。语法格式： sp_droplogin [ @loginame = ] 'login' 参数含义： 'login'：将被删除的登录账号名。返回值：0（成功）或 1（失败）。

8.2.2 混合认证模式下SQL Server登录账号的建立与删除说明： (1) 不能删除任何数据库现有用户的登录账号。必须首先使用 sp_dropuser 删除该用户。 (2) 不能删除系统管理员 (sa) 的登录账号。 (3) 不能在用户定义的事务内执行 sp_droplogin。 (4) 只有 sysadmin 和 securityadmin 固定服务器角色的成员才能执行 sp_droplogin。【例8.5】zhang是数据库XSCJ的用户，执行如下语句后能从 SQL Server 中删除登录账号zhang吗？ EXEC sp_droplogin 'zhang' 执行结果出现如下信息：登录 'zhang' 在一个或多个数据库中有别名或映射到了用户上。请除去这些用户或别名后再除去该登录。此时，应先使用 sp_dropuser 删除该用户，然后删除登录账号zhang。删除SQL Server登录账号也可通过企业管理器实现，请读者自己试试。

8.3.1 固定服务器角色 服务器角色独立于各个数据库。如果我们在SQL Server中创建一个登录账号后，要赋予该登录者具有管理服务器的权限，此时可设置该登录账号为服务器角色的成员。SQL Server提供了以下固定服务器角色： ● sysadmin：系统管理员，可对SQL Server服务器进行所有的管理工作，为最高管理角色。 ● securityadmin：安全管理员，可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码。 ● serveradmin：服务器管理员，具有对服务器进行设置及关闭服务器的权限。 ● setupadmin：设置管理员，添加和删除链接服务器，并执行某些系统存储过程（如 sp_serveroption）。 ● processadmin：进程管理员，可以管理磁盘文件。 ● dbcreator：数据库创建者，可以创建、更改和删除数据库。 ● bulkadmin：可执行BULK INSERT语句，但是这些成员对要插入数据的表必须有 INSERT权限。 BULK INSERT语句的功能是以用户指定的格式复制一个数据文件至数据库表或视图。用户只能将一个用户添加为上述某个固定服务器角色的成员，不能自定义服务器角色。对于前面已建立的登录账号“zhou”，如果要给其赋予系统管理员权限，可通过企业管理器或系统存储过程将该用户登录账号加入sysadmin角色。

8.3.1 固定服务器角色 通过企业管理器添加服务器角色成员第1步以系统管理员身份登录到SQL Server服务器，在登录图标对应的列表项中，选择登录账号“zhou”的项目双击；第2步选择“服务器角色” 选项卡，如图8.9所示，选项卡中列出了SQL Server所有的固定服务器角色，将“System administrators”服务器角色前的复选框选中。图8.9 SQL Server服务器角色设置窗口

8.3.1 固定服务器角色 服务器角色的设置也可在新建用户登录账号时进行。 2. 利用系统存储过程添加固定服务器角色成员利用系统存储过程sp_addsrvrolemember可将一登录账号添加到某一固定服务器角色中，使其成为固定服务器角色的成员。语法格式： sp_addsrvrolemember [ @loginame = ] 'login'，[@rolename =] 'role' 参数含义： login：添加到固定服务器角色role的登录账号名，login 可以是SQL Server登录或 Windows NT 用户账号，对于 Windows NT 登录账号，如果还没有授予 SQL Server 访问权限，将自动对其授予访问权限。固定服务器角色名role必须为sysadmin，securityadmin，serveradmin，setupadmin，processadmin，diskadmin，dbcreator， bulkadmin之一。返回值：0（成功）或 1（失败）。说明： (1) 将登录账号添加到固定服务器角色时，该登录就会得到与此固定服务器角色相关的权限。 (2) 不能更改 sa角色成员资格。 (3) 不能在用户定义的事务内执行 sp_addsrvrolemember 存储过程。 (4)sysadmin 固定服务器的成员可以将登录账号添加到任何固定服务器角色，其他固定服务器角色的成员可以执行 sp_addsrvrolemember 将登录账号添加到同一个固定服务器角色。

8.3.1 固定服务器角色 【例8.6】将 Windows NT 用户 dream\zhouym 添加到 sysadmin 固定服务器角色中。 EXEC sp_addsrvrolemember ' dream\zhouym ', 'sysadmin' 3. 利用系统存储过程删除固定服务器角色成员利用sp_dropsrvrolemember系统存储过程可从固定服务器角色中删除SQL Server 登录账号或Windows NT用户或组。语法格式： sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role' 参数含义： 'login'：将要从固定服务器角色删除的登录账号名。 'role'：服务器角色名，默认值为NULL，role 必须是有效的固定服务器角色名。返回值：0（成功）或 1（失败）说明： (1) 不能删除 sa 登录账号。 (2) 不能从用户定义的事务内执行 sp_dropsrvrolemember。 (3) sysadmin 固定服务器角色的成员执行 sp_dropsrvrolemember，可删除任意固定服务器角色中的登录，其他固定服务器角色的成员只可以删除相同固定服务器角色中的其他成员。【例8.7】从 sysadmin 固定服务器角色中删除登录 zhou。 EXEC sp_dropsrvrolemember 'zhou', 'sysadmin'

8.3.2 固定数据库角色 1. 固定数据库角色固定数据库角色定义在数据库级别上，并且有权进行特定数据库的管理及操作。SQL Server提供了以下固定数据库角色：（1）db_owner：数据库所有者，可执行数据库的所有管理操作。 SQL Server 数据库中的每个对象都有所有者，通常创建该对象的用户即为其所有者。其他用户只有在相应所有者对其授权后，方可访问该对象。用户发出的所有SQL语句均受限于该用户具有的权限。例如，CREATE DATABASE仅限于 sysadmin 和 dbcreator 固定服务器角色的成员使用。 sysadmin 固定服务器角色的成员、db_owner 固定数据库角色的成员以及数据库对象的所有者都可授予、拒绝或废除某个用户或某个角色的权限。使用GRANT赋予执行T-SQL 语句或对数据进行操作的权限；使用DENY拒绝权限，并防止指定的用户、组或角色从组和角色成员的关系中继承权限；使用REVOKE取消以前授予或拒绝的权限。（2）db_accessadmin：数据库访问权限管理者，具有添加、删除数据库使用者、数据库角色和组的权限。（3）db_securityadmin：数据库安全管理员，可管理数据库中的权限，如设置数据库表的增、删、修改和查询等存取权限。（4）db_ddladmin：数据库DDL管理员，可增加、修改或删除数据库中的对象。（5）db_backupoperator：数据库备份操作员，具有执行数据库备份的权限。（6）db_datareader：数据库数据读取者。（7）db_datawriter：数据库数据写入者，具有对表进行增、删修改的权限。（8）db_denydatareader：数据库拒绝数据读取者，不能读取数据库中任何表的内容

8.3.2 固定数据库角色 （9）db_denydatawriter：数据库拒绝数据写入者，不能对任何表进行增、删修改操作。（10）public：是一个特殊的数据库角色，每个数据库用户都是public 角色的成员，因此，不能将用户、组或角色指派为public角色的成员，也不能删除public角色的成员。通常将一些公共的权限赋给public角色。 2. 数据库用户的操作权限在SQL Server中，可授予数据库用户的权限分为三个层次：（1）在当前数据库中创建数据库对象及进行数据库备份的权限，主要有：创建表、视图、存储过程、规则、缺省值对象、函数的权限及备份数据库、日志文件的权限。（2）用户对数据库表的操作权限及执行存储过程的权限，主要有： SELECT：对表或视图执行 SELECT 语句的权限； INSERT：对表或视图执行 INSERT 语句的权限； UPDATE：对表或视图执行 UPDATE 语句的权限； DELETE：对表或视图只 DELETE 语句的权限； REFERENCES：用户对表的主键和唯一索引字段生成外码引用的权限； EXECUTE：执行存储过程的权限。（3）用户对数据库中指定表字段的操作权限，主要有： SELECT：对表字段进行查询操作的权限； UPDATE：对表字段进行更新操作的权限。

8.3.3 用户自定义数据库角色 1．通过企业管理器创建数据库角色第１步创建数据库角色：以系统管理员身份登录SQL Server，并进入企业管理器，选中目录树XSCJ数据库结点的“角色”图标右击，出现如图8.10所示的快捷菜单，选择“新建数据库角色”，进入如图8.11的界面，输入角色名，选择确定按钮。第2步创建数据库用户并加入数据库角色。所谓创建数据库用户，即在某一数据库中为SQL Server服务器的登录账号或Windows NT的登录账号创建一数据库用户账号，使其能连接到该数据库。将数据库用户加入该数据库中的某一角色，即使数据库用户成为某一角色的成员。一个数据库角色也可作为该数据库中另一角色的成员。创建数据库用户并加入数据库角色的方法如下：在企业管理器目录树中，选中XSCJ数据库下的“用户”图标右击，出现一快捷菜单，选择“新建数据库用户”菜单项，出现如图8.12所示的界面，在窗口中选择登录账号，输入用户名（缺省情况下，即为登录账号），选中该用户所加入的数据库角色，选择“确定”；按此方法，可将登录账号“wang”、“zhou”创建为数据库XSCJ的用户，并添加到数据库角色ROLE1中；

8.3.3 用户自定义数据库角色 图8.10数据库角色的快捷菜单

8.3.3 用户自定义数据库角色 图8.11新建数据库角色的属性界面

8.3.3 用户自定义数据库角色 图8.12 新建数据库用户的属性界面

8.3.3 用户自定义数据库角色 第3步给数据库角色赋予创建数据库对象的权限：在企业管理器目录树中，选择XSCJ数据库结点右击，出现一快捷菜单，选择菜单项“属性”，进入如图8.13所示的界面，选择“权限”选项卡，根据需要，选中允许数据库角色或数据库用户执行的权限。图8.13 创建数据库对象的权限设置窗口

8.3.3 用户自定义数据库角色 第4步给数据库角色赋予表操作权限：在企业管理器的目录树中，选择XSCJ数据库结点下角色图标的项目“ROLE1”双击，出现如图8.14所示的界面，选择“权限”按钮，进入如图8.15所示的界面，根据允许的操作可设置相应的权限。例如，在图中设置ROLE1角色的权限为：可对XSCJ数据库的表XS、KC、XS_KC进行查询、插入、删除、修改操作。图8.14 数据库角色的属性窗口

8.3.3 用户自定义数据库角色 图8.15 数据库表操作权限设置窗口图8.16 表的列操作权限设置窗口

8.3.3 用户自定义数据库角色 如果只允许对一个表的部分字段进行查询和修改操作，例如，假设角色ROLE1对于XSCJ数据库的表XS，只能对姓名、学号字段进行查询和修改，此时，可设置列操作权限，方法如下：在图8.15所示的窗口中，先选中数据库对象，例如：选中表“XS”，然后选中“列”按钮，出现如图8.16所示的窗口，在窗口中选择对应字段允许的操作权限。 2．通过SQL命令创建数据库角色 1）定义数据库角色语法格式： sp_addrole [ @rolename = ] 'role'[ , [ @ownername = ] 'owner' ] 参数含义： 'role'：新的数据库角色名，role 必须是有效标识符，并且不能已经存在于当前数据库中。 'owner'：新角色的所有者，默认值为 dbo。owner 必须是当前数据库中的某个用户或角色。当指定Windows NT 用户时，应指定该 Windows NT 用户在数据库中可被识别的名称（用 sp_grantdbaccess 添加）。返回值：0（成功）或 1（失败）。

8.3.3 用户自定义数据库角色 说明： (1) 角色名可以包括字母、符号及数字。但是不能含有反斜线 (\)。 (2) 不能在用户定义的事务内使用 sp_addrole。 (3) 只有 sysadmin 固定服务器角色及 db_securityadmin 和 db_owner 固定数据库角色的成员才能执行 sp_addrole。【例8.8】如下示例在当前数据库中创建名为ROLE1 的新角色。 USE XSCJ EXEC sp_addrole 'ROLE1' 2）将一个登录账号添加为某个数据库的用户利用系统存储过程sp_grantdbaccess可将一个登录账号添加为某个数据库的用户。语法格式： sp_grantdbaccess [@loginame =] 'login'[,[@name_in_db =] 'name_in_db'] 参数含义： 'login'：SQL Server的登录账号，Windows NT 组和用户必须用 Windows NT 域名限定，格式为“域\用户”，例如 dream\zhouym。 'name_in_db' ：数据库用户名，如果没有指定，则使用登录账号作为数据库用户名。返回值：0（成功）或 1（失败）。

8.3.3 用户自定义数据库角色 说明： (1) 数据库用户名可含有字母、符号和数字。但不能包含反斜线，不能为 NULL，也不能为空字符串 ('')。 (2) sp_grantdbaccess 仅可以在当前数据库中添加用户（账户），若要从数据库中删除账户，使用 sp_revokedbaccess。 (3) 如果当前数据库中没有 guest账户，而且 login 为 guest，则可将guest添加为当前数据库的账户。 (4) sa 登录不能添加到数据库中。 (5) 不能从用户定义的事务中执行 sp_grantdbaccess。 (6) 只有 sysadmin 固定服务器角色、db_accessadmin 和 db_owner 固定数据库角色的成员才能执行 sp_grantdbaccess； (7) 存储过程sp_adduser的功能与sp_grantdbaccess的功能相同。【例8.9】将Windows NT 登录账号 “dream\zhou”添加为当前数据库的账户，并取名为 Dongdong。将SQL Server的登录账号“WANG”添加为当前数据库的账户，并取名为 WANG。 USE XSCJ EXEC sp_grantdbaccess 'dream\zhou', ' Dongdong ' GO EXEC sp_grantdbaccess 'WANG' GO

8.3.3 用户自定义数据库角色 3）给数据库角色添加成员利用系统存储过程sp_addrolemember可将当前数据库的用户或角色添加到当前数据库的某个角色中。语法格式： sp_addrolemember [ @rolename = ] 'role' ,[ @membername = ] security_account' 参数含义： 'role'：当前数据库中角色名。 ‘security_account’：添加到角色的数据库用户账号，可以是所有有效的SQL Server用户、当前数据库角色。返回值：0（成功）或 1（失败）。说明： (1) 当使用 sp_addrolemember 将用户账号添加到角色时，新成员将继承所有应用到角色的权限。 (2) 不能将固定数据库或固定服务器角色，或者 dbo 添加到其他角色。例如，不能将 db_owner 固定数据库角色添加成为用户定义的角色 YourRole 的成员。 (3) 在用户定义的事务中不能使用 sp_addrolemember。 (4) 只有 sysadmin 固定服务器角色和 db_owner 固定数据库角色中的成员可以执行 sp_addrolemember，以将成员添加到固定数据库角色。

8.3.3 用户自定义数据库角色 (5) db_securityadmin 固定数据库角色的成员可以将用户添加到任何用户定义的角色。【例8.10】将 Windows NT 用户 dream\zhou 添加为XSCJ数据库的用户，用户名为 dong，然后再将 dong 添加到XSCJ数据库的 ROLE1角色中。 USE XSCJ GO EXEC sp_grantdbaccess 'dream\zhou', 'dong' /*dong为数据库用户名*/ GO EXEC sp_addrolemember 'ROLE1', 'dong' 【例8.11】将SQL Server登录账号“WANG”添加到当前数据库，其用户名为“WANG”，然后再将“WANG”添加到XSCJ数据库的ROLE1角色中。。 USE XSCJ EXEC sp_grantdbaccess 'WANG', 'WANG' EXEC sp_addrolemember 'ROLE1','WANG' GO 4）数据库用户、角色操作权限的授予、拒绝和收回 ● 数据库用户、角色操作权限的授予利用GRANT语句可以给数据库用户或数据库角色赋予执行T-SQL语句的权限及对数据库对象进行操作的权限。授予执行T-SQL语句的权限。

8.3.3 用户自定义数据库角色 语法格式： GRANT { ALL | statement [ ,...n ] } TO security_account [ ,...n ] 授予对数据库对象操作的权限。语法格式： GRANT{ ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } TO security_account [ ,...n ] [ WITH GRANT OPTION ] [ AS { group | role } ] 参数含义： ALL：关键字，表示授予所有可用的权限。对于语句权限，只有 sysadmin 角色成员可以使用 ALL；对于对象操作权限，sysadmin角色成员和数据库对象所有者都可以使用 ALL。 statement：指被授予权限的语句。可为如下语句： CREATE DATABASE、CREATE TABLE、CREATE VIEW、CREATE FUNCTION、 CREATE PROCEDURE、CREATE RULE、CREATE DEFAULT、BACKUP DATABASE、BACKUP LOG

8.3.3 用户自定义数据库角色 n：表示对应的项可重复若干次。 security_account：指被授予权限的对象，可为当前数据库的用户、数据库角色、 Windows NT 用户或Windows NT 组， security_account指定的数据库用户、角色必须在当前数据库中存在，不可将权限授予其它数据库中的用户、角色。 GRANT语句可使用两个特殊的用户账号：public 角色和guest 用户，授予 public 角色的权限可应用于数据库中的所有用户；授予guest 用户的权限可为所有在数据库中没有用户账号的用户使用。当授予某个 Windows NT 本地组或全局组权限时，应指定在其上定义该组的域名或计算机名，然后依次输入反斜线和组名，但若要授予访问 Windows NT 内置本地组的权限，应指定 BUILTIN 而不是域名或计算机名。 PRIVILEGES：可选关键字。 Permission用于指定授予的权限类型。对于表、表值函数或视图，permission的取值可为：SELECT、INSERT、DELETE、UPDATE或 REFENENCES；对于存储过程 permission取值为 EXECUTE；对于用户函数permission可为 EXECUTE 和 REFERENCES。对于列授予的权限可为SELECT、UPDATE。

8.3.3 用户自定义数据库角色 column：指当前数据库中授予权限的列名。 table：指当前数据库中授予权限的表名。 view：指当前数据库中被授予权限的视图名。 stored_procedure：指当前数据库中授予权限的存储过程名。 extended_procedure：指当前数据库中授予权限的扩展存储过程名。 user_defined_function：指当前数据库中授予权限的用户定义函数名。 WITH GRANT OPTION：表示允许 security_account 将指定的对象权限转授其他用户、角色或Windows NT组，WITH GRANT OPTION 子句仅对对象权限有效。 AS {group | role}：指定当前数据库中执行 GRANT 语句的用户所属的角色名或组名。当对象上的权限被授予一个组或角色时，用 AS将对象权限进一步授予不是组或角色成员的用户。【例8.12】给用户zhang 和 wang 以及 Windows NT 组 dream\zhou 授予执行T-SQL语句的权限。 GRANT CREATE DATABASE, CREATE TABLE TO zhang, wang, [dream\zhou] /*如果用户账号含有空格、反斜杠（\），则要用引号或中括号将安全账号括起来*/ GO

8.3.3 用户自定义数据库角色 【例8.13】首先在当前数据库XSCJ中给 public 角色授予 SELECT 权限。然后，将特定的权限授予用户 liu、zhang 和 dong，使用户有对 XS 表的所有操作权限。 USE XSCJ GO GRANT SELECT ON XS TO public GO GRANT INSERT, UPDATE, DELETE ON XS TO zhou,zhang,dong GO 【例8.14】将 CREATE TABLE 权限授予 Accounting 角色的所有成员。 GRANT CREATE TABLE TO Accounting 【例8.15】在数据库XSCJ中，用户 zhang 拥有对表 XS的所有操作权限，zhang 将表 XS的 SELECT 权限授予 Accounting 角色（指定 WITH GRANT OPTION 子句）。用户 li 是 Accounting 的成员，要将表 XS上的 SELECT 权限授予用户 huang，huang 不是 Accounting 的成员。 /*用户zhang*/ GRANT SELECT ON XS TO Accounting WITH GRANT OPTION /*用户 li*/ GRANT SELECT ON XS TO huang AS Accounting /*由于 li是Accounting 角色的成员，因此必须用 AS 子句对huang授予权限。*/

8.3.3 用户自定义数据库角色 【例8.16】在当前数据库XSCJ中给public角色赋予对表XS中学号、姓名字段的SELECT权限。 USE XSCJ GRANT SELECT (学号,姓名) ON XS TO public GO ● 数据库用户、角色操作权限的拒绝使用DENY命令可以拒绝给当前数据库内的用户授予的权限，并防止数据库用户通过其组或角色成员资格继承权限。拒绝语句权限。语法格式： DENY { ALL | statement [ ,...n ] } TO security_account [ ,...n ] 拒绝对象权限。语法格式： DENY{ ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } TO security_account [ ,...n ] [ CASCADE ]

8.3.3 用户自定义数据库角色 参数含义： CASCADE：指拒绝security_account 的权限时，也将拒绝由 security_account 授权的任何其它用户账号。语法格式中其它各项的含义同GRANT语句的定义。说明： (1) 如果使用 DENY 语句禁止用户获得某个权限，那么以后将该用户添加到已得到该权限的组或角色时，该用户不能访问这个权限。 (2) 默认情况下， sysadmin、、db_securityadmin 角色成员和数据库对象所有者具有执行DENY 的权限。【例8.17】对多个用户不允许使用 CREATE DATABASE 和 CREATE TABLE 语句。 DENY CREATE DATABASE, CREATE TABLE TO zhang, wang, [dream\zhou] GO 【例8.18】首先给 public 角色授予对于表XS的SELECT 权限，然后，拒绝用户 zhang, wang, [Nanjing\liu] 的特定权限，这样，这些用户就没有对XS表的操作权限了。 USE XSCJ GO GRANT SELECT ON XS TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON XS TO zhang, wang, [dream\zhou] GO

8.3.3 用户自定义数据库角色 【例8.19】对所有 Accouting 角色成员拒绝 CREATE TABLE 权限。 DENY CREATE TABLE TO Accounting GO 如果用户wang是Accouting的成员，并显式授予了 CREATE TABLE 权限，但仍拒绝wang的CREATE TABLE权限。 ● 数据库用户、角色操作权限的取消利用REVOKE命令可取消以前给当前数据库用户授予或拒绝的权限。取消以前授予或拒绝的语句权限。语法格式： REVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] 取消以前授予或拒绝的对象权限。语法格式： REVOKE [ GRANT OPTION FOR ]{ ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } { TO | FROM } security_account [ ,...n ] [ CASCADE ] [ AS { group | role } ]

8.3.3 用户自定义数据库角色 各参数含义同GRANT语句的定义。说明： (1) REVOKE 只适用于当前数据库内的权限。 (2) REVOKE只在指定的用户、组或角色上取消授予或拒绝的权限。例如，给zhang 用户账号授予了查询XS表的权限，该用户账号是ROLE角色的成员。如果取消了ROLE角色查询XS表的访问权，由于已显式授予zhang查询表的权限，因此zhang仍能查询该表；若未显式授予 zhang查询XS的权限，那么取消ROLE角色的权限也将禁止 zhang查询该表。 (3) REVOKE 权限默认授予 sysadmin 固定服务器角色成员、db_owner 和 db_securityadmin 固定数据库角色成员。【例8.20】取消已授予用户 zhang和 dream\zhou 的 CREATE TABLE 权限。 REVOKE CREATE TABLE FROM zhang, [dream\zhou] GO 【例8.21】取消授予多个用户的多个语句权限。 REVOKE CREATE TABLE, CREATE DEFAULT FROM wang, liu GO 【例8.22】取消以前对zhang 授予或拒绝的权限。 REVOKE SELECT ON XS FROM zhang

8.3.3 用户自定义数据库角色 【例8.23】在数据库XSCJ中，用户 zhang 拥有表 XS的操作权限，zhang 将表 XS的 SELECT 权限授予ROLE 角色（指定 WITH GRANT OPTION 子句）。用户 li是 ROLE 的成员，他要将已授予用户huang的在表XS上的SELECT权限取消，huang 不是 ROLE 的成员。 /* User zhang */ GRANT SELECT ON XS TO ROLE WITH GRANT OPTION /* User li*/ GRANT SELECT ON XS TO huang AS ROLE /* User li*/ REVOKE SELECT ON XS TO huang AS ROLE /*用户li是角色ROLE的成员，通过用户li取消权限时，必须用AS指定其所属的角色*/ 3．数据库用户、角色的删除 1) 删除数据库用户在企业管理器中删除数据库用户很简单，直接在目录树的某一数据库结点下，选中须删除的用户项目，按“Del”键即可。下面介绍如何利用sp_revokedaccess 系统删除当前数据库的一个用户。

8.3.3 用户自定义数据库角色 语法格式： sp_revokedbaccess [ @name_in_db = ] 'name' 参数含义： @name_in_db =：常量字符串； 'name'：指要删除的数据库帐户名。返回值：0（成功）或 1（失败）说明： (1) 在用户定义事务内部不能执行 sp_revokedbaccess。 (2) 只有sysadmin固定服务器角色成员及 db_accessadmin 和 db_owner 固定数据库角色成员才能执行sp_revokedbaccess。【例8.24】从当前数据库中删除用户帐户’li’。 USE XSCJ EXEC sp_revokedbaccess ’li’ GO 2）删除数据库角色要删除用户自定义的数据库角色，首先应删除该角色的所有成员，下面介绍删除数据库角色成员及删除数据库角色的系统存储过程。

8.3.3 用户自定义数据库角色 ● 利用系统存储过程删除数据库角色成员语法格式： sp_droprolemember [ @rolename = ] 'role' ,[ @membername = ] 'security_account' 参数含义： 'role'：当前数据库的一个角色名。 'security_account'：指将要从'role'所指角色中删除的用户账号，security_account 可以是当前数据库用户或另一个角色。返回值：0（成功）或 1（失败）说明： (1) 不能从用户定义的事务内执行 sp_droprolemember。 (2) 只有 sysadmin 固定服务器角色、db_owner 和 db_securityadmin 固定数据库角色的成员才能执行 sp_droprolemember。只有 db_owner 固定数据库角色的成员才可以从固定数据库角色中删除用户。【例8.25】从角色ROLE中删除用户 zhang。 USE XSCJ EXEC sp_droprolemember 'ROLE', 'zhang' GO

8.3.3 用户自定义数据库角色 ● 利用系统存储过程删除数据库角色语法格式： sp_droprole [ @rolename = ] 'role' 参数含义： @rolename =：常量字符串； 'role'：当前数据库中用户自定义的角色名。返回值：0（成功）或 1（失败）说明： (1) 在删除角色之前，首先必须从该角色删除其所有的成员。 (2) 不能删除固定角色及 public 角色。 (3) 如果角色拥有对象，那么就不能将其删除。 (4) 不能在用户定义的事务内执行 sp_droprole。 (5) 只有 sysadmin 固定服务器角色、db_owner 和 db_securityadmin 固定数据库角色成员才能执行 sp_droprole。【例8.26】删除当前数据库角色ROLE。 USE XSCJ EXEC sp_droprole 'ROLE'

8.4.1 事务类型 SQL Server的事务可分为两类：系统提供的事务和用户定义的事务。系统提供的事务是指在执行某些T-SQL语句时，一条语句就构成了一个事务，这些语句是： ALTER TABLE CREATE DELETE DROP FETCH GRANT INSERT OPEN REVOKE SELECT UPDATE TRUNCATE TABLE 例如执行如下的创建表语句： create table xx ( f1 int not null, f2 char(10), f3 varchar(30) ) 这条语句本身就构成了一个事务，它要么建立起含3列的表结构，要么对数据库没有任何影响，而不会建立起含1列或2列的表结构。在实际应用中，大量使用的是用户定义的事务。事务的定义方法是：用BEGIN TRANSACTION语句指定一个事务的开始，用COMMIT或ROLLBACK语句表明一个事务的结束。注意必须明确指定事务的结束，否则系统将把从事务开始到用户关闭连接之间所有的操作都作为一个事务来处理。

8.4.2 事务处理语句 与事务处理有关的语句包括BEGIN TRANSACTION、COMMIT TRANSACTION、和ROLLBACK TRANSACTION语句。 1. BEGIN TRANSACTION语句 BEGIN TRANSACTION语句定义事务的开始。语法格式： BEGIN TRAN[SACTION] [ transaction_name | @tran_name_variable [ WITH MARK [ ‘description’ ]] 其中，transaction_name是事务的名称，必须遵循标识符规则，但字符数不超过32个。@tran_name_variable是用户定义的、含有效事务名称的变量，该变量必须是char、varchar、nchar或nvarchar类型的。WITH MARK指定在日志中标记事务，description是描述该标记的字符串。 BEGIN TRANSACTION语句的执行使全局变量@@TRANCOUNT的值加1。 2. COMMIT TRANSACTION语句 COMMIT语句是提交语句，它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，也标志一个事务的结束，其语法格式为： COMMIT [ TRAN[SACTION] [ transaction_name | @tran_name_variable ] ]

8.4.2 事务处理语句 其中参数transaction_name和@tran_name_variable分别是事务名称和事务变量名。与BEGIN TRANSACTION语句相反，COMMIT TRANSACTION语句的执行使全局变量 @@TRANCOUNT的值减1。标志一个事务的结束也可以使用COMMIT WORK语句。语法格式： COMMIT [ WORK ] 它与COMMIT TRANSACTION语句的差别在于:COMMIT WORK不带参数。 3. ROLLBACKTRANSACTION语句 ROLLBACK语句是回滚语句，它使得事务回滚到起点或指定的保存点处，它也标志一个事务的结束。语法格式： ROLLBACK [ TRAN[SACTION] [ transaction_name | @tran_name_variable | savepoint_name | @ savepoint_variable ] ] 其中参数transaction_name和@tran_name_variable分别是事务名称和事务变量名，savepoint_name是保存点名，@savepoint_variable是含有保存点名称的变量名，它们可用SAVE TRANSACTION语句设置： SAVE TRAN[SACTION] {savepoint_name | @ savepoint_variable }

第 8 章 系统安全管理