MSSQL · 最佳实践 · 使用混合密钥实现列加密-阿里云开发者社区

开发者社区> db匠> 正文

MSSQL · 最佳实践 · 使用混合密钥实现列加密

简介: 摘要 在SQL Server安全系列专题的上两期月报分享中,我们分别分享了:如何使用对称密钥实现SQL Server列加密技术和使用非对称密钥加密方式实现SQL Server列加密。本期月报我们分享使用混合密钥加密方式实现SQL Server列加密技术,最大限度减少性能损失,最大程度保护用户数据安全。
+关注继续查看

摘要

在SQL Server安全系列专题的上两期月报分享中,我们分别分享了:如何使用对称密钥实现SQL Server列加密技术和使用非对称密钥加密方式实现SQL Server列加密。本期月报我们分享使用混合密钥加密方式实现SQL Server列加密技术,最大限度减少性能损失,最大程度保护用户数据安全。

场景引入

对称加密是指加密和解密过程使用同一个密钥的加密算法,非对称加密是指加密和解密过程使用不同的密钥进行的加密算法。因此,通常来说对称加密安全性较弱,非对象加密安全性相对较高。凡事都具有两面性,非对称密钥加密的安全性较好,但通常算法相比对称密钥复杂许多,因此会带来性能上的损失也更大。有没有一种方法既可以最大限度保证数据安全性,又能够最大限度的减少性能损失呢?这便是本期月报分享的价值所在:SQL Server使用混合密钥实现列加密技术。

具体实现

在SQL Server 2005及以后版本,在支持对称密钥实现列加密的同时,也同样支持非对称密钥实现列加密,以下是使用混合密钥加密用户手机号码的具体实现步骤以及详细过程,以此最大限度满足数据库安全性和减少加密解密过程的性能损失。

创建测试数据库

创建一个专门的测试数据库,名为:TestDb。

--Step 1 - Create MSSQL sample database
USE master
GO
IF DB_ID('TestDb') IS NOT NULL
	DROP DATABASE [TestDb];
GO
CREATE DATABASE [TestDb];
GO

创建测试表

在TestDb数据库下,创建一张专门的测试表,名为:CustomerInfo。

--Step 2 - Create Test Table, init data & verify
USE [TestDb]
GO
IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
	DROP TABLE dbo.CustomerInfo
CREATE TABLE dbo.CustomerInfo
(
CustomerId		INT IDENTITY(10000,1)	NOT NULL PRIMARY KEY,
CustomerName	VARCHAR(100)			NOT NULL,
CustomerPhone	CHAR(11)				NOT NULL
);

-- Init Table
INSERT INTO dbo.CustomerInfo 
VALUES ('CustomerA','13402872514')
,('CustomerB','13880674722')
,('CustomerC','13487759293')
GO

-- Verify data
SELECT * 
FROM dbo.CustomerInfo
GO

原始数据中,用户的电话号码为明文存储,任何有权限查看表数据的用户,都可以清楚明了的获取到用户的电话号码信息,展示如下:

01.png

创建实例级别Master Key

在SQL Server数据库实例级别创建Master Key(在Master数据库下,使用CREATE MASTER KEY语句):

-- Step 3 - Create SQL Server Service Master Key
USE master;
GO
IF NOT EXISTS(
	SELECT *
	FROM sys.symmetric_keys
	WHERE name = '##MS_ServiceMasterKey##')
BEGIN
	CREATE MASTER KEY ENCRYPTION BY 
	PASSWORD = 'MSSQLSerivceMasterKey'
END;
GO

创建数据库级别Master Key

在用户数据库TestDb数据库下,创建Master Key:

-- Step 4 - Create MSSQL Database level master key
USE [TestDb]
GO
IF NOT EXISTS (SELECT * 
				FROM sys.symmetric_keys 
				WHERE name LIKE '%MS_DatabaseMasterKey%')
BEGIN		
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TestDbMasterKey@3*';
END
GO

创建非对称密钥

在用户数据库下,创建非对称密钥,并使用密码对非对称密钥进行加密:

