SQL Server 安全篇——安全元数据（4）——加密（Ecryption）元数据

本文属于SQL Server安全专题系列

     这里需要注意，加密不是动词，而是指关于加密功能的元数据。SQL Server作为成熟的数据库管理系统，必须有加密功能，而加密功能又需要有一定的元数据作为协助，不然对于用户或者DBA来说会很难使用。本文重点介绍在SQL Server 2016出现的Always Encrypted（始终加密）和2008开始出现的TDE功能的元数据。

Always Encrypted：

     简称AE。始终加密包含了两部分，列主密钥（column master keys）和列加密密钥（column encryption keys），两者是一对多的关系，因此，元数据也需要体现出这种关系。这两部分的元数据主要来自于：sys.column_encryption_keys、sys.column_encryption_key_values和sys.column_master_keys目录视图，它们组合成一个层次结构用来显示列主密钥和列加密密钥的关系。

     本文不是专门介绍AE的，所以这里不打算展开太多，下面我们快速来实操一下AE，首先我们要使用SQL Server 2016及以上版本，因为这个是2016才引入的功能。然后下载一个AdventureWorks2016做演示。按照图中的方式打开：

打开之后的样子：

在这里，输入列主密钥的名字和选择存储类型：

这里选择使用【Windows证书存储-当前用户】，然后点击左下角的【生成证书】，可以看到多了一行： 现在列主密钥已经创建完毕，接下来创建列加密密钥，按照上面的方式打开：

选择名字及对应的列主密钥：

点击确定之后，完成密钥的创建。接下来我们找个数据来加密一下。这里暂时不解释细节问题。

我们使用AdventureWorks2016中的Sales.CreditCard表加密信用卡号（CardNumber）、过期月份（ExpMonth）和过期年份（ExpYear）三列。

注意状态：在选择了【加密类型】后，cardnumber变成【警告】状态。因为它需要改变列的排序规则以便支持BIN2。而modifiedData由于是不支持AE的数据类型所以不能使用AE功能。

因为这里只是简单演示，所以不做详细截图。

下面的语句用来展开始终加密的信息：

SELECT t.NAME AS TableName
	,c.NAME AS ColumnName
	,c.encryption_type_desc
	,c.encryption_algorithm_name
	,cek.NAME AS ColumnEncryptionKeyName
	,cev.encrypted_value
	,cev.encryption_algorithm_name
	,cmk.NAME AS ColumnMasterKeyName
	,cmk.key_store_provider_name AS column_master_key_store_provider_name
	,cmk.key_path
FROM sys.columns c
INNER JOIN sys.column_encryption_keys cek ON c.column_encryption_key_id = cek.column_encryption_key_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.column_encryption_key_values cev ON cek.column_encryption_key_id = cev.column_encryption_key_id
JOIN sys.column_master_keys cmk ON cev.column_master_key_id = cmk.column_master_key_id;

    结果如下：虽然看起来没什么价值，但是一旦开启某些功能，那么对这些功能的状态收集就很有必要。

TDE元数据：

     在元数据层面，TDE跟AE是很像的。TDE有sys.databases、sys.certificates和sys.database_encryption_keys目录视图来综合表现。在sys.databases视图中包含了一列is_encrypted列，这列Bit类型中0代表没有用TDE加密，而1则表示已加密，注意这里是指TDE加密而不是其他加密。如果库已经使用了TDE加密，则关联sys.certificates获取加密密钥。密钥的信息又从sys.database_encryption_keys中展示。

 关于TDE的元数据有挺多使用场景，比如：

     1. 列出已使用TDE的数据库：

SELECT name 
FROM sys.databases 
WHERE is_encrypted = 1 ; 

     2. 检查证书是否已经备份：

SELECT 
     DB_NAME(dek.database_id) AS DatabaseName 
    ,c.name AS CertificateName 
FROM AdventureWorks2014.sys.dm_database_encryption_keys dek 
INNER JOIN master.sys.certificates c 
ON c.thumbprint = dek.encryptor_thumbprint 
WHERE c.pvt_key_last_backup_date IS NULL ; 

    3. 还有一个非常实用的需求——加密实例上N个数据库，此时就可以使用元数据来实现。这个脚本首先创建使用一个服务器证书用于加密每个数据库的数据库加密密钥（database encryption key），然后使用sp_msforeachdb来遍历每个数据库。在循环内部检查是否为数据库，是否已经进行了TDE加密：

USE  master 
GO 
CREATE CERTIFICATE TDECert WITH SUBJECT = 'My DEK Certificate'; 
GO 
EXEC sys.sp_MSforeachdb @command1 = 'USE ? 
IF (SELECT DB_ID()) > 4 
BEGIN 
    IF (SELECT is_encrypted FROM sys.databases WHERE database_id = DB_ID()) 
= 0 
        BEGIN 
        CREATE DATABASE ENCRYPTION KEY   
        WITH ALGORITHM = AES_128   
        ENCRYPTION BY SERVER CERTIFICATE TDECert 
        ALTER DATABASE ?   
        SET ENCRYPTION ON 
        END 
END' ; 

总结：

     本文只介绍了一些脚本，还有一些相关视图，内容很少，但是作者希望读者能看出“方法”、“思路”，然后根据实际情况进行改造，举一反三地扩展。

    移除AE的方法可以查看 “SQL Server 安全篇——SQL Server加密（2）——加密数据”