AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

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

2018-10-22 1854
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月
推荐场景：
对影评进行热评分析
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
简介： 摘要在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列加密技术，以此来最大限度保护用户核心数据信息安全的同时，又最大限度降低了加密解密对的性能损失。

文章标签：
密钥管理服务
云数据库 RDS SQL Server 版
数据安全/隐私保护
Go
数据库
SQL
安全
关键词：
密钥管理服务最佳实践
密钥密钥管理服务
密钥管理服务密钥
密钥管理服务mssql
mssql最佳实践列密钥管理服务
db匠
目录
相关文章
爱的不是纯牛奶-47754
|
12天前
|
存储 安全 前端开发
端到端加密：确保数据传输安全的最佳实践
【10月更文挑战第12天】端到端加密（E2EE）是确保数据传输安全的重要手段，通过加密技术保障数据在传输过程中的隐私与完整性，防止第三方窃听和篡改。本文介绍E2EE的工作原理、核心优势及实施步骤，并探讨其在即时通讯、文件共享和金融服务等领域的应用，强调了选择加密协议、密钥管理、数据加密及安全接口设计的重要性，旨在帮助企业和开发者有效保护用户数据，满足数据保护法规要求。
爱的不是纯牛奶-47754
50 4
热爱技术的小郑
|
3月前
|
安全 算法 数据安全/隐私保护
加密与安全：公开密钥加密、加密过程、数字签名等
这篇文章详细解释了非对称加密算法，包括公开密钥加密的原理、加密过程、数字签名的功能，以及它与对称加密的比较和实际应用场景。
热爱技术的小郑
88 4
加密与安全：公开密钥加密、加密过程、数字签名等
请看我回答~
|
3月前
|
SQL 安全 API
数字堡垒之下：网络安全漏洞、加密技术与安全意识的博弈探索RESTful API设计的最佳实践
【8月更文挑战第27天】在数字化浪潮中，网络安全成为守护个人隐私与企业资产的关键防线。本文深入探讨了网络漏洞的成因与影响，分析了加密技术如何为数据保驾护航，并强调了提升公众的安全意识对于构建坚固的信息防御系统的重要性。文章旨在为读者提供一场思维的盛宴，启发更多关于如何在日益复杂的网络世界中保护自己的思考。
请看我回答~
42 3
请看我回答~
|
3月前
|
安全 物联网 网络安全
网络安全的屏障与密钥：揭秘网络漏洞、加密技术与安全意识的重要性新技术趋势与应用：区块链、物联网与虚拟现实的未来展望
【8月更文挑战第27天】在数字时代的浪潮中，网络安全成为了我们不可忽视的话题。本文将深入浅出地探讨网络安全中的漏洞问题、加密技术的应用及其重要性，以及提升个人和组织的安全意识的必要性。通过分析常见的网络攻击手段和防御策略，我们将了解到保护信息资产的紧迫性，并掌握一些基础的防护措施。文章还将分享几个实用的代码示例，帮助读者更好地理解加密技术在实际应用中的作用。
请看我回答~
43 3
土木林森
|
3月前
|
安全 数据安全/隐私保护 C++
RSA密钥的秘密花园：Python带你漫步加密解密的知识殿堂
【8月更文挑战第2天】RSA密钥的秘密花园以非对称加密守护信息安全。对称加密如乡间小屋, 发送方与接收方共享钥匙; 而RSA像宏伟城堡, 拥有公钥和私钥。公钥加密信息, 私钥解密, 解决了密钥安全传递难题。借助Python和pycryptodome库, 我们可体验RSA加密解密过程, 生成密钥对, 加密消息, 并成功解密, 展现其强大能力和在信息安全中的独特作用。
土木林森
62 2
aliyun8599273441-30642
|
4月前
|
SQL 安全 网络安全
网络安全的屏障与密钥：漏洞防御、加密技术与安全意识
【7月更文挑战第14天】在数字化浪潮中，网络安全成为维护信息完整性和隐私权的基石。本文将深入探讨网络环境中存在的安全漏洞，分析其成因及潜在的风险，并介绍当前主流的加密技术如何作为防线保护数据不受侵犯。此外，强调安全意识的重要性，并提出提升个人和企业防护能力的策略。通过技术与认知的双重提升，构建更为坚固的网络安全防御体系。
aliyun8599273441-30642
39 0
Charles_yang
|
5月前
|
存储 安全 API
使用KMS为Apollo配置中心敏感配置加密的最佳实践
使用KMS为Apollo配置中心敏感配置加密的最佳实践
Charles_yang
596 2
聚娃科技开发者团队
|
4月前
|
存储 安全 Java
Java中数据加密与解密的最佳实践
Java中数据加密与解密的最佳实践
聚娃科技开发者团队
60 0
游客762btuqu5wybw666
|
1天前
|
SQL 存储 安全
网络安全与信息安全：防范漏洞、加密技术及安全意识
随着互联网的快速发展，网络安全和信息安全问题日益凸显。本文将探讨网络安全漏洞的类型及其影响、加密技术的应用以及提高个人和组织的安全意识的重要性。通过深入了解这些关键要素，我们可以更好地保护自己的数字资产免受网络攻击的威胁。
游客762btuqu5wybw666
10 5
我不是游客20240119
|
1天前
|
SQL 安全 算法
网络安全与信息安全：漏洞、加密和意识的三维防护网
【10月更文挑战第25天】在数字时代的浪潮中，网络安全和信息安全如同守护我们虚拟家园的坚固城墙。本文将深入探讨网络安全漏洞的种类与应对策略，解析加密技术的核心原理及其应用，并强调提升个人与企业的安全意识对于构建安全防线的重要性。通过深入浅出的方式，我们将一起探索网络世界的安全之道，确保数据资产的坚不可摧。
我不是游客20240119
10 4
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL 如何实现批量更新、删除、插入
  • 4
    在Docker上玩转PostgreSQL -- Mac篇
  • 5
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 6
    Linux 性能诊断 perf使用指南
  • 7
    PostgreSQL 9种索引的原理和应用场景
  • 8
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 9
    Jedis常见异常汇总
  • 10
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 1
    数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
    16
  • 2
    MySQL 回收表碎片实践教程
    43
  • 3
    浅析MySQL优化器统计信息
    48
  • 4
    Mysql(4)—数据库索引
    50
  • 5
    Mysql(5)—函数
    49
  • 6
    阿里云RDS服务巴黎奥运会赛事系统，助力云上奥运稳定运行
    675
  • 7
    开发者如何使用云原生数据库PolarDB
    28
  • 8
    彻底搞懂InnoDB的MVCC多版本并发控制
    247
  • 9
    MySQL事务日志-Redo Log工作原理分析
    1585
  • 10
    开发者如何使用云数据库RDS
    85

    • 相关课程

    更多
  • 阿里云数据库产品家族及特性

    • 相关电子书

    更多
  • 基于可信计算与加密计算 打造云上原生计算安全
  • \"视频服务特色解决方案——直播连麦与点播加密 \"
  • 量子加密通信技术

    • 相关实验场景

    更多
  • 通过HTTPS加速网关快速部署网站加密
  • RSA非对称加密算法
  • 基于函数计算一键部署简易论坛
  • 数据库实验室挑战任务-中级任务
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月