-- Step 5 - Create MSSQL Asymmetric Key
USE [TestDb]
GO
IF NOT EXISTS (SELECT * 
				FROM sys.asymmetric_keys 
				WHERE name = 'AsymKey_TestDb')
BEGIN
	CREATE ASYMMETRIC KEY AsymKey_TestDb 
	WITH ALGORITHM = RSA_512 
	ENCRYPTION BY PASSWORD = 'Password4@Asy'
	;
END
GO

USE [TestDb]
GO
SELECT *
FROM  sys.asymmetric_keys

查看非对称密钥

您可以使用如下查询语句查看非对称密钥:

USE [TestDb]
GO
SELECT *
FROM  sys.asymmetric_keys

结果展示如下:

02.png

当然,您也可以用SSMS图形界面来查看证书和非对称密钥对象,方法是在用户数据库下,打开Security => Certificates => Asymmetric Keys,如下图所示:

03.png

创建对称密钥

使用非对称密钥AsymKey_TestDb来加密对称密钥SymKey_TestDb,然后使用这个对称密钥SymKey_TestDb来加密用户数据。这样既可以利用非对称密钥的安全性来保护对称密钥,又能兼顾对称密钥加密数据的高效性,两全其美。这种使用非对称密钥加密对称密钥,然后使用对称密钥加密用户敏感数据的方式,我且称之为“混合密钥”加密,这一步是本篇文章的关键点,也是很多人没有关注到的点。

--Step 6 - Create Symmetric Key Encrypted by symmetic key
USE [TestDb]
GO
IF NOT EXISTS (SELECT * 
				FROM sys.symmetric_keys 
				WHERE name = 'SymKey_TestDb')
BEGIN
	CREATE SYMMETRIC KEY SymKey_TestDb 
	WITH ALGORITHM = AES_256 
	ENCRYPTION BY ASYMMETRIC KEY AsymKey_TestDb;  -- Asymmetric Key
	;
END
GO


USE [TestDb]
GO
SELECT *
FROM  sys.symmetric_keys

对称密钥展示如下: 04.png

修改表结构

接下来,我们需要修改表结构,添加一个数据类型为varbinary(max)的新列,假设列名为EncryptedCustomerPhone ,用于存储加密后的手机号码密文。

-- Step 7 - Change your table structure
USE [TestDb]
GO 
ALTER TABLE CustomerInfo 
ADD EncryptedCustomerPhone varbinary(MAX) NULL
GO

新列数据初始化

新列添加完毕后,我们将表中历史数据的用户手机号CustomerPhone,加密为密文,并存储在新字段EncryptedCustomerPhone中。方法是使用EncryptByKey函数加密CustomerPhone列,如下语句所示:

-- Step 8 - init the encrypted data into the newly column
USE [TestDb]
GO 
-- Opens the symmetric key: SymKey_TestDb
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
GO
UPDATE A
SET EncryptedCustomerPhone = EncryptByKey (Key_GUID('SymKey_TestDb'), CustomerPhone)
FROM dbo.CustomerInfo AS A;
GO
-- Closes the symmetric key: SymKey_TestDb
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO
-- Double check the encrypted data of the new column
SELECT * FROM dbo.CustomerInfo

查看表中EncryptedCustomerPhone列的数据,已经变成CustomerPhone对称加密后的密文,如下展示: 05.png

查看加密数据

手机号被加密为密文后,我们需要使用DecryptByKey函数将其解密为明文(解密前,需要打开对称密钥),让我们尝试看看能否成功解密EncryptedCustomerPhone字段。

-- Step 9 - Reading the SQL Server Encrypted Data
USE [TestDb]
GO 
-- Opens the symmetric key: SymKey_TestDb
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
GO

-- Now, it's time to list the original phone, encrypted phone and the descrypted phone.
SELECT 
	*,
	DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
FROM dbo.CustomerInfo;
 
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

查询语句执行结果如下,CustomerPhone和DescryptedCustomerPhone字段数据内容是一模一样的,因此加密和解密成功。 06.png

