摘要

从SQL Server 2005开始引入了列加密技术，实现一些关键、核心的隐私数据列信息加密。本期月报是我们分享SQL Server安全系列专题的开篇：如何使用对称秘钥实现SQL Server列加密技术。

场景引入

在平日的生活中，我相信大家或多或少都经历过广告、推销、诈骗电话的骚扰，不厌其烦，比如：“喂，您XXX的房子要不要考虑出售？XXX的楼盘您要不要考虑购买？”。

往往这个时候，我们会想，我的电话，手机号码都是被谁，哪些途径泄露出去呢？到底有没有技术手段来尽量或最大限度的减少手机号码的泄露呢？答案是肯定的，那就让我们一起来看看在SQL Server数据库中如何实现类似于手机号、身份证号、驾照号等关键信息如何被加密存储。

原理分析

为了避免枯燥的原理性解释，我们用一句话、一张图、以及简单的实现方法，三个方面来介绍SQL Server数据库列加密技术。

一句话解释

一句话解释SQL Server数据库的列加密技术：是使用加密算法，将表中关键字段信息列从明文变成密文，使得用户信息可以被严格保护，不被轻易获取的技术。

一张图解释

从下面这张图，我们可以清楚的看到SQL Server数据库中的关键信息列是如何被加密的：

01.png

图片来自微软官方网站

实现方法

要实现SQL Server数据库列级别加密（本例中使用对称秘钥进行列加密方法，当然还有其他方法），我们需要：

创建实例级别的Master Key

创建数据库级别Master Key

创建数据库级别证书

创建数据库级别对称秘钥

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

具体实现

以下是使用对称秘钥加密用户手机号码的具体实现步骤以及详细过程。

创建测试数据库

创建一个专门的测试数据库，名为：TestDb。


1. --Step 1 - Create MSSQL sample database
2. USE master
3. GO
4. IF DB_ID('TestDb') IS NULL
5. CREATE DATABASE [TestDb];
6. GO

创建测试表

在TestDb数据库下，创建一张专门的测试表，名为：CustomerInfo。


1. --Step 2 - Create Test Table, init data & verify
2. USE [TestDb]
3. GO
4. IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
5. DROP TABLE dbo.CustomerInfo
6. CREATE TABLE dbo.CustomerInfo
7. (
8. CustomerId        INT IDENTITY(10000,1)    NOT NULL PRIMARY KEY,
9. CustomerName    VARCHAR(100)            NOT NULL,
10. CustomerPhone    CHAR(11)                NOT NULL
11. );

13. -- Init Table
14. INSERT INTO dbo.CustomerInfo
15. VALUES ('CustomerA','13402872514')
16. ,('CustomerB','13880674722')
17. ,('CustomerC','13487759293')
18. GO

20. -- Verify data
21. SELECT *
22. FROM dbo.CustomerInfo
23. GO

原始数据中，用户的电话号码为明文存储，任何有权限查看表数据的用户，都可以清楚明了的获取到用户的电话号码信息，展示如下：

02.png

创建实例级别Master Key

在SQL Server数据库实例级别创建Master Key（在Master数据库下，使用CREATE MASTER KEY语句）：


1. -- Step 3 - Create SQL Server Service Master Key
2. USE master;
3. GO
4. IF NOT EXISTS(
5. SELECT *
6. FROM sys.symmetric_keys
7. WHERE name = '##MS_ServiceMasterKey##')
8. BEGIN
9. CREATE MASTER KEY ENCRYPTION BY
10. PASSWORD = 'MSSQLSerivceMasterKey'
11. END;
12. GO

创建数据库级别Master Key

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


1. -- Step 4 - Create MSSQL Database level master key
2. USE [TestDb]
3. GO
4. IF NOT EXISTS (SELECT *
5. FROM sys.symmetric_keys
6. WHERE name LIKE '%MS_DatabaseMasterKey%')
7. BEGIN
8. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TestDbMasterKey@3*';
9. END
10. GO

创建数据库级别证书

在测试数据库TestDb下，创建证书，用于加密对称秘钥：


1. -- Step 5 - Create a Self Signed MSSQL Certificate:
2. USE [TestDb]
3. GO
4. IF NOT EXISTS(
5. SELECT *
6. FROM sys.certificates
7. WHERE name = 'Cert_TestDb'
8. )
9. BEGIN
10. CREATE CERTIFICATE Cert_TestDb
11. AUTHORIZATION dbo
12. WITH SUBJECT = 'Cert_TestDb to protect my phone',
13. START_DATE = '08/10/2018',
14. EXPIRY_DATE = '12/30/9999'
15. END
16. GO

