MSSQL · 最佳實踐 · 使用非對稱秘鑰實作列加密

摘要

上一篇月報，我們分享了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非對稱秘鑰實作列加密的方法，來保護使用者核心資料資訊安全。