SQLServer 数据加密解密：常用的加密解密（一）

来源：互联网发布：虎鲨软件站编辑：程序博客网时间：2024/05/17 01:54

来源：http://blog.csdn.net/kk185800961/article/details/44871603

都是基本示例，更多参考官方文档：

1. Transact-SQL 函数
2. 数据库密钥
3. 证书
4. 非对称密钥
5. 对称密钥

[sql] view plain copy
 print?
--  drop table EnryptTest  
create table EnryptTest  
(  
    id int not null primary key,  
    EnryptData nvarchar(20),  
)  
  
insert into EnryptTest  
values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');  
  
  
select * from EnryptTest;  

【Transact-SQL 函数加密】

[sql] view plain copy
 print?
/***********************************【Transact-SQL 函数加密】********************************/  
--  使用 TRIPLE DES 算法（128 密钥位长度）的通行短语加密数据。  
  
  
--  添加测试列  
alter table EnryptTest add PassPhrase varbinary(256)  
alter table EnryptTest add PassPhrase2 varbinary(256)--用于验证器验证  
  
  
--  加密(EncryptByPassPhrase)  
--  https://technet.microsoft.com/zh-cn/library/ms190357%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
update EnryptTest set PassPhrase = EncryptByPassPhrase('Hello.kk',EnryptData)  
go  
  
update EnryptTest   
set PassPhrase2 = EncryptByPassPhrase  
    (  
      'Hello.kk'            --用于生成对称密钥的通行短语  
    , EnryptData            --要加密的明文  
    , 1                     --指示是否将验证器与明文一起加密。如果将添加验证器，则为 1  
    , convert(varbinary,id) --用于派生验证器的数据(如 主键)  
    )  
go  
  
--  解密(DecryptByPassPhrase)  
--  https://technet.microsoft.com/zh-cn/library/ms188910%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
  
select convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase)) from EnryptTest;  
go  
  
select convert(nvarchar,DecryptByPassphrase(  
      'Hello.kk'    --生成解密密钥的通行短语  
    , PassPhrase2   --要解密的加密文本varbinary   
    , 1             --添加验证器  
    , convert(varbinary,id)))--验证器为主键  
from EnryptTest;  
go  
  
  
--附：未用验证器的，数据并不安全  
--如：把所有id的密码都改为与A一样，其他密码的解密与A一样，别人就有可能登录其他账号  
update EnryptTest set PassPhrase = (select PassPhrase from EnryptTest where id=4)  
go  
select id,EnryptData,convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase))   
from EnryptTest;  
  
  
--  删除测试列  
alter table EnryptTest drop column PassPhrase   
alter table EnryptTest drop column PassPhrase2  
go  

[sql] view plain copy
 print?
DECLARE @ENPWD VARBINARY(MAX)   
DECLARE @DEPWD NVARCHAR(100)  
DECLARE @ENSTR NVARCHAR(100)    
SET @ENSTR = 'hello.KK' --加密密码  
--加密  
SELECT @ENPWD = ENCRYPTBYPASSPHRASE( @ENSTR, N'13500000000')  
SELECT @ENPWD  
--解密  
SELECT @DEPWD =CAST( DECRYPTBYPASSPHRASE(@ENSTR,@ENPWD) AS NVARCHAR(MAX))  
SELECT @DEPWD  
go  

【数据库主密钥】

[sql] view plain copy
 print?
/***************************************【数据库主密钥】***********************************/  
  
select * from sys.key_encryptions  
select * from sys.crypt_properties  
  
--  创建数据库主密钥  
--  https://technet.microsoft.com/zh-cn/library/ms174382(v=sql.105).aspx  
create master key encryption by password = N'Hello@MyMasterKey' --必须符合Windows密码策略要求  
go  
  
--  打开当前数据库的数据库主密钥  
--  https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx  
open master key decryption by password = N'Hello@MyMasterKey'  
go  
  
--  更改数据库主密钥的属性  
--  https://technet.microsoft.com/zh-cn/library/ms186937(v=sql.105).aspx  
alter master key regenerate with encryption by password = N'Hello@MyMasterKey'  
  
alter master key add encryption by password = N'Hello@kk'  
alter master key drop encryption by password = N'Hello@kk'  
  
alter master key add encryption by service master key  
alter master key drop encryption by service master key  
  
--  导出数据库主密钥  
--  https://technet.microsoft.com/zh-cn/library/ms174387(v=sql.105).aspx  
backup master key   
to file = N'D:\XXDB_MasterKey'   
encryption by password = N'Hello@MyMasterKey'  
go  
  
