第10章 SQL Server的安全性管理

第10章

安全与权限本章学习目标了解SQLServer2005登录验证模式掌握管理两类SQLServer2005登录帐户的方法掌握管理SQLServer2005数据库用户的方法了解基于角色的权限管理掌握管理服务器角色的方法掌握管理数据库角色的方法掌握管理权限的方法10.1SQLServer2005的安全机制SQLServer2005数据库的安全性通过以下几个方面得以保证：网络系统的安全性：这可以通过在网络系统边界安装防火墙系统得以实施。服务器的安全性：即保证运行SQLServer2005的服务器本身及其操作系统的安全。SQLServer2005的登录安全性：即允许哪些用户登录SQLServer服务器。数据库的安全性：即规定用户登录SQLServer服务器以后可以使用哪些数据库。数据库对象的安全性：即规定用户打开某一数据库后，可以操作哪些数据库对象以及怎样操作。10.1.1SQLServer2005登录身份验证模式SQLServer2005登录身份验证用来确认该用户是否具有连接SQLServer的权限。任何用户在使用SQLServer数据库之前，必须通过系统的安全身份验证。SQLServer2005提供了两种确认用户的验证模式：Windows身份验证模式SQLServer和Windows身份验证模式10.1.1SQLServer2005登录身份验证模式1．Windows身份验证用户通过MicrosoftWindows用户帐户连接时，SQLServer使用Windows操作系统中的信息验证帐户名和密码。这是默认的身份验证模式，它比混合模式要安全得多，如果可能，请使用Windows身份验证。10.1.1SQLServer2005登录身份验证模式2．SQLServer和Windows身份验证模式（又称为混合模式）使用混合模式时，无论是使用Windows身份验证方式的用户，还是使用SQLServer身份验证方式的用户，都可以连接到SQLServer系统上。使用混合模式中的SQLServer身份验证方式时，系统管理员创建一个登录账号和口令，并将它们存储在SQLServer中，当用户连接到SQLServer上时，必须提供SQLServer登录账号和口令。

SQLServer密码可包含1到128个字符，包括字母、符号和数字的任意组合。10.1.1SQLServer2005登录身份验证模式当采用Windows身份验证模式时，密码长度必须多于8个字符。强密码不能使用禁止的条件或字词，包括：空条件或NULL条件、“Password”、“Admin”、“Administrator”、“sa”、“sysadmin”、当前计算机的名称、当前登录到计算机上的用户的名称。并且要满足下列四个条件中的三个：必须包含大写字母。必须包含小写字母。必须包含数字。必须包含非字母数字字符；例如，#、%或^。10.1.1SQLServer2005登录身份验证模式SQLServer2005安装成功后，默认的身份验证模式是“Windows身份验证模式”，利用SQLServer管理控制台可以重新设置身份验证模式。方法如下：打开SQLServer管理控制台，右击SQLServer服务器名称，在弹出的快捷菜单中选择“属性”选项。会打开“服务器属性”对话框。在窗口左端选择“安全性”选择页。10.1.1SQLServer2005登录身份验证模式根据需要单击“服务器身份验证”下面对应的“Windows身份验证模式”或“SQLServer和Windows身份验证模式”单选按钮，并单击“确定”按钮。打开SQLServer配置管理器，单击窗口左边的“SQLServer2005服务”，在窗口右边找到“SQLServer服务”，并重新启动它。10.1.2SQLServer数据库的安全性用户成功登录SQLServer服务器后，并不自动拥有对所有数据库的访问权限。用户必须在想要访问的数据库中也有一个与登录帐户相对应的数据库帐户。当需要访问某个数据库时，SQLServer的安全系统会根据这个数据库帐户的权限决定是否允许用户访问该数据库。10.1.3SQLServer数据库对象的

