下面咱们来看看使用证书对数据进行加密
-----------------证书对数据加密---------------
1)生成自签名证书
--使用指定口令保护私钥
create certificate cert1 encryption by password='DUfei2008' with subject='test', start_date='01/01/2009',expiry_date='01/01/2010'
--使用数据库主密钥保护私钥
create certificate cert2 with subject='test', start_date='01/01/2009',expiry_date='01/01/2010'
2) 查看证书信息
select * from sys.certificates
3)修改私钥的口令
alter certificate cert1 with private key (decryption by password='DUfei2008',encryption by password='DUfei2008')
4)备份证书
backup certificate cert1 to file='c:\bak\mycert.cer' with private key (decryption by password='DUfei2008', file='c:\bak\mypvt',encryption by password='DUfei2008')
5)删除私钥
alter certificate cert1 remove private key
6)增加私钥
alter certificate cert1 with private key (file='c:\bak\mypvt', decryption by password='DUfei2008', encryption by password='DUfei2008')
7)删除证书
drop certificate cert1
8) 还原证书
create certificate cert1 from file='c:\bak\mycert.cer' with private key (file='c:\bak\mypvt' , decryption by password='DUfei2008', encryption by password='DUfei2008')
接下来可以使用下面的小脚本来体验一下加密的结果。
declare @atext varchar(100),@acipher varbinary(max)set @atext='dufei hao!' set @acipher=encryptbycert(cert_id('cert1'),@atext) select @acipher select cast(decryptbycert(cert_id('cert1'),@acipher,N'DUfei2008') as varchar(200) )
以上介绍的是SQL Server中实现数据加密过程,至于非对称加密、对称加密、混合加密的操作过程基本相同,甚至在操作过程还可以引入验证因子防止绕过数据加密的攻击等,使用好数据加密功能,就不用再担心数据丢失问题了,安全无小事,时刻不能大意。
更多精彩尽在2014年4月10日-12日在北京五洲皇冠国际酒店举办的第五届中国数据库技术大会,2月29日之前订票可享受7.8折最低票价。