當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MSSQL · 最佳实践 · 使用混合密钥实现列加密

發(fā)布時間：2025/5/22 数据库 14 豆豆

生活随笔收集整理的這篇文章主要介紹了 MSSQL · 最佳实践 · 使用混合密钥实现列加密小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

摘要

在SQL Server安全系列專題的上兩期月報分享中，我們分別分享了：如何使用對稱密鑰實現(xiàn)SQL Server列加密技術(shù)和使用非對稱密鑰加密方式實現(xiàn)SQL Server列加密。本期月報我們分享使用混合密鑰加密方式實現(xiàn)SQL Server列加密技術(shù)，最大限度減少性能損失，最大程度保護(hù)用戶數(shù)據(jù)安全。

場景引入

對稱加密是指加密和解密過程使用同一個密鑰的加密算法，非對稱加密是指加密和解密過程使用不同的密鑰進(jìn)行的加密算法。因此，通常來說對稱加密安全性較弱，非對象加密安全性相對較高。凡事都具有兩面性，非對稱密鑰加密的安全性較好，但通常算法相比對稱密鑰復(fù)雜許多，因此會帶來性能上的損失也更大。有沒有一種方法既可以最大限度保證數(shù)據(jù)安全性，又能夠最大限度的減少性能損失呢？這便是本期月報分享的價值所在：SQL Server使用混合密鑰實現(xiàn)列加密技術(shù)。

具體實現(xiàn)

在SQL Server 2005及以后版本，在支持對稱密鑰實現(xiàn)列加密的同時，也同樣支持非對稱密鑰實現(xiàn)列加密，以下是使用混合密鑰加密用戶手機號碼的具體實現(xiàn)步驟以及詳細(xì)過程，以此最大限度滿足數(shù)據(jù)庫安全性和減少加密解密過程的性能損失。

創(chuàng)建測試數(shù)據(jù)庫

創(chuàng)建一個專門的測試數(shù)據(jù)庫，名為：TestDb。

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

創(chuàng)建測試表

在TestDb數(shù)據(jù)庫下，創(chuàng)建一張專門的測試表，名為：CustomerInfo。

--Step 2 - Create Test Table, init data & verify USE [TestDb] GO IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULLDROP 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

原始數(shù)據(jù)中，用戶的電話號碼為明文存儲，任何有權(quán)限查看表數(shù)據(jù)的用戶，都可以清楚明了的獲取到用戶的電話號碼信息，展示如下：

創(chuàng)建實例級別Master Key

在SQL Server數(shù)據(jù)庫實例級別創(chuàng)建Master Key（在Master數(shù)據(jù)庫下，使用CREATE MASTER KEY語句）：

-- Step 3 - Create SQL Server Service Master Key USE master; GO IF NOT EXISTS(SELECT *FROM sys.symmetric_keysWHERE name = '##MS_ServiceMasterKey##') BEGINCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MSSQLSerivceMasterKey' END; GO

創(chuàng)建數(shù)據(jù)庫級別Master Key

在用戶數(shù)據(jù)庫TestDb數(shù)據(jù)庫下，創(chuàng)建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

創(chuàng)建非對稱密鑰

在用戶數(shù)據(jù)庫下，創(chuàng)建非對稱密鑰，并使用密碼對非對稱密鑰進(jìn)行加密：

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

查看非對稱密鑰

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

USE [TestDb] GO SELECT * FROM sys.asymmetric_keys

結(jié)果展示如下：

當(dāng)然，您也可以用SSMS圖形界面來查看證書和非對稱密鑰對象，方法是在用戶數(shù)據(jù)庫下，打開Security => Certificates => Asymmetric Keys，如下圖所示：

創(chuàng)建對稱密鑰

使用非對稱密鑰AsymKey_TestDb來加密對稱密鑰SymKey_TestDb，然后使用這個對稱密鑰SymKey_TestDb來加密用戶數(shù)據(jù)。這樣既可以利用非對稱密鑰的安全性來保護(hù)對稱密鑰，又能兼顧對稱密鑰加密數(shù)據(jù)的高效性，兩全其美。這種使用非對稱密鑰加密對稱密鑰，然后使用對稱密鑰加密用戶敏感數(shù)據(jù)的方式，我且稱之為“混合密鑰”加密，這一步是本篇文章的關(guān)鍵點，也是很多人沒有關(guān)注到的點。

--Step 6 - Create Symmetric Key Encrypted by symmetic key USE [TestDb] GO IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SymKey_TestDb') BEGINCREATE SYMMETRIC KEY SymKey_TestDb WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY AsymKey_TestDb; -- Asymmetric Key; END GOUSE [TestDb] GO SELECT * FROM sys.symmetric_keys

對稱密鑰展示如下：

修改表結(jié)構(gòu)