安全性在创建一个数据库对象时，创建者将自动拥有对该数据库对象的所有权限，即可以完全控制该对象。当一个非数据库拥有者想要访问数据库中的对象时，必须事先由数据库拥有者赋予该用户对指定对象的操作权限。10.2管理服务器的安全性10.2.1SQLServer登录帐户在SQLServer2005的SQLServer管理控制台的树形目录中展开服务器下的“安全性”，选择“登录名”，可以查看当前该服务器所有的登录帐户信息。SQLServer2005服务器在安装成功后，已经自动创建了一些登录帐户。sa帐户：给SQLServer2005系统管理员使用的，它是一个特殊的帐户，该帐户拥有最高的管理权限，可以执行服务器范围内的所有操作。为了安全起见，sa帐户在默认情况下是禁用的。“BUILTIN\Administrators”帐户：是为Windows系统管理员管理SQLServer2005服务器而提供的，它也可以执行服务器范围内的所有操作。10.2.2添加登录帐户1．使用SQLServer管理控制台添加登录帐户（1）在SQLServer管理控制台中，展开SQLServer服务器组中相应服务器。（2）右击“安全性|登录名”，选择“新建登录名”，会打开“登录名-新建”对话框。（3）根据所要创建的登录帐户的类型选择相应的身份验证类型单选按钮。10.2.2添加登录帐户

1．将Windows账号添加到SQLServer中（1）在SQLServer管理控制台的“对象资源管理器”中，中，展开SQLServer服务器组中相应服务器。（2）右击“安全性|登录名”，选择“新建登录名”，会打开“登录名-新建”对话框。（3）根据所要创建的登录帐户的类型选择“Windows身份验证”类型单选按钮。图15-6新建登录名对话框单击“查找”按钮，出现如图15-7所示的对话框，将Windows账号添加到SQLServer中。单击“OK”按钮，即完成了登录名的创建。图15-7选择Windows用户或组2．新建SQLServer账号

（1）在SQLServer管理控制台的“对象资源管理器”中，中，展开SQLServer服务器组中相应服务器。（2）右击“安全性|登录名”，选择“新建登录名”，会打开“登录名-新建”对话框。（3）根据所要创建的登录帐户的类型选择“SQLServer身份验证”类型单选按钮。在“登陆名”文本框中，输入新的SQLServer账号名，在“密码”和“确认密码”中，输入登录名对应的密码。单击“OK”按钮，完成登录名的创建。

2．使用Transact-SQL语句添加登录帐户（1）将Windows账号添加到SQLServer中sp_grantlogin：使WindowsNT用户或组帐户得以使用Windows身份验证连接到MicrosoftSQLServer。语法：sp_grantlogin[@loginame=]'login'参数：[@loginame=]‘login’

是要添加的WindowsNT用户或组的名称。WindowsNT组和用户必须用WindowsNT域名限定，格式为“域\用户"，例如London\Joeb。10.2.2添加登录帐户例1：使WindowsNT用户Corporate\BobJ

得以连接到SQLServer。EXECsp_grantlogin'Corporate\BobJ'10.2.2添加登录帐户（2）可以使用CREATELOGIN语句新建SQLServer

登录帐户。CREATELOGINlogin_name{WITH<option_list1>|FROM<sources>}<sources>::=WINDOWS[WITH<windows_options>[,...]]<option_list1>::=PASSWORD='password'[HASHED][MUST_CHANGE][,<option_list2>[,...]]10.2.2添加登录帐户<option_list2>::=DEFAULT_DATABASE=database|DEFAULT_LANGUAGE=language|CHECK_EXPIRATION={ON|OFF}|CHECK_POLICY={ON|OFF}<windows_options>::=DEFAULT_DATABASE=database|DEFAULT_LANGUAGE=language10.2.2添加登录帐户其中的参数说明如下：login_name：指定创建的登录名。WINDOWS：指定将登录名映射到Windows登录名。PASSWORD='password'：仅适用于SQLServer登录名。指定正在创建的登录名的密码。HASHED：仅适用于SQLServer登录名。指定在PASSWORD参数后输入的密码已经过哈希运算。10.2.2添加登录帐户MUST_CHANGE：仅适用于SQLServer登录名。SQLServer将在首次使用新登录名时提示用户输入新密码。DEFAULT_DATABASE=database：指定将指派给登录名的默认数据库。如果未包括此选项，则默认数据库将设置为master。DEFAULT_LANGUAGE=language：指定将指派给登录名的默认语言。10.2.2添加登录帐户例：创建一个SQLServer登录帐户USER1，密码为Abc123#$。CREATELOGINuser1

WITH

