---

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非對稱金鑰實現列加密的方法，來保護使用者核心資料資訊保安。