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

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

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

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, 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'[email protected]' );  --新证书密码   '[email protected]')) --  删除测试数据   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

总结

以上是内存溢出为你收集整理的SQLServer 数据加密解密：常用的加密解密（一）全部内容，希望文章能够帮你解决SQLServer 数据加密解密：常用的加密解密（一）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。