添加新数据

历史数据加密解密后的数据保持一致,然后,让我们看看新添加的数据:

-- Step 10 - What if we add new record to table.
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';
GO
-- Performs the update of the record
INSERT INTO dbo.CustomerInfo (CustomerName, CustomerPhone, EncryptedCustomerPhone)
VALUES ('CustomerD', '13880975623', EncryptByKey( Key_GUID('SymKey_TestDb'), '13880975623'));  

-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

更新数据手机号

接下来,我们尝试更新用户手机号:

-- Step 11 - So, what if we upadate the phone
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

-- Performs the update of the record
UPDATE A
SET EncryptedCustomerPhone = EncryptByKey( Key_GUID('SymKey_TestDb'), '13880971234')
FROM dbo.CustomerInfo AS A
WHERE CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone)) = '13880975623'

-- Close the symmetric key
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

删除手机号明文列

一切没有问题,我们可以将用户手机号明文列CustomerPhone删除:

-- Step 12 - Remove old column
USE [TestDb]
GO 
ALTER TABLE CustomerInfo
DROP COLUMN CustomerPhone;
GO

再次查看加密数据

将用户手机号码的明文列删除后,我们再次查看解密用户手机号码明文列

--Step 13 - verify again
USE [TestDb]
GO 
OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

SELECT 
	*,
	DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
FROM dbo.CustomerInfo
 
CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

结果展示如下: 07.png

一切正常,历史数据、新添加的数据、更新的数据,都可以工作完美。按理,文章到这里也就结束。但是有一个问题我们是需要搞清楚的,那就是:如果我们新创建了用户,他能够访问这个表的数据吗?以及我们如何让新用户能够访问该表的数据呢?

添加新用户

模拟新添加一个用户EncryptedDbo:

-- Step 14 - Create a new user & access the encrypted data
USE [TestDb]
GO
IF EXISTS(
	SELECT TOP 1 *
	FROM sys.server_principals
	WHERE name = 'EncryptedDbo'
)
BEGIN
	DROP LOGIN EncryptedDbo;
END
GO

CREATE LOGIN EncryptedDbo
	WITH PASSWORD=N'EncryptedDbo@3*', CHECK_POLICY = OFF;
	
GO

CREATE USER EncryptedDbo FOR LOGIN EncryptedDbo;

GRANT SELECT ON OBJECT::dbo.CustomerInfo TO EncryptedDbo;
GO

新用户查询数据

使用刚才创建的用户,在SSMS中新打开一个新连接,查询数据:

-- Step 15 -- OPEN a new connection query window using the new user and query data 
USE [TestDb]
GO

OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

SELECT 
	*,
	DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
FROM dbo.CustomerInfo

CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

新用户也无法解密EncryptedCustomerPhone,解密后的DescryptedCustomerPhone 字段值为NULL,即新用户无法查看到用户手机号明文,避免了未知用户获取用户手机号等核心数据信息。 08.png

而且,还会因为权限的问题,OPEN SYMMETRIC KEY和CLOSE SYMMETRIC KEY报错,可以在Messages窗口中看到: 09.png

为新用户赋权限

新用户没有查看加密列数据的权限,如果需要赋予权限,这里需要授权对称密钥DEFINITION权限和非对称密钥CONTROL权限,方法如下:

--Step 16 - Grant permissions to EncryptedDbo
USE [TestDb]
GO

GRANT VIEW DEFINITION ON 
	SYMMETRIC KEY::[SymKey_TestDb] TO [EncryptedDbo];
GO

GRANT CONTROL ON 
	ASYMMETRIC KEY::[AsymKey_TestDb] TO [EncryptedDbo];
GO

新用户再次查询

赋权限完毕后,新用户再次执行“新用户查询数据”中的查询语句,已经可以正常获取到加密列的明文数据了。

-- Step 15 -- OPEN a new connection query window using the new user and query data 
USE [TestDb]
GO

OPEN SYMMETRIC KEY SymKey_TestDb
DECRYPTION BY ASYMMETRIC KEY AsymKey_TestDb WITH PASSWORD = 'Password4@Asy';

