在 SQL Server中管理欄位層級的加密

by adonisy 21. 九月 2020 12:24

管理欄位層級的加密

作 者:楊先民

出刊日期: 2020/09/21

 

欄位層級的加密讓你可以使用對稱式金鑰,非對稱密鑰,證書或密碼來加密單個資料列,甚至可以加密該列中的特定單元。

儘管這可以為您的資料提供額外的安全保護,但它也可能導致嚴重的性能影響和大量的膨脹。

膨脹意味著加密後的資料大小比以前大得多。此外,實現欄位層級的加密是一個手動過程,需要你對應用程序進行程式碼更改。

因此,加密資料不應成為您的預設設定,並且僅應在你有監管要求或明確的業務理由時才進行加密。


儘管通常使用對稱式金鑰對資料進行加密,但也可以使用非對稱式金鑰,證書甚至密碼對資料進行加密。如果您使用密碼對資料進行加密,

那麼將使用TRIPLE DES算法對資料進行加密。下面列出了可使用這些方法來加密或解密資料的加密功能。

 

當我們在資料庫中創建 SensitiveData表時,您可能已經留意到,當顯而易見的選擇是 CHAR(19)時,我們將VARBINARY(8000)資料類型

用於CreditCardNumber列。這是因為加密的資料必須儲存為二進位資料類型之一。我們將長度設置為8000字節,因為這是從用於加密資料的函數

回傳資料的最大長度。


下面程式將建立 Chapter11Encrypted資料庫的副本。然後,該程式為此資料庫創建一個資料庫主要金鑰和一個證書。之後,它將建立一個對稱式金鑰,

該對稱式金鑰將使用證書進行加密。


最後,它打開對稱式金鑰,並使用它對我們的 SensitiveData表中的 CreditCardNumber列進行加密。


--Create the duplicate Database 

CREATE DATABASE Chapter11CellEncrypted ; GO 

USE Chapter11CellEncrypted GO 


--Create the table 


CREATE TABLE dbo.SensitiveData 

(ID INT PRIMARY KEY IDENTITY, FirstName NVARCHAR(30), 

LastName NVARCHAR(30), CreditCardNumber VARBINARY(8000) ) 

GO 


--Populate the table SET identity_insert dbo.SensitiveData ON


INSERT INTO dbo.SensitiveData(id, firstname, lastname, CreditCardNumber) 

SELECT id ,firstname ,lastname ,CreditCardNumber FROM Chapter11Encrypted.dbo.SensitiveData 

SET identity_insert dbo.SensitiveData OFF 


--Create Database Master Key 


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO 


--Create Certificate 


CREATE CERTIFICATE CreditCardCert WITH SUBJECT = 'Credit Card Numbers'; GO 


--Create Symmetric Key 


CREATE SYMMETRIC KEY CreditCardKey WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE CreditCardCert; GO 


--Open Symmetric Key 


OPEN SYMMETRIC KEY CreditCardKey DECRYPTION BY CERTIFICATE CreditCardCert;


--Encrypt the CreditCardNumber column 


UPDATE dbo.SensitiveData SET CreditCardNumber = ENCRYPTBYKEY(KEY_GUID('CreditCardKey'), CreditCardNumber); GO 


CLOSE SYMMETRIC KEY CreditCardKey 


--Close the key so it cannot be used again, unless reopened


注意,我們用來加密資料的 UPDATE語句使用一個稱為 ENCRYPTBYKEY()的函數來加密資料。下列說明了 ENCRYPTBYKEY()函數接受的參數。

如果我們只希望加密儲存格的子集,則可以在 UPDATE語句中添加WHERE子句。

 

 

還請注意,在使用加密金鑰加密資料之前,我們會發出一條聲明以打開金鑰。在將金鑰用於加密或解密資料之前,必須始終將其打開。

為此,用戶必須具有打開金鑰的權限。


當使用下面程式中所示的方法對一列資料進行加密時,仍然存在安全風險,這是由用於加密的算法的確定性引起的,這意味著當您加密相同的值時,

您將獲得相同的雜湊值。

想像一個場景,其中用戶可以訪問 SensitiveData表,但無權查看信用卡號。如果該用戶也是該表中有記錄的客戶,則他們可以使用與該表中

另一個客戶相同的雜湊值更新自己的信用卡號。

然後,他們已經成功竊取了另一位客戶的信用卡號,而不必解密 CreditCardNumber列中的資料。這就是所謂的全值替換攻擊。

為了防止這種情況,您可以添加一個authenticator列,也稱為salt值。它可以是任何列,但通常是表的主鍵列。加密資料後,身份驗證器列將與資料一起加密。

在解密時,然後檢查身份驗證器值,如果不匹配,則解密失敗。

小心永遠不要更新Authenticator列中的值,這一點非常重要。如果是這樣,則可能無法訪問敏感數據。

下面的程式顯示了我們如何使用 Authenticator列將表的主鍵用作Authenticator列來加密 CreditCardNumber列。


在這裡,我們使用HASHBYTES()函數創建 Authenticator列的雜湊值,然後使用雜湊表示對資料進行加密。

提示:該腳本僅作為示例提供,但此時應避免執行該程式,以便您可以遵循後面的代碼範例。


OPEN SYMMETRIC KEY CreditCardKey DECRYPTION BY CERTIFICATE CreditCardCert; 

--Encrypt the CreditCardNumber column 

 

UPDATE SensitiveData SET CreditCardNumber = ENCRYPTBYKEY(Key_GUID('CreditCardKey') ,CreditCardNumber ,1 ,HASHBYTES('SHA1', CONVERT(VARBINARY(8000), ID))); GO 


CLOSE SYMMETRIC KEY CreditCardKey ;


在程式的末端,我們關閉鍵。如果我們沒有明確關閉它,那麼它將在其餘的會話中保持打開狀態。如果我們要使用同一個金鑰執行多個活動,這將很有用,

但是優良作法是在 session中最終使用它後立即將其明確關閉。


即使出於性能原因可以使用對稱金鑰,非對稱金鑰或證書對資料進行加密,但是您通常會選擇使用對稱金鑰,然後使用非對稱金鑰或證書對金鑰進行加密。


存取加密資料為了讀取使用ENCRYPTBYKEY()加密的列中的資料,我們需要使用 DECRYPTBYKEY()函數對其進行解密。下表說明了此功能的參數。

 


下面的程式示範了在沒有身份驗證器的情況下加密資料後,如何使用DECRYPTBYKEY()函數讀取 CreditCardNumber列中的加密資料。


--Open Key 


OPEN SYMMETRIC KEY CreditCardKey DECRYPTION BY CERTIFICATE CreditCardCert; 


--Read the Data using DECRYPTBYKEY() 


SELECT FirstName ,LastName ,CreditCardNumber AS [Credit Card Number Encrypted] ,CONVERT(VARCHAR(30), DECRYPTBYKEY(CreditCardNumber)) 

AS [Credit Card Number Decrypted] ,CONVERT(VARCHAR(30), CreditCardNumber) AS [Credit Card Number Converted Without Decryption] FROM 

dbo.SensitiveData ; 


--Close the Key 


CLOSE SYMMETRIC KEY CreditCardKey ;


該程式中最終查詢的結果示例如下圖所示。你可以看到查詢加密列直接返回加密的二進制值。通過直接轉換為VARCHAR數據類型查詢加密列成功,

但是沒有返回任何資料。但是,使用DECRYPTBYKEY()函數查詢加密列時,將值轉換為VARCHAR資料類型時,返回正確的結果。

Tags:

SQL Server資料庫 | 楊先民Adonis Young

不允許評論

NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List