接下來，我們需要修改表結(jié)構(gòu)，添加一個數(shù)據(jù)類型為varbinary(max)的新列，假設(shè)列名為EncryptedCustomerPhone ，用于存儲加密后的手機號碼密文。

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

新列數(shù)據(jù)初始化

新列添加完畢后，我們將表中歷史數(shù)據(jù)的用戶手機號CustomerPhone，加密為密文，并存儲在新字段EncryptedCustomerPhone中。方法是使用EncryptByKey函數(shù)加密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列的數(shù)據(jù)，已經(jīng)變成CustomerPhone對稱加密后的密文，如下展示：

查看加密數(shù)據(jù)

手機號被加密為密文后，我們需要使用DecryptByKey函數(shù)將其解密為明文（解密前，需要打開對稱密鑰），讓我們嘗試看看能否成功解密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

查詢語句執(zhí)行結(jié)果如下，CustomerPhone和DescryptedCustomerPhone字段數(shù)據(jù)內(nèi)容是一模一樣的，因此加密和解密成功。

添加新數(shù)據(jù)

歷史數(shù)據(jù)加密解密后的數(shù)據(jù)保持一致，然后，讓我們看看新添加的數(shù)據(jù)：

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

更新數(shù)據(jù)手機號

接下來，我們嘗試更新用戶手機號：

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

再次查看加密數(shù)據(jù)

將用戶手機號碼的明文列刪除后，我們再次查看解密用戶手機號碼明文列

--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.CustomerInfoCLOSE SYMMETRIC KEY SymKey_TestDb; GO

結(jié)果展示如下：

一切正常，歷史數(shù)據(jù)、新添加的數(shù)據(jù)、更新的數(shù)據(jù)，都可以工作完美。按理，文章到這里也就結(jié)束。但是有一個問題我們是需要搞清楚的，那就是：如果我們新創(chuàng)建了用戶，他能夠訪問這個表的數(shù)據(jù)嗎？以及我們?nèi)绾巫屝掠脩裟軌蛟L問該表的數(shù)據(jù)呢？

添加新用戶

模擬新添加一個用戶EncryptedDbo:

-- Step 14 - Create a new user & access the encrypted data USE [TestDb] GO IF EXISTS(SELECT TOP 1 *FROM sys.server_principalsWHERE name = 'EncryptedDbo' ) BEGINDROP LOGIN EncryptedDbo; END GOCREATE LOGIN EncryptedDboWITH PASSWORD=N'EncryptedDbo@3*', CHECK_POLICY = OFF;GOCREATE USER EncryptedDbo FOR LOGIN EncryptedDbo;GRANT SELECT ON OBJECT::dbo.CustomerInfo TO EncryptedDbo; GO

新用戶查詢數(shù)據(jù)

使用剛才創(chuàng)建的用戶，在SSMS中新打開一個新連接，查詢數(shù)據(jù)：

-- Step 15 -- OPEN a new connection query window using the new user and query data USE [TestDb] GOOPEN SYMMETRIC KEY SymKey_TestDb DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';SELECT *,DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone)) FROM dbo.CustomerInfoCLOSE SYMMETRIC KEY SymKey_TestDb; GO

新用戶也無法解密EncryptedCustomerPhone，解密后的DescryptedCustomerPhone 字段值為NULL，即新用戶無法查看到用戶手機號明文，避免了未知用戶獲取用戶手機號等核心數(shù)據(jù)信息。

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

為新用戶賦權(quán)限

新用戶沒有查看加密列數(shù)據(jù)的權(quán)限，如果需要賦予權(quán)限，這里需要授權(quán)對稱密鑰DEFINITION權(quán)限和非對稱密鑰CONTROL權(quán)限，方法如下：

--Step 16 - Grant permissions to EncryptedDbo USE [TestDb] GOGRANT VIEW DEFINITION ON SYMMETRIC KEY::[SymKey_TestDb] TO [EncryptedDbo]; GOGRANT CONTROL ON ASYMMETRIC KEY::[AsymKey_TestDb] TO [EncryptedDbo]; GO

新用戶再次查詢

賦權(quán)限完畢后，新用戶再次執(zhí)行“新用戶查詢數(shù)據(jù)”中的查詢語句，已經(jīng)可以正常獲取到加密列的明文數(shù)據(jù)了。

再次查詢結(jié)果展示如下：

最后總結(jié)

本篇月報分享了如何利用非對稱密鑰加密對稱密鑰，然后使用對稱密鑰加密用戶數(shù)據(jù)，即混合密鑰的方式實現(xiàn)SQL Server列加密技術(shù)，以此來最大限度保護(hù)用戶核心數(shù)據(jù)信息安全的同時，又最大限度降低了加密解密對的性能損失。

總結(jié)

以上是生活随笔為你收集整理的MSSQL · 最佳实践 · 使用混合密钥实现列加密的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

密钥
MSSQL

上一篇： Java网络方面
下一篇： lvs fullnat+ECMP【1】内