SQL Server 2005 数据加密

加密是为了保证敏感数据的安全性，如用户密码，银行卡密码等信息。

1.最简单的加密方式就是对单个字符，某列的加密，利用HashBytes 返回输入的哈希值。语法:HashBytes('加密类型','加密文本') 返回值最大为varbinary(8000)

以下示例前提为已安装AdventureWorks示例数据库.
--创建示例表，填充数据
set nocount on;
select CreditCardID,CardNumber into dbo.t1 from Sales.CreditCard;
go
--添加列，以便存储对卡号加密后的Hash值
alter table dbo.t1 add md2Number varbinary(20) null;
alter table dbo.t1 add md4Number varbinary(20) null;
alter table dbo.t1 add md5Number varbinary(20) null;
alter table dbo.t1 add shaNumber varbinary(20) null;
alter table dbo.t1 add sha1Number varbinary(20) null;
go
--加密
update dbo.t1 set
    md2Number = HashBytes('md2',CardNumber),
    md4Number = HashBytes('md4',CardNumber),
    md5Number = HashBytes('md5',CardNumber),
    shaNumber = HashBytes('sha',CardNumber),
    sha1Number = HashBytes('sha1',CardNumber);
go
select  top (10) *  from dbo.t1;

Point：结果中可以看出sha,sha1这两种加密的结果一样的。HashBytes加密是不可逆的，也就是说只能修改，但是不能找回加密前的值。

varchar 类型字符,与nvarchar类型的字符返回的Hash值是不一样的(理由：占用字节不一样)如：
select HashBytes('md5','SQL Server 2005') as md5varchar, HashBytes('md5',N'SQL Server 2005') as md5nvarchar;

 

2.通行短语加密 优点可以解密，安全性比Hash加密更高，缺点,如果忘了通行短语就没救了
--填充数据
set nocount on;
select CreditCardID,CardNumber into dbo.t2 from Sales.CreditCard;
go
--添加存储加密后的列
alter table dbo.t2 add PhraseNumber varbinary(256) null;
go
--加密
update dbo.t2 set PhraseNumber = EncryptByPassPhrase('phrasetext',CardNumber,1,convert(varbinary(30),CreditCardID));
go
--查看加密后的结果
select * from dbo.t2;
go
--解密
select CreditCardID,CardNumber,PhraseNumber,
    convert(nvarchar,DecryptByPassphrase('phrasetext',PhraseNumber,1,convert(varbinary(30),CreditCardID))) as "Decrypted card number"
from dbo.t2;
go

 
 
3, 证书公钥加密  优点：密码更复杂，安全， 缺点：开销大,还得维护证书
--创建自我签名的证书，证书的一种，该例主要讲述加密，有关其他证书类型关注以后的文章
create certificate cer001
    encryption by password = N'!@#$%^&*'
    with subject = 'cer_encryption',
    start_date = '20111101',
    expiry_date = '20120101';
go

--填充数据
set nocount on;
select CreditCardID,CardNumber into dbo.t3 from Sales.CreditCard;
go
--添加存储加密后的列
alter table dbo.t3 add CerEncryptionENumber varbinary(256) null;
go
--查看未加密结果
select * from dbo.t3;
go
--加密
update dbo.t3 set CerEncryptionENumber = EncryptByCert(Cert_ID('cer001'),CardNumber);
go
--查看加密后结果
select * from dbo.t3;
go
--查看解密后结果
select top (10) CardNumber as '原始卡号',
    convert(nvarchar(30), DecryptByCert(Cert_Id('cer001'),CerEncryptionENumber,N'!@#$%^&*')) as '解密后'
from dbo.t3;
go

 

 

 

4 非对称密钥加密,解密  缺点：开销大,不要在处理大型数据集使使用
create asymmetric key  asym_key_001
with algorithm = RSA_2048 
encryption by password = N'!@#$%^&*';
go
--填充数据
set nocount on;
select CreditCardID,CardNumber into dbo.t4 from Sales.CreditCard;
go
--添加存储加密后的列
alter table dbo.t4 add AsymKeyEncryptionENumber varbinary(256) null;
go
--查看未加密结果
select * from dbo.t4;
go
--加密
update dbo.t4 set AsymKeyEncryptionENumber = EncryptByAsymKey(AsymKey_ID('asym_key_001'),CardNumber);
go
--查看加密后结果
select * from dbo.t4;
go
--查看解密后数据
select CardNumber, convert(nvarchar(30),DecryptByAsymKey(AsymKey_Id('asym_key_001'), AsymKeyEncryptionENumber, N'!@#$%^&*' 

)) as DecryptedData 
from dbo.t4;
go

 
 
5 对称密钥加密,解密  指定的算法必须在操作系统中,加密，解密速度快， 解密语法简单
--使用 DESX 算法创建名为 sym_key_001 的对称密钥，然后使用证书 cer001 对新密钥进行加密。
create symmetric key  sym_key_001
with algorithm = DESX
encryption by certificate cer001;
go
--填充数据
set nocount on;
select CreditCardID,CardNumber into dbo.t5 from Sales.CreditCard;
go
--添加存储加密后的列
alter table dbo.t5 add SymKeyEncryptionENumber varbinary(256) null;
go
--查看未加密结果
select * from dbo.t5;
go
--加密
open symmetric key  sym_key_001 decryption by certificate cer001 with password = N'!@#$%^&*';
update dbo.t5 set SymKeyEncryptionENumber = EncryptByKey(Key_GUID('sym_key_001'),CardNumber);
go
--查看加密后结果
select * from dbo.t5;
go
--查看解密后数据
open symmetric key  sym_key_001 decryption by certificate cer001 with password = N'!@#$%^&*';
select CardNumber, SymKeyEncryptionENumber,convert(nvarchar(30),DecryptByKey(SymKeyEncryptionENumber)) as DecryptedData 
from dbo.t5;
go