摘要

在SQL Server安全系列专题的上两期月报分享中,我们分别分享了:如何使用对称密钥实现SQL Server列加密技术和使用非对称密钥加密方式实现SQL Server列加密。本期月报我们分享使用混合密钥加密方式实现SQL Server列加密技术,最大限度减少性能损失,最大程度保护用户数据安全。

场景引入

对称加密是指加密和解密过程使用同一个密钥的加密算法,非对称加密是指加密和解密过程使用不同的密钥进行的加密算法。因此,通常来说对称加密安全性较弱,非对象加密安全性相对较高。凡事都具有两面性,非对称密钥加密的安全性较好,但通常算法相比对称密钥复杂许多,因此会带来性能上的损失也更大。有没有一种方法既可以最大限度保证数据安全性,又能够最大限度的减少性能损失呢?这便是本期月报分享的价值所在:SQL Server使用混合密钥实现列加密技术。

具体实现

在SQL Server 2005及以后版本,在支持对称密钥实现列加密的同时,也同样支持非对称密钥实现列加密,以下是使用混合密钥加密用户手机号码的具体实现步骤以及详细过程,以此最大限度满足数据库安全性和减少加密解密过程的性能损失。

创建测试数据库

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

  1. --Step 1 - Create MSSQL sample database
  2. USE master
  3. GO
  4. IF DB_ID('TestDb') IS NOT NULL
  5. DROP DATABASE [TestDb];
  6. GO
  7. CREATE DATABASE [TestDb];
  8. 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. );
  12. -- Init Table
  13. INSERT INTO dbo.CustomerInfo
  14. VALUES ('CustomerA','13402872514')
  15. ,('CustomerB','13880674722')
  16. ,('CustomerC','13487759293')
  17. GO
  18. -- Verify data
  19. SELECT *
  20. FROM dbo.CustomerInfo
  21. GO

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

01.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

创建非对称密钥

在用户数据库下,创建非对称密钥,并使用密码对非对称密钥进行加密:

  1. -- Step 5 - Create MSSQL Asymmetric Key
  2. USE [TestDb]
  3. GO
  4. IF NOT EXISTS (SELECT *
  5. FROM sys.asymmetric_keys
  6. WHERE name = 'AsymKey_TestDb')
  7. BEGIN
  8. CREATE ASYMMETRIC KEY AsymKey_TestDb
  9. WITH ALGORITHM = RSA_512
  10. ENCRYPTION BY PASSWORD = 'Password4@Asy'
  11. ;
  12. END
  13. GO
  14. USE [TestDb]
  15. GO
  16. SELECT *
  17. FROM sys.asymmetric_keys

查看非对称密钥

您可以使用如下查询语句查看非对称密钥:

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

结果展示如下:

02.png

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

03.png

创建对称密钥

使用非对称密钥AsymKey_TestDb来加密对称密钥SymKey_TestDb,然后使用这个对称密钥SymKey_TestDb来加密用户数据。这样既可以利用非对称密钥的安全性来保护对称密钥,又能兼顾对称密钥加密数据的高效性,两全其美。这种使用非对称密钥加密对称密钥,然后使用对称密钥加密用户敏感数据的方式,我且称之为“混合密钥”加密,这一步是本篇文章的关键点,也是很多人没有关注到的点。

  1. --Step 6 - Create Symmetric Key Encrypted by symmetic 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 ASYMMETRIC KEY AsymKey_TestDb; -- Asymmetric Key
  11. ;
  12. END
  13. GO
  14. USE [TestDb]
  15. GO
  16. SELECT *
  17. FROM sys.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 ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
  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 ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
  7. GO
  8. -- Now, it's time to list the original phone, encrypted phone and the descrypted phone.
  9. SELECT
  10. *,
  11. DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
  12. FROM dbo.CustomerInfo;
  13. -- Close the symmetric key
  14. CLOSE SYMMETRIC KEY SymKey_TestDb;
  15. 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 ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
  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'));
  10. -- Close the symmetric key
  11. CLOSE SYMMETRIC KEY SymKey_TestDb;
  12. 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 ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
  6. -- Performs the update of the record
  7. UPDATE A
  8. SET EncryptedCustomerPhone = EncryptByKey( Key_GUID('SymKey_TestDb'), '13880971234')
  9. FROM dbo.CustomerInfo AS A
  10. WHERE CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone)) = '13880975623'
  11. -- Close the symmetric key
  12. CLOSE SYMMETRIC KEY SymKey_TestDb;
  13. 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 ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
  6. SELECT
  7. *,
  8. DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
  9. FROM dbo.CustomerInfo
  10. CLOSE SYMMETRIC KEY SymKey_TestDb;
  11. GO

结果展示如下: 07.png

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

添加新用户

模拟新添加一个用户EncryptedDbo:

  1. -- Step 14 - Create a new user & access the encrypted data
  2. USE [TestDb]
  3. GO
  4. IF EXISTS(
  5. SELECT TOP 1 *
  6. FROM sys.server_principals
  7. WHERE name = 'EncryptedDbo'
  8. )
  9. BEGIN
  10. DROP LOGIN EncryptedDbo;
  11. END
  12. GO
  13. CREATE LOGIN EncryptedDbo
  14. WITH PASSWORD=N'EncryptedDbo@3*', CHECK_POLICY = OFF;
  15. GO
  16. CREATE USER EncryptedDbo FOR LOGIN EncryptedDbo;
  17. GRANT SELECT ON OBJECT::dbo.CustomerInfo TO EncryptedDbo;
  18. GO

新用户查询数据

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

  1. -- Step 15 -- OPEN a new connection query window using the new user and query data
  2. USE [TestDb]
  3. GO
  4. OPEN SYMMETRIC KEY SymKey_TestDb
  5. DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
  6. SELECT
  7. *,
  8. DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
  9. FROM dbo.CustomerInfo
  10. CLOSE SYMMETRIC KEY SymKey_TestDb;
  11. GO

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

而且,还会因为权限的问题,OPEN SYMMETRIC KEY和CLOSE SYMMETRIC KEY报错,可以在Messages窗口中看到: 09.png

为新用户赋权限

新用户没有查看加密列数据的权限,如果需要赋予权限,这里需要授权对称密钥DEFINITION权限和非对称密钥CONTROL权限,方法如下:

  1. --Step 16 - Grant permissions to EncryptedDbo
  2. USE [TestDb]
  3. GO
  4. GRANT VIEW DEFINITION ON
  5. SYMMETRIC KEY::[SymKey_TestDb] TO [EncryptedDbo];
  6. GO
  7. GRANT CONTROL ON
  8. ASYMMETRIC KEY::[AsymKey_TestDb] TO [EncryptedDbo];
  9. GO

新用户再次查询

赋权限完毕后,新用户再次执行“新用户查询数据”中的查询语句,已经可以正常获取到加密列的明文数据了。

  1. -- Step 15 -- OPEN a new connection query window using the new user and query data
  2. USE [TestDb]
  3. GO
  4. OPEN SYMMETRIC KEY SymKey_TestDb
  5. DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
  6. SELECT
  7. *,
  8. DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
  9. FROM dbo.CustomerInfo
  10. CLOSE SYMMETRIC KEY SymKey_TestDb;
  11. GO

再次查询结果展示如下: 10.png

最后总结

本篇月报分享了如何利用非对称密钥加密对称密钥,然后使用对称密钥加密用户数据,即混合密钥的方式实现SQL Server列加密技术,以此来最大限度保护用户核心数据信息安全的同时,又最大限度降低了加密解密对的性能损失。