SQL Server对称与非对称密钥加密字段

恰饭广告

创建表如下：

注意：需要加密的字段必须是varbinary。

对称密钥加密：

--创建master Key，设置密钥密码
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
--创建证书，设置有效期
 CREATE CERTIFICATE loginCert
 WITH SUBJECT = 'loginTest',EXPIRY_DATE = '12/31/9999';
 --建立对称key
  CREATE SYMMETRIC KEY loginKey
 WITH ALGORITHM = TRIPLE_DES
 ENCRYPTION BY CERTIFICATE loginCert;
 --使用一个对称密钥前必须打开它
 OPEN SYMMETRIC KEY loginKey
 DECRYPTION BY CERTIFICATE loginCert;
 --插入数据
insert into users values(1,'daobin', EncryptByKey(Key_GUID('loginKey'), '123456'))
insert into users values(2,'test', EncryptByKey(Key_GUID('loginKey'), '123456'))
--查询加密数据
select * from users
--查询解密数据
select username, Cast(DecryptByKey(password) as varchar(10)) FROM users
--删除以上操作
 delete from users;
 CLOSE SYMMETRIC KEY loginKey
 DROP SYMMETRIC KEY loginKey
 DROP CERTIFICATE loginCert
 DROP MASTER KEY

实现效果：

非对称密钥加密：

--创建asymmetric key 设置密钥
create asymmetric key loginKey
with algorithm =rsa_512
encryption by password='p@ssw0rd'
--插入数据
insert into users values (1,'daobin',EncryptByAsymKey(AsymKey_ID('loginKey'),'123456'))
insert into users values (2,'test',EncryptByAsymKey(AsymKey_ID('loginKey'),'654321'))
--查询未解密数据
select * from users
--查询解密数据
select username,Convert( varchar(20), DecryptByAsymKey (AsymKey_ID ( 'loginKey'), password, N'p@ssw0rd'))as password from users
--删除以上操作
drop ASYMMETRIC KEY loginKey
delete from users

注意：使用RSA512算法，另外还有RSA1024和RSA2048，强度更高

实现效果：