PASSWORD=‘Abc123#$'10.2.3修改登录帐户属性使用SQLServer管理控制台修改登录帐户属性，只需双击要修改属性的登录帐户，并在登录属性对话框中进行修改即可。注意：对于SQLServer帐户，可以修改其密码。对于Windows帐户，只能使用Windows的“计算机管理器”或“域用户管理器”修改帐户密码。10.2.4拒绝或禁用登录帐户有时可能需要暂时拒绝或禁用一个登录帐户连接到SQLServer服务器，过一段时间后再恢复。使用SQLServer管理控制台拒绝登录帐户的步骤如下：（1）在SQLServer管理控制台中，展开SQLServer服务器组中相应服务器。（2）选择“安全性|登录名”，双击要拒绝访问的登录帐户，打开登录属性对话框。（3）选择“状态”选择页，并选中“拒绝”或“禁用”单选按钮。（4）单击“确定”按钮。10.2.5删除登录帐户1．使用SQLServer管理控制台删除登录帐户（1）在SQLServer管理控制台中，展开SQLServer服务器组中相应服务器。（2）选择“安全性|登录名”，在右侧窗口的登录帐户列表中右击要删除的帐户，从弹出的快捷菜单中选择“删除”选项。10.2.5删除登录帐户2．使用Transact-SQL语句删除登录帐户（1）使用DROPLOGIN删除SQLServer登录帐户语法形式如下：DROPLOGINlogin_name其中login_name是要删除的登录名。例：删除SQLServer帐户USER1。（2）删除windows登录账号

sp_revokelogin用来删除NT用户或用户组在SQLSERVER上的登录信息语法：

sp_revokelogin[@loginname=]‘login’参数：

[@loginame=]‘login’：是WindowsNT用户或组的名称。例：删除WindowsNT用户Corporate\MollyA

的登录条目。

EXECsp_revokelogin'Corporate\BobJ'（3）删除SQLServer登陆帐户

sp_droplogin：删除MicrosoftSQLServer登录，以阻止使用该登录名访问SQLServer。语法：

sp_droplogin[@loginame=]'login'参数：

[@loginame=]‘login’：将被删除的登录。例5：从SQLServer中删除用户

student。

EXECsp_droplogin‘student'

