摘要:[SQL] 資料庫加密 - 使用 Master key、Certificate 與 Symmetric Key
MS-SQL 2008 提供了幾種資料加、解密的方式，防範的目標從外部的駭客、內部的使用者甚至掌管資料庫的DBA都是防範的對象，有哪些方式呢？我來稍微整理一下...
--------------------------------
這一篇主要說的是「使用 Master key、Certificate 與 Symmetric Key 來對欄位進行加解密」的動作。
在說明細項之前，要先來瞭解一下為何不要用「ENCRYPTbypassphrase」而要用「Symmetric Key EncryptByKey」呢？從字面上與動作方式來說，這兩種方式都是採用「對稱式加解密 Symmetric」原理，也就是使用同一把金鑰（KEY）來進行加密或解密（就跟我們家裡的鑰匙一樣，用同一把打開跟鎖上）。
差別在於～
「ENCRYPTbypassphrase(KEY, 'plain_text')」在執行 T-SQL 時要帶上所使用的金鑰，只要能看到程式碼的人就可以清楚的知道其KEY為何，安全性較低。
而「Symmetric Key EncryptByKey」 是以一個密碼產生的「Master Key」去建立「憑證 Certificate」，然後再用此 Certificate 來建立「對稱式金鑰 Symmetric Key」，使用的過程中只要呼叫憑證來解開此KEY（取出KEY來使用），便可以對資料作加解密的動作。
憑證跟對稱式金鑰的內容完全不知道也不需要知道，只需保管產生 master key 的密碼，跟妥善的備份憑證就可以了。
----------------------------------
欄位加密、解密 - #使用 Master key、Certificate 與 Symmetric Key#
建立步驟：
-
建立 Master Key
-
建立 Certification
-
建立 Symmetric Key
使用執行步驟：
-
針對欄位加密
-
欄位解密
備份金鑰
------------------------------------
|
IF EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
DROP MASTER KEY ;
--SELECT '刪除master key';
ELSE
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
--SELECT '建立master key';
GO
-- 上面的 123456 是用來加密 Master key 的密碼(Password)
-- 如果此 Master key 關連到某憑證之後會無法刪除
訊息15580，層級16，狀態1，行2
無法卸除主要金鑰，因為憑證'xCert' 用它來加密。
-- 查詢DB的 Master Key
SELECT name N'資料庫', is_master_key_encrypted_by_server N'DB Master Key'
FROM master.sys.databases
ORDER BY 2 DESC
|
＠＠ 建立憑證 Certificate
|
-- 建立certificate
CREATE CERTIFICATE xCert
WITH SUBJECT = 'X-ray Certificate'
GO
-- 刪除certificate
DROP CERTIFICATE xCert
-- 查詢憑證
SELECT * FROM sys.certificates
-- 有效期限 expiry_date 預設為一年
-- SQL Server Service Broker 會檢查到期日，不過當憑證用於加密時，不會強制執行到期。
-- 以自訂密碼加密憑證，並自訂過期日
CREATE CERTIFICATE xCert_extra
ENCRYPTION BY PASSWORD = '663322'
WITH SUBJECT = N'地球毀滅日',
EXPIRY_DATE = '12/20/2012';
-- 查詢
SELECT certificate_id,name,pvt_key_encryption_type 'key_type',pvt_key_encryption_type_desc ,subject ,start_date ,expiry_date FROM sys.certificates
註： Alter certificate 可以修改密碼，但無法變更 expiry date ，需於建立時詳細考慮過期日期。
|
＠＠ 建立對稱式金鑰 Symmetric Key
-- 建立 symmetric key
CREATE SYMMETRIC KEY xKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE xCert
GO
-- Triple_DES 就是做三次正反正 DES加解密動作（當然也可以反反正...囉）
-- 刪除 symmetric key
DROP SYMMETRIC KEY xKey
-- 查詢 symmetric key
SELECT * FROM sys.symmetric_keys
SELECT Key_GUID('xKey') -- 取得key 的 GUID 值
AAAA |
＠＠ 欄位加密 encrypt
|
要對欄位進行加密後存入，其時機是在於 Insert / Update 的時候，底下我就舉例當在 Insert 時，對於要加密的欄位怎麼來做
最重要的一點，當你要開始使用 symmetric key 去做時，記得先「開啟 Open」KEY，用完之後在「關掉 Close」
OPEN SYMMETRIC KEY xKey DECRYPTION BY CERTIFICATE xCert
-- ..你的 T-SQL .......
CLOSE ALL SYMMETRIC KEYS
當中 Insert / Update 的資料表示部份，就用
加密「EncryptByKey(Key_GUID('xKey'),'要插入的值內容')」
解密「CONVERT(varchar, DecryptByKey(Column_Name))」
整個使用例子如下
-- 插入一筆資料
OPEN SYMMETRIC KEY xKey DECRYPTION BY CERTIFICATE xCert
INSERT INTO xEmployee
VALUES(1,'一般欄位', EncryptByKey(Key_GUID('xKey'),'要插入的值內容'))
CLOSE ALL SYMMETRIC KEYS
欄位儲存內容
ㄝ～怎麼會不一樣呢？是 SELECT 語法造成的嗎？
不是..............這是因為資料表宣告了不同的欄位型別，上面是宣告為 varbinary 而下面是宣告為 nvarchar
在定義裡，使用「EncryptByKey」的回傳值為 varbinary 型態，上限為 8000 位元組。所以請你將需要加密的欄位，其資料型別定義為 varbinary。
|
＠＠ 欄位解密 decrypt
-- 解密欄位
OPEN SYMMETRIC KEY xKey DECRYPTION BY CERTIFICATE xCert
SELECT CONVERT(varchar,DecryptByKey(Comments)) AS Comments FROM xEmployee
CLOSE ALL SYMMETRIC KEYS |
最前面描述了使用欄位加密「ENCRYPTbypassphrase」與「EncryptByKey」的不同處，說到了 encryptbypassphrase 因為金鑰跟隨著加密的 T-sql 語法，所以很容易在程式碼裡被看到，安全性較低。反之「EncryptByKey」使用了層層保護，甚至連最後用來加密的 symmetric key 是啥都不知道，只知道該用那個憑證來開啟他。
那麼萬一資料庫需要移機？萬一 master key 的密碼忘記？該怎麼辦？
越簡單通常越不安全，越麻煩比較安全一些～～安全是至高無上的......
所以，當你使用了 symmetric key 來加密欄位，那請你也要妥善的「備份」與「保管」你的憑證與金鑰。
＠＠ 備份金鑰
|
-- 匯出憑證和私密金鑰
BACKUP CERTIFICATE xCert
TO FILE = 'C:\backup\xCert'
WITH PRIVATE KEY (FILE='C:\backup\xCert_Private_Key',
ENCRYPTION BY PASSWORD='123456')
到目的地主機上還原 certificate
-
建立目的地主機的 master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'w2e3r4t5y6'
目的地主機這所用的 Master Key 可以不同於來源的
-
建立 certificate 從檔案來源
CREATE CERTIFICATE xCert
FROM FILE='C:\backup\xCert'
WITH PRIVATE KEY (FILE = 'C:\backup\xCert_Private_Key',
DECRYPTION BY PASSWORD='123456')
而這邊用以解密的密碼，則必須同為 Backup 時，所用的密碼
-
查看資料庫加密狀態，使用 sys.dm_database_encryption_keys
SELECT DB_NAME(database_id) N'資料庫' , encryption_state '加密狀態', *
FROM sys.dm_database_encryption_keys
encryption_state 指出資料庫已加密或未加密。
0 = 沒有資料庫加密金鑰存在，未加密
1 = 未加密
2 = 加密進行中
3 = 已加密
4 = 金鑰變更進行中
5 = 解密進行中
|
~ End