6.1.3 SQL Server的安全性控制
SQL Server的安全性控制包含通过登录认证机制登录SQL Server实例；通过用户认证机制实现对数据库的访问；通过权限认证机制实现对数据库对象的访问，如图6-2所示。
图6-2 SQL Server的安全性控制
具体来说，体现在以下3个方面：
登录认证：用户连接数据库服务器时，数据库服务器将验证该用户的账户和口令，确定该用户是否有连接到数据库服务器的资格，属于服务器级别的用户身份验证。
用户认证：登录认证成功以后，当用户访问数据库时，确认用户账户是否有访问数据库的权限，属于数据库级别的用户身份认证。
权限认证：经过登录认证和用户认证以后，当用户操作数据库对象时，确定用户是否有操作许可，验证用户的操作权限，属于存取控制权限认证。在创建数据库对象时，SQL Server自动把该数据库对象拥有权赋予该对象的创建者。当一个非数据库对象拥有者想访问数据库里的对象时，必须事先由数据库拥有者赋予用户对指定对象执行特定操作的权限。一般来说，为减少管理的开销，在权限认证安全管理上应该在大多数场合赋予数据库用户以广泛的权限，然后再针对实际情况在某些敏感的数据上实施具体的访问控制权限控制。
登录认证、用户认证、权限认证，每个安全认证好像是一道门，如果门没有上锁，或者用户拥有开门的权利，则可以通过这道大门到达下一个安全等级。如果通过了所有的大门，用户就可以实现对数据的访问了。
1）SQL Server的登录认证
（1）设置服务器的登录认证模式
SQL Server提供了两种登录认证模式登录服务器，即Windows登录认证和SQL Server登录认证。
Windows登录认证：用户登录Windows进行身份验证后，再登录SQL Server时就不再进行身份验证了。也就是说有了Windows的登录账户就可以直接登录SQL Server服务器。Windows操作系统负责登录账户的创建、管理，由Windows授权连接SQL Server，并将Windows账户映射为SQL Server账户。
SQL Server登录认证：由SQL Server服务器对要登录的用户进行身份验证。SQL Server负责登录账户的创建、管理，并将其保存在数据库中。此种模式登录服务器时，需要提供SQL Server的用户账户和密码。
在SQL Server Management Studio中设置数据库服务器登录认证模式的步骤如下：
启动SQL Server Management Studio，并登录到服务器。
在左边的“对象资源管理器”窗口中的服务器上单击右键，在出现的快捷菜单中选择“属性”命令，出现“服务器属性”对话框。
选择“选择页”中的“安全性”选项，进入设置服务器登录认证模式页面，如图6-3所示。目前采用的是SQL Server和Windows身份验证模式，表示既可以通过Windows登录认证模式登录服务器，也可以采用SQL Server登录认证模式登录服务器。
（2）创建SQL Server登录账户
可以通过SQL Server Management Studio创建SQL Server登录账户，具体步骤如下：
启动SQL Server Management Studio，并登录到服务器。
在左边的“对象资源管理器”窗口中，展开“安全性”目录，找到“登录名”选项，单击右键，在出现的快捷菜单中选择“新建登录名”命令，如图6-4所示。
在出现的“登录名-新建”窗口中，可以设置登录名、密码、默认数据库、服务器角色等，如图6-5所示。
图6-3 设置服务器的登录认证模式
图6-4 新建登录名
图6-5 创建SQL Server登录账户
创建SQL Server登录账户后，在“对象资源管理器”窗口中，展开“安全性”目录下的“登录名”节点，可以看到刚才创建的SQL Server登录账户zzz，如图6-6所示。
图6-6 查看创建好的SQL Server登录账户
在图6-6中，sa（system administrator）是SQL Server认证的默认系统管理员登录账户。该账户拥有服务器级别最高的权限管理，属于固定服务器角色sysadmin中的成员，并且不能从该角色中删除，可以执行服务器范围内的所有操作。
除了图形用户界面，SQL Server还可以通过CREATE LOGIN语句创建登录账户。其语法格式如下：
各参数说明如下：
login_name指定创建的登录名。
PASSWORD=密码：仅适用于SQL Server登录名。
PASSWORD=hashed_password：仅适用于HASHED关键字。指定要创建的登录名的密码的哈希值。HASHED仅适用于SQL Server登录。
MUST_CHANGE：仅适用于SQL Server登录。如果包括此选项，则SQL Server将在首次使用新登录时提示用户输入新密码。
SID=登录sid：用于重新创建登录名。仅适用于SQL Server身份验证登录，不适用于Windows身份验证登录。指定新SQL Server身份验证登录的SID。如果未使用此选项，SQL Server将自动分配SID。SID结构取决于SQL Server版本。
DEFAULT_DATABASE=数据库名：指定将指派给登录名的默认数据库。如果未包括此选项，则默认数据库将设置为master。
DEFAULT_LANGUAGE=语言：指定将指派给登录名的默认语言。如果未包括此选项，则默认语言将设置为服务器的当前默认语言。即使将来服务器的默认语言发生更改，登录名的默认语言也仍保持不变。
CHECK_EXPIRATION=｛ON｜OFF｝：仅适用于SQL Server登录名。指定是否应对此登录账户强制实施密码过期策略。默认值为OFF。
CHECK_POLICY=｛ON｜OFF｝：仅适用于SQLServer登录名。指定应对此登录强制实施运行SQL Server的计算机的Windows密码策略。默认值为ON。
WINDOWS：指定将登录名映射到Windows登录名。
CERTIFICATE证书名：指定将与此登录名关联的证书名称。此证书必须已存在于master数据库中。
ASYMMETRIC KEY非对称密钥名：指定将与此登录名关联的非对称密钥的名称。此密钥必须已存在于master数据库中。
【例6.1】 创建SQL Server登录账户sql2，密码为sql2，默认登录数据库为test。
2）SQL Server的用户认证
前面已经创建了SQL Server的登录账户，登录账户验证成功后会连接到SQL Server数据库服务器，但登录账户本身不能访问服务器中的任何数据库。只有创建了数据库的用户，成为数据库的合法用户后，才能访问数据库。数据库的用户只能来自服务器的登录账户，而且是可以访问该数据库的登录账户。一个登录账户可以映射为多个数据库中的用户，但是在一个数据库中只能映射为一个用户。
在创建数据库时，SQL Server自动创建了两个默认的数据库用户：
dbo：数据库的拥有者（Database Owner），隶属于sa登录账户，拥有public和db_owner 数据库角色，具有该数据库的所有权限。
guest：客户访问用户，没有隶属的登录账户，拥有public数据库角色。除了master和tempdb两个系统数据库的guest用户不能删除外，其他数据库的guest用户可以删除。
除了这两个默认的数据库用户，用户还可以创建自定义用户。
（1）通过SQL Server Management Studio创建数据库用户
具体步骤如下：
启动SQL Server Management Studio，并登录到服务器。
在左边的“对象资源管理器”窗口中，展开“安全性”目录，找到“用户”选项，单击右键，在出现的快捷菜单中选择“新建用户”命令，出现“数据库用户-新建”对话框，如图6-7所示。
“数据库用户-新建”对话框中，在“常规”页面中填写“用户名”，再分别单击“登录名”和“默认架构”右侧的
按钮，选择相应的登录名和默认架构，即可完成数据库用户的创建。
图6-7 “数据库用户-新建”对话框
（2）使用CREATE USER语句创建数据库用户
其基本的语法如下：
CREATE USER用户名
［｛FOR｜FROM｝｛LOGIN登录名｝］
［WITH DEFAULT_SCHEMA=架构名］
各参数说明如下：
用户名：指定在此数据库中用于识别该用户的名称。
LOGIN登录名：指定要为其创建数据库用户的登录名。登录名必须是服务器中的有效登录名。可以是基于Windows身份验证的登录名，也可以是使用SQL Server身份验证的登录名。
WITH DEFAULT_SCHEMA=架构名：指定服务器为此数据库用户解析对象名时将搜索的第一个架构。
【例6.2】 为登录账户sql1创建用户sqlUser1。
CREATE USER SqlUser1 FOR LOGIN Sql1WITH DEFAULT_SCHEMA=dbo；
3）SQL Server的权限认证
权限用来指定认证用户可以使用的数据库对象以及这些用户可以对这些数据库对象执行的操作。用户在登录到SQL Server之后，其用户账号所归属的组或角色被赋予的权限（许可）决定了该用户能够对那些数据库对象执行那种操作以及能够访问、修改那些数据。在每个数据库中用户的权限独立于用户账号和用户在数据库中的角色，每个数据库独有自己独立的权限系统，在SQL Server中包括3种类型的权限：
①服务器权限：服务器权限是指在数据库服务器级别上对整个服务器和数据库进行管理的权限，例如SHUTDOWN、CREATE DATABASE、BACKUPDATABASE等，允许DBA执行管理任务。这些权限定义在固定服务器角色中，这些角色可以分配给登录用户，但不能修改。一般情况下，只将服务器权限授权给DBA，而不需要授权给其他登录用户。
②数据库对象权限：数据库对象权限是指数据库级别上对数据库对象的操作权限，例如对某数据库中表的SELECT、INSERT、UPDATE、DELETE；对存储过程的EXECUTE权限等。
③数据库权限：数据库权限表示对数据库的操作许可，也就是说，创建数据库或者创建数据库中的其他对象所需要的权限类型。
权限的管理包含3个内容：
授予权限：允许用户或角色具有某种操作权。
收回权限：不允许用户或角色具有某种操作权，或者收回曾经授予的权限。
拒绝访问：拒绝某用户或角色具有某种操作权。即使用户或角色是通过继承而获得这种操作权，也不允许该用户执行相应的操作。
（1）通过SQL Server Management Studio给用户添加数据库权限
具体步骤如下：
启动SQL Server Management Studio，并登录到服务器。
在左边的“对象资源管理器”窗口中，展开“数据库”目录，在需要授予权限的数据库上右击，在出现的快捷菜单中选择“属性”命令，出现“数据库属性”对话框，进入“权限”页面，如图6-8所示。在其中可以设置需要的权限，单击确定按钮后完成操作。
图6-8 数据库权限设置
（2）用户权限的两个要素
用户权限主要由数据对象和操作类型两个要素组成。定义一个用户的存取权限就是定义这个用户在哪些数据对象上进行哪些类型的操作。Transaction_SQL语句使用GRANT、REVOKE和DENY三种语句来管理权限，分别表示授权、收回权限以及拒绝权限。
①GRANT语句的格式为：
GRANT对象权限名［，…］ON｛表名｜视图名｜存储过程名｝TO｛数据库用户名｜用户角色名｝［，…］
【例6.3】 为student表上的数据库用户sqlUser1授予修改、删除、插入、查询、更新的权限。
GRANT ALTER，DELETE，INSERT，SELECT，UPDATE ON student TO sqlUser1；
②REVOKE语句的格式为：
REVOKE对象权限名［，…］ON｛表名｜视图名｜存储过程名｝FROM｛数据库用户名｜用户角色名｝［，…］
【例6.4】 为student表上的数据库用户sqlUser1收回查询权限。
REVOKE SELECTON student FROM sqlUser1；--sqlUser1的其他权限不变
③DENY语句的格式为：
DENY对象权限名［，…］ON｛表名｜视图名｜存储过程名｝TO｛数据库用户名｜用户角色名｝［，…］
【例6.5】 禁止student表上的数据库用户sqlUser1的删除权限。
DENY DELETE ON student To sqlUser1；
4）SQL Server的角色管理
在实际操作中，逐个设置每个用户的权限比较直观、方便，但当数据库的用户数量很大时，设置权限的工作将会变得烦琐、复杂。为了方便管理SQL Server数据库中的权限，引入了“角色”这一概念。
角色类似于Windows操作系统中的“组”，角色是具有指定权限的用户，用于管理数据库的访问权限。SQL Server管理者可以将某些用户设置为某一角色，这样只对角色进行权限设置便可以实现对所有用户权限进行设置，大大减少了管理员的工作量，也便于用户分组。一个用户可以属于多个角色，一个角色中可以包含多个用户。登录账户、数据库用户、角色三者之间的关系如图6-9所示。
图6-9 登录账户、数据库用户、角色三者之间的关系
SQL Server的角色分为服务器角色、数据库角色和应用程序角色。
（1）服务器角色
服务器角色是用户管理SQL Server服务器级别的权限，是在SQL Server安装时创建的，不允许增加和删除，服务器角色对应的权限也不允许修改，因此服务器角色也称固定服务器角色。服务器角色的作用范围是服务器，因此，用户可以向服务器角色中添加SQL Server登录账户和Windows账户。服务器角色包括：
服务器管理员serveradmin：管理SQL Server服务器端的设置。
磁盘管理员diskadmin：管理数据库在磁盘上的文件。
进程管理员processadmin：管理SQL Server系统进程。
安全管理员securityadmin：管理和审核SQL Server系统登录。
安装管理员setupadmin：增加、删除连接服务器，建立数据库复制以及管理扩展存储过程。
数据库创建者dbcreator：创建数据库，并对数据库进行修改、删除和还原。
系统管理员sysadmin：执行SQL Server中的任何操作。
public：每个SQL Server的登录账户都属于public服务器角色。
大容量插入操作管理员bulkadmin：可以执行BULK INSERT操作。
可以使用系统存储过程sp_helpsrvrolemember查看某个固定服务器角色被分配给了哪些SQL Server服务器登录账户，使其拥有相应的服务器级操作权限。可以使用sp_addsrvrolemember和sp_dropsrvrolemember过程添加或删除固定服务器角色成员。
注意：固定服务器角色本身是不能添加、修改或删除的。另外，只有固定服务器角色的成员才能执行上述两个系统过程进行固定服务器角色的添加和删除。
（2）数据库角色
数据库角色是为某一用户或某一组用户授予不同级别的管理或者访问数据库以及数据库对象的权限，这些权限的作用范围是数据库范围，一个用户可以具有属于同一数据库的多个角色。在SQL Server中，数据库角色分为两类：即固定的数据库角色和用户自定义的数据库角色。
固定数据库角色是在数据库层上进行定义的，存在于每个数据库中。固定数据库角色用来提供最基本的数据库权限管理。包括：
public：维护全部默认权限，所有数据库用户都属于public角色。public角色存在于每一个数据库中，包括系统数据库和用户数据库，而且不能被删除。因为所有的数据库用户都属于该角色，所以不能直接将该角色分配给任何用户。public角色在初始状态没有任何权限，但可以为该角色分配权限。因为所有的数据库都属于该角色，所以为该角色授权时，实际是为所有数据库用户授权。
db_owner：数据库的所有者，可以对所拥有的数据库执行任何的操作。
db_accessadmin：可以增加或者删除数据库用户、工作组和角色。
db_addladmin：可以增加、删除和修改数据库中任何对象。
db_securityadmin：执行语句权限和对象权限。
db_backupoperator：可以备份和恢复数据库。
db_datareader：能且仅能对数据库中的任何表执行select操作，从而读取所有表的信息。
db_datawriter：能够增加、修改和删除表中的数据，但不能进行select操作。
db_denydatareader：不能读取数据库中任何表中的数据。
db_denydataewriter：不能对数据库中任何表执行增加、修改和删除数据操作。
（3）应用程序角色
应用程序角色是特殊的数据库角色，用于允许用户通过特定的应用程序获取特定数据。应用程序角色使应用程序能够用其自身的、类似用户的权限来运行。
与一般的数据库角色不同的是，应用程序角色默认情况下不包含任何成员，而且是非活动的，在使用它们之前要在当前连接中将它们激活。激活一个应用程序角色后，当前连接将丧失它所具备的特定用户权限，只获得应用程序角色所拥有的权限。