10.2.6服务器角色SQLServer管理者可以将某一组用户设置为某一角色，这样只要对角色进行权限设置便可以实现对所有用户权限的设置，大大减少了管理员的工作量。SQLServer提供了通常管理工作的预定义服务器角色和数据库角色。用户还可以创建自己的数据库角色。10.2.6服务器角色服务器角色是指根据SQLServer的管理任务，以及这些任务相对的重要性等级来把具有SQLServer管理职能的用户划分为不同的用户组，每一组所具有的管理SQLServer的权限都是SQLServer内置的，即不能对其进行添加、修改和删除，只能向其中加入用户或者其他角色。10.2.6服务器角色固定服务器角色描述bulkadmin允许非sysadmin用户运行BULKINSERT语句dbcreator创建、更改、删除和还原任何数据库diskadmin管理磁盘文件processadmin终止SQLServer实例中运行的进程securityadmin管理登录名及其属性serveradmin更改服务器范围的配置选项和关闭服务器setupadmin添加和删除链接的服务器，并且也可以执行某些系统存储过程sysadmin在服务器中执行任何活动10.2.6服务器角色使用SQLServer管理控制台更改服务器角色成员的步骤如下：（1）在SQLServer管理控制台中，展开SQLServer服务器组中相应服务器。（2）选择“安全性|服务器角色”，双击右侧窗口的服务器角色列表中要更改的服务器角色，将弹出“服务器角色属性”对话框，其中显示出当前服务器角色成员列表。10.3管理数据库的用户一台服务器除了有一套服务器登录帐户列表外，每个数据库中也都有一套相互独立的数据库用户列表。每个数据库用户都和服务器登录帐户之间存在着一种映射关系。系统管理员可以将一个服务器登录帐户映射到用户需要访问的每一个数据库中的一个用户帐户和角色上。一个登录帐户在不同的数据库中可以映射成不同的用户，从而拥有不同的权限。10.3管理数据库的用户在SQLServer中，每个数据库一般都有两个默认的用户：dbo和guest。dbo代表数据库的拥有者。例如，sysadmin服务器角色的成员被自动映射成dbo。Guest用户主要是让那些没有属于自己的用户帐户的SQLServer登录者作为其默认的用户，从而使该登录者能够访问具有guest用户的数据库。不能删除guest用户，但可通过撤消该用户的CONNECT权限将其禁用。10.3管理数据库的用户在SQLServer管理控制台中，展开SQLServer服务器组中相应服务器，在“数据库”目录树中，选择某个数据库名称下面包含的“安全性|用户”，可以在右侧窗口中看到该数据库的所有用户。10.3.1添加数据库用户1．使用SQLServer管理控制台添加数据库用户（1）在SQLServer管理控制台中，展开SQLServer服务器组中相应服务器。（2）展开“数据库”目录树，再展开待添加用户的数据库，右击“安全性|用户”，在弹出的快捷菜单中选择“新建用户”选项。将弹出“数据库用户-新建”对话框。10.3.1添加数据库用户2．使用Transact-SQL语句添加数据库用户可以使用CREATEUSER添加数据库用户，其基本语法形式如下：CREATEUSERuser_name[{FOR|FROM}

{LOGINlogin_name}|WITHOUTLOGIN][WITHDEFAULT_SCHEMA=schema_name]10.3.1添加数据库用户参数说明如下：user_name：指定在此数据库中用于识别该用户的名称。user_name的长度最多是128个字符。LOGINlogin_name：指定要创建数据库用户的SQLServer登录名。login_name必须是服务器中有效的登录名。WITHDEFAULT_SCHEMA=schema_name：指定服务器为此数据库用户解析对象名时将搜索的第一个架构。WITHOUTLOGIN：指定不应将用户映射到现有登录名。10.3.1添加数据库用户例10-3-1：在STUDENT数据库中为SQLServer用户USER1添加数据库用户，并取名为USER1，默认架构为STU。USESTUDENTCREATEUSERUSER1FORLOGINUSER1WITHDEFAULT_SCHEMA=STU10.3.2删除数据库用户删除数据库用户实际上就是删除一个登录帐户到一个数据库中的映射。在SQLServer管理控制台中删除一个数据库用户可以用鼠标右击欲删除的用户，从弹出的快捷菜单中选择“删除”选项，在随后弹出的“删除对象”对话框中单击“确定”按钮即可将该用户从数据库中删除。10.3.2删除数据库用户利用Transact-SQL语句同样可以删除数据库用户，其语法形式如下：DROPUSERuser_name其中的参数user_name指定在此数据库中用于识别该用户的名称。例：从STUDENT数据库中删除数据库用户USER1。10.3.3数据库角色数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以给一个用户授予属于同一数据库的多个角色。SQLServer在安装成功后，提供了十种固定数据库角色。固定数据库角色是在数据库级别定义的，并且存在于每个数据库中。固定数据库角色描述db_accessadmin添加或删除Windows登录账户、Windows组和SQLServer登录账户的访问权限db_backupoperator备份数据库固定数据库角色描述固定数据库角色描述db_datareader读取所有用户表中的所有数据db_datawriter添加、删除或更改所有用户表中的数据db_ddladmin在数据库中运行任何数据定义语言（DDL）命令db_denydatareader不能读取数据库内用户表中的任何数据db_denydatawriter不能添加、修改或删除数据库内用户表中的任何数据db_owner执行数据库的所有配置和维护活动db_securityadmin修改角色成员身份和管理权限10.3.3数据库角色在固定的数据库角色中，public是一个特殊的数据库角色，每个数据库用户都属于public数据库角色。当尚未对某个用户授予或拒绝对安全对象的特定权限时，则该用户将继承授予该安全对象的public角色的权限。110.3.3数据库角色在SQLServer管理控制台中，展开SQLServer服务器组中相应服务器，展开“数据库”目录树，再展开某个具体的数据库，选择“安全性|角色|数据库角色”，在右侧窗口中会看到数据库中已存在的角色。在未创建新角色之前，数据库中只有固定数据库角色。角色管理：2.角色管理的内容（1）管理服务器角色使用企业管理器 增加服务器角色成员、 查看服务器角色权限、 删除服务器角色成员。使用系统过程sp_addsrvrolemember

sp_dropsrvrrolemember管理服务器角色。

①sp_addsrvrolemember

添加登录，使其成为固定服务器角色的成员。语法：

