SQL Server 2008中的代碼平安(六) 對稱密鑰加密。本站提示廣大學習愛好者:(SQL Server 2008中的代碼平安(六) 對稱密鑰加密)文章只能為提供參考,不一定能成為您想要的結果。以下是SQL Server 2008中的代碼平安(六) 對稱密鑰加密正文
證書和非對稱密鑰應用數據庫級的外部公鑰加密數據,而且應用數據庫級外部私鑰解密數據。而對稱密鑰絕對簡略,它們包括一個同時用來加密息爭密的密鑰。困此,應用對稱密鑰加密數據更快,而且用在年夜數據時加倍適合。雖然龐雜度是斟酌應用它的身分,但它依然是一個很好的加密數據的選擇。
我們看一組例子:
示例1、創立對稱密鑰
對稱密鑰的特征是:在數據庫會話中應用它對數據停止加密息爭密前必需起首翻開。
創立對稱密鑰應用以下敕令:CREATE SYMMETRIC KEY 創立對稱密鑰。(http://msdn.microsoft.com/en-us/library/ms188357.aspx)
use DB_Encrypt_Demo
go
-- 創立一個用於加密對稱密鑰的非對稱密鑰
CREATE ASYMMETRIC KEY symDemoKey --稱號
WITH ALGORITHM = RSA_512 --加密算法
ENCRYPTION BY PASSWORD = 'TestSYM456!'--暗碼
-- 創立一個對稱密鑰
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey
示例2、檢查以後數據庫中的對稱密鑰
應用目次視圖sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)來檢查。
--檢查以後數據庫中的非對稱密鑰
use DB_Encrypt_Demo
go
SELECT name, algorithm_desc FROM sys.symmetric_keys
----成果前往
/*
name algorithm_desc
sym_Demo TRIPLE_DES
*/
示例3、修正非對稱密鑰的加密方法
你可使用ALTER SYMMETRIC KEY(http://technet.microsoft.com/en-us/library/ms189440.aspx)敕令修正對稱密鑰的加密方法。但履行前必需應用OPEN SYMMETRIC KEY(http://msdn.microsoft.com/en-us/library/ms190499.aspx)敕令翻開它。
use DB_Encrypt_Demo
go
--先用私鑰暗碼翻開對稱密鑰
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY ASYMMETRIC KEY symDemoKey
WITH PASSWORD = 'TestSYM456!'
--翻開以後,先增長暗碼加密,代替原密鑰
ALTER SYMMETRIC KEY sym_Demo
ADD ENCRYPTION BY PASSWORD = 'newnew!456'
--再刪除非對稱密鑰加密
ALTER SYMMETRIC KEY sym_Demo
DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey
--完成操作後,封閉對稱密鑰
CLOSE SYMMETRIC KEY sym_Demo
示例4、應用對稱密鑰對數據停止加密息爭密。
1、為了應用對稱密鑰對數據停止加密,必需起首翻開它,然後應用函數EncryptByKey 加密數據。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
2、應用DecryptByKey來解密應用對稱密鑰加密的數據。留意DecryptByKey不像甩EncryptByKey,不必應用對稱密鑰GUID。是以,為懂得密,必需翻開准確的對稱密鑰會話,不然會顯示null。
上面是一個例子:
/****************************[email protected]***********************/
USE DB_Encrypt_Demo
GO
--創立測試數據表,用於對稱加密
CREATE TABLE dbo.PWDQuestion
(CustomerID int NOT NULL PRIMARY KEY,
PasswordHintQuestion nvarchar(300) NOT NULL,
PasswordHintAnswer varbinary(200) NOT NULL)
GO
--拔出加密數據
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = 'newnew!456'
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES
(12, '您出身的病院稱號?',
EncryptByKey(Key_GUID('sym_Demo '), '杭州市一'))
CLOSE SYMMETRIC KEY sym_Demo
檢查未加密的數據:
--解密數據
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
--翻開後切記封閉!!![email protected]
CLOSE SYMMETRIC KEY sym_Demo
--不翻開直接讀取
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12
至此,似乎曾經年夜功樂成了,別,萬萬別愉快得太早!
這裡有個成績,假如歹意用戶不曉得CustomerID=13的PasswordHintAnswer列的真實值,但曉得CustomerID=14的PasswordHintAnswer列的真實值,則完整可以經由過程歹意調換PasswordHintAnswer列而繞過加密!![email protected]此時,我們索性連CustomerID列作為驗證列也一路加密,以絕後患 !
留意:加密的驗證列也能夠由另外一個相干表的列作為參數傳入。
看一個完全的例子:
truncate table dbo.PWDQuestion
go
--添加兩個未加密的行
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
select 13, '您出身的病院稱號?',cast('浙江婦保院' as varbinary)
union all
select 14, '您出身的病院稱號?',cast('浙江婦保二院' as varbinary)
--翻開對稱密鑰,連CustomerID列一路加密
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = 'newnew!456'
UPDATE dbo.PWDQuestion
SET PasswordHintAnswer =
EncryptByKey(Key_GUID('sym_Demo'),
PasswordHintAnswer,1,--1表現應用驗證器值
CAST(CustomerID as varbinary))
WHERE CustomerID in (13,14)
--翻開後切記封閉!!![email protected]
CLOSE SYMMETRIC KEY sym_Demo
--此時必需如許檢查原數據
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = 'newnew!456'
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,--1表現應用驗證器值
CAST(CustomerID as varbinary)) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 13
--翻開後切記封閉!!![email protected]
CLOSE SYMMETRIC KEY sym_Demo
歹意調換開端:
/**********************************************************
--我們用適才的CustomerID = 13的PasswordHintAnswer列值
--調換CustomerID = 14的PasswordHintAnswer列值,
--再用適才讀取14的辦法讀取真實值
**********************************************************/
update dbo.PWDQuestion set PasswordHintAnswer=
(select PasswordHintAnswer from dbo.PWDQuestion where CustomerID = 14)
where CustomerID = 13
此時,我們再檢查:
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = 'newnew!456'
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,--1表現應用驗證器值
CAST(CustomerID as varbinary)) as varchar(200)) PasswordHintAnswer,
PasswordHintAnswer as binaryValue
FROM dbo.PWDQuestion
WHERE CustomerID in(13,14)
--翻開後切記封閉!!![email protected]
CLOSE SYMMETRIC KEY sym_Demo
郎勒個郎!爽吧!固然復制了雷同的二進制數據,可是讀取成果令進擊者年夜掉所望啊!
示例5、刪除對稱密鑰
敕令:DROP SYMMETRIC KEY 刪除指定的對稱密鑰( http://technet.microsoft.com/en-us/library/ms182698.aspx)
例子:
DROP SYMMETRIC KEY symDemoKey
留意:假如加密密鑰翻開沒有封閉,則drop掉敗。
小結:
1、本文重要引見對稱密鑰的創立、刪除、檢查和用它來修正加密方法、停止數據的加密息爭密。
2、對稱密鑰的特征是:在數據庫會話中應用它對數據停止加密息爭密前必需起首翻開。
3、對稱密鑰可用於年夜數據的加密。
下文將重要引見證書加密(Certificate Encryption)
邀月