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

发布时间：2020-12-12 13:51:31 所属栏目：MsSql教程来源：网络整理

导读：都是基本示例，更多参考官方文档： 1. Transact-SQL 函数 2.?数据库密钥 3. 证书 4. 非对称密钥 5. 对称密钥 [sql] ? view plain ?copy ? --??drop?table?EnryptTest ?? create ? table ?EnryptTest?? (?? ????id? int ? not ? null ? primary ? key ,?? ???

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

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

[sql]? view plain ?copy ?

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

【Transact-SQL 函数加密】

copy ? /***********************************【Transact-SQL?函数加密】********************************/??

--??使用?TRIPLE?DES?算法（128?密钥位长度）的通行短语加密数据。??

--??添加测试列??

alter?table?EnryptTest?add?PassPhrase?varbinary(256)??

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)?--用于派生验证器的数据(如?主键)??

????)??

--??解密(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;??

??????'Hello.kk'????--生成解密密钥的通行短语??

--要解密的加密文本varbinary???

--添加验证器??

--验证器为主键??

--附：未用验证器的，数据并不安全??

--如：把所有id的密码都改为与A一样，其他密码的解密与A一样，别人就有可能登录其他账号??

set?PassPhrase?=?(select?PassPhrase?from?EnryptTest?where?id=4)??

select?id,EnryptData,from?EnryptTest;??

--??删除测试列??

drop?column?PassPhrase???

column?PassPhrase2??

go??

copy ? 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??

【数据库主密钥】

copy ? /***************************************【数据库主密钥】***********************************/??

from?sys.key_encryptions??

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密码策略要求??

--??打开当前数据库的数据库主密钥??

--??https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx??

open?master?key?decryption?password?=?N'Hello@MyMasterKey'??

go??

--??更改数据库主密钥的属性??

--??https://technet.microsoft.com/zh-cn/library/ms186937(v=sql.105).aspx??

alter?master?key?regenerate?with?encryption?password?=?N'Hello@MyMasterKey'??

key?add?encryption?password?=?N'Hello@kk'??

drop?encryption?password?=?N'Hello@kk'??

by?service?master?key??

key??

--??导出数据库主密钥??

--??https://technet.microsoft.com/zh-cn/library/ms174387(v=sql.105).aspx??

backup?master?key???

to?file?=?N'D:XXDB_MasterKey'???

encryption?--??从备份文件中导入数据库主密钥??

restore?master?key???

????from?file?=?N'D:XXDB_MasterKey'??

????decryption? ????encryption?--New?Password??

--??从当前数据库中删除主密钥??

--??https://technet.microsoft.com/zh-cn/library/ms180071(v=sql.105).aspx??

drop?master? go??

【证书】

copy ? /*****************************************【证书】*************************************/??

--??证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据??

--??当使用数据库主密钥对私钥进行加密时，不需要?ENCRYPTION?BY?PASSWORD?选项。私钥使用数据库主密钥进行加密??

--（有点难理解，最后给出例子）??

from?sys.key_encryptions??

from?sys.crypt_properties??

from?sys.certificates??

from?EnryptTest??

--??添加测试列??

add?CertificateCol?varbinary(max)??

--??创建证书??

--??https://technet.microsoft.com/zh-cn/library/ms187798%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396??

create?certificate?Mycertificate??

encryption?password?=?N'Hello@Mycertificate'?--加密密码??

with?subject?=?N'EnryptData?certificate',???????--证书描述????

start_date?=?N'20150401',???--证书生效日???

expiry_date?=?N'20160401';??--证书到期日????

--??使用证书的公钥加密数据??

--??https://msdn.microsoft.com/zh-cn/library/ms188061(v=sql.105).aspx??

set?CertificateCol?=?EncryptByCert(CERT_ID('Mycertificate'),147); background-color:inherit">CONVERT(VARCHAR(MAX),EnryptData))??

--??用证书的私钥解密数据??

--??https://msdn.microsoft.com/zh-cn/library/ms178601(v=sql.105).aspx??

select?*,153); background-color:inherit; font-weight:bold">MAX?),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))??

--??修改私钥密码???

--??https://msdn.microsoft.com/zh-cn/library/ms189511(v=sql.105).aspx??

alter?certificate?mycertificate???

with?private?key?(??

password?=?N'Hello@Mycertificate',???

password?=?N'Hello@Mycertificate')??

--??从证书中删除私钥???

alter?certificate?mycertificate?remove?private?--??备份证书??

--??https://msdn.microsoft.com/zh-cn/library/ms178578(v=sql.105).aspx??

backup?certificate?mycertificate???

