ymmetric'),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