SQL Server 2008中SQL應用系列--目錄索引

證書可以在數據庫中加密和解密數據。證書包含密鑰對、關于證書擁有者的信息、證書可用的開始和結束過期日期。證書同時包含公鑰和密鑰，前者用來加密，后者解密。SQL Server可以生成它自己的證書，也可以從外部文件或程序集載入。因為可以備份然后從文件中載入它們，證書比非對稱密鑰更易于移植，而非對稱密鑰卻做不到。這意味著可以在數據庫中方便地重用同一個證書。

注意：證書和非對稱密鑰同樣的消耗資源。

我們看一組例子：

示例一、創建數據庫證書

創建數據庫證書:CREATE SYMMETRIC KEY （ http://msdn.microsoft.com/en-us/library/ms187798.aspx ）

USE DB_Encrypt_Demo GO --創建證書 CREATE CERTIFICATE cert_Demo --證書名稱 ENCRYPTION BY PASSWORD = 'asdfG!!!' --加密證書的密碼 WITH SUBJECT = 'DB_Encrypt_Demo Database Encryption Certificate',--證書主題 START_DATE = '3/14/2011', EXPIRY_DATE = '10/20/2012'--起止日期 GO

示例二、查看數據庫中的證書

使用目錄視圖sys.certificates( http://msdn.microsoft.com/en-us/library/ms189774.aspx )來查看。

--查看當前數據庫中的證書 use DB_Encrypt_Demo go --查看證書 SELECT name, pvt_key_encryption_type_desc, issuer_name FROM sys.certificates ----結果返回 /* name pvt_key_encryption_type_desc issuer_name cert_Demo ENCRYPTED_BY_PASSWORD DB_Encrypt_Demo Database Encryption Certificate */

示例三、備份和還原證書

創建證書后，也可以使用BACKUP CERTIFICATE（ http://msdn.microsoft.com/en-us/library/ms178578.aspx ）命令備份到文件，為了安全地保存或在其他數據庫中還原它。

--備份證書 BACKUP CERTIFICATE cert_Demo TO FILE = 'H:/SqlBackup/certDemo.BAK'--證書備份路徑,用來加密 WITH PRIVATE KEY (FILE='H:/SqlBackup/certDemoPK.BAK',--證書私鑰文件路徑，用來解密 ENCRYPTION BY PASSWORD = '1234GH!!!',--加密私鑰密碼 DECRYPTION BY PASSWORD = 'asdfG!!!' )--解密私鑰密碼 --備份后，可以在其他數據庫中使用這個證書，或使用DROP CERTIFICATE命令刪除它。 DROP CERTIFICATE cert_Demo GO --從備份文件中還原證書到數據庫中 CREATE CERTIFICATE cert_Demo FROM FILE = 'H:/SqlBackup/certDemo.BAK' WITH PRIVATE KEY (FILE = 'H:/SqlBackup/certDemoPK.BAK', DECRYPTION BY PASSWORD = '1234GH!!!' ,--解密私鑰密碼 ENCRYPTION BY PASSWORD = 'asdfG!!!')--加密私鑰密碼

示例四、管理證書的私鑰

使用ALTER CERTIFICATE( http://msdn.microsoft.com/en-us/library/ms189511.aspx )命令為證書增加或刪除私鑰。這個命令允許刪除私鑰（默認通過數據庫主密鑰時行加密）、增加私鑰或修改私鑰的密碼。

--從證書中刪除私鑰 ALTER CERTIFICATE cert_Demo REMOVE PRIVATE KEY --從備份文件為既有證書重新增加私鑰 ALTER CERTIFICATE cert_Demo WITH PRIVATE KEY (FILE = 'H:/SqlBackup/certDemoPK.BAK', DECRYPTION BY PASSWORD = '1234GH!!!' ,--解密私鑰密碼 ENCRYPTION BY PASSWORD = 'asdfG!!!')--加密私鑰密碼 --修改既有私鑰的密碼 ALTER CERTIFICATE cert_Demo WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'asdfG!!!', ENCRYPTION BY PASSWORD = 'mynewpassword!!!13E')

示例五、使用證書加密和解密 。

使用函數EncryptByCert加密數據。( http://msdn.microsoft.com/zh-cn/library/ms174361.aspx )

下面是一個例子：

USE DB_Encrypt_Demo GO --插入測試數據 INSERT dbo.PWDQuestion (CustomerID, PasswordHintQuestion, PasswordHintAnswer) VALUES (10, '您出生的醫院名稱?', EncryptByCert(Cert_ID('cert_Demo'), '北京四合院家中')) --查看明文 SELECT CAST(PasswordHintAnswer as varchar(200)) PasswordHintAnswer FROM dbo.PWDQuestion WHERE CustomerID = 10

--查看原文 3w@live.cn SELECT PasswordHintQuestion, CAST(DecryptByCert(Cert_ID('cert_Demo'),PasswordHintAnswer, N'mynewpassword!!!13E') as varchar(200)) PasswordHintAnswer FROM dbo.PWDQuestion WHERE CustomerID = 10

示例六、使用對稱密鑰對數據進行加密和解密 。

在前面的文章中，你已經看到打開用非對稱密鑰加密的對稱密鑰的演示，它分兩個步驟，首先用OPEN SYMMETRIC KEY命令，然后是實際的DecryptByKey函數調用。SQL Server也提供了能夠將這兩個步驟合二為一的額外的解密函數：DecryptByKeyAutoAsymKey（ http://msdn.microsoft.com/en-us/library/ms365420.aspx ）和DecryptByKeyAutoCert（ http://msdn.microsoft.com/en-us/library/ms182559.aspx ）

USE DB_Encrypt_Demo GO --本例使用數據庫主密碼加密，因而不需要密碼。3w@live.cn ----Create master Key Encryption By password='123ASD!' ----go --創建非對稱密鑰 3w@live.cn CREATE ASYMMETRIC KEY asymDemo_V2 WITH ALGORITHM = RSA_512 --創建對稱密鑰 3w@live.cn CREATE SYMMETRIC KEY sym_Demo_V2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY ASYMMETRIC KEY asymDemo_V2 --打開對稱密鑰，插入記錄 OPEN SYMMETRIC KEY sym_Demo_V2 DECRYPTION BY ASYMMETRIC KEY asymDemo_V2 INSERT dbo.PWDQuestion (CustomerID, PasswordHintQuestion, PasswordHintAnswer) VALUES (22, '您出生的醫院名稱?', EncryptByKey(Key_GUID('sym_Demo_V2'), '邵逸夫醫院')) CLOSE SYMMETRIC KEY sym_Demo_V2

此時，使用DecryptByKeyAutoAsymKey解密數據，只需要一個操作

SELECT CAST(DecryptByKeyAutoAsymKey(ASYMKEY_ID('asymDemo_V2'),NULL, PasswordHintAnswer) as varchar) FROM dbo.PWDQuestion WHERE CustomerID = 22

小結：

1、本文主要介紹證書的創建、刪除、查看以及用它來修改加密方式、進行數據的加密和解密。

2、證書加密跟非對稱密鑰加密相對對稱密鑰加密更為消耗資源。

下文將主要介紹SQL Server中最為令人鼓舞的透明數據加密(TDE)

邀月注：本文版權由邀月和CSDN共同所有，轉載請注明出處。
助人等于自助! 3w@live.cn

SQL Server 2008中的代碼安全（七）：證書加密