title: MSSQL-最佳实践-使用非对称密钥实现列加密

author: 风移

摘要

上一篇月报，我们分享了SQL Server使用对称密钥实现列加密的方法。为了解决对称加密安全性低的问题，本期月报我们分享使用非对称密钥加密方式实现SQL Server列加密方法，保护用户的关键、核心隐私数据列信息。

场景引入

对称加密是指加密和解密过程使用同一个密钥的加密算法，而非对称加密方法加密和解密过程使用不同的密钥进行。因此，通常来说对称加密安全性较弱，非对象加密安全性相对较高。以下是关于对称加密和非对称加密的过程介绍。

对称加密过程

对称加密算法使用相同的密钥对数据进行加密，如下图所示：

对称加密数据整个流转过程包括：
数据发送者使用密钥将数据明文加密为密文
通过网络将数据密文和密钥发送给接受者
接受者使用相同密钥对密文进行解密，获取到最终的明文数据
从数据整个流转过程来看，很可能用于加密的密钥会被窃取，比如：
网络传输过程中，密钥很可能被窃取
窃取者有可能使用大数据分析手段，穷举出密文数据规律，分析出加密算法
因此，对称加密密钥存在被窃取的可能，安全性相对较弱。

非对称加密过程

与对称加密方式不同，非对称加密算法使用不同的密钥对数据进行加密和解密，用于加密的密钥叫公钥，用于解密的密钥叫私钥。因此安全性更高，如下图所示：

非对称加密整个数据流转的过程包括：
数据接受者首先生成公钥和私钥，然后将公钥发送给数据发送者（图中未画出）
数据发送者使用公钥对明文进行加密为密文
通过网络将密文发送给数据接受者
数据接受者获取到密文，使用私钥对数据进行解密，获取到最终明文数据
非对称加密整个数据流转过程中，私钥根本没有在网络上进行传递，因此不存在被窃取的可能性，安全性更高。

非对称密钥列加密

因此，本篇月报，在上一期月报MSSQL · 最佳实践 · 使用对称密钥实现列加密的基础上，使用非对称密钥方式实现SQL Server列加密技术。以下是使用非对称密钥实现SQL Server列加密的详细实现。

具体实现

在SQL Server 2005及以后版本，在支持对称密钥实现列加密的同时，也同样支持非对称密钥实现列加密，以下是使用非对称密钥加密用户手机号码的具体实现步骤以及详细过程。

创建测试数据库

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

--Step 1 - Create MSSQL sample database
USE master
GO
IF DB_ID('TestDb') IS NOT NULL
    DROP DATABASE [TestDb];
GO
CREATE DATABASE [TestDb];
GO

创建测试表

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

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

-- Init Table
INSERT INTO dbo.CustomerInfo 
VALUES ('CustomerA','13402872514')
,('CustomerB','13880674722')
,('CustomerC','13487759293')
GO

-- Verify data
SELECT * 
FROM dbo.CustomerInfo
GO

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

创建实例级别Master Key

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

-- Step 3 - Create SQL Server Service 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

创建数据库级别Master Key

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

-- Step 4 - Create MSSQL Database level 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

创建非对称密钥

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

-- Step 5 - Create MSSQL Symmetric Key
USE [TestDb]
GO
IF NOT EXISTS (SELECT * 
                FROM sys.asymmetric_keys 
                WHERE name = 'AsymKey_TestDb')
BEGIN
    CREATE ASYMMETRIC KEY AsymKey_TestDb 
    WITH ALGORITHM = RSA_512 
    ENCRYPTION BY PASSWORD = 'Password4@Asy'
    ;
END
GO

查看非对称密钥

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

USE [TestDb]
GO
SELECT *
FROM  sys.asymmetric_keys

结果展示如下：

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

修改表结构

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

-- Step 6 - Change your table structure
USE [TestDb]
GO 
ALTER TABLE CustomerInfo 
ADD EncryptedCustomerPhone varbinary(MAX) NULL
GO

新列数据初始化

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

-- Step 7 - init the encrypted data into the newly column
USE [TestDb]
GO 
UPDATE A
SET EncryptedCustomerPhone = ENCRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), CustomerPhone)
FROM dbo.CustomerInfo AS A;
GO
-- Double check the encrypted data of the new column
SELECT * FROM dbo.CustomerInfo

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

查看加密数据

手机号被加密为密文后，我们需要使用DecryptByAsymKey函数将其解密为明文，让我们尝试看看能否成功解密EncryptedCustomerPhone字段。

-- Step 8 - Reading the SQL Server Encrypted Data
USE [TestDb]
GO 

-- Now, it's time to list the original phone, encrypted phone and the descrypted phone.
SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo;
GO

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

添加新数据

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

-- Step 9 - What if we add new record to table.
USE [TestDb]
GO 

-- Performs the update of the record
INSERT INTO dbo.CustomerInfo (CustomerName, CustomerPhone, EncryptedCustomerPhone)
VALUES ('CustomerD', '13880975623', ENCRYPTBYASYMKEY( ASYMKEY_ID('AsymKey_TestDb'), '13880975623'));  
GO

更新数据手机号

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

-- Step 10 - So, what if we upadate the phone
USE [TestDb]
GO 
-- Performs the update of the record
UPDATE A
SET EncryptedCustomerPhone = ENCRYPTBYASYMKEY( ASYMKEY_ID('AsymKey_TestDb'), '13880971234')
FROM dbo.CustomerInfo AS A
WHERE CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy')) = '13880975623'
GO

删除手机号明文列

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

-- Step 11 - Remove old column
USE [TestDb]
GO 
ALTER TABLE CustomerInfo
DROP COLUMN CustomerPhone;
GO

SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO

结果展示如下：

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

添加新用户

模拟新添加一个用户EncryptedDbo:

-- Step 12 - Create a new user & access the encrypted data
USE [TestDb]
GO 
CREATE LOGIN EncryptedDbo
    WITH PASSWORD=N'EncryptedDbo@3*', CHECK_POLICY = OFF;
GO

CREATE USER EncryptedDbo FOR LOGIN EncryptedDbo;

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

新用户查询数据

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

-- Step 13 -- OPEN a new connection query window using the new user and query data 
USE [TestDb]
GO

SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO

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

为新用户赋权限

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

--Step 14 - Grant permissions to EncryptedDbo
USE [TestDb]
GO

GRANT VIEW DEFINITION ON 
    ASYMMETRIC KEY::[AsymKey_TestDb] TO [EncryptedDbo];
GO
GRANT CONTROL ON 
    ASYMMETRIC KEY::[AsymKey_TestDb] TO [EncryptedDbo];
GO

新用户再次查询

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

-- Step 13 -- OPEN a new connection query window using the new user and query data 
USE [TestDb]
GO

SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO

再次查询结果展示如下：

最后总结

本篇月报分享了对称加密和非对称加密的工作原理，以及如何利用SQL Server非对称密钥实现列加密的方法，来保护用户核心数据信息安全。

MSSQL-最佳实践-使用非对称密钥实现列加密