MSSQL · 最佳實踐 · 使用混合密鑰實作列加密

摘要

在SQL Server安全系列專題的上兩期月報分享中，我們分别分享了：如何使用對稱密鑰實作SQL Server列加密技術和使用非對稱密鑰加密方式實作SQL Server列加密。本期月報我們分享使用混合密鑰加密方式實作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 Asymmetric 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

檢視非對稱密鑰

您可以使用如下查詢語句檢視非對稱密鑰：

USE [TestDb]
GO
SELECT *
FROM  sys.asymmetric_keys

結果展示如下：

當然，您也可以用SSMS圖形界面來檢視證書和非對稱密鑰對象，方法是在使用者資料庫下，打開Security => Certificates => Asymmetric Keys，如下圖所示：

建立對稱密鑰

使用非對稱密鑰AsymKey_TestDb來加密對稱密鑰SymKey_TestDb，然後使用這個對稱密鑰SymKey_TestDb來加密使用者資料。這樣既可以利用非對稱密鑰的安全性來保護對稱密鑰，又能兼顧對稱密鑰加密資料的高效性，兩全其美。這種使用非對稱密鑰加密對稱密鑰，然後使用對稱密鑰加密使用者敏感資料的方式，我且稱之為“混合密鑰”加密，這一步是本篇文章的關鍵點，也是很多人沒有關注到的點。

--Step 6 - Create Symmetric Key Encrypted by symmetic key
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 ASYMMETRIC KEY AsymKey_TestDb;  -- Asymmetric Key
	;
END
GO


USE [TestDb]
GO
SELECT *
FROM  sys.symmetric_keys

對稱密鑰展示如下：

修改表結構

接下來，我們需要修改表結構，添加一個資料類型為varbinary(max)的新列，假設列名為EncryptedCustomerPhone ，用于存儲加密後的手機号碼密文。

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

新列資料初始化

新列添加完畢後，我們将表中曆史資料的使用者手機号CustomerPhone，加密為密文，并存儲在新字段EncryptedCustomerPhone中。方法是使用EncryptByKey函數加密CustomerPhone列，如下語句所示：

-- Step 8 - init the encrypted data into the newly column
USE [TestDb]
GO 
-- Opens the symmetric key: SymKey_TestDb
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
GO
UPDATE A
SET EncryptedCustomerPhone = EncryptByKey (Key_GUID('SymKey_TestDb'), CustomerPhone)
FROM dbo.CustomerInfo AS A;
GO
-- Closes the symmetric key: SymKey_TestDb
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO
-- Double check the encrypted data of the new column
SELECT * FROM dbo.CustomerInfo

檢視表中EncryptedCustomerPhone列的資料，已經變成CustomerPhone對稱加密後的密文，如下展示：

檢視加密資料

手機号被加密為密文後，我們需要使用DecryptByKey函數将其解密為明文（解密前，需要打開對稱密鑰），讓我們嘗試看看能否成功解密EncryptedCustomerPhone字段。

-- Step 9 - Reading the SQL Server Encrypted Data
USE [TestDb]
GO 
-- Opens the symmetric key: SymKey_TestDb
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
GO

-- Now, it's time to list the original phone, encrypted phone and the descrypted phone.
SELECT 
	*,
	DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
FROM dbo.CustomerInfo;
 
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

查詢語句執行結果如下，CustomerPhone和DescryptedCustomerPhone字段資料内容是一模一樣的，是以加密和解密成功。

添加新資料

曆史資料加密解密後的資料保持一緻，然後，讓我們看看新添加的資料：

-- Step 10 - What if we add new record to table.
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
GO
-- Performs the update of the record
INSERT INTO dbo.CustomerInfo (CustomerName, CustomerPhone, EncryptedCustomerPhone)
VALUES ('CustomerD', '13880975623', EncryptByKey( Key_GUID('SymKey_TestDb'), '13880975623'));  

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

更新資料手機号

接下來，我們嘗試更新使用者手機号：

-- Step 11 - So, what if we upadate the phone
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

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

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

删除手機号明文列

一切沒有問題，我們可以将使用者手機号明文列CustomerPhone删除：

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

再次檢視加密資料

将使用者手機号碼的明文列删除後，我們再次檢視解密使用者手機号碼明文列

--Step 13 - verify again
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

SELECT 
	*,
	DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
FROM dbo.CustomerInfo
 
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

一切正常，曆史資料、新添加的資料、更新的資料，都可以工作完美。按理，文章到這裡也就結束。但是有一個問題我們是需要搞清楚的，那就是：如果我們新建立了使用者，他能夠通路這個表的資料嗎？以及我們如何讓新使用者能夠通路該表的資料呢？

添加新使用者

模拟新添加一個使用者EncryptedDbo:

-- Step 14 - Create a new user & access the encrypted data
USE [TestDb]
GO
IF EXISTS(
	SELECT TOP 1 *
	FROM sys.server_principals
	WHERE name = 'EncryptedDbo'
)
BEGIN
	DROP LOGIN EncryptedDbo;
END
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 15 -- OPEN a new connection query window using the new user and query data 
USE [TestDb]
GO

OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

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

CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

新使用者也無法解密EncryptedCustomerPhone，解密後的DescryptedCustomerPhone 字段值為NULL，即新使用者無法檢視到使用者手機号明文，避免了未知使用者擷取使用者手機号等核心資料資訊。

而且，還會因為權限的問題，OPEN SYMMETRIC KEY和CLOSE SYMMETRIC KEY報錯，可以在Messages視窗中看到：

為新使用者賦權限

新使用者沒有檢視加密列資料的權限，如果需要賦予權限，這裡需要授權對稱密鑰DEFINITION權限和非對稱密鑰CONTROL權限，方法如下：

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

GRANT VIEW DEFINITION ON 
	SYMMETRIC KEY::[SymKey_TestDb] TO [EncryptedDbo];
GO

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

新使用者再次查詢

賦權限完畢後，新使用者再次執行“新使用者查詢資料”中的查詢語句，已經可以正常擷取到加密列的明文資料了。

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

OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

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

CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

再次查詢結果展示如下：

最後總結

本篇月報分享了如何利用非對稱密鑰加密對稱密鑰，然後使用對稱密鑰加密使用者資料，即混合密鑰的方式實作SQL Server列加密技術，以此來最大限度保護使用者核心資料資訊安全的同時，又最大限度降低了加密解密對的性能損失。

MSSQL · 最佳實踐 · 使用混合密鑰實作列加密

摘要

場景引入

具體實作

建立測試資料庫

建立測試表

建立執行個體級别Master Key

建立資料庫級别Master Key

建立非對稱密鑰

檢視非對稱密鑰

建立對稱密鑰

修改表結構

新列資料初始化

檢視加密資料

添加新資料

更新資料手機号

删除手機号明文列

再次檢視加密資料

添加新使用者

新使用者查詢資料

為新使用者賦權限

新使用者再次查詢

最後總結

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述