資料加解密與資料庫的備份還原

by adonis 8. 六月 2012 15:56

前言

SQL Server 自2005之後就開始支援欄位的資料加密,本期將來探討一些欄位的資料加密,以及當資料庫備份還原到其他電腦中,該如何重現原來加解密的資訊等相關問題。

何謂資料加密

資料加密簡單來説就是將原本的資料編碼,以及用來保護這個編碼的資料而稱之。

例如你的密碼是1234,如果你直接把它記錄在一張白紙上,上面寫著:xxx銀行帳號密碼:1234,只要撿到這張紙的,就會知道你銀行的密碼是1234,所以資料加密要把這個1234編碼,例如明明密碼是1234,但我記錄的時後編碼為qwer(此乃我的編碼演算法),其實對照一下鍵盤,1234的下方就是qwer啦!

但光是編碼還不夠,還要保護這個編碼的紙條,例如把這張紙條放在保險櫃中,只要你想知道紙中的編碼資訊,必需先打開保險櫃再將編碼的資訊解開,最後才能完成任務。

而這樣的動作,在 SQL Server中,必需要設定所謂的 KEY來完成,也就是用電腦產生一把虛擬的鑰匙做這些事情,而 SQL Server自 2005之後既然可以支援加解密,自然可以允許管理者設定一把鑰匙進行加解密。

那麼,之前 SQL Server並沒有提供加解密時,難不成資料就無法加解密嗎?其實也不是,主要是 SQL Server沒有提供加解密的鑰匙之外,也沒有提供加解密的函數,所以在以前資料要加解密的話,必需在 SQL Server中先設定資料 image或 binary的資料型別,利用前端應用程式的演算法將資料在前端加密後再存入 binary資料型別中。

所以, SQL Server自2005之後開始支援資料加解密,是非常方便的事,可以在 SQL Server中利用自身加解密功能進行加解密,而無需再利用前端應用程式,你可以寫一個預存程序,應用程式端只需要給帳號密碼,加解密的動作由預存程序來完成,大幅減少了開發的時間。

然而,提到了加解密,就不得不提到所謂的 KEY這個東西。

在電腦中,所謂的 KEY,主要是由編碼的演算法與保護密碼所組合而成,而 KEY的種類也被分類為對稱式(Symmetric)與非對稱式(Asymmetric)兩種。

對稱式金鑰:加密與解密都是使用同一把金鑰。

非對稱式金鑰:先由數學演算法產生出一對金鑰,可以提供給別人的金鑰稱為公鑰,而留給使用者自已用的,稱為私鑰。公鑰負責加密資料,而私鑰負責解密,即便是在網路中攔截到公鑰也沒有用處,需要有給使用者的私鑰方可解密。而非對稱式正向可以用來做資料加密(公鑰加密,私鑰解密),而反向可以用來做數位簽章(私鑰包信件,而公鑰證明傳來的信件是擁有私鑰的某人所傳來的)。

一般來説,就安全性的角度而言,對稱式金鑰的安全性沒有非對稱式金鑰安全,而非對稱式金鑰的加解密速度也沒有對稱式金鑰來的快速,也就是説,想要完全兼顧速度與安全性,是兩難的選擇。

不過,通常如果資料要對外,才會選擇非對稱式加密,例如線上購物要填寫信用卡卡號,為了避免卡號外洩,會使用 SSL加密, SSL加密即是一種非對稱式的加密,而由於資料會出去,不是只有在本機或是本區域網路,所以需要比較安全的機制進行資料加密。不然的話,以速度的角度而言,使用對稱式加密會比較理想。

然而,有些人會問,對稱式加密比較不安全,會不會有安全上的疑慮?為此,我們特別説明,為了兼顧安全與效能,如果使用對稱式加密的話,會採用所謂的「階層式」的加密。

就好像機車上鎖一樣,鎖一道可能不安全,那就多鎖幾道,對稱式加密理論上也相同。

在 SQL Server中,你可以在建立密鑰的時後,利用密碼來保護這把密鑰,或是利用密鑰來保護這把密鑰,甚至你也可以建立憑證來保護這把密鑰都可以,而如果你是用密碼保護密鑰,則到時要使用這把密鑰時,就必需輸入當初所提供的密碼以開啟密鑰。

不過為了節省時間,不希望你記一堆密碼,所以你可以選擇在一個資料庫中建一個 Master key,而這把 master key是建構在 service層級的 master key之上的密鑰(這也就是做到所謂的階層式的密鑰),然後你可以基於資料庫的 master key,建立屬於自已的密鑰以達成第二層的階層密鑰的設定,正如同以下微軟教材的設定:

image

當然,如果為了之後方便確認是否你真的具有此密鑰的權力,你也可以在建立自已的密鑰之上,再包一個憑證,以後只需有憑證的權力就不需要記住一堆的密碼。

所以資料的加解密的程式如同以下的方式設定:

CREATE TABLE HumanResources.EmployeeReview

(EmployeeID int NOT NULL,

ReviewDate datetime DEFAULT GETDATE() NOT NULL,

Comments varchar(2000) NOT NULL)

(這裡是先建立一個資料表,comments是我們預估要加密的欄位)

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

接下來是為止資料庫建一個 MASTER KEY,這個 MASTER KEY的密碼要記住,以後資料備份還原的時後可能還需要用到。

CREATE CERTIFICATE HRAppCert

WITH SUBJECT = 'HR certificate'

GO

建立一個憑證,此憑證在建立時有沒有發現並不需要密碼?因為這就是之前提到的階層式的密鑰設定,如果你沒有設定 MASTER KEY的話,你就需要設定一個密碼來保護這個憑證,然後每次要開啟這個憑證就需要用密碼來開啟。

CREATE SYMMETRIC KEY HRKey

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE HRAppCert

利用憑證來保護自已所建立的對稱式的密鑰,以後要開啟這個密鑰,只要擁有憑證控制權能力的人,就可以使用這個密鑰。

OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert

INSERT INTO HumanResources.EmployeeReview

VALUES

(1, DEFAULT, EncryptByKey(Key_GUID('HRKey'),'Increasing salary to $35,000'))

CLOSE ALL SYMMETRIC KEYS

利用憑證來開啟對稱式的密鑰,並且在新增資料時使用 EncryptByKey函數將要加密的資料填入即可。

至於解密的話,只要利用下面的語法即可解密:

OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert

SELECT CONVERT(varchar,DecryptByKey(Comments)) AS Comments FROM HumanResources.EmployeeReview

CLOSE ALL SYMMETRIC KEYS

也就是開啟憑證,並利用 DecryptByKey函數將已經加密的資料利用解密函數解密。

如果你沒有憑證以及金鑰的控制權,則你將無法看到加密的資料,所以如果你的身份不是 dbo,則你需要給與使用者下面的權力才行(假設此人的資料庫使用者帳號為 John)

GRANT CONTROL ON CERTIFICATE::[HRAppCert] TO [John]

GRANT CONTROL ON SYMMETRIC KEY::[HRKey] TO [John]

然而,如果你的資料庫透過備份還原,進而還原到別台機器的 SQL Server上,如果你只是單純的利用 backup與 restore就想把資料還原回去,你會發現沒有用,還原到新的伺服器並沒有辦法看到加密的資料,你必需要使用下列的方式才能滿足需求。

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

CLOSE MASTER KEY

也就是,你必需用原來 master key的密碼先開啟 master key,然後把新電腦的 service master key的資訊蓋回去,才能使你原本的資料加解密資訊能夠正常的使用。而即便是一般微軟的教科書也未必會有寫到這方面的內容。給各位做個參考!

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

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

月分類Month List