SQL SERVER敏感数据加密解决方案

# 背景

在数据库领域，数据是极其敏感和珍贵的，对客户的敏感数据进行加密，不仅是数据库设计的基本原则，更是 IT 服务行业的合规操作。因此，需要采取一些预防措施来帮助保护数据库的安全，如设计一个安全系统、加密机密资产以及在数据库服务器的周围构建防火墙。一种解决方案是加密数据库中的敏感数据，并通过证书保护用于加密数据的密钥，这可以防止任何没有密钥的人使用这些数据，这里将提供两种基于 MS SQL SERVER 数据库的加密方法。

# 透明数据加密 (Transparent Data Encryption)

# 实现原理

数据库文件的加密在页级别执行。已加密数据库中的页在写入磁盘之前会进行加密，在读入内存时进行解密，可对数据和日志文件执行实时的 I/O 加密和解密。数据库加密密钥存储在服务器的 master 数据库中，由证书文件保护的对称密钥，或者是由 EKM 模块保护的非对称密钥。主要用于防止数据库文件被未经授权地拷贝或服务器被盗后通过附加/还原等操作访问数据库中的敏感数据。

透明数据库加密体系结构

# 实现方法

  1. 创建主密钥
  2. 创建证书
  3. 创建数据库加密密钥
  4. 启用加密

# 实现步骤

# 1. 创建 Master Key

先判断是否存在 master key，如果存在请找出密码，否则先 drop 再创建，mypassword 为用户设置的密码。

USE master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='mypassword'

-- 查询验证：
SELECT
	*
FROM sys.symmetric_keys
WHERE name='##MS_DatabaseMasterKey##'

# 2. 创建数据库证书

创建受 master key 保护的证书，TDE_Cert 是证书的名字，START_DATE 和 EXPIRY_DATE 是证书的有效起止日期。

USE master
GO
IF NOT EXISTS(
	SELECT *
	FROM sys.certificates
	WHERE name = 'TDE_Cert'
)
BEGIN
	CREATE CERTIFICATE Cert_TDE
	AUTHORIZATION dbo
	WITH SUBJECT = 'Database_Encryption',
	START_DATE = '01/01/2021',
	EXPIRY_DATE = '12/30/9999'
END
GO

-- 查询验证：
SELECT
  *
FROM sys.certificates
WHERE name='TDE_Cert'

# 3. 创建数据库加密密钥

使用 USE 命令来切换到需要加密的数据库，然后把刚才创建的证书和加密数据库之间进行连接。 这里使用 AES_256 加密算法进行加密。

USE TestDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

# 4. 启用加密

这个过程需要耗费一定的时间才能完成，具体时间取决于数据库的大小。我们可以通过查询 sys.dm_database_encryption_keys DMV 来查看进度。

USE master
GO
ALTER DATABASE TestDB 
SET ENCRYPTION ON;
GO

# 5. 备份证书

将创建的证书保存到本地或者其他安全的位置，务必保证证书的可用。 如果数据库服务器出现故障，重置数据库或者迁移数据库，我们需要将备份的证书导入回回去。

-- 备份证书 
BACKUP CERTIFICATE TDE_Cert
TO FILE = 'D:\backup\TDE_Cert'
WITH PRIVATE KEY (file='D:\backup\TDE_Cert_Key.pvk',
ENCRYPTION BY PASSWORD='mypassword') 
GO

-- 备份主密钥  
BACKUP MASTER KEY   
TO FILE = 'D:\backup\MasterKey'     
ENCRYPTION BY PASSWORD = 'mypassword'  
GO

# 6. 验证结果

如果 encryption_state 的值为 3，则说明数据库已开启 TDE 加密。

SELECT
		d.name,DEK.encryption_state
FROM sys.dm_database_encryption_keys dek
  INNER JOIN sys.databases d
		ON dek.database_id=d.database_id

# 数据库列加密（Column Level Encryption）

# 实现原理

在数据库中单独对列进行操作，使用加密算法，将表中需要加密的字段从明文变成密文。

列加密

# 实现方法

此处使用对称秘钥对列进行加密，也可以使用其他的加密方法，步骤如下：

  1. 创建实例级别的 Master Key

  2. 创建数据库级别 Master Key

  3. 创建数据库级别证书

  4. 创建数据库级别对称秘钥

  5. 使用对称秘钥加密关键列数据

# 实现步骤

假设数据库名称为 TestDb，我们需要对 TestTable 表中的 CustomField 字段进行加密，以下是使用对称秘钥加密的具体实现步骤以及详细过程。

# 1.分析数据

查看数据表结构，以及 CustomField 列的数据存储类型，对需要加密列的数据有基本认知。

USE [TestDb]

SELECT * 
FROM dbo.TestTable
GO

# 2.创建实例级别 Master Key

在 master 数据库下，使用 CREATE MASTER KEY 语句：

USE master;
GO
IF NOT EXISTS(
	SELECT *
	FROM sys.symmetric_keys
	WHERE name = '##MS_ServiceMasterKey##')
BEGIN
	CREATE MASTER KEY ENCRYPTION BY 
	PASSWORD = 'MSSQLSerivceMasterKey'
END;
GO

# 3.创建数据库级别 Master Key

在用户数据库 TestDb 中创建 Master Key：

USE [TestDb]
GO
IF NOT EXISTS (SELECT * 
				FROM sys.symmetric_keys 
				WHERE name LIKE '%MS_DatabaseMasterKey%')
BEGIN		
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TestDbMasterKey@3*';
END
GO

# 4.创建数据库级别证书

在用户数据库 TestDb 下，创建证书，作为加密对称秘钥：