to?file?=?N'D:mycertificate.cer'?--用于加密的证书备份路径??

key?(???

????file?=?N'D:mycertificate_saleskey.pvk'?,?--用于解密证书私钥文件路径???

password?=?N'Hello@Mycertificate'?,--对私钥进行解密的密码??

password?=?N'Hello@Mycertificate'?);--对私钥进行加密的密码??

--??创建/还原证书??

create?certificate?mycertificate???

from?file?=?N'D:mycertificate.cer'???

????file?=?N'D:mycertificate_saleskey.pvk',248); line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important; list-style-position:outside!important"> ????decryption?password?=?'Hello@Mycertificate');??

--??删除对称密钥??

--??https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx??

drop?certificate??Mycertificate;??

column?CertificateCol;??

【非对称密钥】

copy ? /***************************************【非对称密钥】*************************************/??

--??默认情况下，私钥受数据库主密钥保护??

from?sys.asymmetric_keys??

from?sys.openkeys??

add?AsymmetricCol?varbinary(--??创建非对称密钥??

--??https://msdn.microsoft.com/zh-cn/library/ms174430(v=sql.105).aspx??

create?asymmetric?key?MyAsymmetric???

with???

????algorithm=rsa_512???

????encryption?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'),147); background-color:inherit">convert(varchar(max?),EnryptData))????

--??解密（DecryptByAsymKey）??

--??https://msdn.microsoft.com/ZH-CN/library/ms189507(v=sql.105).aspx??

max),DecryptByAsymKey(asymkey_id('MyAsymmetric'),AsymmetricCol,N'Hello@MyAsymmetric'))??

from?EnryptTest??

--??更改非对称密钥属性??

--??https://msdn.microsoft.com/zh-cn/library/ms187311(v=sql.105).aspx??

--??更改私钥密码??

alter?asymmetric? ????key?(??

password?=?N'Hello@MyAsymmetric',0); background-color:inherit">--原私钥密码??

password?=?N'Hello@MyAsymmetric');--新私钥密码??

--??删除私钥,只保留公钥??

--??如果将非对称密钥映射到?EKM?设备上的可扩展密钥管理?(EKM)?密钥并且未指定?REMOVE?PROVIDER?KEY?选项，??

--??则会从数据库中删除该密钥，但不会从设备上删除它。这时会发出一条警告。??

key?MyAsymmetric?remove?private?key;??

--??删除非对称密钥??

--??https://msdn.microsoft.com/ZH-CN/library/ms188389(v=sql.105).aspx??

drop?symmetric?key?MyAsymmetric?;??

--??删除测试列??

column?AsymmetricCol??

go??

【对称密钥】

copy ? /***************************************【对称密钥】*************************************/??

--??也称为单密钥加密,采用单钥密码系统的加密方法，同一个密钥可以同时用作信息的加密和解密.??

--??非对称密钥消耗多些系统性能，一般使用对称密钥加密数据，使用非对称密钥保护对称密钥??

from?sys.certificates??

from?sys.asymmetric_keys??

from?sys.openkeys??

from?sys.symmetric_keys??

add?SymmetricCol?varbinary(--??创建对称密钥??

--??https://msdn.microsoft.com/zh-cn/library/ms188357(v=sql.105).aspx??

create?symmetric?key?MySymmetric????--以密码加密的对称密钥??

????algorithm=aes_128???

password='Hello@MySymmetric';??

--以非对称密钥加密的对称密钥??

with???

????algorithm=aes_128???

by?asymmetric?key?MyAsymmetric??

--??打开对称密钥(打开才能有效使用加密解密函数)??

--??https://msdn.microsoft.com/zh-cn/library/ms190499(v=sql.105).aspx??

open?symmetric?key?MySymmetric?decryption?key?MyAsymmetric?with?--??加密数据??

--??https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396??

set?SymmetricCol?=?encryptbykey(key_guid('MySymmetric'),0); background-color:inherit">--??解密数据??

--??https://technet.microsoft.com/zh-cn/library/ms181860(v=sql.105).aspx??

max?)?,147); background-color:inherit">convert?(--??关闭对称密钥，或关闭在当前会话中打开的所有对称密钥??

--??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;??

--??alter?symmetric?添加或删除新的加密方式(如添加多多个密码，任何一个密码都可用)??

--??https://msdn.microsoft.com/zh-cn/library/ms189440(v=sql.105).aspx??

password='Hello@MySymmetric';??

alter?symmetric?key?MySymmetric?password?=?'Hello@kk'?--New?another?Password??

password='Hello@kk';?--Use?New?Password??

password?=?'Hello@kk'--Drop?the?new?Password??

