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

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

2018-10-22 1859
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月
推荐场景：
对影评进行热评分析
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
简介： 摘要在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
|
1月前
|
存储 安全 前端开发
端到端加密：确保数据传输安全的最佳实践
【10月更文挑战第12天】端到端加密（E2EE）是确保数据传输安全的重要手段，通过加密技术保障数据在传输过程中的隐私与完整性，防止第三方窃听和篡改。本文介绍E2EE的工作原理、核心优势及实施步骤，并探讨其在即时通讯、文件共享和金融服务等领域的应用，强调了选择加密协议、密钥管理、数据加密及安全接口设计的重要性，旨在帮助企业和开发者有效保护用户数据，满足数据保护法规要求。
爱的不是纯牛奶-47754
141 4
热爱技术的小郑
|
3月前
|
安全 算法 数据安全/隐私保护
加密与安全：公开密钥加密、加密过程、数字签名等
这篇文章详细解释了非对称加密算法，包括公开密钥加密的原理、加密过程、数字签名的功能，以及它与对称加密的比较和实际应用场景。
热爱技术的小郑
127 4
加密与安全：公开密钥加密、加密过程、数字签名等
请看我回答~
|
3月前
|
SQL 安全 API
数字堡垒之下：网络安全漏洞、加密技术与安全意识的博弈探索RESTful API设计的最佳实践
【8月更文挑战第27天】在数字化浪潮中，网络安全成为守护个人隐私与企业资产的关键防线。本文深入探讨了网络漏洞的成因与影响，分析了加密技术如何为数据保驾护航，并强调了提升公众的安全意识对于构建坚固的信息防御系统的重要性。文章旨在为读者提供一场思维的盛宴，启发更多关于如何在日益复杂的网络世界中保护自己的思考。
请看我回答~
44 3
请看我回答~
|
3月前
|
安全 物联网 网络安全
网络安全的屏障与密钥：揭秘网络漏洞、加密技术与安全意识的重要性新技术趋势与应用：区块链、物联网与虚拟现实的未来展望
【8月更文挑战第27天】在数字时代的浪潮中，网络安全成为了我们不可忽视的话题。本文将深入浅出地探讨网络安全中的漏洞问题、加密技术的应用及其重要性，以及提升个人和组织的安全意识的必要性。通过分析常见的网络攻击手段和防御策略，我们将了解到保护信息资产的紧迫性，并掌握一些基础的防护措施。文章还将分享几个实用的代码示例，帮助读者更好地理解加密技术在实际应用中的作用。
请看我回答~
49 3
土木林森
|
3月前
|
安全 数据安全/隐私保护 C++
RSA密钥的秘密花园：Python带你漫步加密解密的知识殿堂
【8月更文挑战第2天】RSA密钥的秘密花园以非对称加密守护信息安全。对称加密如乡间小屋, 发送方与接收方共享钥匙; 而RSA像宏伟城堡, 拥有公钥和私钥。公钥加密信息, 私钥解密, 解决了密钥安全传递难题。借助Python和pycryptodome库, 我们可体验RSA加密解密过程, 生成密钥对, 加密消息, 并成功解密, 展现其强大能力和在信息安全中的独特作用。
土木林森
66 2
aliyun8599273441-30642
|
4月前
|
SQL 安全 网络安全
网络安全的屏障与密钥：漏洞防御、加密技术与安全意识
【7月更文挑战第14天】在数字化浪潮中，网络安全成为维护信息完整性和隐私权的基石。本文将深入探讨网络环境中存在的安全漏洞，分析其成因及潜在的风险，并介绍当前主流的加密技术如何作为防线保护数据不受侵犯。此外，强调安全意识的重要性，并提出提升个人和企业防护能力的策略。通过技术与认知的双重提升，构建更为坚固的网络安全防御体系。
aliyun8599273441-30642
46 0
Charles_yang
|
5月前
|
存储 安全 API
使用KMS为Apollo配置中心敏感配置加密的最佳实践
使用KMS为Apollo配置中心敏感配置加密的最佳实践
Charles_yang
715 2
聚娃科技开发者团队
|
4月前
|
存储 安全 Java
Java中数据加密与解密的最佳实践
Java中数据加密与解密的最佳实践
聚娃科技开发者团队
70 0
aliyun8599273441-30642
|
3天前
|
安全 网络安全 数据安全/隐私保护
网络安全与信息安全：关于网络安全漏洞、加密技术、安全意识等方面的知识分享
在数字化时代，网络安全和信息安全已成为我们生活中不可或缺的一部分。本文将介绍网络安全漏洞、加密技术和安全意识等方面的知识，并提供一些实用的技巧和建议，帮助读者更好地保护自己的网络安全和信息安全。
aliyun8599273441-30642
37 23
请看我回答~
|
3天前
|
安全 算法 网络安全
网络安全与信息安全：关于网络安全漏洞、加密技术、安全意识等方面的知识分享
在当今数字化时代，网络安全和信息安全已经成为了全球关注的焦点。随着技术的发展，网络攻击手段日益狡猾，而防范措施也必须不断更新以应对新的挑战。本文将深入探讨网络安全的常见漏洞，介绍加密技术的基本概念和应用，并强调培养良好安全意识的重要性。通过这些知识的分享，旨在提升公众对网络安全的认识，共同构建更加安全的网络环境。
请看我回答~
25 9
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 4
    Linux 性能诊断 perf使用指南
  • 5
    PostgreSQL 9种索引的原理和应用场景
  • 6
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 7
    Jedis常见异常汇总
  • 8
    PostgreSQL 百亿数据 秒级响应 正则及模糊查询
  • 9
    PostgreSQL 触发器用法详解 - 1
  • 10
    时序数据库分析 - TimescaleDB时序数据库介绍
  • 1
    【赵渝强老师】PostgreSQL的表空间
    7
  • 2
    Sybase数据恢复—Sybase数据库常见问题之数据库无法启动的恢复案例
    10
  • 3
    【赵渝强老师】PostgreSQL的数据库
    16
  • 4
    Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
    10
  • 5
    【赵渝强老师】PostgreSQL的数据库集群
    15
  • 6
    【赵渝强老师】PostgreSQL的逻辑存储结构
    40
  • 7
    【赵渝强老师】MySQL的参数文件
    44
  • 8
    【赵渝强老师】MySQL的全量日志文件
    29
  • 9
    【赵渝强老师】MySQL的慢查询日志
    49
  • 10
    Pgloader极简教程
    37

    • 相关课程

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

    • 相关电子书

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

    • 相关实验场景

    更多
  • 通过HTTPS加速网关快速部署网站加密
  • RSA非对称加密算法
  • 基于函数计算一键部署简易论坛
  • 数据库实验室挑战任务-高级任务
  • 数据库实验室挑战任务-中级任务
    • 下一篇
    无影云桌面