在数据库中用证书加密数据

版权声明：原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://boyi55.blog.51cto.com/4345/62054

下午在某个数据库群中无意中说到SQL 2005的证书。本来以前证书只是用来在传输数据的时候起到加密作用。交流中发现还有其它好的用处，在次感谢群中数位网友。
在mysql中有encode和decode函数可以通过自己设定的密钥来加密数据库的表的某些列来达到数据安全的目的。在目前的SQL 2005中应该怎么做呢。无意中网友说可以用证书实现。下面是具体的实现代码：

--创建主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'fengjicai'

--用密码fengjicai创建证书boyi
CREATE CERTIFICATE boyi
   ENCRYPTION BY PASSWORD = 'fengjicai'
   WITH SUBJECT = 'boyi55 test certificate',
    start_date='02/08/2008',
   EXPIRY_DATE = '02/08/2009';
GO
--建测试表，name字段为要加密的列，数据类型要为varbinary
--因为加密后的数据是二进制数据
create table testB(id int identity(1,1),name varbinary(5000))

--向测试表中写入一条测试数据
insert into testB(name)
select encryptbycert(cert_id('boyi'),'boyi55')
/*
附：encryptbycert函数用法

EncryptByCert ( certificate_ID , { 'cleartext' | @cleartext } )

certificate_ID
    数据库中证书的 ID。

cleartext
    将使用证书进行加密的数据字符串。

@cleartext
    类型为 nvarchar、char、wchar、varchar 或 nchar 的变量，其中包含将使用证书的公钥进行加密的数据。

返回类型
Varbinary，最大大小为 8000 个字节。

*/
--提取加密后数据
SELECT  id, cast(DecryptByCert(Cert_Id('boyi'),
    name, N'fengjicai')as varchar(20)) from testb
/*
说明：decryptbycert函数的返回类型为varbinary，所以要将二进制转化为原始类型。
name为字段名，此处也必须是二进制类型。如果不是要做相应转换。
附：decryptbycert函数用法
DecryptByCert (
                            certificate_ID ,
              { 'ciphertext' | @ciphertext }
              [ , { 'cert_password' | @cert_password } ]
              )

certificate_ID
    数据库中的证书的 ID。

ciphertext
    已用证书的公钥加密的数据的字符串。

@ciphertext
    类型为 varbinary 的变量，其中包含已用证书加密的数据。

cert_password
    用来加密证书私钥的密码。必须为 Unicode 字符。

@cert_password
    包含密码的变量，该密码用来加密证书的私钥。必须为 Unicode 字符。

*/

此加密比较安全，每次加密后的二进制数据也是不一样的。但会消耗大量的资源，不适合在大数据量的环境下部署。另外加密后的数据量也比较大，如果加密内容太多则数据库的增长会更加明显。