SELECT 
	*,
	DescryptedCustomerPhone = CONVERT(CHAR(11), DecryptByKey(EncryptedCustomerPhone))
FROM dbo.CustomerInfo

CLOSE SYMMETRIC KEY SymKey_TestDb;
GO

再次查询结果展示如下: 10.png

最后总结

本篇月报分享了如何利用非对称密钥加密对称密钥,然后使用对称密钥加密用户数据,即混合密钥的方式实现SQL Server列加密技术,以此来最大限度保护用户核心数据信息安全的同时,又最大限度降低了加密解密对的性能损失。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
安全管理最佳实践系列:给ECS实例配置一个RAM角色身份(使用动态STS-Token访问云服务API)
如果你的应用程序部署在ECS实例中,你可能会苦恼于应用程序的AK配置安全问题及管理难的问题。为此阿里云提供了给ECS实例配置RAM角色的解决方案,使得运行ECS实例中的应用程序将不再需要配置AK,而只需要从ECS Metadata服务中获取StsToken就可以访问阿里云服务API,让你不再担心应用程序的AK配置安全问题和难管理的问题。
6703 0
【巡检问题分析与最佳实践】RDS MySQL 实例IO高问题
RDS MySQL的IO性能受到硬件层存储介质类型、软件层的DB内核架构、具体SQL语句扫描或修改数据量的影响。
345 0
【最佳实践】OSS开源工具ossutil-上传性能调优
经常碰到内部同学或者外部客户问ossutil关于并发上传性能的问题。本文简单描述下ossutil并发上传原理并举例说明。 用户可从这里获取ossutil。 官网:https://help.aliyun.com/document_detail/50452.html代码:https://github.com/aliyun/ossutil 参数 --recursive 上传文件到oss时,如果file_url为目录,则必须指定--recursive选项,否则无需指定--recursive选项。
5649 0
MSSQL · 最佳实践 · 使用混合密钥实现列加密
摘要 在SQL Server安全系列专题的上两期月报分享中,我们分别分享了:如何使用对称密钥实现SQL Server列加密技术和使用非对称密钥加密方式实现SQL Server列加密。本期月报我们分享使用混合密钥加密方式实现SQL Server列加密技术,最大限度减少性能损失,最大程度保护用户数据安全。
1543 0
【最佳实践】大数据时代,通过OSS快照迁移Elasticsearch数据
本文以将自建Elasticsearch迁移至阿里云Elasticsearch中为例,为您介绍通过OSS快照迁移数据的具体方法。
1100 0
【最佳实践】使用 Elasticsearch SQL 实现数据查询
如何使用 Elasticsearch SQL 来对我们的数据进行查询。
2343 0
MSSQL - 最佳实践 - 使用SSL加密连接
--- title: MSSQL - 最佳实践 - 使用SSL加密连接 author: 风移 --- # 摘要 在SQL Server安全系列专题月报分享中,往期我们已经陆续分享了:[如何使用对称密钥实现SQL Server列加密技术](http://mysql.taobao.org/monthly/2018/08/03/)、[使用非对称密钥实现SQL Server列加密](http:/
2525 0
MSSQL - 最佳实践 - 如何打码隐私数据列
--- title: MSSQL - 最佳实践 - 如何打码隐私数据列 author: 风移 --- # 摘要 在SQL Server安全系列专题月报分享中,我们已经分享了:如何使用对称密钥实现SQL Server列加密技术、使用非对称密钥加密方式实现SQL Server列加密、使用混合密钥实现SQL Server列加密技术、列加密技术带来的查询性能问题以及相应解决方案和行级别安全解决方
1115 0
【最佳实践】OSS移动端断点下载简单实现
概要 技术点 最佳实践 改进 参考 概要 所谓断点下载,其实是客户端在从网络上下载资源时,由于某种原因中断下载。再次开启下载时可以从已经下载的部分开始继续下载未完成的部分,从而节省时间和流量。
2040 0
+关注
db匠
rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/
497
文章
0
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载