[SQL] 資料庫加密 - 使用 Master key、Certificate 與 Symmetric Key

摘要:[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#
建立步驟:
  1. 建立 Master Key
  2. 建立 Certification
  3. 建立 Symmetric Key
使用執行步驟:
  1. 針對欄位加密
  2. 欄位解密
備份金鑰
------------------------------------
@ 建立 Master 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(varcharDecryptByKey(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

 

  1. 建立目的地主機的 master key
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'w2e3r4t5y6'

    目的地主機這所用的 Master Key 可以不同於來源的
  2. 建立 certificate 從檔案來源
    CREATE CERTIFICATE  xCert
    FROM FILE='C:\backup\xCert'
    WITH PRIVATE KEY (FILE =
    'C:\backup\xCert_Private_Key',
    DECRYPTION BY PASSWORD='123456')

    而這邊用以解密的密碼,則必須同為 Backup 時,所用的密碼
  3. 查看資料庫加密狀態,使用 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