DB encrypt

/*      通过SQL Server2005提供的EncryptByKey 和DecryptByKey系统函数对数据进行加解密

1.创建主密钥 2.创建证书        3.创建对称密钥         4.开启对称密钥         5.加密,解密

*/

use pubs

go

--1.创建主密钥master key

create master key encryption by password='xumh&swp2mao'

--2.创建证书foo

create certificate foo

-- encryption by password='xumh&swp2mao'

-- 若是不用密码就需要先设置Database Master Key

with subject=N'测试加解密', expiry_date='10/07/2007';

--3.根据证书foo,创建'对称密钥' fookey

create symmetric key fookey with algorithm=des encryption by certificate foo

go

--4.开启对称密钥fookey; 若是不开启symmetric key, EncryptByKey函数将返回NULL

open symmetric key fookey decryption by certificate foo --with password='xumh&swp2mao'

--5.创建测试表

create table tblEnc(c1 int identity(1,1) primary key, c2 nvarchar(100), c3 varbinary(200) )

go

--6.加密和解密

insert tblEnc values( N'测试加密', encryptbykey(key_guid('fookey'),N'测试加密'))

select * from tblEnc

select c2,解密后=cast(decryptbykey(c3) as nvarchar(100)) from tblEnc

go

--用完后关闭密钥

close symmetric key fookey

drop table tblEnc

drop symmetric key fookey

drop certificate foo

drop master key

go