快捷導(dǎo)航

Sql Server數(shù)據(jù)庫實(shí)現(xiàn)表中字段的列加密

更新時(shí)間：2023年10月24日 10:06:36 作者：金斗潼關(guān)

本文主要介紹了Sql Server數(shù)據(jù)庫實(shí)現(xiàn)表中字段的列加密,主要包括利用證書對(duì)數(shù)據(jù)進(jìn)行加密和解密,利用非對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密,利用對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密,感興趣的可以了解一下

1、問題描述

去年6月份的時(shí)候做過一個(gè)系統(tǒng)，要對(duì)里面的一些敏感字段進(jìn)行一下加密。Sqlserver列加密可以參考官方文檔：SQL Server 技術(shù)文檔 - SQL Server | Microsoft Learn。主要看下來有三種加密方法：1、利用證書對(duì)數(shù)據(jù)進(jìn)行加密和解密。2、利用非對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密。3、利用對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密。

2、加密方法介紹

2.1、證書加密（推薦）

2.1.1、創(chuàng)建證書

use mydb;

--查看數(shù)據(jù)庫中的證書
select * from sys.certificates;

--創(chuàng)建數(shù)據(jù)庫主密鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='123@#456';

--創(chuàng)建證書
CREATE CERTIFICATE MyCert
with SUBJECT = 'My Test Certificate'
GO

--創(chuàng)建證書并授權(quán)給指定用戶(zhangsan)
CREATE CERTIFICATE MyCertToZhangSan AUTHORIZATION zhangsan 
with SUBJECT = 'My Test Certificate To ZhangSan'
GO

2.1.2、使用證書加解密

創(chuàng)建一個(gè)測(cè)試表

--創(chuàng)建測(cè)試表TUser_test_Cert，要對(duì)pwd字段進(jìn)行加密因此其字段類型設(shè)為varbinary
--varbinary表示可變長(zhǎng)度二進(jìn)制數(shù)據(jù)，但是在sqlserver表存放是以十六進(jìn)制存的。
create table TUser_test_Cert(username varchar(50),pwd varbinary(2000));

在2.1.1環(huán)節(jié)一共建了兩個(gè)證書，MyCert這個(gè)證書是公用的，而MyCertToZhangSan這個(gè)證書是僅限zhangsan這個(gè)用戶可以使用！

使用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證書進(jìn)行加解密

因?yàn)槲沂菍?duì)mydb數(shù)據(jù)庫進(jìn)行證書加解密嘛！因此首先要確保mydb下是否有zhangsan這個(gè)用戶！有了zhangsan這個(gè)用戶才能創(chuàng)建證書并對(duì)其AUTHORIZATION

有個(gè)地方要提一下，這里的zhangsan這個(gè)用戶是在mydb下的，并不是直接在mydb下直接手動(dòng)創(chuàng)建的，而是整個(gè)sqlserver登錄名zhangsan映射到mydb的一個(gè)用戶映射！

其實(shí)就是在你新建數(shù)據(jù)庫登錄名的時(shí)候做的一個(gè)用戶映射，相當(dāng)于授權(quán)這個(gè)登錄名可以有哪些數(shù)據(jù)庫的權(quán)限了！

不過此時(shí)mydb新加的zhangsan這個(gè)用戶還沒有給他分配角色（所謂角色就是權(quán)限集合）

--創(chuàng)建一個(gè)角色zhangsan_role
create role zhangsan_role;
--給zhangsan_role這個(gè)角色分配TUser_test_Cert表的增刪改查權(quán)限
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、非對(duì)稱密鑰加解密

----利用非對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密---

--查看數(shù)據(jù)庫中的非對(duì)稱密鑰
select * from sys.asymmetric_keys;

--創(chuàng)建非對(duì)稱密鑰
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、對(duì)稱密鑰加解密

----利用對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密----

--查看數(shù)據(jù)庫中的對(duì)稱密鑰
select * from sys.symmetric_keys;

--創(chuàng)建對(duì)稱密鑰
CREATE SYMMETRIC KEY MyTestSymmetric
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = '123456@aes';
GO
--打開密鑰并進(jìn)行加密/解密（只有對(duì)稱加密需打開密鑰！?。。?
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、總結(jié)

Sqlserver數(shù)據(jù)庫列字段加密推薦使用證書加密，使用證書加密是對(duì)特定的賬號(hào)授予他們使用證書加解密的權(quán)限，他們只是用了證書的cert_id，并不會(huì)涉及password密鑰。但如果使用其他方法加密的話，比如AES，RSA的話，那么在加解密的時(shí)候會(huì)把密鑰暴露給開發(fā)人員