摘要:[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