資料加密的資料庫搬移

by adonis 19. 六月 2013 16:06

作    者:楊先民
審    稿:張智凱
文章編號:
出刊日期:   

前言
SQL Server自 2005之後開始支援資料庫加密,也就是如果你想把使用者的帳號、密碼存放在資料表的話,密碼的資訊肯定是不能被別人看到的。

所以你必需做資料加密,將密碼這個欄位編碼讓即便有查詢權力的人,也不能直接看到未加密的密碼欄位資訊。


然而,如果你把資料庫備份還原到其他台的 SQL Server,你會發現原來加密的資料不見了,本期就是在介紹加密後的資料表,如果資料庫搬移後該怎麼辦。

欄位加密與透明資料加密的不同
在介紹資料庫搬移之前,先說明本期的內容並不是針對 SQL Server 2008之後所提供的新功能「透明資料加密」(TDE),而是 SQL Server 2005之後所提供的功能「欄位資料加密」,換言之,透明資料加密固然在搬移資料庫也需要搬移伺服器憑證,但這方面的參考資訊相當多,而本期要講的欄位加密卻很少人知道如果你搬移它的話,也是要做一些設定方能讓你可以看到加密後的資料內容,不然可是會變成 null的。

欄位資料加密簡介設定方式
當然,在提到資料庫搬移之前,還是先簡介一下一般會做資料加密的流程:
1.    首先我們會選擇用對稱式的金鑰進行加密,因為速度較非對稱式金鑰快。
2.    如果擔心安全性比較沒有像非對稱式金鑰安全的話,可以採用階層式的加密方式,如下圖:

image

3.    也就是說,當 SQL Server在安裝時,已經基於 Windows DPAPI建立了一個屬於 SQL Server服務自已的 master key,而你可以替每個資料庫,基於這個 SMK(Service master key),產生一個資料庫層級的 master key(DMK),這就是所謂的「階層式加密」,可以兼顧安全性與效能。

4.    如果你不替使用者建立資料庫層級的 master key,則以後你所建立的金鑰就必需設定一個密碼保護,而如果你有設定資料庫層級的 master key,則以後你所建立的金鑰就是基於資料庫層級的 master key所做的階層加密。


5.    通常這種機制會把憑證加進來,以利透過憑證來開啟資料加密、解密的權力。
如果你的加密機制是利用如上的階層加密設定方式,那麼當資料庫在轉換伺服器時,你必需要針對 Master database key重新設定方能讓你看到原本的資料加密值。

何故?我們試想一下,這種階層式的加密方式,如果有一層斷掉的話,會有什麼後果?

以本例而言,Database master key是基於 Service master key而產生階層加密,而你所建立的憑證是基於 Database master key而產生的階層加密,如果有人有備份的權力,將資料庫備份出來,並且回復到另一台主機,他將是該資料庫的 dbo,勢必可以有憑證以及自行設定的加密金鑰控制權力,是否就代表他可以隨時用憑證開啟自行設定的加密金鑰,並且將已經加密的資料解密呢?

答案當然是不可以!就算可以我們也不能讓這種事情發生。

這個問題就和 SQL Server 2008之後所提供的透明資料加密(TDE)一樣,如果資料庫光憑從 A伺服器備份,B伺服器還原就將資料偷走,似乎是不行的,所以有設定透明資料加密的資料庫,無論加密選項有沒有打開,資料庫就是會檢查你的伺服器憑證是否正確,如果不正確或是不存在,則還原資料庫時會出現錯誤訊息。

換言之,以欄位加密的角度來看,當你備份還原到另一台伺服器時,除非你把 service master key備份並且還原到另一台 SQL Server,不然你原來的加密資料將無法被解密。

所以你可能想說是否我必需在第一台 SQL Server使用下列指令備份:

USE AdventureWorks; 
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7';  
BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'     
ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg'; 
GO

然後在第二台 SQL Server使用下列指令還原:

USE AdventureWorks2012;
RESTORE MASTER KEY     
FROM FILE = 'c:\temp\exportedmasterkey'     
DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04'     
ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';
GO


這是很多人的想法,但後來發現這個做法並不可行?
為何?因為 Database master key一直是存在於資料庫的啊!

你為何要備份 Database master key,並且再還原它呢?這根本是個不必要的動作啊。

所以斷掉的階層是在 Service master key 與 Database master key之間,我們必需要將新資料庫移到新伺服器時,將他們之間的階層關係重建起來。

因此,我們必需要使用下面的指令先把資料庫的 master key「開啟」
OPEN MASTER KEY DECRYPTION BY PASSWORD ='原來建立 master key的密碼'

然後再把現有的 master key 與 service master key階層關係重新建立產生,如下:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

最後把 master key關閉即可以正常恢復運作。

如此一來, Service master key 與 database master key之間的階層不會中斷,也讓我們了解一件事就是:

「千萬不要忘記你當初所設定的 database master key」,不然到時要搬移資料庫時,勢必要頭痛萬分了。

結語
本期主要還是回應一個朋友常遇到的問題,而這個問題在一般教科書上並沒有說明,而大多數的人如果資料庫沒有搬移,可能也沒有發現這個問題,畢竟資料庫加密的範例到處都是,但會在後面補充如果資料庫搬移時該怎麼辦的不多,也剛好利用這個機會將資料加密的部分補完。

image

Tags:

評論 (63) -

cours de theatre
cours de theatre United States
2017/9/30 上午 10:14:06 #

Really appreciate you sharing this blog post.Much thanks again. Really Great.

回覆

buy hacklinks
buy hacklinks United States
2017/10/9 下午 01:00:08 #

Major thankies for the article post.Really looking forward to read more.

回覆

Osimi seaview
Osimi seaview United States
2017/10/9 下午 04:55:02 #

Thanks-a-mundo for the article post.Really looking forward to read more. Great.

回覆

Sterling Businesses Ltd
Sterling Businesses Ltd United States
2017/10/10 下午 08:49:45 #

Really appreciate you sharing this blog. Really Cool.

回覆

pirater un compte facebook
pirater un compte facebook United States
2017/10/10 下午 11:08:27 #

Very neat post.Much thanks again. Really Great.

回覆

Buy illegal backlinks
Buy illegal backlinks United States
2017/10/12 下午 07:33:04 #

Thanks a lot for the blog article.Really looking forward to read more. Want more.

回覆

look at more info
look at more info United States
2017/10/14 下午 02:13:03 #

Say, you got a nice blog post. Great.

回覆

dragon city hack no root
dragon city hack no root United States
2017/10/15 下午 02:14:24 #

Muchos Gracias for your article.Much thanks again. Great.

回覆

sletrokor review
sletrokor review United States
2017/10/17 下午 07:05:15 #

I am so grateful for your blog article. Really Great.

回覆

Opal Skyview
Opal Skyview United States
2017/10/19 上午 04:27:43 #

Wow, great blog post.Really looking forward to read more. Much obliged.

回覆

VigRx
VigRx United States
2017/10/19 上午 06:07:47 #

Thank you for your post.Much thanks again. Will read on...

回覆

Check Out This URL
Check Out This URL United States
2017/10/19 下午 04:54:40 #

Very informative blog post.Really thank you! Will read on...

回覆

pure slim 1000
pure slim 1000 United States
2017/10/20 上午 02:25:16 #

I really liked your post.Really looking forward to read more. Really Cool.

回覆

vung tau melody
vung tau melody United States
2017/10/21 上午 02:03:14 #

Thank you for your blog post.Really looking forward to read more. Keep writing.

回覆

carte grise en ligne
carte grise en ligne United States
2017/10/21 上午 05:44:08 #

Thanks for sharing, this is a fantastic blog article.Really looking forward to read more. Awesome.

回覆

elake
elake United States
2017/10/21 下午 04:08:42 #

Awesome post. Much obliged.

回覆

website designing company in Delhi India
website designing company in Delhi India United States
2017/10/24 上午 07:47:15 #

I appreciate you sharing this article post.Really thank you! Much obliged.

回覆

can ho osimi
can ho osimi United States
2017/10/28 上午 08:24:08 #

A big thank you for your blog article. Keep writing.

回覆

EZ Battery Reconditioning Review
EZ Battery Reconditioning Review United States
2017/10/30 上午 07:18:28 #

Awesome article. Awesome.

回覆

lepszy plock
lepszy plock United States
2017/10/30 下午 03:03:29 #

Very informative blog article.Much thanks again. Much obliged.

回覆

mamaweeb
mamaweeb United States
2017/11/1 上午 07:32:01 #

Thanks for the blog post.Much thanks again.

回覆

phenocal review
phenocal review United States
2017/11/1 下午 02:54:03 #

Looking forward to reading more. Great blog article.Really looking forward to read more. Awesome.

回覆

phentaslim
phentaslim United States
2017/11/3 上午 09:36:38 #

Im grateful for the blog post.Really looking forward to read more. Will read on...

回覆

spinal stenosis surgery failed
spinal stenosis surgery failed United States
2017/11/15 上午 07:56:51 #

Thanks again for the blog.Thanks Again. Much obliged.

回覆

avocat criminel quebec
avocat criminel quebec United States
2017/11/16 下午 06:32:21 #

Enjoyed every bit of your article.Thanks Again.

回覆

http://strand.projectsurabaya.com/
http://strand.projectsurabaya.com/ United States
2017/11/17 下午 05:03:11 #

Awesome blog.Much thanks again. Will read on...

回覆

5 gig
5 gig United States
2017/11/22 上午 03:12:35 #