USE [TestDb]
GO
IF NOT EXISTS(
	SELECT *
	FROM sys.certificates
	WHERE name = 'Cert_TestDb'
)
BEGIN
	CREATE CERTIFICATE Cert_TestDb
	AUTHORIZATION dbo
	WITH SUBJECT = 'Cert_TestDb to protect Customfield',
	START_DATE = '01/01/2021',
	EXPIRY_DATE = '12/30/9999'
END
GO

然后将证书备份到本地：

USE [TestDb]
GO
BACKUP CERTIFICATE Cert_TestDb
TO FILE = 'D:\Backup\Cert_TestDb.cer';
GO

# 5.创建对称秘钥

在用户数据库 TestDb 下，创建对称秘钥，并使用证书对其进行加密：

USE [TestDb]
GO
IF NOT EXISTS (SELECT * 
				FROM sys.symmetric_keys 
				WHERE name = 'SymKey_TestDb')
BEGIN
	CREATE SYMMETRIC KEY SymKey_TestDb 
	WITH ALGORITHM = AES_256 
	ENCRYPTION BY CERTIFICATE Cert_TestDb
	;
END
GO

# 6.查看证书和对称秘钥

使用如下查询语句查看对称秘钥以及证书：

USE [TestDb]
GO
SELECT *
FROM  sys.symmetric_keys

SELECT *
FROM sys.certificates

# 7.修改表结构

通常情况下需要加密的列可能存在各种类型，所以我们需要修改表结构，添加一个数据类型为 varbinary(max) 的新列，假设列名为 EncryptedCustomField ，用于存储加密后的密文。

USE [TestDb]
GO 
ALTER TABLE CustomField 
ADD EncryptedCustomField varbinary(MAX) NULL
GO

# 8.新列数据初始化

新列添加完毕后，我们将表中 CustomField 列的数据，加密为密文，并存储在新字段 EncryptedCustomField 中。方法是打开对称秘钥，然后使用 EncryptByKey 函数加密，如下所示：

USE [TestDb]
GO 
-- Opens the symmetric key: SymKey_TestDb
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY CERTIFICATE Cert_TestDb;
GO
UPDATE A
SET EncryptedCustomField = EncryptByKey(Key_GUID('SymKey_TestDb'), CustomField)
FROM dbo.TestTable AS A;
GO
-- Closes the symmetric key: SymKey_TestDb
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

# 9.检验数据

查看表中 TestTable 表的数据，检查 EncryptedCustomField 是否为加密后的密文：

SELECT *, EncryptedCustomField FROM dbo.TestTable

# 10.变更列名

如果没有问题，我们可以将明文列 CustomField 删除，然后把加密列 EncryptedCustomField 的名称改为 CustomField

USE [TestDb]
GO 
ALTER TABLE TestTable
DROP COLUMN CustomField;
GO
exec sp_rename 'TestTable.EncryptedCustomField','CustomField','column';
GO

至此，针对数据表中的列加密已经完成。以下部分是如何对加密列的数据进行读写操作。

# 11.读写操作

以下是对加密列的增改查操作，基本操作为，首先打开对称秘钥，然后对加密列的数据进行加解密处理，最后关闭秘钥。可以在数据库中创建触发器进行添加和删除数据操作，避免对外部程序代码进行修改。但是需要注意的是，数据查询不存在触发器，所以查询语句仍需要修改代码。

# 11.1 查询加密数据

使用 DecryptByKey 函数将其解密为明文，NVARCHAR(255) 为加密字段原来的数据类型，DescryptedCustomField 为解密后的字段名称。

USE [TestDb]
GO 
-- Opens the symmetric key: SymKey_TestDb
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY CERTIFICATE Cert_TestDb;
GO

SELECT 
	*,
	DescryptedCustomField = CONVERT(NVARCHAR(255), DecryptByKey(CustomField))
FROM dbo.TestTable;
 
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO
# 11.2 添加新数据 
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY CERTIFICATE Cert_TestDb;
GO
-- Performs the update of the record
INSERT INTO dbo.TestTable (CustomField)
VALUES (EncryptByKey( Key_GUID('SymKey_TestDb'), 'new content'));  

-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO
# 11.3 更新数据 
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY CERTIFICATE Cert_TestDb;

UPDATE A
SET CustomField = EncryptByKey( Key_GUID('SymKey_TestDb'), 'updated content')
FROM dbo.TestTable AS A
WHERE CONVERT(NVARCHAR(255),DecryptByKey(CustomField)) = 'content'

-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

# 总结

以上两种加密方法已在 Azure Sql 数据库中验证，可以根据实际的需求选择加密方法。

TDE 加密在页级别进行，是对数据文件和日志文件的加密，而列加密在列级别进行，是对数据内容的加密。

TDE 透明地在数据库中进行加密，不会影响现有的外部应用，而列加密需要对外部应用进行修改，虽然可以使用数据库触发器，但是查询方法仍然需要修改外部应用，如果需要加密的字段过多，比较费时费力。

从数据库性能的角度来看，列加密在数据读写中不会产生额外的开销，而 TDE 有一个加解密过程，对数据库性能存在一定的影响。

从数据库的文件大小来看，列加密对字段加密后，增加了字段的长度，数据文件变大，而 TDE 不会增加已加密数据库文件的大小。其次还有一些跟索引相关的区别暂未详细研究。

TDE 只加密当前的数据库，如果数据库存在备份或者镜像，需要创建多个加密密钥和证书，对于多台数据库的管理和维护增加了难度。其次，TDE 无法针对不同的数据库用户单独分配权限。

# 参考资料

  1. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
  2. https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966395(v=technet.10)?redirectedfrom=MSDN
Database
Licensed under CC BY-NC-SA 4.0
© 2008 - 2024 Coding Journey
使用 Hugo 构建
主题 Stack 由 Jimmy 设计