SQL Server教程 - SQL Server 安全管理（SECURITY）

更新记录
转载请注明出处：https://www.cnblogs.com/cqpanda/p/16539396.html
2022年8月5日 发布。
2022年7月2日 从笔记迁移到博客。

SQL Server安全体系

SQL Server的安全体系主要分为：认证 和 授权

SQL Server安全机制分级

汇总

客户机安全机制
网络传输级别安全
实例级别安全
数据库级别安全
数据库对象级别安全

这些层级由高到低，所有的层级之间相互联系，用户只有通过了高一层级的安全验证，才能继续访问数据库中低一层级的内容。

客户机安全机制

数据库管理系统需要运行在某一特定的操作系统平台下，客户机操作系统的安全性直接影响到SQL Server 2019的安全性。在用户使用客户计算机通过网络访问SQL Server 2019 服务器时，用户首先要获得客户计算机操作系统的使用权限。保证操作系统的安全性是操作系统管理员或网络管理员的任务。由于SQL Server 2019采用了集成 Windows NT网络安全性机制，因此提高了操作系统的安全性，但与此同时也加大了管理数据库系统安全的难度。

网络传输的安全机制

SQL Server 2019对关键数据进行了加密，即使攻击者通过了防火墙和服务器上的操作系统到达了数据库，还要对数据进行破解。SQL Server 2019有两种对数据加密的方式：数据加密和备份加密。数据加密：可以执行所有的数据库级别的加密操作，省去了应用程序开发人员创建定制的代码来实现数据的加密和解密的开发工作。数据在写到磁盘时进行加密，从磁盘读的时候解密。使用SQL Server来管理加密和解密，可以保护数据库中的数据。备份加密：对备份进行加密可以防止数据泄露和被篡改。

实例级别安全机制

SQL Server 2019采用了标准 SQL Server 登录和集成 Windows 登录两种。无论使用哪种登录方式，用户在登录时必须提供账号和登录密码，管理和设计合理的登录方式是SQL Server数据库管理员的重要任务，也是SQL Server安全体系中重要的组成部分。SQL Server服务器中预先设定了许多固定服务器的角色，用来为具有服务器管理员资格的用户分配使用权利，固定服务器角色的成员可以用于服务器级的管理权限。

数据库级别安全机制

在建立用户的登录账号信息时，SQL Server提示用户选择默认的数据库，并分配给用户权限，以后每次用户登录服务器后，都会自动转到默认数据库上。对任何用户来说，如果在设置登录账号时没有指定默认数据库，则用户的权限将限制在 master 数据库以内。SQL Server 2019允许用户在数据库上建立新的角色，然后为该角色授予多个权限，最后再通过角色将权限赋给SQL Server 2019的用户，使其他用户获取具体数据库的操作权限。

对象级别安全机制

对象安全性检查时，数据库管理系统的最后一个安全等级。创建数据库对象时，SQL Server 2019将自动把该数据库对象的用户权限赋予该对象的所有者，对象的所有者可以实现该对象的安全控制。数据库对象访问权限定义了用户对数据库中数据对象的引用、数据操作语句的许可权限，这通过定义对象和语句的许可权限来实现。

认证和授权过程

SQL Server 2019 安全模式下的层次对于用户权限的划分并不是孤立的，相邻的层次之间通过账号建立关联，用户访问的时候需要经过3个阶段的处理。
第一阶段：用户登录到 SQL Server的实例进行身份鉴别，被确认合法才能登录到 SQL Server 实例。
第二阶段：用户在每个要访问的数据库里必须有一个账号，SQL Server 实例将登录映射到数据库用户账号上，在这个数据库的账号上定义数据库的管理和数据库对象访问的安全策略。
第三阶段：检查用户是否具有访问数据库对象、执行操作的权限，经过语句许可权限的验证，才能够实现对数据的操作。

安全相关术语

数据库对象所有者(Database Object Owner)(DBO)

数据库中的表、索引、视图、触发器、规则和存储过程都是对象。建立对象的用户称为对象所有者。对象所有者可以设置对象的具体授权。

