SqlServer数据库实现表中字段的列加密，
目录
- 1、问题描述
- 2、加密方法介绍
- 2.1、证书加密（推荐）
- 2.2、非对称密钥加解密
- 2.3、对称密钥加解密
- 3、总结
- 4、参考资料
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
3、总结
Sqlserver数据库列字段加密推荐使用证书加密，使用证书加密是对特定的账号授予他们使用证书加解密的权限，他们只是用了证书的cert_id，并不会涉及password密钥。但如果使用其他方法加密的话，比如AES，RSA的话，那么在加解密的时候会把密钥暴露给开发人员
所以就不太安全！用证书的话就不会把密码暴露给开发人员，只暴露给特定的账户！
4、参考资料
创建数据库主密钥 - SQL Server | Microsoft Learn
通俗易懂的对称加密与非对称加密原理浅析 - 掘金
SQL Server 数据库对称加密_QAQ_的博客-CSDN博客
AES加密算法的详细介绍与实现_TimeShatter的博客-CSDN博客_aes
SQLSERVER加密解密函数(非对称密钥 证书加密 对称密钥)_weixin_33877092的博客-CSDN博客
获取所有用户名 sql server_jgwei的博客-CSDN博客_sqlserver 查看数据库所有用户名
[转]【SQLServer】获取SQL Server数据库用户名、数据库名、所有表名、所有字段名_liujzss的博客-CSDN博客
SQL Server 创建登录名和用户名【详细介绍】_Henry_626的博客-CSDN博客_sqlserver新建登录名
到此这篇关于Sql Server数据库实现表中字段的列加密的文章就介绍到这了,更多相关Sql 列加密内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友！
您可能感兴趣的文章:- MSSQL数据加密解密代码
- SQL Server储过程加密和解密原理深入分析
- 实例讲解SQL Server加密功能
- MySQL加密和解密实例详解
- MySQL数据库对敏感数据加密及解密的实现方式
- MySQL InnoDB表空间加密示例详解
- 关于SQL Server加密与解密的问题
- sqlserver数据库加密后无法使用MDF,LDF,log文件名称被修改的数据恢复
- MySQL中使用MD5加密的实现
- mysql如何对已经加密的字段进行模糊查询详解