加密是为了保证敏感数据的安全性,如用户密码,银行卡密码等信息。
加密是为了保证敏感数据的安全性,如用户密码,银行卡密码等信息。
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;
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;
![](https://images.cnblogs.com/cnblogs_com/itboyblog/1.jpg)
Point:结果中可以看出sha,sha1这两种加密的结果一样的。HashBytes加密是不可逆的,也就是说只能修改,但是不能找回加密前的值。
varchar 类型字符,与nvarchar类型的字符返回的Hash值是不一样的(理由:占用字节不一样)如:
select HashBytes('md5','SQL Server 2005') as md5varchar, HashBytes('md5',N'SQL Server 2005') as md5nvarchar;
select HashBytes('md5','SQL Server 2005') as md5varchar, HashBytes('md5',N'SQL Server 2005') as md5nvarchar;
![](https://images.cnblogs.com/cnblogs_com/itboyblog/2.jpg)
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
--填充数据
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
![](https://images.cnblogs.com/cnblogs_com/itboyblog/3.jpg)
--解密
select CreditCardID,CardNumber,PhraseNumber,
convert(nvarchar,DecryptByPassphrase('phrasetext',PhraseNumber,1,convert(varbinary(30),CreditCardID))) as "Decrypted card number"
from dbo.t2;
go
![](https://images.cnblogs.com/cnblogs_com/itboyblog/4.jpg)
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
--创建自我签名的证书,证书的一种,该例主要讲述加密,有关其他证书类型关注以后的文章
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
![](https://images.cnblogs.com/cnblogs_com/itboyblog/5.jpg)
--查看解密后结果
select top (10) CardNumber as '原始卡号',
convert(nvarchar(30), DecryptByCert(Cert_Id('cer001'),CerEncryptionENumber,N'!@#$%^&*')) as '解密后'
from dbo.t3;
go
![](https://images.cnblogs.com/cnblogs_com/itboyblog/6.jpg)
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
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
![](https://images.cnblogs.com/cnblogs_com/itboyblog/7.jpg)
--查看解密后数据
select CardNumber, convert(nvarchar(30),DecryptByAsymKey(AsymKey_Id('asym_key_001'), AsymKeyEncryptionENumber, N'!@#$%^&*'
)) as DecryptedData
from dbo.t4;
go
![](https://images.cnblogs.com/cnblogs_com/itboyblog/8.jpg)
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
--使用 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
![](https://images.cnblogs.com/cnblogs_com/itboyblog/9.jpg)
--查看解密后数据
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
![](https://images.cnblogs.com/cnblogs_com/itboyblog/10.jpg)