Very good article. Awesome.

回覆

fashion
fashion United States
2017/11/23 下午 10:00:57 #

Major thankies for the article post.Really looking forward to read more. Keep writing.

回覆

Search Engine Marketing Nz
Search Engine Marketing Nz United States
2017/11/25 下午 06:58:24 #

This is one awesome blog post.Much thanks again. Awesome.

回覆

Chad Boonswang and Jeffrey Goodman
Chad Boonswang and Jeffrey Goodman United States
2017/11/26 下午 06:08:02 #

I really liked your blog.Much thanks again. Awesome.

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/27 上午 12:13:12 #

I really enjoy the article.Thanks Again. Really Cool.

回覆

fake cash for cars
fake cash for cars United States
2017/11/29 下午 04:35:35 #

Thanks again for the post.Thanks Again. Much obliged.

回覆

Calla Garden
Calla Garden United States
2017/11/29 下午 11:11:50 #

I loved your article post.Thanks Again.

回覆

commercial real estate loan
commercial real estate loan United States
2017/12/1 上午 01:00:39 #

I truly appreciate this article post.Much thanks again. Really Cool.

回覆

Get Business Credit
Get Business Credit United States
2017/12/3 上午 04:59:30 #

Thanks a lot for the article post. Really Great.

回覆

free porn apps
free porn apps United States
2017/12/5 上午 09:46:46 #

Major thanks for the blog post. Fantastic.

回覆

I value the article.Really thank you! Much obliged.

回覆

http://pleasantcar.fun/story.php?id=13136
http://pleasantcar.fun/story.php?id=13136 United States
2017/12/10 上午 08:57:51 #

I loved your post.Really thank you!

回覆

best blog
best blog United States
2017/12/10 下午 07:23:26 #

I cannot thank you enough for the blog post.Really thank you! Great.

回覆

Delphine Dinning
Delphine Dinning United States
2017/12/14 上午 09:30:36 #

A big thank you for your article.Thanks Again.

回覆

useful content
useful content United States
2017/12/14 下午 04:22:22 #

Thanks so much for the blog.

回覆

canon driver software
canon driver software United States
2017/12/16 下午 06:04:34 #

I loved your post.Thanks Again. Cool.

回覆

green bean coffee
green bean coffee United States
2017/12/17 上午 12:22:07 #

Really appreciate you sharing this article.Much thanks again. Really Cool.

回覆

Im obliged for the article post.Really looking forward to read more. Really Cool.

回覆

Enterprise
Enterprise United States
2017/12/17 下午 05:44:43 #

I truly appreciate this article post.Really thank you! Really Cool.

回覆

degrees shortcut
degrees shortcut United States
2017/12/18 上午 12:48:23 #

Really appreciate you sharing this article post.Really thank you! Much obliged.

回覆

income opportunity
income opportunity United States
2017/12/20 下午 06:57:54 #

Great article post.Thanks Again. Great.

回覆

canon drivers
canon drivers United States
2017/12/23 上午 09:21:12 #

Say, you got a nice blog article. Great.

回覆

Darwin Horan
Darwin Horan United States
2017/12/23 下午 07:49:39 #

Im grateful for the article post. Want more.

回覆

driver hp
driver hp United States
2017/12/25 下午 09:18:51 #

wow, awesome blog post. Much obliged.

回覆

رگ خواب
رگ خواب United States
2017/12/26 上午 04:06:04 #

Very informative blog post.Much thanks again.

回覆

Appreciate you sharing, great article. Fantastic.

回覆

SOCCER HIGHLIGHTS
SOCCER HIGHLIGHTS United States
2017/12/26 下午 02:15:37 #

I truly appreciate this article. Will read on...

回覆

canon drivers
canon drivers United States
2017/12/27 下午 03:11:42 #

Awesome post.Really looking forward to read more. Great.

回覆

hp drivers
hp drivers United States
2018/1/2 上午 05:56:37 #

I loved your blog. Much obliged.

回覆

helpful hints
helpful hints United States
2018/1/2 下午 01:54:07 #

Appreciate you sharing, great blog post.Much thanks again. Want more.

回覆

hp drivers
hp drivers United States
2018/1/3 上午 05:13:02 #

I think this is a real great blog post.

回覆

real money pokies
real money pokies United States
2018/1/4 下午 03:45:04 #

Great article post.Much thanks again. Much obliged.

回覆

hp driver
hp driver United States
2018/1/5 下午 07:09:12 #

Thank you for your blog post.Really thank you! Much obliged.

回覆

FBA
FBA United States
2018/1/6 上午 11:15:20 #

Very informative article post.Really looking forward to read more. Keep writing.

回覆

colocation miami
colocation miami United States
2018/1/10 上午 07:09:39 #

Thanks a lot for the article.Much thanks again. Great.

回覆

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List