我们强烈建议您备份您的证书到本地：


1. -- It's better to BACKUP your certificate to local disk.
2. USE [TestDb]
3. GO
4. BACKUP CERTIFICATE Cert_TestDb
5. TO FILE = 'C:\Temp\Cert_TestDb.cer';
6. GO

创建对称秘钥

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


1. -- Step 6 - Create MSSQL Symmetric Key
2. USE [TestDb]
3. GO
4. IF NOT EXISTS (SELECT *
5. FROM sys.symmetric_keys
6. WHERE name = 'SymKey_TestDb')
7. BEGIN
8. CREATE SYMMETRIC KEY SymKey_TestDb
9. WITH ALGORITHM = AES_256
10. ENCRYPTION BY CERTIFICATE Cert_TestDb
11. ;
12. END
13. GO

查看证书和对称秘钥

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


1. USE [TestDb]
2. GO
3. SELECT *
4. FROM  sys.symmetric_keys

6. SELECT *
7. FROM sys.certificates

结果展示如下： 03.png

当然，您也可以用SSMS图形界面来查看证书和对称秘钥对象，方法是在用户数据库下，打开Security => Certificates => Symmetric Keys，如下图所示：

04.png

修改表结构

接下来，我们需要修改表结构，添加一个数据类型为varbinary(max)的新列，假设列名为EncryptedCustomerPhone ，用于存储加密后的手机号码密文。


1. -- Step 7 - Change your table structure
2. USE [TestDb]
3. GO
4. ALTER TABLE CustomerInfo
5. ADD EncryptedCustomerPhone varbinary(MAX) NULL
6. GO

新列数据初始化

新列添加完毕后，我们将表中历史数据的用户手机号CustomerPhone，加密为密文，并存储在新字段EncryptedCustomerPhone中。方法是打开对称秘钥，然后使用EncryptByKey函数加密CustomerPhone列，如下语句所示：


1. -- Step 8 - init the encrypted data into the newly column
2. USE [TestDb]
3. GO
4. -- Opens the symmetric key: SymKey_TestDb
5. OPEN SYMMETRIC KEY SymKey_TestDb
6. DECRYPTION BY CERTIFICATE Cert_TestDb;
7. GO
8. UPDATE A
9. SET EncryptedCustomerPhone = EncryptByKey (Key_GUID('SymKey_TestDb'), CustomerPhone)
10. FROM dbo.CustomerInfo AS A;
11. GO
12. -- Closes the symmetric key: SymKey_TestDb
13. CLOSE SYMMETRIC KEY SymKey_TestDb;
14. GO
15. -- Double check the encrypted data of the new column
16. SELECT * FROM dbo.CustomerInfo

查看表中EncryptedCustomerPhone列的数据，已经变成CustomerPhone加密后的密文，如下展示：05.png

查看加密数据

手机号被加密为密文后，我们需要使用DecryptByKey函数将其解密为明文（解密前，需要打开对称秘钥），让我们尝试看看能否成功解密EncryptedCustomerPhone字段。


1. -- Step 9 - Reading the SQL Server Encrypted Data
2. USE [TestDb]
3. GO
4. -- Opens the symmetric key: SymKey_TestDb
5. OPEN SYMMETRIC KEY SymKey_TestDb
6. DECRYPTION BY CERTIFICATE Cert_TestDb;
7. GO

9. -- Now, it's time to list the original phone, encrypted phone and the descrypted phone.
10. SELECT
11. *,
12. DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
13. FROM dbo.CustomerInfo;

15. -- Close the symmetric key
16. CLOSE SYMMETRIC KEY SymKey_TestDb;
17. GO

查询语句执行结果如下，CustomerPhone和DescryptedCustomerPhone字段数据内容是一模一样的，因此加密和解密成功。 06.png

添加新数据

历史数据加密解密后的数据保持一致，然后，让我们看看新添加的数据：


1. -- Step 10 - What if we add new record to table.
2. USE [TestDb]
3. GO
4. OPEN SYMMETRIC KEY SymKey_TestDb
5. DECRYPTION BY CERTIFICATE Cert_TestDb;
6. GO
7. -- Performs the update of the record
8. INSERT INTO dbo.CustomerInfo (CustomerName, CustomerPhone, EncryptedCustomerPhone)
9. VALUES ('CustomerD', '13880975623', EncryptByKey( Key_GUID('SymKey_TestDb'), '13880975623'));

