MSSQL-最佳实践-使用非对称密钥实现列加密-阿里云开发者社区

开发者社区> 风移> 正文

MSSQL-最佳实践-使用非对称密钥实现列加密

简介: --- title: MSSQL-最佳实践-使用非对称密钥实现列加密 author: 风移 --- # 摘要 上一篇月报,我们分享了SQL Server使用对称密钥实现列加密的方法。为了解决对称加密安全性低的问题,本期月报我们分享使用非对称密钥加密方式实现SQL Server列加密方法,保护用户的关键、核心隐私数据列信息。 # 场景引入 对称加密是指加密和解密过程使用同一个密钥的加密
+关注继续查看

title: MSSQL-最佳实践-使用非对称密钥实现列加密

author: 风移

摘要

上一篇月报,我们分享了SQL Server使用对称密钥实现列加密的方法。为了解决对称加密安全性低的问题,本期月报我们分享使用非对称密钥加密方式实现SQL Server列加密方法,保护用户的关键、核心隐私数据列信息。

场景引入

对称加密是指加密和解密过程使用同一个密钥的加密算法,而非对称加密方法加密和解密过程使用不同的密钥进行。因此,通常来说对称加密安全性较弱,非对象加密安全性相对较高。以下是关于对称加密和非对称加密的过程介绍。

对称加密过程

对称加密算法使用相同的密钥对数据进行加密,如下图所示:
01.png

对称加密数据整个流转过程包括:
数据发送者使用密钥将数据明文加密为密文
通过网络将数据密文和密钥发送给接受者
接受者使用相同密钥对密文进行解密,获取到最终的明文数据
从数据整个流转过程来看,很可能用于加密的密钥会被窃取,比如:
网络传输过程中,密钥很可能被窃取
窃取者有可能使用大数据分析手段,穷举出密文数据规律,分析出加密算法
因此,对称加密密钥存在被窃取的可能,安全性相对较弱。

非对称加密过程

与对称加密方式不同,非对称加密算法使用不同的密钥对数据进行加密和解密,用于加密的密钥叫公钥,用于解密的密钥叫私钥。因此安全性更高,如下图所示:
02.png
非对称加密整个数据流转的过程包括:
数据接受者首先生成公钥和私钥,然后将公钥发送给数据发送者(图中未画出)
数据发送者使用公钥对明文进行加密为密文
通过网络将密文发送给数据接受者
数据接受者获取到密文,使用私钥对数据进行解密,获取到最终明文数据
非对称加密整个数据流转过程中,私钥根本没有在网络上进行传递,因此不存在被窃取的可能性,安全性更高。

非对称密钥列加密

因此,本篇月报,在上一期月报MSSQL · 最佳实践 · 使用对称密钥实现列加密的基础上,使用非对称密钥方式实现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

原始数据中,用户的电话号码为明文存储,任何有权限查看表数据的用户,都可以清楚明了的获取到用户的电话号码信息,展示如下:
03.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 Symmetric 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

结果展示如下:
04.png

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

修改表结构

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

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

新列数据初始化

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

-- Step 7 - init the encrypted data into the newly column
USE [TestDb]
GO 
UPDATE A
SET EncryptedCustomerPhone = ENCRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), CustomerPhone)
FROM dbo.CustomerInfo AS A;
GO
-- Double check the encrypted data of the new column
SELECT * FROM dbo.CustomerInfo

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

查看加密数据

手机号被加密为密文后,我们需要使用DecryptByAsymKey函数将其解密为明文,让我们尝试看看能否成功解密EncryptedCustomerPhone字段。

-- Step 8 - Reading the SQL Server Encrypted Data
USE [TestDb]
GO 

-- Now, it's time to list the original phone, encrypted phone and the descrypted phone.
SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo;
GO

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

添加新数据

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

-- Step 9 - What if we add new record to table.
USE [TestDb]
GO 

-- Performs the update of the record
INSERT INTO dbo.CustomerInfo (CustomerName, CustomerPhone, EncryptedCustomerPhone)
VALUES ('CustomerD', '13880975623', ENCRYPTBYASYMKEY( ASYMKEY_ID('AsymKey_TestDb'), '13880975623'));  
GO

更新数据手机号

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

-- Step 10 - So, what if we upadate the phone
USE [TestDb]
GO 
-- Performs the update of the record
UPDATE A
SET EncryptedCustomerPhone = ENCRYPTBYASYMKEY( ASYMKEY_ID('AsymKey_TestDb'), '13880971234')
FROM dbo.CustomerInfo AS A
WHERE CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy')) = '13880975623'
GO

删除手机号明文列

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

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

SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO

结果展示如下:
08.png

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

添加新用户

模拟新添加一个用户EncryptedDbo:

-- Step 12 - Create a new user & access the encrypted data
USE [TestDb]
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 13 -- OPEN a new connection query window using the new user and query data 
USE [TestDb]
GO

SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO

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

为新用户赋权限

新用户没有查看加密列数据的权限,如果需要赋予权限,方法如下:

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

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

新用户再次查询

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

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

SELECT 
    *,
    DescryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO

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

最后总结

本篇月报分享了对称加密和非对称加密的工作原理,以及如何利用SQL Server非对称密钥实现列加密的方法,来保护用户核心数据信息安全。

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

相关文章
安全管理最佳实践系列:给ECS实例配置一个RAM角色身份(使用动态STS-Token访问云服务API)
如果你的应用程序部署在ECS实例中,你可能会苦恼于应用程序的AK配置安全问题及管理难的问题。为此阿里云提供了给ECS实例配置RAM角色的解决方案,使得运行ECS实例中的应用程序将不再需要配置AK,而只需要从ECS Metadata服务中获取StsToken就可以访问阿里云服务API,让你不再担心应用程序的AK配置安全问题和难管理的问题。
6592 0
xshell,putty远程连接Linux并使用密钥认证
putty秘钥登录 1.软件:putty、puttygen puttygen点击Generate生成公钥和私钥   二次保障,输入设置密码   点击保存私钥文件即可。
1144 0
MSSQL-最佳实践-实例级别数据库上云RDS SQL Server
--- title: MSSQL-最佳实践-实例级别数据库上云RDS SQL Server author: 风移 --- # 摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种
1456 0
MSSQL · 最佳实践 · 使用混合密钥实现列加密
摘要 在SQL Server安全系列专题的上两期月报分享中,我们分别分享了:如何使用对称密钥实现SQL Server列加密技术和使用非对称密钥加密方式实现SQL Server列加密。本期月报我们分享使用混合密钥加密方式实现SQL Server列加密技术,最大限度减少性能损失,最大程度保护用户数据安全。
1504 0
使用ssh-keygen生存密钥对
个人博客: alex-my.xyz      ssh-keygen具体更加详细的用法请参见“ssh-keygen中文手册”。 本处仅讲述生成过程,为了github.com服务添加安全保障。     1、在~下有一个隐藏文件夹.ssh,大家可以输入命令,cd~/.ssh,如果不存在,请建立。     2、一般我们会给生成的key添加一个注释,因此我们的命令一般是这样的:    
990 0
MSSQL - 最佳实践 - 使用SSL加密连接
--- title: MSSQL - 最佳实践 - 使用SSL加密连接 author: 风移 --- # 摘要 在SQL Server安全系列专题月报分享中,往期我们已经陆续分享了:[如何使用对称密钥实现SQL Server列加密技术](http://mysql.taobao.org/monthly/2018/08/03/)、[使用非对称密钥实现SQL Server列加密](http:/
2440 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载