3月19日,网上爆出微博数据泄露。
微博方亦迅速回应,表示数据泄露属实。
微博表示一直有提供根据通讯录手机号查询微博好友昵称的服务,用户授权后可以使用该服务。
但微博不提供用户性别和身份证号等信息,也没有“根据用户昵称查手机号”的服务。
因此这起数据泄露不涉及身份证、密码,对微博服务没有影响。
目前微博已经及时强化安全策略,并将不断强化。
相对来说此次数据泄露引起的危险性不大,不过又引起了我们对数据安全和隐私的关注,且近几年重大的数据泄露事件不断,数据泄露带来的影响也是不可估量的,这足以引起我们对数据安全的重视。作为一个数据管理人员,防范于未然,是必要的事情。
防止数据泄露的方案之一是防止未授权的用户访问数据,我们可以使用视图隐藏原表中的私密数据,开放公开数据;当然我们也可以给以指定列的权限,限制用户访问私密数据。限定指定列的访问权限,在前面的SQL Server数据库用户权限等文章中都有涉猎,需要的可以查阅前文。第二种方案即是对数据进行加密,而加密可以前端直接对信息进行加密后存储到数据库,另外一种方式是在数据库进行加密。
作为数据管理人员,在数据库中进行的数据加密解密是我们所关注的重点。所以从本文开始的几篇文章中,我将把SQL Server数据库中的数据的加密方案逐一介绍给大家,开启我们数据的安全之路。
我们先从使用对称秘钥加密数据开始。首先搭建我们的测试环境,创建数据库EncryptionTest ,在数据库中创建表EncryptionData, 加密证书的数据库主秘钥,使用DMK加密的证书,以及使用证书加密的对称秘钥,该对称秘钥将用来加密数据:
--创建测试数据库
CREATE DATABASE EncryptionTest ON PRIMARY
(NAME='EncryptionTest',FILENAME='D:\database\EncryptionTest.mdf')
LOG ON
(NAME='EncryptionTest_log',FILENAME='D:\database\EncryptionTest_log.ldf')
USE EncryptionTest
GO
--创建测试表
CREATE TABLE EncryptionData(id INT,EncryptionCol VARBINARY(128))
--创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD='[J,XJK8|AwE*rLk'
--创建证书
CREATE CERTIFICATE CertData
WITH SUBJECT='DMK Certificate CertData'
--创建对称密钥
CREATE SYMMETRIC KEY SYKey_Data
WITH ALGORITHM=AES_256
ENCRYPTION BY CERTIFICATE CertData
使用SQL Server提供的加密函数 ENCRYPTBYKEY 实现数据加密,并查看加密后的效果:
INSERT INTO EncryptionData VALUES(1,ENCRYPTBYKEY(KEY_GUID('SYKey_Data'),'Jack'))
--打开对称密钥
OPEN SYMMETRIC KEY SYKey_Data
DECRYPTION BY CERTIFICATE CertData
INSERT INTO EncryptionData VALUES(2,ENCRYPTBYKEY(KEY_GUID('SYKey_Data'),'Jack'))
--关闭对称密钥
CLOSE SYMMETRIC KEY SyKey_Data
SELECT * FROM EncryptionData
注意:
-
没有打开对称秘钥时,加密函数ENCRYPTBYKEY不能将字符串转换为加密字符串,返回的NULL。
-
对称秘钥打开是会话级别的
-
对称秘钥打开后,如果没有在会话中显示关闭,直到会话结束,对称秘钥会一直保持开启状态
为了屏蔽非相关人员查看数据加密内容,保护数据安全,我们可以将插入脚本封装为过程:
CREATE PROC InsertEncryptData
@id INT,
@EncryptionCol VARCHAR(20)
AS
BEGIN
--打开对称密钥
OPEN SYMMETRIC KEY SYKey_Data
DECRYPTION BY CERTIFICATE CertData
INSERT INTO EncryptionData VALUES(@id,ENCRYPTBYKEY(KEY_GUID('SYKey_Data'),@EncryptionCol))
--关闭对称密钥
CLOSE SYMMETRIC KEY SyKey_Data
END
GO
执行验证封装过程,已经实现了加密的功能:
然后创建一个用户,仅仅给以执行该存储过程的权限,创建一个角色给以证书的控制权限,赋予角色查看对称秘钥定义的权限:
CREATE USER Jack WITHOUT LOGIN
GRANT EXEC ON OBJECT::InsertEncryptData TO Jack
CREATE ROLE OpenSysKey
GRANT CONTROL ON CERTIFICATE::CertData TO OpenSysKey
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SyKey_Data TO OpenSysKey
EXEC sys.sp_addrolemember OpenSysKey,Jack
然后在Jack用户上下文中执行存储过程,并检查结果:
EXEC AS USER='Jack'
EXEC InsertEncryptData 4,'Jack2'
REVERT
SELECT * FROM EncryptionData
这样我们可以看到,通过调用过程InsertEncryptData 可以成功的实现加密的功能。
注意:如果没有赋予证书控制权限及查看对称秘钥定义权限,会报如下错误:
消息15151,级别16,状态1,过程InsertEncryptData,第7 行
无法对对称密钥'SYKey_Data' 执行查找,因为它不存在,或者您没有所需的权限。
(1 行受影响)
消息15315,级别16,状态1,过程InsertEncryptData,第13 行
密钥'SyKey_Data' 未打开。请先打开它,然后再使用它。
但是我们可以通过资源管理器右击过程,生成脚本,或者对有查看InsertEncryptData 定义的用户,仍然能够完全掌握数据加密的过程,我们给Jack用户赋予查看存储过程InsertEncryptData 定义的权限,然后在Jack用户上下文中使用sp_helptext 查看过程的定义,如下:
--为Jack用户赋予查看存储过程InsertEncryptData定义的权限
GRANT VIEW DEFINITION ON OBJECT::InsertEncryptData TO Jack
EXEC AS USER='Jack'
EXEC sp_helptext 'InsertEncryptData'
REVERT
从结果中可以完整的看到数据加密的过程,这说明单单是封装存储过程还是不够的,我们可以使用存储过程的创建选项WITH ENCRYPTION 对存储过程加密,下面我们直接修改上面创建的存储过程,脚本如下:
ALTER PROC InsertEncryptData
@id INT,
@EncryptionCol VARCHAR(20)
WITH ENCRYPTION AS
BEGIN
--打开对称密钥
OPEN SYMMETRIC KEY SYKey_Data
DECRYPTION BY CERTIFICATE CertData
INSERT INTO EncryptionData VALUES
(@id,ENCRYPTBYKEY(KEY_GUID('SYKey_Data'),@EncryptionCol))
--关闭对称密钥
CLOSE SYMMETRIC KEY SyKey_Data
END
GO
从对象资源管理器中查看过程,会发现此时过程左边图标比原先多了一个锁的图案:
此时我们再次查看过程的定义,会发现,虽然已经给了Jack用户查看过程定义的权限,但是只返回了文本已加密的提示:
至此,我们虽然尽可能的保证加密过程不被泄漏,但是存在一个比较明显的漏洞,使得很容易试验出加密的过程,即如果我们有查看系统视图sys.key_encryptions 确定对称秘钥的加密方式,并且有查看系统视图sys.symmetric_keys、sys.certificates 中的对称秘钥名称和证书名称,我们就很容易获得数据的加密过程,从而对数据进行解密,因为解密也是相当简单的,只需知道对称秘钥名称和加密证书名称及加密方式即可,如下是解密脚本:
OPEN SYMMETRIC KEY SYKey_Data
DECRYPTION BY CERTIFICATE CertData
SELECT
id,
CONVERT(VARCHAR(MAX), DECRYPTBYKEY(EncryptionCol))
,DECRYPTBYKEY(EncryptionCol)
,EncryptionCol
FROM EncryptionData
--关闭对称密钥
CLOSE SYMMETRIC KEY SyKey_Data
为了弥补这一漏洞,我们需要为证书增加私钥:
ALTER CERTIFICATE CertData
WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='!<Ilc/cby=`Qpae')
再次在Jack用户上下问中执行存储过程,我们会发现报如下错误:
消息15334,级别16,状态1,过程InsertEncryptData,第7 行
证书具有受用户定义密码保护的私钥。若要启用该私钥,需要提供该密码。
(1 行受影响)
消息15315,级别16,状态1,过程InsertEncryptData,第13 行
密钥'SyKey_Data' 未打开。请先打开它,然后再使用它。
打开证书,需要指定私钥密码,为了能够实现加密功能,我们将过程修改为:
ALTER PROC InsertEncryptData
@id INT,
@EncryptionCol VARCHAR(20)
WITH ENCRYPTION AS
BEGIN
--打开对称密钥
OPEN SYMMETRIC KEY SYKey_Data
DECRYPTION BY CERTIFICATE CertData
WITH PASSWORD='!<Ilc/cby=`Qpae'
INSERT INTO EncryptionData VALUES(@id,ENCRYPTBYKEY(KEY_GUID('SYKey_Data'),@EncryptionCol))
--关闭对称密钥
CLOSE SYMMETRIC KEY SyKey_Data
END
GO
此时再次执行过程,发现可以对数据成功进行加密:
从查询结果我们可以看到,前面报错时,仍然有id=5的记录被插入,只是此时加密列为NULL。
这样只要我们的密码不泄漏,我们的数据相对来说还比较安全的。
而有授权的用户,可能需要查看解密后的数据,这里我们再给出解密的过程:
CREATE PROC SelectDecryptionData
WITH ENCRYPTION AS
BEGIN
--打开对称密钥
OPEN SYMMETRIC KEY SYKey_Data
DECRYPTION BY CERTIFICATE CertData
WITH PASSWORD='!<Ilc/cby=`Qpae'
SELECT
id,
EncryptionCol,
CONVERT(VARCHAR(MAX), DECRYPTBYKEY(EncryptionCol)) AS DecryptionCol
FROM EncryptionData
--关闭对称密钥
CLOSE SYMMETRIC KEY SyKey_Data
END
注意这里封装的解密过程也是加密的
下面我们赋予Jack 执行存储过程SelectDecryptionData 的权限,并检验解密结果:
GRANT EXEC ON OBJECT::SelectDecryptionData TO Jack
EXEC AS USER='Jack'
EXEC SelectDecryptionData
REVERT