證書(shū)和非對(duì)稱密鑰使用數(shù)據(jù)庫(kù)級(jí)的內(nèi)部公鑰加密數(shù)據(jù),并且使用數(shù)據(jù)庫(kù)級(jí)內(nèi)部私鑰解密數(shù)據(jù)。而對(duì)稱密鑰相對(duì)簡(jiǎn)單,它們包含一個(gè)同時(shí)用來(lái)加密和解密的密鑰。
證書(shū)和非對(duì)稱密鑰使用數(shù)據(jù)庫(kù)級(jí)的內(nèi)部公鑰加密數(shù)據(jù),并且使用數(shù)據(jù)庫(kù)級(jí)內(nèi)部私鑰解密數(shù)據(jù)。而對(duì)稱密鑰相對(duì)簡(jiǎn)單,它們包含一個(gè)同時(shí)用來(lái)加密和解密的密鑰。困此,使用對(duì)稱密鑰加密數(shù)據(jù)更快,并且用在大數(shù)據(jù)時(shí)更加合適。盡管復(fù)雜度是考慮使用它的因素,但它仍然是一個(gè)很好的加密數(shù)據(jù)的選擇。
我們看一組例子:
示例一、創(chuàng)建對(duì)稱密鑰
對(duì)稱密鑰的特性是:在數(shù)據(jù)庫(kù)會(huì)話中使用它對(duì)數(shù)據(jù)進(jìn)行加密和解密前必須首先打開(kāi)。
創(chuàng)建對(duì)稱密鑰使用如下命令:CREATE SYMMETRIC KEY 創(chuàng)建對(duì)稱密鑰。()
代碼如下:
use DB_Encrypt_Demo
go
— 創(chuàng)建一個(gè)用于加密對(duì)稱密鑰的非對(duì)稱密鑰
CREATE ASYMMETRIC KEY symDemoKey –名稱
WITH ALGORITHM = RSA_512 –加密算法
ENCRYPTION BY PASSWORD = ‘TestSYM456!’–密碼
— 創(chuàng)建一個(gè)對(duì)稱密鑰
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey
示例二、查看當(dāng)前數(shù)據(jù)庫(kù)中的對(duì)稱密鑰
使用目錄視圖sys.symmetric_keys()來(lái)查看。
代碼如下:
–查看當(dāng)前數(shù)據(jù)庫(kù)中的非對(duì)稱密鑰
use DB_Encrypt_Demo
go
SELECT name, algorithm_desc FROM sys.symmetric_keys
—-結(jié)果返回
/*
name algorithm_desc
sym_Demo TRIPLE_DES
*/
示例三、修改非對(duì)稱密鑰的加密方式
你可以使用ALTER SYMMETRIC KEY()命令修改對(duì)稱密鑰的加密方式。但執(zhí)行前必須使用OPEN SYMMETRIC KEY()命令打開(kāi)它。
代碼如下:
use DB_Encrypt_Demo
go
–先用私鑰密碼打開(kāi)對(duì)稱密鑰
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY ASYMMETRIC KEY symDemoKey
WITH PASSWORD = ‘TestSYM456!’
–打開(kāi)之后,先增加密碼加密,取代原密鑰
ALTER SYMMETRIC KEY sym_Demo
ADD ENCRYPTION BY PASSWORD = ‘newnew!456’
–再刪除非對(duì)稱密鑰加密
ALTER SYMMETRIC KEY sym_Demo
DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey
–完成操作后,關(guān)閉對(duì)稱密鑰
CLOSE SYMMETRIC KEY sym_Demo
示例四、使用對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密。
1、為了使用對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密,必須首先打開(kāi)它,然后使用函數(shù)EncryptByKey 加密數(shù)據(jù)。()
2、使用DecryptByKey來(lái)解密使用對(duì)稱密鑰加密的數(shù)據(jù)。注意DecryptByKey不像甩EncryptByKey,無(wú)須使用對(duì)稱密鑰GUID。因此,為了解密,必須打開(kāi)正確的對(duì)稱密鑰會(huì)話,否則會(huì)顯示null。
下面是一個(gè)例子:
代碼如下:
/****************************3w@live.cn***********************/
USE DB_Encrypt_Demo
GO
–創(chuàng)建測(cè)試數(shù)據(jù)表,用于對(duì)稱加密
CREATE TABLE dbo.PWDQuestion
(CustomerID int NOT NULL PRIMARY KEY,
PasswordHintQuestion nvarchar(300) NOT NULL,
PasswordHintAnswer varbinary(200) NOT NULL)
GO
–插入加密數(shù)據(jù)
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES
(12, ‘您出生的醫(yī)院名稱?’,
EncryptByKey(Key_GUID(‘sym_Demo ‘), ‘杭州市一’))
CLOSE SYMMETRIC KEY sym_Demo
查看未加密的數(shù)據(jù):
代碼如下:
–解密數(shù)據(jù)
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12
–打開(kāi)后切記關(guān)閉!!!3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo
代碼如下:
–不打開(kāi)直接讀取
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12
至此,好像已經(jīng)大功告成了,別,千萬(wàn)別高興得太早!
這里有個(gè)問(wèn)題,如果惡意用戶不知道CustomerID=13的PasswordHintAnswer列的真實(shí)值,但知道CustomerID=14的PasswordHintAnswer列的真實(shí)值,則完全可以通過(guò)PasswordHintAnswer列而繞過(guò)加密!!此時(shí),我們索性連
注意:作為參數(shù)傳入。
看一個(gè)完整的例子:
代碼如下:
truncate table dbo.PWDQuestion
go
–添加兩個(gè)未加密的行
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
select 13, ‘您出生的醫(yī)院名稱?’,cast(‘浙江婦保院’ as varbinary)
union all
select 14, ‘您出生的醫(yī)院名稱?’,cast(‘浙江婦保二院’ as varbinary)
–打開(kāi)對(duì)稱密鑰,連CustomerID列一起加密
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
UPDATE dbo.PWDQuestion
SET PasswordHintAnswer =
EncryptByKey(Key_GUID(‘sym_Demo’),
PasswordHintAnswer,1,–1表示使用驗(yàn)證器值
CAST(CustomerID as varbinary))
WHERE CustomerID in (13,14)
–打開(kāi)后切記關(guān)閉!!!3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo
–此時(shí)必須這樣查看原數(shù)據(jù)
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,–1表示使用驗(yàn)證器值
CAST(CustomerID as varbinary)) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 13
–打開(kāi)后切記關(guān)閉!!!3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo
惡意替換開(kāi)始:
代碼如下:
/**********************************************************
–我們用剛才的CustomerID = 13的PasswordHintAnswer列值
–替換CustomerID = 14的PasswordHintAnswer列值,
–再用剛才讀取14的方法讀取真實(shí)值
**********************************************************/
update dbo.PWDQuestion set PasswordHintAnswer=
(select PasswordHintAnswer from dbo.PWDQuestion where CustomerID = 14)
where CustomerID = 13
此時(shí),我們?cè)俨榭矗?
代碼如下:
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,–1表示使用驗(yàn)證器值
CAST(CustomerID as varbinary)) as varchar(200)) PasswordHintAnswer,
PasswordHintAnswer as binaryValue
FROM dbo.PWDQuestion
WHERE CustomerID in(13,14)
–打開(kāi)后切記關(guān)閉!!!3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo
郎勒個(gè)郎!爽吧!雖然復(fù)制了相同的二進(jìn)制數(shù)據(jù),可是讀取結(jié)果令攻擊者大失所望啊!
示例五、刪除對(duì)稱密鑰
命令:DROP SYMMETRIC KEY 刪除指定的對(duì)稱密鑰( )
例子:
DROP SYMMETRIC KEY symDemoKey
注意:如果加密密鑰打開(kāi)沒(méi)有關(guān)閉,則drop失敗。
小結(jié):
1、本文主要介紹對(duì)稱密鑰的創(chuàng)建、刪除、查看以及用它來(lái)修改加密方式、進(jìn)行數(shù)據(jù)的加密和解密。
2、對(duì)稱密鑰的特性是:在數(shù)據(jù)庫(kù)會(huì)話中使用它對(duì)數(shù)據(jù)進(jìn)行加密和解密前必須首先打開(kāi)。
3、對(duì)稱密鑰可用于大數(shù)據(jù)的加密。
下文將主要介紹證書(shū)加密(Certificate Encryption)