SQL Server On Linux（11）—— SQL Server On Linux安全性（4）——Always Encrypted

本人新书上市，请多多关照： 《SQL Server On Linux运维实战 2017版从入门到精通》

Always Encrypted，简称AE，官方翻译叫始终加密，下一节会介绍叫透明数据加密（TDE）的功能，如果搭配TDE连接SQL Server，那么数据在客户端和服务器上包含磁盘上的数据已经是被加密了的。但是还有一些不足的地方：1. 在内存中的数据页不被加密。2. 没有隔离密钥，SQL Server的管理员可以获取加密密钥和证书进行数据破解。基于大数据时代的数据安全性跟 GDRP(General Data Protection Regulation) （它由欧盟推出，目的在于遏制个人信息被滥用，保护个人隐私。）的实行，数据的安全被推到非常高的程度。SQL Server在这方面也推出了各种功能来满足GDRP，其中一个就是AE。AE不仅解决了上面提到的两点不足，还实现了端对端的加密。

AE工作流

先来看第一个图，AE使用两类密钥来保护数据：

1. CEK：列加密密钥（column encryption key)，用于加密数据库中的数据。已加密的CEK（可以有多个）存储在数据库中。
2. CMK：列主密钥(column master key)，用于加密CEK。存储在可信任的密钥存储（Trusted Key Store）中，比如Windows 证书存储、Azure Key Vault、其他硬件安全模块中甚至自定义的密钥存储。
   在SQL Server中需要加密的数据使用 AEAD_AES_256_CBC_HMAC_SHA_256 算法进行加密，最重要的是解密过程不由SQL Server处理。也就是说单纯从SQL Server内部是看不到已加密数据的明文。解密过程由客户端执行，但是需要使用“启用始终加密”的客户端驱动程序来实现加解密。支持AE的驱动可以参阅官方文档 始终加密（客户端开发）
   驱动程序需要在连接字符串中使用***Column Encryption Setting***属性来处理AE，当SQL Server发送已加密的数据到应用程序时，会在结果集上附带上已加密的CEK和CMK的路径，客户端驱动程序会把这些信息整合并根据路径去找CMK，用于结合CEK解密数据。这个过程也同样适用于参数化查询。驱动程序会与SQL Server一起决定对哪些参数进行加密，要知道很多参数本身就是安全风险。
   简单来时候，整个加解密过程都发生在应用程序的驱动程序中，而SQL Server仅存储已加密的数据和CEK及CMK的位置。驱动程序通过位置去可信任密钥存储中获取实际CMK然后合并解密数据。这个过程对应用程序是透明的，不需要修改现有程序。另外注意上图的“内存缓存中的已解密CEK”，通过这种缓存，可以减少到密钥存储的往返次数。但是不可否认，这种过程是肯定会增加交互的往返次数和网络流量。另外因为它是缓存在客户端中，所以也不存在SQL Server高权限用户可以获取并解密的可能。

AE加密类型

AE支持两种加密类型：

1. 确定性加密：deterministic encryption，对特定的值总产生相同的加密值，可用于需要创建所以并使用精确查找、分组等操作的数据列。但是很显然这种加密方式安全性不足。
2. 随机加密：randomized encryption，如其名，产生随机加密值，更具有安全性但是由于其随机性，不适合进行索引化和分组等操作。
   跟其他功能类似，当允许你选择“用”还是“不用”的时候，就意味着它有一些适应场景，AE也同样，特别是对某些数据类型等的限制，完整的限制可以查阅官方文档： AE限制
   最后还要提醒一下，所有的加密技术都会增加存储空间，根据数据类型和算法的不同二不同，另外加解密也是CPU消耗操作，所以对资源的要求更加高。

接下来做个快速演示，为了避免影响其他环节，这次创建一个专门的演示库：AEDemo。

新建列主密钥

即前面的CMK，按下图路径新建列主密钥。

配置新列主密钥

这里选择默认的选项也就是当前用户，可以看到一共有四种可选，根据具体情况而定。 然后点击生成证书，可以看到证书信息，点击确定后再刷新SSMS的目录，新的列主密钥已经生成完毕。

CMK创建完毕，提醒一下，CMK可以有多个。

新建列加密密钥：

即CEK，按下图方式打开并输入CEK名字，同时选择使用什么CMK来对其加密。前面提到，CMK是可以有多个的。

选择用于加密CEK的CMK。

创建完毕

创建测试表并加密

创建一个Patients

use AEDemo
GO
CREATE TABLE [dbo].[Patients](
 [PatientId] [int] IDENTITY(1,1) NOT NULL,
 [SSN] [nvarchar](11) NOT NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [MiddleName] [nvarchar](50) NULL,
 [StreetAddress] [nvarchar](50) NULL,
 [City] [nvarchar](50) NULL,
 [ZipCode] [int] NULL,
 [State] [nvarchar](50) NULL,
 [BirthDate] [datetime2](7) NOT NULL,
 PRIMARY KEY CLUSTERED 
 (
 [PatientId] ASC
 ) WITH (
 PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

插入测试数据

insert into [dbo].[Patients]
values(N'1097-22-14',N'abc',N'george',N'def',N'china',N'guangzhou',123456,N'guangdong','1900-01-01')


select * from [dbo].[Patients]

结果如下图

接下来开始使用密钥进行加密。选择加密列，如下图

选择需要加密的列及加密类型和密钥，这里可以选择已建好的CEK，或者由系统自动创建，不过系统创建的步骤较多，就不多演示，等后续有时间详细介绍的时候再演示，因为这个系列我想尽快介绍完 SQL 2019的新功能。

选了已有的CEK后，后面的步骤就简单多了。

使用默认选项一直进行下去：

检验效果

再次执行查询表的语句，可以看到已经加密成功

更多的资料可以访问官方博客： SQL Server Security Blog ，我也会抽空翻译或者整合。

AE是非常重要且易用的功能，但是在本人研究过程发现AE实际上还有很多内容可以使用，正如上面官方博客，基于这个系列的初衷是介绍SQL on Linux 特别是2019为主，所以不打算前期过于深入。

AE从SQL Server 2016引入，跟平台无关，本文虽然是在 Linux 上操作，但是还没有深入演示如何在Linux上使用，由于时间关系，暂时不写出来，不过有兴趣的读者可以看一下国外的文章： SQL Server 2016: Always Encrypted