11. -- Close the symmetric key
12. CLOSE SYMMETRIC KEY SymKey_TestDb;
13. GO

更新数据手机号

接下来，我们尝试更新用户手机号：


1. -- Step 11 - So, what if we upadate the phone
2. USE [TestDb]
3. GO
4. OPEN SYMMETRIC KEY SymKey_TestDb
5. DECRYPTION BY CERTIFICATE Cert_TestDb;

7. -- Performs the update of the record
8. UPDATE A
9. SET EncryptedCustomerPhone = EncryptByKey( Key_GUID('SymKey_TestDb'), '13880971234')
10. FROM dbo.CustomerInfo AS A
11. WHERE CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone)) = '13880975623'

13. -- Close the symmetric key
14. CLOSE SYMMETRIC KEY SymKey_TestDb;
15. GO

删除手机号明文列

一切没有问题，我们可以将用户手机号明文列CustomerPhone删除：


1. -- Step 12 - Remove old column
2. USE [TestDb]
3. GO
4. ALTER TABLE CustomerInfo
5. DROP COLUMN CustomerPhone;
6. GO

再次检查数据

再次尝试解密密文字段数据：


1. --Step 13 - verify again
2. USE [TestDb]
3. GO
4. OPEN SYMMETRIC KEY SymKey_TestDb
5. DECRYPTION BY CERTIFICATE Cert_TestDb;

7. SELECT
8. *,
9. DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
10. FROM dbo.CustomerInfo

12. CLOSE SYMMETRIC KEY SymKey_TestDb;
13. GO

结果展示如下： 07.png

一切正常，历史数据、新添加的数据、更新的数据，都可以工作完美。按理，文章到这里也就结束。但是有一个问题我们是需要搞清楚的，那就是：如果我们新创建了用户，他能够访问这个表的数据吗？以及我们如何让新用户能够访问该表的数据呢？

添加新用户

模拟新添加一个用户EncryptedDbo:


1. -- Step 14 - Create a new user & access the encrypted data
2. USE [TestDb]
3. GO
4. CREATE LOGIN EncryptedDbo
5. WITH PASSWORD=N'EncryptedDbo@3*', CHECK_POLICY = OFF;
6. CREATE USER EncryptedDbo FOR LOGIN EncryptedDbo;

8. GRANT SELECT ON OBJECT::dbo.CustomerInfo TO EncryptedDbo;
9. GO

新用户查询数据

使用刚才创建的用户，在SSMS中新打开一个连接，查询数据：


1. -- Step 15 -- OPEN a new connection query window using the new user and query data
2. USE [TestDb]
3. GO

5. OPEN SYMMETRIC KEY SymKey_TestDb
6. DECRYPTION BY CERTIFICATE Cert_TestDb;

8. SELECT
9. *,
10. DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
11. FROM dbo.CustomerInfo

13. CLOSE SYMMETRIC KEY SymKey_TestDb;
14. GO

首先，新用户无法OPEN SYMMETRIC KEY和CLOSE SYMMETRIC KEY，会报告如下异常： 08.png

其次，新用户也无法解密EncryptedCustomerPhone，解密后的DescryptedCustomerPhone 字段值为NULL，即新用户无法查看到用户手机号明文，避免了未知用户获取用户手机号等核心数据信息。 09.png

为新用户赋权限

新用户没有查看加密列数据的权限，如果需要赋予权限，方法如下：


1. --Step 16 - Grant permissions to EncryptedDbo
2. USE [TestDb]
3. GO

5. GRANT VIEW DEFINITION ON
6. SYMMETRIC KEY::[SymKey_TestDb] TO [EncryptedDbo];

8. GRANT VIEW DEFINITION ON
9. CERTIFICATE::[Cert_TestDb] TO EncryptedDbo;
10. GO
11. GRANT CONTROL ON
12. CERTIFICATE::[Cert_TestDb] TO [EncryptedDbo];
13. GO

新用户再次查询

赋权限完毕后，新用户再次执行“新用户查询数据”中的查询语句，已经可以正常获取到加密列的明文数据了。展示如下： 10.png

最后总结

本文做为SQL Server安全系列文章开篇，介绍了如何使用对称秘钥实现列加密的详细步骤和过程，实现关键信息列加密，保护用户核心信息安全。

原文：http://mysql.taobao.org/monthly/2018/08/03/