SQLServer 数据加密解密（一）
浏览数：38 /
时间：2015年06月12日
都是基本示例，更多参考官方文档：
1. Transact-SQL 函数
2. 数据库密钥
3. 证书
4. 非对称密钥
5. 对称密钥
-- 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 函数加密】
/***********************************【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
【数据库主密钥】
/***************************************【数据库主密钥】***********************************/
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
【证书】
/*****************************************【证书】*************************************/
-- 证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据
-- 当使用数据库主密钥对私钥进行加密时，不需要 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
【非对称密钥】
/***************************************【非对称密钥】*************************************/
-- 默认情况下，私钥受数据库主密钥保护
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
【对称密钥】
/***************************************【对称密钥】*************************************/
-- 也称为单密钥加密,采用单钥密码系统的加密方法，同一个密钥可以同时用作信息的加密和解密.
-- 非对称密钥消耗多些系统性能，一般使用对称密钥加密数据，使用非对称密钥保护对称密钥
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
【示例】
-- 测试数据
/*
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
插图2张：
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
