⭐第11章 SQL Server的安全管理
SQL Server数据库系统具有各种高度精确的可配置安全特性，使用这些功能，DBA可根据所处环境的特定安全风险，实现经过优化的深度防御，帮助用户制定自己的信息管理安全策略。
在数据库管理系统中，用检查口令等手段来检查用户身份，从而只有保证合法的用户才能进入数据库系统，当用户对数据库执行操作时，系统自动检查用户是否有权限进行这些操作，以防止因不合法用户的访问而造成数据的泄密或破坏。
本章首先介绍SQL Server的安全体系结构，然后介绍两种验证模式及其设置、登录账号的设置、角色与用户的创建方法以及权限设置与使用等。
SQL Server的安全性机制
安全性管理是数据库管理员在实际工作中经常遇到的问题，从安全策略的制定到具体用户的权限设置，都与数据库的安全管理息息相关。SQL Server 2016的安全性机制如图11-1所示，主要包括以下5个方面的内容：
（1）SQL Server 2016客户机的安全机制。
（2）网络传输的安全机制。
（3）SQL Server 2016服务器的安全机制。
（4）数据库的的安全机制。
（5）数据对象的安全机制。
由图11-1可以看出，一般情况下SQL Server 2016安全机制设置4道防线。
用户要访问数据库中的数据，首先要登录客户机，对于Windows系统上的客户机来说，其安全机制主要涉及的是操作系统的安全，这是第1道阻止非法用户的防线。
网络传输的安全涉及到网络数据的加密和解密技术。一般的SQL Server网络数据是明文传送的，因为加密的网络传速较慢。
用户使用客户机登录SQL Server 2016服务器时，必须使用一个服务器上分配给用户的账号（即登录名）和密码，服务器会根据不同的身份验证方式来判断账号和密码的正确性。
登录到SQL Server 2016服务器的账号和密码都对应一个默认的工作数据库，不同的账号对于不同的数据库用户，数据库的安全机制要求对不同的数据库用户设置不同的默认数据库。
用户通过4道防线才能访问到数据库中的数据对象，这时不同的用户还可以具有不同的对象和语句操作权限，SQL Server中最常见的访问权限有SELECT查询权限、UPDATE更新权限、INSERT录入权限和DELETE删除权限。
基本概念
若要在SQL Server 2016的安全机制下，定义和实现有效的、可管理的安全解决方案，对其安全体系结构有很好的理解，首先需要了解下面常用的基本概念或术语。
（1）主体（Principal）
。主体是可以请求对SQL Server资源的访问权限的用户、组和进程。每个主体都有自己的安全标识号（SID）。主体可以是集合形式（比如数据库角色或Windows组）或不可分割的单一主体形式（比如本地登录或域登录）。每个主体有一个作用域，作用域基于定义主体的级别，如表11-1所示。
（2）安全对象
。安全对象是 SQL Server数据库引擎授权系统控制对其进行访问的资源。用户可以为自己设置安全性称为“范围”的嵌套层次结构，可以将某些安全对象包含在其他安全对象中。安全对象范围包括服务器、数据库、架构和对象如表11-2所示。
1.基本概念
（3）用户、数据库用户、账号、登录名和密码
。用户是指能够在SQL Server安全机制下，访问数据库中数据的操作员或客户。一般用户若要访问数据库对象，必须获得管理员分配的账号和密码。在服务器中的账号又叫登录名（Login），因此访问服务器也称为登录服务器。从SQL Server服务器的角度来看，用户就是一组匹配的账号和密码。服务器的合法登录名可以映射到数据库中成为数据库用户。一个登录名可以映射对应多个数据库用户，而一个数据库用户只能对应一个登录名。
（4）角色(roles)
。角色是SQL Server中管理权限相近的安全账户的集合，相当于Windows域中的组。利用角色作为主体可以同时对角色中的若干用户授予相同权限，这样有利于简化数据库管理员的工作。角色可以用来提供有效而复杂的安全模型，以及管理可保护对象的访问权限。SQL Server中的角色分为服务器角色、数据库角色和应用程序角色。
（5）权限
。权限是SQL Server安全性的最后一道防线，实际上是安全机制的设计者授权给某一个用户（或角色）访问数据库时，允许其对数据对象的可以进行的操作集合。要拥有对SQL Server上的安全对象的访问权限，主体必须具有在数据对象上执行操作的权限。
SQL Server系统中的对象模型，具有较细粒度的权限和层次结构组织，大约包含200个单独权限。
（6）身份验证与授权
。身份验证(Authentication)是SQL Server系统标识用户或进程的过程，SQL Server 2016 中有两种身份验证方式：Windows身份验证模式和混合身份验证模式。客户自身必须通过服务器的身份验证后才可以请求其他资源。授权(Authorization)是授予通过身份验证的用户或进程以访问或修改资源的指定权限的过程。
2.权限层次结构
SQL Server 2016系统中，主体对安全对象的访问权是分层进行的，权限的层次结构如表11-3所示。
可以通过访问服务器和数据库的主体，授予用户访问权限。这些访问权限是分层继承的，即上层授予的权限，可以被下一层对象默认继承使用。例如，授予登录的服务器管理权限可以被其映射的用户继承。主体访问安全对象的授权、撤销授权和拒绝授权的操作分别可以由GRANT、DENY和REVOKE命令实现。
3.查询权限
用户可以利用fn_my_permissions函数查询用户的有效权限，该函数一般返回调用对方服务器的有效权限列表。
fn_my_permissions函数语法格式如下：
fn_my_permissions (securable, 'securable_class')
【例11.1】列出对服务器的有效权限。
【例11.2】列出对数据库test01的有效权限
【例11.3】列出对表teacher的有效权限。
【例11.4】列出一个用户的有效权限
分析：以下示例返回数据库用户dbo对 teaching 数据库内dbo架构中score表的有效权限的列表。调用方需要对用户dbo具有 IMPERSONATE 权限。
管理服务器范围的安全性
服务器访问权限是属于SQL Server的第1个安全层次，该权限决定是否允许客户端访问服务器，这个安全级别总是由DBA负责。SQL Server 2016支持用Windows或SQL Server身份验证模式来验证客户端的身份。
1 . SQL Server 2016的验证模式
SQL Server 2016的身份验证基于SQL Server存储在主数据库中的登录名和密码。客户端必须提供登录名和密码，才能获得授权访问服务器。
SQL Server的安全性是和Windows操作系统集成在一起的，因此SQL Server提供了两种确认用户的验证模式：Windows验证和混合验证模式。
Windows身份验证模式
SQL Server数据库系统通常运行的Windows服务器平台，其本身就具备管理登录、验证用户合法性的能力，因此Windows 验证模式正是利用了这一用户安全性和账号管理的机制，允许SQL Server使用Windows的用户账户和密码
在这种模式下，用户只需要通过Windows的验证，就可以连接到SQL Server服务器，而SQL Server系统本身也就不需要管理一套登录数据。在这种方式下，用户不必提交登录名和密码让SQL Server验证。
由于Microsoft公司已经在Windows中完成了基础设施工作，Windows身份验证通常被认为更安全和更易维护。Windows身份验证对于用户和管理员来说都比较容易管理
混合身份验证模式
混合验证模式允许以SQL Server验证模式或者Windows验证模式来进行验证。混合验证模式先将客户机的账号和密码与SQL Server数据库中存储的账号和密码进行比较，如果符合就通过验证；
如果不符合，则再和Windows中存储的账号和密码进行比较，如果符合就通过验证。如果两者都不符合就无法登录SQL Server 2016服务器。
Microsoft公司仍然推荐使用Windows身份验证，因为SQL Server身份验证只应用于兼容的应用程序模式。而在实际工作中，使用SQL Server来管理账户和密码更普遍一些。
更新验证机制的步骤
（1）启动SQL Server Management Studio，在“对象资源管理器”中，右击SQL Server 2016数据库实例，在弹出的快捷菜单中，选择“属性”命令。如图11-6所示。
（2）在“服务器属性”对话框中选择“安全性”选项卡，如图11-7所示。
（3）在“服务器身份验证”区域可以设置服务器身份验证模式，然后单击“确定”按钮即可完成设置。
（4）重启SQL Server 2016，即可改变身份验证模式。
2. 服务器角色
SQL Server 2016的安全体系结构中包括含有特定隐含权限的两类预定义的角色：服务器角色和固定数据库角色。
服务器角色是执行服务器管理操作的具有相近权限的用户集合。
根据SQL Server的管理任务和重要性等级来把具有SQL Server管理职能的用户划分到不同的服务器角色，每一个角色所具有的管理SQL Server的权限都是SQL Server内置的，即DBA不能对服务器角色进行创建、修改和删除，只能向其中加入登录名或其他角色。
服务器角色是服务器级别的主体，可以成为服务器角色的成员以控制服务器作用域中的可保护对象。表11-5列出了SQL Server 2016默认创建的服务器角色及其功能。
3.管理登录名
登录名就是可以访问SQL Server数据库系统的账户，创建登录名可以通过SQL Server Management Studio图形工具，也可以利用Transact-SQL语句实现。
创建
利用SQL Server Management Studio创建登录名
注意：设为完登录名后，需要设置配置，才能登录成功
（1）启动SQL Server Management Studio工具后，展开“对象资源管理器”“安全性”子目录，右击“登录名”，在弹出的快捷菜单中选择“新建登录名”命令。
（2）在“登录名-新建”界面上，设置登录名（sql16）、身份验证模式（SQL Server身份验证）、密码（123456）、默认数据库（teaching）和语言的类型等，如图11-9所示。
（3）可以选择“服务器角色”选项卡，配置登录的服务器角色，如sysadmin。选择选项卡进行“用户映射”进行设置，如：teaching。
（4）也可以选择其他选项卡 “安全对象”和“状态”进行配置。
（5）然后单击“确定”按钮即可完成登录名的创建。
（6）在“对象资源管理器”下查看新建登录名如图11-10所示
（7）右击登录名sql16，在弹出的快捷菜单中，选择“编写登录脚本为：”“CREATE到”“新查询编辑器窗口”子目录，系统将创建登录名的过程以脚本形式保存下来。由此可知利用Transact-SQL语句创建登录名的方法。
Transact-SQL语句创建
脚本中的主要代码如下：
CREATE LOGIN [sql16]
WITH PASSWORD=N'123456',
DEFAULT_DATABASE=[teaching],
DEFAULT_LANGUAGE=[简体中文],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
ALTER LOGIN [sql16] DISABLE
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [sql16]
GO
测试登录名
下面使用SQL Server Management Studio测试新登录名是否成功连接服务器。
（1）右击SQL Server Management Studio中的实例，在快捷菜单中选择“连接”命令。
（2）在弹出的“连接服务器”界面中，选择SQL Server身份验证，然后输入登录名和密码，如图11-11所示。
（3）单击“连接”按钮可以测试连接是否成功。若不成功，会出现错误信息提示框。若测试成功，则会在“对象资源管理器”中出现连接成功的信息，如图11-12所示。
利用系统过程管理登录名
利用master数据库下的下列系统存储过程sp_addlogin、sp_droplogin、 sp_password也可以用于管理SQL Server的登录名。
（1）sp_addlogin 。系统过程sp_addlogin可以用于创建SQL Server登录名，用户可以通过该登录访问SQL Server系统，其语法过程如下：
sp_addlogin ‘login_name’ [,’passwd’[,’database’ [,language’]]]
【例11.5】利用系统过程sp_addlogin向teaching数据库创建3个新登录。
exec sp_addlogin 'rose', 'aabbcc', 'teaching'
GO
exec sp_addlogin 'hanry', 'aabbcc', 'teaching '
GO
exec sp_addlogin 'pool', 'aabbcc', 'teaching '
GO
（2）sp_droplogin。利用系统存储过程sp_droplogin可以删除一个现有的SQL Server登录名，sp_droplogin系统过程可以通过系统表syslogins中删除相应的行来达到删除登录名的目的
（3）sp_password。系统存储过程sp_password为SQL Server登录创建密码，或替换现有的口令密码。
利用该过程用户可以随时修改自己的口令密码，系统管理员通过sp_password可以更改任何口令密码。
例如：
sp_password 'aabbcc', '112233', 'hanry'
另外还有3个与Windows用户有关的系统存储过程：
① sp_grantlogin 允许Windows用户或组连接SQL Server或是为组内的用户重置先前的sp_denylogin限制。
② sp_revokelogin 用以从SQL Server中删除Windows用户或组的登录条目。
③ sp_denylogin 用以防止Windows用户或组（包括被授予访问权的用户和组）连接到SQL Server上。因为只有系统或安全管理员才能使用这些存储过程。
密码的复杂性策略
在服务器安全部署中，密码可能是最薄弱的一个环节。
SQL Server 2016的密码复杂性策略是指一系列限制密码复杂性的规则。密码复杂性策略通过增加可能密码的数量来阻止强力攻击。实施密码复杂性策略时，新密码必须符合以下原则：
（1）长度至少有6个字符，最多可包含 128 个字符。
（2）密码包含以下4类字符中的3类： 英文大写字母 (A - Z) 、英文小写字母 (a - z) 、10个基本数字 (0 - 9) 、非字母数字（例如：!、$、# 或 %）
（3）字典中查不到，且不是命令名、人名或用户名，不得包含全部或部分用户名。
（4）定期更改且与以前的密码明显不同的密码。
注意：
如果 SQL Server 登录名、用户、角色或密码具有以下特征，请在 Transact-SQL 语句中使用分隔符双引号 (") 或方括号 ([ ])：
- 含有空格或以空格开头。
- 以 $ 或 @ 字符开头。
另外，利用密码过期策略管理密码的使用期限，系统将提醒用户更改旧密码和用户，并禁用过期的密码。
4.管理凭据(了解)
凭据是包含连接到 SQL Server之外的资源所需的身份验证信息的记录。主要用于执行具有 EXTERNAL_ACCESS 权限集的程序集中的代码。当 SQL Server 身份验证用户需要访问域资源（例如存储备份的文件位置）时，也可以使用凭据。
凭据的构成：大多数凭据包含一个 Windows 登录名和密码。通过凭据，使用 SQL Server身份验证连接到 SQL Server 的用户可以连接到 Windows 或其他 SQL Server 以外的资源。在创建凭据之后，可以将凭据映射到登录名。单个凭据可映射到多个SQL Server 登录名，但是一个SQL Server 登录名只能映射到一个凭据。系统凭据是自动创建的，并与特定端点关联，其名称以 '##' 开头。
创建凭据的过程
：下面介绍创建凭据cred的一般步骤。
（1）启动SQL Server Management Studio图形工具。
（2）在“对象资源管理器”下，右击“安全性”下的“凭据”子目录，在弹出的快捷菜单中选择“新建凭据”命令。
（3）在弹出的“新建凭据”对话框中，输入凭据名称（credent）、标识（PGIG1MIWWYPOFBS\ Administrator）和密码，如图11-13所示，即可完成创建凭据的操作。如果单击“脚本”按钮，代码如下：
USE master
CREATE CREDENTIAL [credent]
WITH IDENTITY = N'LG37CEYPE9YWCSG\Administrator',
SECRET = N'123456'
GO
【例11.6】在 sys.credentials 目录视图中查看凭据的有关信息。
分析：用户可以利用SELECT语句在sys.credentials目录视图中查看凭据的相关信息。
【例11.7】创建映射到凭据的登录名
分析：创建一个登录名USER1，然后将其映射到凭据credent。
管理数据库范围的安全性
对于数据库的安全性管理，SQL Server 2016通过数据库用户、角色和架构来实现。
访问一个服务器并不意味着用户自动拥有数据库的访问权限。DBA以下列方式之一指定一个数据库登录用户.
（1）在每个用户需要访问的数据库中，创建一个与用户登录名对应的数据库用户。
（2）将数据库配置为把登录名或数据库用户作为数据库角色的成员对待的方式，使得用户能够继承角色中的所有权限。
（3）将登录名设置为使用默认账户之一：guest或dbo（数据库拥有者）。
一旦授予了对数据库的访问权限，用户就可以看到所有数据库对象。
数据库角色
数据库角色是在数据库级别定义的，并且存在于每个数据库中，是对数据库对象操作权限的集合。
分类：
1.固定数据库角色
2.用户自定义数据库角色。用户自定义数据库角色又分为
- 2.1 标准角色
- 2.2 应用程序角色
固定数据库角色
固定数据库角色
：固定数据库角色是数据库级别的主体，可以管理数据库作用域的可保护对象，其中，public公有角色比较特殊。每个被授予对数据库的访问权限的用户会自动成为公有角色的成员，并继承授予它的权限。
在所有固定数据库角色中，只有 db_owner 数据库的成员可以向固定数据库角色中添加成员。
public 角色包含每一个合法的数据库用户，是一个特殊的固定数据库角色。
一般情况下，public角色允许用户做以下操作：
（1）public角色为数据库中所有用户保持默认权限。当尚未对某个用户授予或拒绝对安全对象的特定权限时，则该用户将继承授予该安全对象的 public 角色的权限。
（2）通过guest账户访问任意数据库。
（3）用某些系统存储过程显示master数据库中的信息，查看系统表。
（4）执行一些不需要权限的语句，例如PRINT。
全部权限如下：
对于某个数据库而言，每一个数据库角色都有它特定的许可。可以用系统过程sp_dbfixdrolepermission来查看每一个固定数据库角色的许可。如果不指定role的值，所有固定服务器角色的许可都会显示出来。这个存储过程的语法结构为：
sp_dbfixedrolepermission [[@rolename=]’role’]
例如：显示db_ddladmin角色的权限
EXECUTE sp_dbfixedrolepermission db_ddladmin
自定义数据库角色
自定义数据库角色：可以创建一个数据库角色，并赋予对数据库作用域和架构作用域的可保护对象的访问权限。一个用户可以是若干个数据库角色的成员。
利用SQL Server Management Studio创建角色的步骤如下
点击查看步骤
（1）启动SQL Server Management Studio图形工具。在“对象资源管理器”下，展开数据库teaching，右击“安全性”下的“角色”子目录，在弹出的快捷菜单中选择“新建”“数据库角色”命令。
（2）在弹出的“数据库角色-新建”窗体中的“常规”选项卡中，输入角色名jsj18、所有者名sql16，并选择架构，如图11-15所示。
（3）在“安全对象”选项卡中，单击“搜索”按钮，在弹出的“添加对象”对话框中，选择其中一项，如“特定对象”，如图11-16所示。
（4）在弹出的“选择对象类型”窗口中选择“表”，单击“确定”按钮。返回到“添加对象”对话框中，单击“浏览”按钮。按照示例提示选择数据对象，如图11-17所示。单击“确定”按钮后，返回到“添加对象”对话框中。
（5）单击“确定”按钮，返回如图11-18所示的“安全对象”选项卡中为表设置权限后。单击“确定”按钮，数据库角色jsj18创建完毕。
（6）此时，对“数据库角色”项进行刷新，即可观察到新建的数据库角色。还可以通过查看脚本的形式，进一步了解创建数据库角色的命令。
了解
另外，下列系统存储过程都是用来管理数据库角色的。
（1）sp_addrole 创建一个新的数据库角色。
（2）sp_addrolemember 添加角色中的成员。
（3）sp_droprolemember 删除某一角色的用户。
（4）sp_droprole 从当前的数据行中删除角色。
（5）sp_helprole 显示当前数据库所有的数据库角色的所有信息。
（6）sp_helprolemember 返回有关当前数据库中某个角色的成员的信息。
应用程序角色
应用程序角色(Application role)是在没有成员的数据库级别上定义的,该角色只能在应用程序中使用。
目的： 防止用户直接访问底层表数据。应用程序角色可以加强对某一个特别的应用程序的安全性。
例如，某公司职员只是用某个特定的应用程序来修改员工数据信息，那么就可以为其建立应用程序角色。
应用程序角色和所有其他的角色都有很大不同。
-
应用程序角色没有成员，因为它们只是应用程序中使用，所以不需要直接对某些用户赋予权限。
-
必须为应用程序角色设计一个密码以激活它。
当应用程序角色被应用程序的会话激活以后，会话就会失去所有属于登录、用户账号或角色的权限，因为这些角色都只适用于它们所在的数据库内部，所以会话只能通过guest 用户账号的权限来访问其他数据库。 -
如果在数据库中没有guest 用户账号的话，会话就不能获得访问数据库的权限。
（1）利用SSMS创建应用程序角色的步骤
① 启动SQL Server Management Studio图形工具。在“对象资源管理器”下，展开数据库teaching，选择“安全性”“角色”子目录，右击“应用程序角色”，在弹出的快捷菜单中选择“新建应用程序角色”命令。
② 在弹出的“应用程序角色-新建”对话框的“常规”选项卡中，输入角色名称、默认架构和密码。
③ 参照创建数据库角色时的步骤设置“安全对象”选项卡后，单击“确定”按钮，应用程序角色建成。
④ 查看创建应用程序角色的脚本了解创建应用程序角色的命令，应用程序角色APP01的脚本如下：
USE [teaching]
GO
CREATE APPLICATION ROLE [APP01] WITH DEFAULT_SCHEMA = [db_owner], PASSWORD = N'123456'
GO
USE [teaching]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [APP01]
GO
（2）使用系统过程sp_addapprole来创建应用程序角色，并且赋予它们权限。
这个过程的语法结构为：
sp_addapprole[@rolename]’role’
[@passwd_name=]’password’
需要注意的是，只有db_owner,db_securityadmin 和sysadmin 这些固定角色可以执行系统过程sp_addapprole。
（3）激活应用程序角色
当一个连接启动以后，必须执行系统过程sp_setapprole来激活应用程序角色所拥有的权限。
这个过程的语法结构为：
sp_setapprole [@rolename]’role’[@passwd=]’password’
[,[@encrypt=]’encrypt_style’]
例如，激活应用程序角色app01的命令如下：
Exec sp_setapprole 'app01', '123456‘
当用系统存储过程sp_setapprole激活应用程序角色的时候，可以了解到应用程序角色总是和数据库绑定的，即应用的范围是当前数据库，如果在会话中改变了当前数据库，那么你就只能做那个数据库中允许的操作。
数据库架构
架构(schema)是管理数据对象的逻辑单位，是形成单个命名空间的数据库对象的集合。
这样，多个用户可以共享一个默认架构以进行统一名称解析。开发人员通过共享默认架构可以将共享对象存储在为特定应用程序专门创建的架构中，而不是 DBO 架构中。
SQL Server 2016在引入架构后，访问数据库对象的完全限定模式为：
sever.database.schema.object
管理数据库架构可以通过SQL Server Management Studio图形工具和Transact-SQL命令对架构进行修改和删除(了解)
点击查看步骤
（1）启动SQL Server Management Studio图形工具。在“对象资源管理器”下，展开数据库teaching，右击“安全性”“架构”子目录，在弹出的快捷菜单中选择“新建架构”。
（2）在弹出的“架构-新建”窗体中的“常规”选项卡中，输入架构名schema1、架构所有者名public，如图11-20所示。
（3）在“权限”选项卡中，单击“添加”按钮，在弹出的“选择用户和角色”对话框中，选择对象类型和对象，如图11-21所示。
（4）单击“确定”按钮，返回如图11-22所示的“权限”选项卡中为用户和角色设置权限后。单击“确定”按钮，数据库架构schema1创建完毕。
（5）此时，对“架构”项进行刷新，即可观察到新建的架构schema1。还可以通过执行创建脚本的操作查看创建架构的代码。
数据库用户
数据库用户是访问某个特定数据库的主体。
用户和登录不同：
- 登录名允许访问SQL Server系统，而数据库用户是访问某个特定数据库的主体。
- 一个登录名可映射多个数据库用户，而一个用户只能映射一个登录名。
利用SSMS创建
（1）启动SQL Server Management Studio图形工具。在“对象资源管理器”下，右击“数据库teaching”“安全性”“用户”子目录,在弹出的快捷菜单中选择“新建用户”命令。
（2）在弹出的“数据库用户-新建”窗体中的“常规”选项卡中，输入用户名hans，选择登录名sql16，并选择架构，也可以指定“默认架构”项。如图11-23所示。
（3）在“安全对象”选项卡中，添加用户的安全对象。
（4）单击“脚本”按钮，可以生成如下脚本代码。
（5）单击“确定”按钮，数据库用户hans创建完毕。
2 .管理数据库用户
利用Transact-SQL命令
向当前数据库添加用户的Transact-SQL 语法如下：
CREATE USER user_name [{FOR|FROM}
{ LOGIN login_name
| CERTIFICATE cert_name }
| WITHOUT LOGIN ]
[WITH DEFAULT_SCHEMA = schema_name ]
【例11.8】在teaching数据库中创建用户Abol。
CREATE LOGIN Abol
WITH PASSWORD = '327Shy';
USE teaching;
CREATE USER Abol;
GO
特殊用户
SQL Server数据库的特殊用户主要指guest和dbo两个用户。所有 SQL Server 2016数据库中均提供的一种特殊用户，不能从任何数据库中删除该用户。
guest用户
：guest（游客）用户在默认情况下存在于所有数据库，且是禁用的。授予guest用户的权限由在数据库中没有账号的用户继承。
另外，guest用户还具有如下特点：
（1）guest 用户不能删除，但可以通过在 master 和 temp 以外的任何数据库中执行 REVOKE CONNECT FROM GUEST来撤销该用户的CONNECT 权限，从而禁用该用户。
（2）guest用户允许没有账号的用户访问数据库。若登录有访问SQL Server实例的权限，数据库中又含有guest用户账号时，登录就可以采用guest用户的标识。
（3）应用程序角色是数据库级别的主体，只能通过其他数据库中授予guest 用户的权限来访问这些数据库。因此，任何已禁用guest用户的数据库对其他数据库中的应用程序角色都是不可访问的。
2 .管理数据库用户
特殊用户
dbo用户
dbo（数据库所有者）是具有在数据库中执行所有活动的暗示性权限的用户。固定服务器角色sysadmin的任何成员都映射到每个数据库内的称为dbo的特殊用户上，由固定服务器角色sysadmin的任何成员创建的任何对象都自动属于dbo。
另外，dbo用户还具有如下特点。
（1）dbo用户无法删除，而且始终存在于每个数据库中。
（2）只有固定服务器角色sysadmin的成员或dbo用户创建的对象才属于dbo。
（3）dbo拥有和固定服务器角色db_owner中的成员有着同样的权力，dbo是唯一一个能在db_owner角色中加入成员的用户。
管理密钥与证书（选修）
SQL Server 使用对称密钥、非对称密钥和数字证书，为各种类型的数据加密提供了丰富的支持。
1.SQL Server 2016的密码系统架构
SQL Server 2016 用分层加密和密钥管理基础结构来加密数据。每一层都使用证书、非对称密钥和对称密钥的组合对它下面的一层进行加密。如图11-24所示，顶层的服务主键是用 Windows 的DPAPI 进行加密的。
服务主键是加密层次结构的根。此密钥是在安装 Microsoft SQL Server 2016 实例时自动生成的，并受 Windows 数据API 保护。只有创建服务主键的Windows 服务账户或有权访问服务账户名称和密码的主体能够打开服务主键。
数据库管理员需要理解服务器级的服务主键和数据库级的数据库主键。每一个密钥都保护其子密钥，子密钥又保护其子密钥，从树形结构图依次向下。
口令密码保护对称密钥或证书时例外，这是 SQL Server 使用户管理自己的密钥，以及负责保密密钥的方法。利用此机制可以对数据库访问进行加密，也可以对数据进行加密。
重新生成或还原服务主密钥涉及解密和重新加密完整的加密层次结构。除非危及到该密钥的安全性，否则应该在需求较低的时间段内安排这种占用大量资源的操作。
- 创建密钥
密钥分为非对称密钥和对称密钥，对称密钥是加密和解密都使用的一个密钥。使用对称密钥进行加密和解密非常快，并且适用于使用数据库中的敏感数据的例程。非对称密钥由私钥和对应的公钥组成。
每个密钥都可以解密另一个密钥加密的数据。非对称加密和解密相对来说会消耗大量资源，但它们比对称加密提供了更高的安全级别。非对称密钥可用于加密对称密钥，以便存储在数据库中。
对称密钥具有速度快，系统占用资源少，密钥的安全分发困难的特点。
非对称密钥具有加密、解密速度慢，占用系统占用资源较多，方便进行密钥分发的特点。
【例11.9】创建和备份服务主密钥示例。
【例11.10】创建数据库的主密钥示例。
- 创建证书
公钥证书（通常只称为证书）是一个数字签名语句，它将公钥的值绑定到拥有对应私钥的人员、设备或服务的标识上。证书是由证书颁发机构 (CA) 颁发和签名的。从 CA 接收证书的实体是该证书的主题。证书中通常包含下列信息：
（1）主题的公钥。
（2）主题的标识符信息，如姓名和电子邮件地址。
（3）有效期。这是指证书被认为有效的时间长度。证书只有在指定的有效期内有效，每个证书都包含一个“有效期始于”和“有效期至”日期。这2个日期设置了有效期的界限。证书超过有效期后，必须由已过期证书的主题请求一个新证书
（4）颁发者标识符信息。
（5）颁发者的数字签名。此签名用于证明主题的公钥和标识符信息之间的绑定的有效性。在对信息进行数字签名的过程中，信息以及发件人拥有的一些秘密信息将被转换成一个称为“签名”的标记。
【例11.11】创建证书mycert1
【例11.12】从证书mycert1中创建数据库用户hongtaoliu。
1.SQL Server 2016的密码系统架构
加密实例：下面看一个关于加密的例子。
【例11.13】利用前面的证书对字符串进行加密和解密。
DECLARE @source varbinary(200)
DECLARE @encrytext varbinary(200)
SET @source=CONVERT(varbinary(200),'This is test!')
SET @encrytext = EncryptByCert(Cert_ID('mycert'),@source)
SELECT @encrytext
SELECT CONVERT(varchar(200),DecryptByCert(Cert_ID('mycert'),
@encrytext, N'PASSWORD')) as [Source]
权限管理
权限是SQL Server安全性的最后一个级别。权限可以明确用户能够使用哪些数据库对象，并对它们进行何种操作。用户在数据库内的权限取决于用户账号的权限和该用户所属的角色的权限。
类似于钥匙
分类：
在SQL Server中，权限分为语句、对象和暗示性3种类型：
- 语句权限。在数据库中创建数据库或其他项目的活动时所受到的权限控制。
- 对象权限。使用数据或执行程序的活动受到的权限控制。
- 暗示性权限。执行只有固定角色的成员或数据库对象的所有者才能够执行的某些活动权限，不能授予、撤销或拒绝。例如，添加到sysadmin角色的成员就会自动继承并获得SQL Server的所有操作权限。
语句权限
语句权限授予用户某些Transact-SQL语句的操作权力,是针对数据库的权限。语句权限是对语句本身定义的，而不是在数据库中定义的一个特定项。只有sysadmin、 db_owner 或db_securityadmin角色的成员才能授予语句权限。
利用SSMS管理语句权限
在SQL Server Management Studio中，为查看现有的角色或用户的语句权限，以及“授予”、“具有授予权限”、“允许”或“拒绝”语句权限提供了图形界面。SQL Server 2016中可以通过多种方式获取这种图形界面。
可以看到下方列表中包含上方窗口中指定的数据库的用户或角色的语句权限。可以利用“添加”和“删除”按钮对数据库用户和角色进行增减，可以用复选框“授予”或“拒绝”指定对象上的各个权限。
SQL Server 2016中用户或角色的权限包括可以将各类权限设置为“授予”、“具有授予权限”、“允许”或“拒绝”，或者不进行任何设置。
“授予”表示指权限分配给用户或角色。“具有授予权限”是指用户或角色获得的权限可以再授予其他用户或角色。选中“拒绝”将覆盖表级对列级权限以外的所有层次的权限设置。如果未进行任何设置，将从其他组成员身份中继承权限。
利用Transact-SQL语句管理语句权限
数据控制语言（DCL）是用来设置或更改数据库用户或角色权限的语句，包括GRANT、DENY和REVOKE等语句。
在默认状态下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人员才有权力执行数据控制语言。3种语句的功能如表11-7所示。
下面对授予、拒绝和撤销安全对象的权限进行说明：
（1）授予权限将删除对所指定安全对象的相应权限的DENY或REVOKE权限。如果在包含该安全对象的更高级别拒绝了相同的权限，则DENY优先。
（2）在 SQL Server 2016 中在更高级别撤销已授予权限的操作并不优先，表级DENY并不优先于列级 GRANT。
（3）REVOKE 语句可用于删除已授予的权限或取消拒绝权限，DENY语句可用于防止主体通过GRANT 获得特定权限。
（4）数据库级权限在指定的数据库范围内授予。如果用户需要另一个数据库中的对象的权限，请在该数据库中创建用户账户，或者授权用户账户访问该数据库以及当前数据库。
（5）sp_helprotect 系统存储过程可报告对数据库级安全对象的权限。
【例11.14】在数据库teaching中，为数据库用户hans和Abol设置DELETE、INSERT和SELECT语句权限。
GRANT DELETE ,UPDATE TO hans,Abol WITH GRANT OPTION
GO
GRANT INSERT TO hans,Abol
GO
DENY SELECT TO hans,Abol
GO
EXECUTE sp_helprotect NULL,NULL,NULL,'S'
GO
对象权限
对象权限就是指使用数据或执行程序的活动时受到的权限控制。对象权限表示对特定的数据库对象（表、视图、字段和存储过程）的操作权限，它决定了能对表、视图等数据库对象执行的操作。
利用SSMS 管理对象权限
在SQL Server Management Studio中，为查看现有的对象权限，以及“授予”、“具有授予权限”、“允许”或“拒绝”语句权限提供了图形界面。
例如， 在“对象资源管理器”下，展开“teaching数据库”“表”子目录，右击st_score表，在弹出的菜单中选择“属性”，然后选择“权限”选项卡，可以查看、设置表的对象权限，如图11-28所示。
如果选择一个操作语句，然后单击“列权限”按钮，还可以设置表中某些列的权限，如图11-29所示。
2.对象权限
利用Transact-SQL语句管理对象权限
管理对象权限的Transact-SQL语句包括GRANT、DENY和REVOKE等语句。
【例11.15】在数据库teaching中，为表st_score设置DELETE、INSERT和SELECT对象权限。
--设置表上的对象权限
GRANT INSERT,SELECT ON dbo.st_score TO Abol WITH GRANT OPTION
GO
GRANT DELETE ON dbo.st_score TO Abol
GO
DENY ALTER ON dbo.st_score TO Abol
GO
--设置列上的对象权限
DENY UPDATE ON dbo.st_score (studentno) TO Abol
GO
GRANT UPDATE ON dbo.st_score (sname) TO Abol AS dbo
GO
DENY UPDATE ON dbo.st_score (courseno) TO Abol
GO
GRANT UPDATE ON dbo.st_score (final) TO Abol WITH GRANT OPTION AS dbo
GO
--查看对象权限
EXECUTE sp_helprotect 'st_score'
GO
【例11.16】在数据库teaching中，撤销用户Abol在表st_score上设置的INSERT和SELECT对象权限。
--CASCADE表示要撤消的权限也会从此主体授予或拒绝该权限的其他主体中撤消。
REVOKE INSERT,SELECT on st_score from Abol CASCADE
EXECUTE sp_helprotect 'st_score'
GO
解决权限冲突
用户在登录到SQL Server 2016后，其用户账号所属的角色所被赋予的权限决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。
在每个数据库中用户的权限独立于用户账号和用户在数据库中的角色，每个数据库都有自己独立的权限系统。
授予角色的权限由它们的成员继承。虽然用户可以在一个级别上授予或撤销权限，但是，若这些权限与更高级别上的权限发生冲突，则可能拒绝或允许用户访问权限。
拒绝
在 SQL Server 2016 中，除了表级的DENY优先权并不优先于列级 GRANT外，拒绝权限具有各层次的优先权，在任何级别上的拒绝权限都拒绝该对象的权限，无论该用户现有的权限是已经授予还是废止。
SQL Server总是首先处理被拒绝的权限，若对public角色设置拒绝权限，则将禁止任何用户访问对象，包括DENY语句的用户。
撤销
撤销权限只删除所撤销级别（如包含该用户、组或角色）上的已经授予的权限或已经拒绝的权限。而在另一层次上所授予或拒绝的主体的同一权限仍然有效。
REVOKE语句能够将在当前数据库内的用户或者角色上授予或拒绝的权限删除，但是该语句并不影响用户或者角色从其他角色中作为成员继承过来的权限。
授权
授予权限删除所授予级别（如包含该用户、组或角色）上的已经拒绝权限或撤销权限。而在另一级别上所拒绝的同一权限仍然有效。在另一级别上所撤销的同一权限仍然适用，但它并不阻止用户访问该对象。
因此，用户得到的权限是在对象上所授予、拒绝或撤销的全部权限的并集，其中拒绝权限比另一级别上授予或撤销的同一权限优先。
例如，用户可以从一个角色中接受一些权限，而从其他一些角色中接受另一些权限。或者，用户可以拒绝将角色其他成员所具有的权限授予某个用户。
