证书可以在数据库中加密和解密数据。证书包含密钥对、关于证书拥有者的信息、证书可用的开始和结束过期日期。证书同时包含公钥和密钥,前者用来加密,后者解密。SQL Server可以生成它自己的证书,也可以从外部文件或程序集载入。因为可以备份然后从文件中载入它们,证书比非对称密钥更易于移植,而非对称密钥却做不到。这意味着可以在数据库中方便地重用同一个证书。注意:证书和非对称密钥同样的消耗资源。
我们看一组例子:
-- 创建证书
CREATE CERTIFICATE TestCertificate
ENCRYPTION BY PASSWORD = 'qljs'
WITH SUBJECT = ' TestCertificate',
START_DATE='2011-9-1',
EXPIRY_DATE = '2011-12-31';
--创建存储表
CREATE TABLE testable
(DecryptValue varchar(4000)
,EncryptValue varbinary(8000))
-- 加密
INSERT
INTO testable (EncryptValue)
values(EncryptByCert(Cert_ID('TestCertificate'),'张英瀚'))
--显示存储表信息
select * from testable
--未加密码无法解密信息
select cast(DecryptByCert(Cert_ID('TestCertificate'),EncryptValue) as varchar(4000)) as DecryptValue from testable
--加上密码后可以解密信息
select cast(DecryptByCert(Cert_ID('TestCertificate'),EncryptValue,N'qljs') as varchar(4000)) as DecryptValue from testable
-- 解密
UPDATE testable
SET DecryptValue = DecryptByCert(Cert_ID('TestCertificate'),EncryptValue,N'qljs')
--显示存储表信息
select * from testable
-- 修改证书解密密码
ALTER CERTIFICATE TestCertificate
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = 'qljs',
ENCRYPTION BY PASSWORD = 'qljs@qljs@qljs'
)
--旧密码无法解密信息
select cast(DecryptByCert(Cert_ID('TestCertificate'),EncryptValue,N'qljs') as varchar(4000)) as DecryptValue from testable
--新密码可以解密信息
select cast(DecryptByCert(Cert_ID('TestCertificate'),EncryptValue,N'qljs@qljs@qljs') as varchar(4000)) as DecryptValue from testable
-- 删除相关密钥证书
DROP TABLE testable
DROP CERTIFICATE TestCertificate