--  从备份文件中导入数据库主密钥  
--  https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx  
restore master key   
    from file = N'D:\XXDB_MasterKey'  
    decryption by password = N'Hello@MyMasterKey'  
    encryption by password = N'Hello@MyMasterKey' --New Password  
go  
  
--  从当前数据库中删除主密钥  
--  https://technet.microsoft.com/zh-cn/library/ms180071(v=sql.105).aspx  
drop master key  
go  

【证书】

[sql] view plain copy
 print?
/*****************************************【证书】*************************************/  
--  证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据  
--  当使用数据库主密钥对私钥进行加密时，不需要 ENCRYPTION BY PASSWORD 选项。私钥使用数据库主密钥进行加密  
--（有点难理解，最后给出例子）  
  
select * from sys.key_encryptions  
select * from sys.crypt_properties  
select * from sys.certificates  
  
select * from EnryptTest  
  
--  添加测试列  
alter table EnryptTest add CertificateCol varbinary(max)  
go  
  
--  创建证书  
--  https://technet.microsoft.com/zh-cn/library/ms187798%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
create certificate Mycertificate  
encryption by password = N'Hello@Mycertificate' --加密密码  
with subject = N'EnryptData certificate',       --证书描述    
start_date = N'20150401',   --证书生效日   
expiry_date = N'20160401';  --证书到期日    
go  
  
--  使用证书的公钥加密数据  
--  https://msdn.microsoft.com/zh-cn/library/ms188061(v=sql.105).aspx  
update EnryptTest   
set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))  
go  
  
--  用证书的私钥解密数据  
--  https://msdn.microsoft.com/zh-cn/library/ms178601(v=sql.105).aspx  
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))  
from EnryptTest;  
go  
  
--  修改私钥密码   
--  https://msdn.microsoft.com/zh-cn/library/ms189511(v=sql.105).aspx  
alter certificate mycertificate   
with private key (  
    decryption by password = N'Hello@Mycertificate',   
    encryption by password = N'Hello@Mycertificate')  
go  
  
--  从证书中删除私钥   
alter certificate mycertificate remove private key  
go  
  
--  备份证书  
--  https://msdn.microsoft.com/zh-cn/library/ms178578(v=sql.105).aspx  
backup certificate mycertificate   
to file = N'D:\mycertificate.cer' --用于加密的证书备份路径  
with private key (   
    file = N'D:\mycertificate_saleskey.pvk' , --用于解密证书私钥文件路径   
    decryption by password = N'Hello@Mycertificate' ,--对私钥进行解密的密码  
    encryption by password = N'Hello@Mycertificate' );--对私钥进行加密的密码  
go  
  
--  创建/还原证书  
create certificate mycertificate   
from file = N'D:\mycertificate.cer'   
with private key (  
    file = N'D:\mycertificate_saleskey.pvk',   
    decryption by password = 'Hello@Mycertificate');  
go  
  
--  删除对称密钥  
--  https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx  
drop certificate  Mycertificate;  
go  
  
--  删除测试列  
alter table EnryptTest drop column CertificateCol;  
go  

【非对称密钥】

[sql] view plain copy
 print?
/***************************************【非对称密钥】*************************************/  
--  默认情况下，私钥受数据库主密钥保护  
  
select * from sys.key_encryptions  
select * from sys.crypt_properties  
select * from sys.certificates  
select * from sys.asymmetric_keys  
select * from sys.openkeys  
  
select * from EnryptTest  
  
--  添加测试列  
alter table EnryptTest add AsymmetricCol varbinary(max)  
go  
  
--  创建非对称密钥  
--  https://msdn.microsoft.com/zh-cn/library/ms174430(v=sql.105).aspx  
create asymmetric key MyAsymmetric   
with   
    algorithm=rsa_512   
    encryption by password='Hello@MyAsymmetric';  
go    
  
--  加密（EncryptByAsymKey）  
--  https://msdn.microsoft.com/ZH-CN/library/ms186950(v=sql.105).aspx  
update EnryptTest   
set AsymmetricCol = EncryptByAsymKey(asymkey_id ('MyAsymmetric'),convert(varchar(max ),EnryptData))    
go  
  
--  解密（DecryptByAsymKey）  
--  https://msdn.microsoft.com/ZH-CN/library/ms189507(v=sql.105).aspx  
select *,convert(varchar(max),DecryptByAsymKey(asymkey_id('MyAsymmetric'),AsymmetricCol,N'Hello@MyAsymmetric'))  
from EnryptTest  
go  
  
--  更改非对称密钥属性  
--  https://msdn.microsoft.com/zh-cn/library/ms187311(v=sql.105).aspx  
  