数据库所有者(Database Owner)

数据库所有者（DBO)是数据库的创建者，每个数据库只有一个数据库所有者。DBO有数据库中的所有特权，可以提供给其他用户访问权限。

数据库对象(Database Object)

数据库对象包含数据表、索引、视图、触发器、规则和存储过程，创建数据库对象的用户是
数据库对象的所有者，数据库对象的所有者可以授予其他用户访问其拥有的数据库对象的权限。

系统管理员(System Administrator)

最高权限的数据库用户

可以进行数据库的一切操作

许可(Permissions)

使用许可可以增强SQL Server数据库的安全性，SQL Server许可系统指定哪些用户被授予使用哪些数据库对象的操作，指定许可的能力由每个用户的状态（系统管理员、数据库所有者或者数据库对象所有者）决定。

域

域是一组计算机的集合，它们可以共享一个通用的安全数据库。

数据库组

数据库组是一组数据库用户的集合。这些用户接受相同的数据库用户许可。使用组可以简化大量数据库用户的管理，组提供了让大量用户授权和取消许可的一种简便方法。

用户名(Username)

SQL Server服务器分配给登录ID的名字，用户使用用户名连接到SQL Server 2019.

访问级别

4个访问级别：网络/操作系统、SQL Server服务器、数据库、数据库对象

角色

角色中包含了SQL Server 2019预定义的一些特殊权限，可以将角色分别授予不同的主体。使用角色可以提供有效而复杂的安全模型，以及管理可保护对象的访问权限。SQL Server 2019中包含4类不同的角色，分别是：固定服务器角色、固定数据库角色、用户自定义数据库角色和应用程序角色。

主体

主体是可以请求对SQL Server 资源访问权限的实体，包括用户、组或进程。主体有以下特征：每个主体都有自己的安全标识号（SID),每个主体有一个作用域，作用域基于定义主体的级别，主体可以是主体的集合（Windows组）或者不可分割的主体（Windows登录名）。Windows 级别的主体包括：Windows域登录名和Windows 本地登录名；SQL Server级别的主体包括：SQL Server登录名和服务器角色；数据库级别的主体包括：数据库用户、数据库角色，以及应用程序角色。

系统管理员

系统管理员是负责管理SQL Server全面性能和综合应用的管理员，简称sa.系统管理员的工作包括安装SQL Server 2019、配置服务器、管理和监视磁盘空间、内存和连接的使用、创建设备和数据库、确认用户和授权许可、从SQL Server数据库导入导出数据、备份和恢复数据库、实现和维护复制调度任务、监视和调配SQL Server 性能、诊断系统问题等。

安全验证模式

说明

验证方式也是用户登录，这是SQL Server实施安全性的第一步，用户只有登录到服务器之后才能对SQL Server数据库系统进行管理。如果把数据库作为大楼的一个个房间的话，那么用户登录数据库就是首先进入这栋大楼。

SQL Server支持2种身份验证模式：

​ Windows验证模式： 基于Windows的安全模式身份验证

​ 混合验证模式： 使用账号+密码的方式进行验证

Windows 身份验证模式

Windows 身份验证模式：一情况下 SQL Server 数据库系统都运行在Windows服务器上，作为一个网络操作系统，Windows 本身就提供账号的管理和验证功能。Windows 验证模式利用了操作系统用户安全性和账号管理机制，允许SQL Server 使用Windows的用户名和口令。在这种模式下，SQL Server把登录验证的任务交给了Windows操作系统，用户只要通过Windows的验证，就可以连接到SQL Server服务器。使用Windows 身份验证模式可以获得最佳工作效率，在这种模式下，域用户不需要独立的SQL Server 账户和密码就可以访问数据库。如果用户更新了自己的域密码，那么就不必更改SQL Server 2019的密码，但是该模式下用户要遵从Windows安全模式的规则。默认情况下，SQL Server 2019 使用Windows 身份验证模式，即本地账号来登录。