sp_addsrvrolemember[@loginame=]‘login’，

[@rolename=]'role'参数：

[@loginame=]‘login’：是添加到固定服务器角色的登录名称。login

可以是MicrosoftSQLServer登录或MicrosoftWindowsNT用户帐户。

[@rolename=]‘role’：要将登录添加到的固定服务器角色的名称。例：添加用户‘admin\user01’为’sysadmin’服务器角色的成员；添加用户‘user01’为’dbcreator’服务器角色的成员。sp_addsrvrolemember‘admin\user01’,’sysadmin’sp_addsrvrolemember‘user01’,’dbcreator’②sp_dropsrvrolemember

从固定服务器角色中删除Microsoft®SQLServer™登录或MicrosoftWindowsNT®用户或组。语法:

sp_dropsrvrolemember[@loginame=]'login',[@rolename=]'role'参数: [@loginame=]'login‘:将要从固定服务器角色删除的登录的名称。login

必须已经存在。

@rolename=]'role‘:服务器角色的名称。例：把用户‘admin\user01’从’sysadmin’服务器角色的成员中删除；把用户‘user01’从’dbcreator’服务器角色的成员中删除。sp_dropsrvrolemember‘admin\user01’,’sysadmin’sp_dropsrvrolemember‘user01’,’dbcreator’（2）管理数据库角色使用企业管理器 增加数据库角色成员、 授予数据库角色权限、 删除数据库角色成员。使用存储过程管理数据库角色成员、

sp_addrole、sp_addapprole、sp_droprole、sp_dropapprole、sp_helprole、sp_helprolemember、sp_addrolemember、sp_droprolemember

sp_addrole:在当前数据库创建新的数据库角色。sp_droprole:从当前数据库删除数据库角色。sp_addapprole:在当前数据库中添加一个特殊类型的角色以使应用程序安全。sp_dropapprole:从当前数据库删除应用程序角色。sp_helprole:显示当前数据库中角色的信息。sp_addrolemember:将指定用户添加为当前数据库中某一角色的成员。sp_droprolemember:从当前数据库中的角色中删除某一成员。

sp_helprolemember:显示当前数据库中某个角色的成员的信息。10.4权限管理权限用来指定授权用户可以使用的数据库对象以及对这些数据库对象可以执行的操作。用户在登录到SQLServer之后，根据其用户帐户所属的Windows组或角色，决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。在每个数据库中，用户的权限独立于用户帐户和用户在数据库中的角色，每个数据库都有自己独立的权限系统。权限的管理主要是完成对权限的授权、拒绝和回收。10.4权限管理管理权限可以通过以下的方式来实现：从数据库的角度来管理。从用户或角色的角度来管理。从数据库对象的角度来管理。在SQLServer中，可以通过SQLServer管理控制台或Transact-SQL语句管理权限。四、权限管理：1.权限管理简介对象权限语句权限管理权限的命令：（1）GRANT： 在安全系统中创建项目，使当前数据库中的用户得以处理当前数据库中的数据或执行特定的Transact-SQL语句。（2）REVOKE： 删除以前在当前数据库内的用户上授予或拒绝的权限。（3）DENY： 在安全系统中创建一项，以拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。10.4.2使用Transact-SQL语句管理权限在Transact-SQL语言中，使用GRANT、DENY和REVOKE三种命令来管理权限。GRANT:把指定的权限授予某一用户。语法：GRANT{ALL[PRIVILEGES]}|权限名[(列名[,...n])][,...n]ON数据库对象名TO用户/角色[WITHGRANTOPTION]说明：GRANTOPTION选项指示被授权者在获得指定权限的同时还可以将指定权限授予其他用户或角色。10.4.2使用Transact-SQL语句管理权限DENY：命令用来禁止用户使用指定的权限。语法：DENY{ALL[PRIVILEGES]}|权限名[(列名[,...n])][,...n][ON数据库对象名TO用户/角色[CASCADE]说明：CASCADE选项指示拒绝授予指定用户该权限，同时，对该用户授予了该权限的所有其他用户，也拒绝授予该权限。如果授权时使用了WITHGRANTOPTION选项，则此处为必选项。10.4.2使用Transact-SQL语句管理权限REVOKE：命令用来收回用户所