Sql Server数据库实现表中字段的列加密_MsSqlserver

1、问题描述

去年6月份的时候做过一个系统，要对里面的一些敏感字段进行一下加密。sqlserver列加密可以参考官方文档：sql server 技术文档 - sql server | microsoft learn。主要看下来有三种加密方法：1、利用证书对数据进行加密和解密。2、利用非对称密钥对数据进行加密和解密。3、利用对称密钥对数据进行加密和解密。

2、加密方法介绍

2.1、证书加密（推荐）

2.1.1、创建证书

use mydb;

--查看数据库中的证书
select * from sys.certificates;

--创建数据库主密钥
create master key encryption by password ='123@#456';

--创建证书
create certificate mycert
with subject = 'my test certificate'
go

--创建证书并授权给指定用户(zhangsan)
create certificate mycerttozhangsan authorization zhangsan 
with subject = 'my test certificate to zhangsan'
go

2.1.2、使用证书加解密

创建一个测试表

--创建测试表tuser_test_cert，要对pwd字段进行加密因此其字段类型设为varbinary
--varbinary表示可变长度二进制数据，但是在sqlserver表存放是以十六进制存的。
create table tuser_test_cert(username varchar(50),pwd varbinary(2000));

在2.1.1环节一共建了两个证书，mycert这个证书是公用的，而mycerttozhangsan这个证书是仅限zhangsan这个用户可以使用！

使用mycert证书加解密

--使用mycert证书加密pwd字段
insert into tuser_test_cert(username,pwd) values('liming',
		encryptbycert(
		cert_id('mycert')
		,'112233'
	)
);

select * from tuser_test_cert;

--使用mycert证书解密pwd字段
select username,convert(
varchar(100),
decryptbycert
	(
	cert_id('mycert'),pwd
	)
) as pwd from tuser_test_cert;

使用mycerttozhangsan证书进行加解密

因为我是对mydb数据库进行证书加解密嘛！因此首先要确保mydb下是否有zhangsan这个用户！有了zhangsan这个用户才能创建证书并对其authorization

有个地方要提一下，这里的zhangsan这个用户是在mydb下的，并不是直接在mydb下直接手动创建的，而是整个sqlserver登录名zhangsan映射到mydb的一个用户映射！

其实就是在你新建数据库登录名的时候做的一个用户映射，相当于授权这个登录名可以有哪些数据库的权限了！

不过此时mydb新加的zhangsan这个用户还没有给他分配角色（所谓角色就是权限集合）

--创建一个角色zhangsan_role
create role zhangsan_role;
--给zhangsan_role这个角色分配tuser_test_cert表的增删改查权限
grant select,update,insert,delete on tuser_test_cert to zhangsan_role;

在mydb里用户选择zhangsan右键属性，在成员身份里选中zhangsan_role就行了！

--使用mycerttozhangsan证书加密pwd字段
insert into tuser_test_cert(username,pwd) values('zhangsan',
		encryptbycert(
		cert_id('mycerttozhangsan')
		,'666333'
	)
);

select * from tuser_test_cert;

--使用mycert证书解密pwd字段
select username,convert(
varchar(100),
decryptbycert
	(
	cert_id('mycerttozhangsan'),pwd
	)
) as pwd from tuser_test_cert;

2.2、非对称密钥加解密

----利用非对称密钥对数据进行加密和解密---

--查看数据库中的非对称密钥
select * from sys.asymmetric_keys;

--创建非对称密钥
create asymmetric key mytestasymmetric
    with algorithm = rsa_2048
    encryption by password = '%^&123%456';
go

insert into tuser_test_cert(username,pwd) values('ssan',
		encryptbyasymkey(
		asymkey_id('mytestasymmetric')
		,'112233'
		)
);

select * from tuser_test_cert;

select username,convert(
varchar(100),
decryptbyasymkey
	(
	asymkey_id('mytestasymmetric'),pwd,n'%^&123%456'
	)
) as pwd from tuser_test_cert;

2.3、对称密钥加解密

----利用对称密钥对数据进行加密和解密----

--查看数据库中的对称密钥
select * from sys.symmetric_keys;

--创建对称密钥
create symmetric key mytestsymmetric
    with algorithm = aes_256
    encryption by password = '123456@aes';
go
--打开密钥并进行加密/解密（只有对称加密需打开密钥！！！）
open symmetric key mytestsymmetric decryption by password='123456@aes';

insert into tuser_test_cert(username,pwd) values('qianqian',
		encryptbykey(key_guid('mytestsymmetric'), 'qw123sa')
);

select * from tuser_test_cert;

select username,convert(
varchar(100),
decryptbykey(pwd) 
) as pwd from tuser_test_cert