--  更改私钥密码  
alter asymmetric key MyAsymmetric   
    with private key (  
    decryption by password = N'Hello@MyAsymmetric',--原私钥密码  
    encryption by password = N'Hello@MyAsymmetric');--新私钥密码  
go  
  
--  删除私钥,只保留公钥  
--  如果将非对称密钥映射到 EKM 设备上的可扩展密钥管理 (EKM) 密钥并且未指定 REMOVE PROVIDER KEY 选项，  
--  则会从数据库中删除该密钥，但不会从设备上删除它。这时会发出一条警告。  
alter asymmetric key MyAsymmetric remove private key;  
go  
  
--  删除非对称密钥  
--  https://msdn.microsoft.com/ZH-CN/library/ms188389(v=sql.105).aspx  
drop symmetric key MyAsymmetric ;  
go  
  
--  删除测试列  
alter table EnryptTest drop column AsymmetricCol  
go  

【对称密钥】

[sql] view plain copy
 print?
/***************************************【对称密钥】*************************************/  
--  也称为单密钥加密,采用单钥密码系统的加密方法，同一个密钥可以同时用作信息的加密和解密.  
--  非对称密钥消耗多些系统性能，一般使用对称密钥加密数据，使用非对称密钥保护对称密钥  
  
select * from sys.key_encryptions  
select * from sys.crypt_properties  
select * from sys.certificates  
select * from sys.asymmetric_keys  
select * from sys.openkeys  
select * from sys.symmetric_keys  
  
select * from EnryptTest  
  
--  添加测试列  
alter table EnryptTest add SymmetricCol varbinary(max)  
go  
  
--  创建对称密钥  
--  https://msdn.microsoft.com/zh-cn/library/ms188357(v=sql.105).aspx  
create symmetric key MySymmetric    --以密码加密的对称密钥  
with   
    algorithm=aes_128   
    encryption by password='Hello@MySymmetric';  
go  
create symmetric key MySymmetric    --以非对称密钥加密的对称密钥  
with   
    algorithm=aes_128   
    encryption by asymmetric key MyAsymmetric  
go  
  
--  打开对称密钥(打开才能有效使用加密解密函数)  
--  https://msdn.microsoft.com/zh-cn/library/ms190499(v=sql.105).aspx  
open symmetric key MySymmetric decryption by password='Hello@MySymmetric';  
go  
open symmetric key MySymmetric decryption by asymmetric key MyAsymmetric with password='Hello@MyAsymmetric';  
go  
  
--  加密数据  
--  https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
update EnryptTest set SymmetricCol = encryptbykey(key_guid('MySymmetric'),convert(varchar(max),EnryptData))  
go  
  
--  解密数据  
--  https://technet.microsoft.com/zh-cn/library/ms181860(v=sql.105).aspx  
select *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol)))    
from EnryptTest  
go  
  
--  关闭对称密钥，或关闭在当前会话中打开的所有对称密钥  
--  https://msdn.microsoft.com/zh-cn/library/ms177938%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
--  close all symmetric keys;   
close symmetric key MySymmetric;  
go  
  
--  alter symmetric 添加或删除新的加密方式(如添加多多个密码，任何一个密码都可用)  
--  https://msdn.microsoft.com/zh-cn/library/ms189440(v=sql.105).aspx  
  
open symmetric key MySymmetric decryption by password='Hello@MySymmetric';  
  
alter symmetric key MySymmetric add encryption by password = 'Hello@kk' --New another Password  
  
close symmetric key MySymmetric;  
  
open symmetric key MySymmetric decryption by password='Hello@kk'; --Use New Password  
  
select convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTest  
  
alter symmetric key MySymmetric drop encryption by password = 'Hello@kk'--Drop the new Password  
  
close symmetric key MySymmetric;  
go  
  
--  删除对称密钥  
--  https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx  
drop symmetric key MySymmetric;  
go  
  
--  删除测试列  
alter table EnryptTest drop column SymmetricCol  
go  

【主密钥证书示例】

[sql] view plain copy
 print?
--  测试数据  
/*  
drop certificate  Mycertificate;  
go  
drop master key  
go  
drop table EnryptTest  
go  
*/  
create table EnryptTest  
(  
    id int not null primary key,  
    EnryptData nvarchar(20),  
)  
go  
  
insert into EnryptTest  
values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');  
go  
  
select * from EnryptTest;  
  
alter table EnryptTest add CertificateCol varbinary(max)  
go  
  
  
  