混合模式

SQL Server和Windows(混合）身份验证模式：使用混合模式登录时，可以同时使用Windows身份验证和SQL Server身份验证。如果用户使用TCP/IP Sockets 进行登录验证，则使用SQL Server身份验证；如果需要使用本地账户来登录数据库，则使用Windows 身份验证。在SQL Server 2019身份验证模式中，用户安全连接到SQL Server 2019.在该认证模式下，用户连接到SQL Server 2019时必须提供登录账号和密码，这些信息保存在数据库中的syslogins系统表中，与Windows的登录账号无关。如果登录的账号是在服务器中注册的，则身份验证失败。登录数据库服务器时，可以选择任意一种方式登录到SQL Server。

修改验证模式-使用SSMS

进入服务器设置页面

设置验证模式

创建登录账户

说明

注意：创建用户时(或后)需要为用户分配角色和基本的权限

创建实例登录账户-使用SSMS

创建实例登录账户-使用T-SQL

创建SQL Server混合账户

CREATE LOGIN 登录名 WITH
[PASSWORD = '密码' | hashed_password HASHED] [MUST_CHANGE],
[SID = sid],
[DEFAULT_DATABASE = 默认数据库],
[DEFAULT_LANGUAGE = 默认语言],
[CHECK_EXPIRATION = ON | OFF],
[CHECK_POLICY = ON | OFF],
[CREDENTIAL = credential_name];

参数说明

hashed_password指已经哈希加密后的密码字符串，HASHED表示已经加密。
MUST_CHANGE表示新登录后必须修改。
sid表示指定用户的GUID，未指定则系统自动生成GUID。
CHECK_EXPIRATION表示必须遵守账户强制密码过期策略。
CHECK_POLICY表示必须遵守Windows密码策略。

登录名:指定创建的登录名。有4种类型的登录名：SQL Server登录名、Windows登录名、证书映射登录名和非对称密钥映射登录名。如果从Windows域账户映射loginName,则loginName必须用方括号（［］）括起来。
PASSWORD = 'password:仅适用于SQL Server登录名。指定正在创建的登录名的密码。应使用强密码。
PASSWORD=hashed_password:仅适用于HASHED关键字。指定要创建的登录名对应的密码的哈希值。
HASHED:仅适用于SQL Server登录名。指定在PASSWORD参数后输入的密码已经过哈希运算。如果未选择此选项，则在把作为密码输入的字符串存储到数据库之前，对其进行哈希运算。
MUST_CHANGE:仅适用于SQL Server登录名。如果包括此选项，则SQL Server将在首次使用新登录名时提示用户输入新密码。
CREDENTIAL= credential_name:将映射到新SQL Server登录名的凭据的名称。该凭据必须已存在于服务器中。当前此选项只将凭据链接到登录名。在未来的SQL Server 版本中可能会扩展此选项的功能。
SID=sid:仅适用于SQL Server登录名。指定新SQL Server登录名的GUID.如果未选择此选项，则SQL Server 自动指派 GUID.
DEFAULT_DATABASE=database.指定将指派给登录名的默认数据库。如果未包括此选项，则默认数据库将设置为master.
DEFAULT_LANGUAGE=language:指定将指派给登录名的默认语言。如果未包括此选项，则默认语言将设置为服务器的当前默认语言。即使将来服务器的默认语言发生更改，登录名的默认语言也仍保持不变。
CHECK_EXPIRATION = { ON|OFF }:仅适用于SQL Server登录名。指定是否对此登录账户强制实施密码过期策略。默认值为OFF.
CHECK_POLICY = { ON| OFF }:仅适用于SQL Server登录名。指定应对此登录名强制实施运行SQL Server的计算机的 Windows密码策略。默认值为ON.
WINDOWS:指定将登录名映射到Windows登录名。
CERTIFICATE certname 指定将与此登录名关联的证书名称。此证书必须已存在于master数据库中。
ASYMMETRIC KEY asym_key_name:指定将与此登录名关联的非对称密钥的名称。此密钥必须已存在于master数据库中。

实例：

CREATE LOGIN panda_test
WITH PASSWORD = '987963'
MUST_CHANGE,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON;

实例：

CREATE LOGIN DBAdmin
WITH PASSWORD= 'dbpwd', DEFAULT_DATABASE=test

创建Windows 登录账号

使用Windows账户登录SQL Server，首先需要在Windows系统中创建账户。然后在SQL Server中创建关联Windows账户的账户。

注意：需要在Windows中创建账号后，再到SQL Server中关联刚才创建Windows登录账号。
注意：Windows域账户必须使用[]进行包裹。

CREATE LOGIN [用户名] FROM
WINDOWS [WITH DEFALUT_DATABASE=database | DEFALUT_LANGUAGE = language]

实例1：

CREATE LOGIN [MT-CC1\SQLUSERNAME]
FROM WINDOWS;

实例2：

CREATE LOGIN [computer_name\Mary] 
FROM WINDOWS 
WITH DEFAULT_DATABASE = [TestData];

修改登录账户

修改登录账户-使用SSMS

修改登录账户-使用T-SQL

ALTER LOGIN 登录名
[ENABLE | DISABLE]
WITH 
[PASSWORD = '密码' | hashed_password HASHED]
[OLD_PASSWORD = 'old_password']
[NAME = login_name]
[MUST_CHANGE]
[UNLOCK]
[DEFAULT_DATABASE = 默认数据库]
[DEFAULT_LANGUAGE = 默认语言]
[CHECK_EXPIRATION = ON | OFF]
[CHECK_POLICY = ON | OFF]
[CREDENTIAL = credential_name | NO CREDENTIAL]

修改指定数据库登录账户-使用SSMS

修改指定数据库登录账户-使用T-SQL

ALTER USER 用户名
WITH 
[NANE = 新用户名]
[DEFAULT_SCHEMA = 默认架构];

修改指定账户的密码

sp_password null,'abc123','sa'

删除登录账户

删除登录账户-使用SSMS

删除登录账户-使用T-SQL

DROP LOGIN 登录名;

删除指定数据库的登录账户-使用SSMS

删除指定数据库的登录账户-使用T-SQL

DROP USER 用户名;

创建数据库账户

创建指定数据库账户-使用SSMS

数据库用户是映射到登录账户上的

创建指定数据库账户-使用T-SQL

CREATE USER 用户名
[LOGIN 登录名 | WITHOUT LOGIN]
[WITH DEFAULT_SCHEMA = 架构];

注意：如果DEFAULT_SCHEMA未设置，则使用默认架构dbo。
注意：如果不指定LOGIN 登录名，则使用用户名。

实例：创建数据库账户

USE [TestData];
CREATE USER [Mary] FOR LOGIN [computer_name\Mary];

SQL Server角色管理

说明

使用登录账户可以连接到服务器，但是如果不为登录账户分配权限，则依然无法对数据库中的数据进行访问和管理。角色相当于Windows操作系统中的用户组，可以集中管理数据库或服务器的权限。

角色分类

固定服务器角色、数据库角色、自定义数据库角色、应用程序角色

固定服务器角色

服务器角色可以授予服务器管理的能力，服务器角色的权限作用域为服务器范围。用户可以向服务器角色中添加SQL Server 登录名、Windows 账户和 Windows组。固定服务器角色的每个成员都可以向其所属角色添加其他登录名。

注意：服务器角色的权限范围是整个服务器。

固定服务器角色列表：

服务器角色名称	说明
public	有两大特点,一是初始状态时没有权限,二是所有的数据库用户都是它的成员。
sysadmin	这个服务器角色的成员有权在SQL Server 2016中执行任何任务。默认情况下,Windows BUILTIN\Administrators组(本地管理员组)的所有成员都是sysadmin 固定服务器。
serveradmin	有权更改服务器范围的配置选项和关闭服务器。例如,SQL Server可以使用多大内存或监视通过网络发送多少信息,或者关闭服务器,这个角色可以减轻管理员的一些管理负担。
bulkadmin	这个服务器角色的成员可以运行BULK INSERT 语句。这条语句允许从文本文件中将数据导入SQL Server 2016数据库中,为需要执行大容量插入数据库的域账户而设计。
dbcreator	这个服务器角色的成员可以创建、更改、删除和还原任何数据库。这既是适合助理DBA的角色,也可能是适合开发人员的角色。
diskadmin	这个服务器角色用于管理磁盘文件,比如镜像数据库和添加备份设备它适合助理DBA。
processadmin	可以终止在SQL Server 实例中运行的进程。
securityadmin	可以管理登录名及其属性。可拥有GRANT 、DENY和REVOKE 服务器级别的权限,也可以拥有GRANT 、DENY和REVOKE 数据库级别的权限。此外,它们还可以重置SQL Server 登录名的密码。可以授权、拒绝和撤销服务器级权限,也可以授权、拒绝和撤销数据库级权限。另外,它们可以重置SQL Server 2016登录名的密码。
setupadmin	为需要管理链接服务器和控制启动的存储过程的用户而设计。可以添加、删除和配置链接服务器。
bulkadmin	可以运行BULK INSERT 语句。
diskadmin	用于管理磁盘文件。

数据库角色

数据库角色是针对某个具体数据库的权限分配，数据库用户可以作为数据库角色的成员，继
承数据库角色的权限，数据库管理人员也可以通过管理角色的权限来管理数据库用户的权限。

自定义数据库角色

实际的数据库管理过程中，某些用户可能只对数据库进行插入、更新和删除的操作，但是固定数据库角色中不能提供这样一个角色，因此，需要创建一个自定义的数据库角色。

自定义实例角色-使用SSMS

自定义数据库角色-使用SSMS

打开SSMS,在【对象资源管理器】窗口中，依次打开【数据库】→【test_db】→【安全
性】→【角色】节点，使用鼠标右击【角色】节点下的【数据库角色】节点。

应用程序角色

定义应用程序角色-使用SSMS

定义应用程序角色-使用T-SQL

定义应用程序角色

CREATE APPLICATION ROLE 应用程序角色
WITH PASSWORD = 'password',
[DEFAULT_SCHEMA = schema_name];

实例：

CREATE APPLICATION ROLE panda_application
WITH PASSWORD = '123qqQQ!',
DEFAULT_SCHEMA = panda_test;

激活应用程序角色

sp_setapprole '应用程序角色名','';

注意：需要激活应用程序角色后才可以使用
实例：

sp_setapprole panda_application,'123qqQQ!';

删除应用程序角色-使用T-SQL

语法：

DROP APPLICATION ROLE 应用程序角色名;

实例：

DROP APPLICATION ROLE panda_application;

删除应用程序角色-使用SSMS

修改应用程序角色-使用SSMS

权限管理

权限说明

用户权限分类：
登录权限
访问特定库的权限
在特定数据库执行特定操作的权限

各种账号权限：
登录名主要用于登录权限
用户名就是访问特定数据库的权限
操作权限就是操作特定数据库的权限
权限层次结构：

权限操作说明

GRANT 语句用来对用户授予权限，DENY 语句用于防止主体通过 GRANT 获得特定权限，REVOKE 语句用于删除已授予的权限。默认状态下，只有 sysadmin、dbcreater、db_owner、db_securityadmin 等成员有权执行数据控制语言。

授予权限

在数据库中添加一个新用户之后，该用户可以查询系统表的权限，而不具有操作数据库对象的任何权限。GRANT 语句可以授予对数据库对象的操作权限，这些数据库对象包括数据表、视图、存储过程、聚合函数等，允许执行的权限包括查询、更新、删除等。

GRANT [ALL | 指定权限]
[ON 表|数据库对象]
TO 用户名,...
[WITH GRANT OPTION];

注意：WITH GRANT OPTION表示该用户可以将自有的权限赋予别人

注意：使用 ALL 参数相当于授予以下权限：
如果安全对象为数据库，则ALL表示BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和CREATE VIEW。如果安全对象为标量函数，则ALL表示 EXECUTE和REFERENCES。如果安全对象为表值函数，则ALL 表示 DELETE、INSERT、REFERENCES、SELECT和UPDATE。如果安全对象是存储过程，则ALL表示EXECUTE。如果安全对象为表，则ALL 表示 DELETE、INSERT、REFERENCES、SELECT 和UPDATE。
如果安全对象为视图，则ALL 表示 DELETE、INSERT、REFERENCES、SELECT 和UPDATE。

PRIVILEGES:包含此参数是为了符合ISO标准。
permission:权限的名称，例如SELECT、UPDATE、EXEC等。
column:指定数据表中将授予其权限的列的名称。需要使用括号（）。
class:指定将授予其权限的安全对象的类。需要范围限定符：：。
securable:指定将授予其权限的安全对象。
TO principal:主体的名称。可为其授予安全对象权限的主体，随安全对象而异。相关有效的组合，请参阅下面列出的子主题。
GRANT OPTION:指示被授权者在获得指定权限的同时还可以将指定权限授予其他主体。
AS principal:指定一个主体，执行该查询的主体从该主体获得授予该权限的权利。

实例：将 Mary 存储过程的 EXECUTE 权限授予 pr_Names

GRANT EXECUTE
ON pr_Names
TO Mary;

实例：对名为guest的用户进行授权，允许该用户对stu_info数据表执行更新和删除的操作

GRANT UPDATE,DELETE
ON stu_info
TO guest WITH GRANT OPTION;

实例：向Monitor 角色授予对 test 数据库中 stu_info 表的 SELECT、INSERT、UPDATE
和DELETE权限

GRANT SELECT,INSERT,UPDATE,DELETE
ON tu info
TO Monitor

实例：

拒绝权限

出于安全性的考虑，可能不太希望让一些人来查看特定的表，此时可以使用DENY 语句来禁止对指定数据表的查询操作，数据库管理员可以用DENY语句来禁止某个用户对指定对象的访问。即限制该用户对指定对象的所有访问权限。

DENY [ALL | 其他权限]
[ON 表|其他数据库对象]
TO 用户名 CASCADE;

实例：禁止guest用户对 stu_info 数据表的操作更新

DENY UPDATE
ON stu info
To guest CASCADE;

实例：拒绝授予 guest 用户对 test_db 数据库中 stu_info表的 INSERT和DELETE 权限

DENY INSERT,DELETE
ON stu info
TO guest

回收权限

既然可以授予用户权限，同样可以收回用户的权限，例如收回用户的查询、更新或者删除权限。REVOKE 语句用于删除已授予的权限。

REVOKE [ALL | 其他权限]
[ON 表]
FROM 用户名;

实例：收回guest 用户对 stu_info数据表的删除权限

REVOKE DELETE 
ON stu info 
FROM guest;

实例：撤销 Monitor角色对 test_db数据库中 stu_info表的 DELETE权限

REVOKE DELETE
ON OBJECT::stu_info
FROM Monitor CASCADE

权限分配技巧

对角色分配权限而不是直接分配权限

将权限分配给角色，而不是指定的账户。基于角色管理权限比直接操作权限便于日后维护。

应用程序角色的有效时间

应用程序激活后，其有效时间只存在于连接会话中。当断开当前服务器连接时，会自动关闭
应用程序角色。

如何利用访问权限减少管理开销

为了减少管理的开销，在对象级安全管理上应该在大多数场合赋予数据库用户以广泛的权限，
然后再针对实际情况在某些敏感的数据上实施具体的访问权限限制。

SQL Server加密方案

Transparent Data Encryption (TDE) solution
AlwaysEncrpyted solution

注意：开启加密方案后，记得在connectionString上加上Encryption Setting=Enabled;

Data Source=localhost;Initial Catalog=InventoryManager;Trusted_Connection=True;Column Encryption Setting=Enabled;