SQLServer 2008中的代碼安全(七) 證書加密

證書可以在數據庫中加密和解密數據。證書包含密鑰對、關于證書擁有者的信息、證書可用的開始和結束過期日期。

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

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

我們看一組例子:

示例一、創建數據庫證書

創建數據庫證書:CREATE SYMMETRIC KEY ()
代碼如下:
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()來查看。

代碼如下:
–查看當前數據庫中的證書
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()命令備份到文件,為了安全地保存或在其他數據庫中還原它。
代碼如下:
–備份證書
BACKUP CERTIFICATE cert_Demo
TO FILE = ‘H:SqlBackupcertDemo.BAK’–證書備份路徑,用來加密
WITH PRIVATE KEY (FILE=’H:SqlBackupcertDemoPK.BAK’,–證書私鑰文件路徑,用來解密
ENCRYPTION BY PASSWORD = ‘1234GH!!!’,–加密私鑰密碼
DECRYPTION BY PASSWORD = ‘asdfG!!!’ )–解密私鑰密碼

–備份后,可以在其他數據庫中使用這個證書,或使用DROP CERTIFICATE命令刪除它。
DROP CERTIFICATE cert_Demo
GO
–從備份文件中還原證書到數據庫中
CREATE CERTIFICATE cert_Demo
FROM FILE = ‘H:SqlBackupcertDemo.BAK’
WITH PRIVATE KEY (FILE = ‘H:SqlBackupcertDemoPK.BAK’,
DECRYPTION BY PASSWORD = ‘1234GH!!!’ ,–解密私鑰密碼
ENCRYPTION BY PASSWORD = ‘asdfG!!!’)–加密私鑰密碼

示例四、管理證書的私鑰

使用ALTER CERTIFICATE( )命令為證書增加或刪除私鑰。這個命令允許刪除私鑰(默認通過數據庫主密鑰時行加密)、增加私鑰或修改私鑰的密碼。
代碼如下:
–從證書中刪除私鑰
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY

–從備份文件為既有證書重新增加私鑰
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE = ‘H:SqlBackupcertDemoPK.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加密數據。()
代碼如下:
–從證書中刪除私鑰
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY

–從備份文件為既有證書重新增加私鑰
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE = ‘H:SqlBackupcertDemoPK.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’)

下面是一個例子:
代碼如下:
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()和DecryptByKeyAutoCert()
代碼如下:
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)

? 版權聲明
THE END
喜歡就支持一下吧
點贊9 分享