--创建主密钥  
create master key encryption by password = N'Hello@MyMasterKey'  
go  
/*  
key_id  thumbprint  crypt_type  crypt_type_desc             crypt_property  
------  ----------  ----------  ---------------------   ------------------  
101     0x01        ESKM        ENCRYPTION BY MASTER KEY    0x7A2FEDA8139F1DE8F3377424C120DBDB8E1F7EAAEC1BBD73E72AC04F5CEECBFAC15FC7E130CA1756281EA0D8E6997F44  
101     NULL        ESKP        ENCRYPTION BY PASSWORD      0x61D8F28F12CE4A1247F91E0ED828F2E937206E5D69B0754EE76756567AB428CBD5B54B76BCD1FC15E5E12202DBA4E187  
*/  
  
--创建证书，因为默认使用主密钥加密，此处不需要密码  
create certificate Mycertificate  
with subject = N'EnryptData certificate',  
start_date = N'20150401',  
expiry_date = N'20160401';  
go  
  
--加密解密都自动使用服务主密钥加密了。即使使用“close master key ”也不起作用  
update EnryptTest   
set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))  
go  
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))   
from EnryptTest;  
go  
  
--现在删除“服务主密钥”  
alter master key drop encryption by service master key  
go  
  
--再查询数据，没有解密出来。不自动使用主密钥加密解密了  
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))   
from EnryptTest;  
go  
  
--这时需要显式打开主密钥,使用主密钥密码加密解密  
open master key decryption by password = N'Hello@MyMasterKey'  
go  
  
--再查询数据，解密出来了。  
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))   
from EnryptTest;  
go  
  
--最后关闭主密钥  
close master key   
go  
  
--查看主密钥，少了"ENCRYPTION BY MASTER KEY",没有了主密钥进行加密,而是使用密码进行加密  
select * from sys.key_encryptions  
/*  
key_id  thumbprint  crypt_type  crypt_type_desc         crypt_property  
------  ----------  ----------  ---------------------   ------------------  
101     NULL        ESKP        ENCRYPTION BY PASSWORD  0xFB7D746C3CF0471147ECD710B1173B69A966421089FBB813BCF108E1ED90574F5C5F0998BA44F48869E05E9093BC59E6  
*/  
  
  
--删除测试数据  
drop certificate  Mycertificate;  
go  
drop master key  
go  
drop table EnryptTest  
go  
  
  
参考:  
服务主密钥:https://msdn.microsoft.com/zh-cn/library/ms189060(v=sql.90).aspx  

【证书备份还原示例】

[sql] view plain copy
 print?
--  drop table EnryptTest    
create table EnryptTest    
(    
    id int not null primary key,    
    EnryptData nvarchar(20),    
)   
go   
  
insert into EnryptTest    
values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');    
go  
  
alter table EnryptTest add CertificateCol varbinary(max)  --证书加密的列  
go    
  
select * from EnryptTest;   
  
  
--将相关信息删除  
drop certificate  Mycertificate;  
go    
drop master key    
go    
  
  
--  创建以密码加密的证书  
create certificate Mycertificate    
encryption by password = N'Hello@Mycertificate'  
with subject = N'EnryptData certificate',   
start_date = N'20150401',  
expiry_date = N'20160401';   
go    
  
  
--  证书加密数据  
update EnryptTest set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))    
go  
  
--  解密(正常)  
select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))   
from EnryptTest;    
go   
  
--  备份证书  
backup certificate mycertificate     
to file = N'D:\mycertificate.cer'    
with private key (     
    file = N'D:\mycertificate_saleskey.pvk' ,  
    decryption by password = N'Hello@Mycertificate' ,  
    encryption by password = N'Hello@Mycertificate' );  
go  
  
--  删除证书  
drop certificate  Mycertificate;  
go  
  
--  解密(失败)  
select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))   
from EnryptTest;    
go   
  
--  还原证书  
create certificate mycertificate     
from file = N'D:\mycertificate.cer'     
with private key (    
    file = N'D:\mycertificate_saleskey.pvk',     
    decryption by password = N'Hello@Mycertificate' ,    
    encryption by password = N'Hello@Hello.KK' );  --新证书密码  
go  
  
--  解密(正常)  
select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'Hello@Hello.KK')) --新证书密码  
from EnryptTest;    
go   
  
--  删除测试数据  
drop certificate  Mycertificate;  
go   
drop table EnryptTest   
go  
  
  
没有数据库主密钥情况下，使用密码加密的证书。证书直接加密解密数据，备份还原后，对之前的加密数据仍正常解密，因为备用还原都是同一个证书。而使用证书加密的对称密钥，对称密钥不能备份，删除重建后，key_guid不一样了，之前使用对称密钥加密的数据已经不能使用新的对称密钥解密了。查看select * from sys.symmetric_keys，可以看到不一样了。  

加密解密函数：https://msdn.microsoft.com/zh-cn/library/ms173744.aspx

插图2张：

阅读全文

0 0