key?MySymmetric;??

column?SymmetricCol??

go??

【主密钥证书示例】

copy ? --??测试数据??

/*??

table?EnryptTest??

*/??

into?EnryptTest??

'A');??

--创建主密钥??

/*??

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??

expiry_date?=?N'20160401';??

--加密解密都自动使用服务主密钥加密了。即使使用“close?master?key?”也不起作用??

--现在删除“服务主密钥”??

--再查询数据，没有解密出来。不自动使用主密钥加密解密了??

--这时需要显式打开主密钥,使用主密钥密码加密解密??

--再查询数据，解密出来了。??

--最后关闭主密钥??

close?master?--查看主密钥，少了"ENCRYPTION?BY?MASTER?KEY",没有了主密钥进行加密,而是使用密码进行加密??

key_id??thumbprint??crypt_type??crypt_type_desc?????????crypt_property??

101?????PASSWORD??0xFB7D746C3CF0471147ECD710B1173B69A966421089FBB813BCF108E1ED90574F5C5F0998BA44F48869E05E9093BC59E6??

--删除测试数据??

参考:??

服务主密钥:https://msdn.microsoft.com/zh-cn/library/ms189060(v=sql.90).aspx??

【证书备份还原示例】

copy ? --??drop?table?EnryptTest????

table?EnryptTest????

(????

)???

go???

into?EnryptTest????

'A');????

max)??--证书加密的列??

from?EnryptTest;???

--将相关信息删除??

drop?certificate??Mycertificate;??

go????

key????

--??创建以密码加密的证书??

create?certificate?Mycertificate????

password?=?N'Hello@Mycertificate'??

start_date?=?N'20150401',108); list-style-type:decimal-leading-zero; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important; list-style-position:outside!important"> expiry_date?=?N'20160401';???

--??证书加密数据??

--??解密(正常)??

'Mycertificate'),N'Hello@Mycertificate'))???

from?EnryptTest;????

go???

--??备份证书??

backup?certificate?mycertificate?????

to?file?=?N'D:mycertificate.cer'????

key?(?????

password?=?N'Hello@Mycertificate'?);??

--??删除证书??

--??解密(失败)??

--??还原证书??

create?certificate?mycertificate?????

from?file?=?N'D:mycertificate.cer'?????

key?(????

????file?=?N'D:mycertificate_saleskey.pvk',?????

password?=?N'Hello@Hello.KK'?);??--新证书密码??

'Hello@Hello.KK'))?--??删除测试数据??

table?EnryptTest???

没有数据库主密钥情况下，使用密码加密的证书。证书直接加密解密数据，备份还原后，对之前的加密数据仍正常解密，因为备用还原都是同一个证书。而使用证书加密的对称密钥，对称密钥不能备份，删除重建后，key_guid不一样了，之前使用对称密钥加密的数据已经不能使用新的对称密钥解密了。查看from?sys.symmetric_keys，可以看到不一样了。??

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

插图2张：

附录：

DECRYPTBYCERT (Transact-SQL)

其他版本

用证书的私钥解密数据。

主题链接图标

?Transact-SQL 语法约定

语法

DecryptByCert ( certificate_ID,{ 'ciphertext' | @ciphertext } 
    [,{ 'cert_password' | @cert_password } ] )

参数

certificate_ID

数据库中证书的 ID。certificate_ID 的数据类型为?int。

ciphertext

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

@ciphertext

包含已使用证书进行加密的数据的?varbinary?类型变量。

cert_password

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

@cert_password

类型为?nchar?或?nvarchar?的变量，其中包含用来加密证书私钥的密码。必须为 Unicode 字符。

返回类型

最大大小为 8,000 个字节的?varbinary。

注释

此函数用证书的私钥解密数据。使用非对称密钥进行的加密转换会消耗大量资源。因此，EncryptByCert 和 DecryptByCert 不适合用于对用户数据的例行加密。

权限

需要对证书具有 CONTROL 权限。

示例

下面的示例从?[AdventureWorks2008R2].[ProtectedData04]?中选择标记为?data encrypted by certificate JanainaCert02?的行。此示例使用证书?JanainaCert02?的私钥对密码进行解密，首次解密时使用的是证书的密码?pGFD4bb925DGvbd2439587y。解密后的数据将从?varbinary?转换为?nvarchar。

SELECT convert(nvarchar(max),DecryptByCert(Cert_Id('JanainaCert02'),ProtectedData,N'pGFD4bb925DGvbd2439587y'))
FROM [AdventureWorks2008R2].[ProtectedData04] 
WHERE Description 
    = N'data encrypted by certificate '' JanainaCert02''';
GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!