TDE与列级数据加密

2023-08-02 19:31:28

一、测试TDE
此部分内容扩展SQL Server安全系列的第九篇：SQL Server安全透明数据加密的测试TDE章节。启用TDE的详细步骤请参考原文。

-- Create a test database

CREATE DATABASE UestDB

GO

-- Create a certificate in master to use with TDE

USE master;

GO

-- TDE hooks into encryption key hierarchy in SQL Server

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!drJP9QXC&Vi%cs';

GO

-- Create the certificate used to protect the database encryption key

CREATE CERTIFICATE UestDBTDECert WITH SUBJECT = 'Certificate to implement TDE on UestDB';

GO

-- Backup the master_key

BACKUP MASTER KEY TO FILE = 'D:\SQL2012\MasterKey.bak' ENCRYPTION BY PASSWORD = 'pass';

GO

-- Backup the certificate

-- Either create the D:\SQL2012 folder or change it in the code below

BACKUP CERTIFICATE UestDBTDECert TO FILE = 'D:\SQL2012\UestDBTDECert'

    WITH PRIVATE KEY ( FILE = 'D:\SQL2012\UestDBTDECertPrivateKey' ,

    ENCRYPTION BY PASSWORD = 'RISiS9Ul%CByEk6' );

GO

-- Must backup private key as well

代码1 创建主密钥、证书并备份主密钥、证书

USE UestDB;

GO

-- Create the database encryption key for TDE. Analogous to database master key for data encryption.

CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = TRIPLE_DES_3KEY

    ENCRYPTION BY SERVER CERTIFICATE UestDBTDECert;

GO

-- Get a warning about backing up the key, if you haven't already

-- ...take the advice and back it up!

-- Now need to turn TDE on.

ALTER DATABASE UestDB SET ENCRYPTION ON;

GO

代码2 创建数据库加密密钥并启用TDE
接下来模拟证书和主库密钥丢失的情况
->1 del master_key + certificate
->2 create master_key + certificate
->3 create master_key + restore certificate
->4 restore master_key + certificate
首先备份UestDB数据库

--backup test database

BACKUP DATABASE UestDB

    TO DISK = N'D:\SQL2012\MSSQL11.SQL12\MSSQL\Backup\UestDB.bak'

    WITH NOFORMAT, INIT, NAME = N'UestDB Full Database Backup',

    SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

GO

代码3 备份数据库
1、删除证书、删除master_key+重启数据库服务

USE master

GO

-- Oops! We lost the certificate and don't have a copy!

-- Or, going to restore the database to another server instance

DROP CERTIFICATE UestDBTDECert;

GO

DROP MASTER KEY;

GO

证书'UestDBTDECert'是由主密钥加密的，因此需先删除证书才能删除master_key。重启数据库服务后在对象资源管理器下展开UestDB数据报错：

图1 UestDB不能访问
查看ERRORLOG日志如下所示：

图2 删除证书、删除master_key
2、用源代码重新创建master_key、创建证书+重启数据库服务

-- Create a certificate in master to use with TDE

USE master;

GO

-- TDE hooks into encryption key hierarchy in SQL Server

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!drJP9QXC&Vi%cs';

GO

-- Create the certificate used to protect the database encryption key

CREATE CERTIFICATE UestDBTDECert WITH SUBJECT = 'Certificate to implement TDE on UestDB';

GO

重启数据库服务后，UestDB库依然不能访问，ERRORLOG日志如下所示：

图3 重新创建master_key、创建证书
3、用源代码重新创建master_key、还原证书+重启数据库服务

-- Clean up

USE master;

GO

DROP CERTIFICATE UestDBTDECert;

GO

DROP MASTER KEY;

GO

-- TDE hooks into encryption key hierarchy in SQL Server

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!drJP9QXC&Vi%cs';

GO

-- Restore the certificate

CREATE CERTIFICATE UestDBTDECert

    FROM FILE = 'D:\SQL2012\UestDBTDECert'

    WITH PRIVATE KEY ( FILE = 'D:\SQL2012\UestDBTDECertPrivateKey',

    DECRYPTION BY PASSWORD = 'RISiS9Ul%CByEk6');

重启数据库服务后，UestDB数据库能正常访问(实际上创建mater_key使用其他密码也可以，总结来说就是证书一定要有备份)，ERRORLOG日志如下所示：

图4 重新创建master_key、还原证书
4、还原master_key、还原证书+重启数据库服务

-- Clean up

USE master;

GO

DROP CERTIFICATE UestDBTDECert;

GO

DROP MASTER KEY;

GO

-- Restore the master_key

RESTORE MASTER KEY FROM FILE='D:\SQL2012\MasterKey.bak'

DECRYPTION BY PASSWORD ='pass'--备份数据库主密钥时指定的密码

ENCRYPTION BY PASSWORD ='newpass'--数据库主密钥使用的新密码，除非重新应用Service Master Key的加密,否则需使用此密码显示打开和关闭数据库主密钥)

GO

--打开数据库主密钥

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'newpass'

-- Restore the certificate

CREATE CERTIFICATE UestDBTDECert

    FROM FILE = 'D:\SQL2012\UestDBTDECert'

    WITH PRIVATE KEY ( FILE = 'D:\SQL2012\UestDBTDECertPrivateKey',

    DECRYPTION BY PASSWORD = 'RISiS9Ul%CByEk6');

--关闭数据库主密钥

CLOSE MASTER KEY

ERRORLOG日志如下所示：

图5 还原master_key、还原证书(未应用Service Master Key的加密)
“在执行此操作之前，请在数据库中创建一个主密钥或在会话中打开该主密钥。”此时UestDB数据库不能访问，想想在我们创建证书的时候如果没有用密码打开master key也会报这样的错，这是由于还原出来master key只使用了密码加密，而没有使用Service Master Key加密。因此对于相关的密钥、证书操作都要先使用OPEN MASTER KEY。下面我们在还原master key之后，重新应用Service Master Key的加密

-- Clean up

USE master;

GO

DROP CERTIFICATE UestDBTDECert;

GO

DROP MASTER KEY;

GO

-- Restore the master_key

RESTORE MASTER KEY FROM FILE='D:\SQL2012\MasterKey.bak'

DECRYPTION BY PASSWORD ='pass'--备份数据库主密钥时指定的密码

ENCRYPTION BY PASSWORD ='newpass'--数据库主密钥使用的新密码，除非重新应用Service Master Key的加密,否则需使用此密码显示打开和关闭数据库主密钥)

GO

--打开数据库主密钥

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'newpass'

--重新应用Service Master Key的加密

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY--执行后,数据库主密钥不再需要被显式打开或关闭,拥有足够许可(如sysadmin)的用户自动使用数据库主密钥解密

--关闭数据库主密钥

CLOSE MASTER KEY

-- Restore the certificate

CREATE CERTIFICATE UestDBTDECert

    FROM FILE = 'D:\SQL2012\UestDBTDECert'

    WITH PRIVATE KEY ( FILE = 'D:\SQL2012\UestDBTDECertPrivateKey',

    DECRYPTION BY PASSWORD = 'RISiS9Ul%CByEk6');

ERRORLOG日志如下所示：

图6 还原master_key、还原证书(重新应用Service Master Key的加密)
意外收获：在自己电脑上测试，频繁重启数据库服务，导致SSMS卡死，相关代码却没有保存。等待一段时候SSMS还是未响应：

图7 SSMS未响应
到相关目录寻找是否有临时文件保存了代码

图8 C:\Users\Administrator\Documents\SQL Server Management Studio\Backup Files\Solution1
第二天打开SSMS提示

图9
点击恢复选定的文件，到相应目录查找C:\Users\Administrator\AppData\Local\Temp

图10
算是幸运，文件在当时已经找到。还是要养成随时保存脚本的习惯！
二、列数据加密
此部分内容扩展SQL Server安全系列的第八篇：SQL Server安全数据加密。列数据加密的详细步骤请参考原文。本节重点关注列数据加密迁移及非对称密钥和对称密钥的修改。
首先，我们继续使用本篇第一部分创建的UestDB数据库，创建测试使用的表：

USE UestDB

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoginsInfo]') AND type in (N'U'))

DROP TABLE [dbo].[LoginsInfo]

GO

CREATE TABLE [LoginsInfo](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Item] [varchar](20) NULL,

    [LoginName] [varchar](30) NULL,

    [PassWords] [varbinary](1000) NULL,

    [Notes] [varbinary](1000) NULL,--alter(varchar->varbinary)

    [BindingMail] [varchar](50) NULL,--add column

    [ULR] [varchar](50) NULL,

    [CType] [tinyint] NULL,

    [InsertTime] [datetime] NULL,

 CONSTRAINT [PK_LoginsInfo] PRIMARY KEY CLUSTERED([Id] ASC)

)

GO

ALTER TABLE [dbo].[LoginsInfo] ADD CONSTRAINT [DF_LoginsInfo_InserTime] DEFAULT (getdate()) FOR [InsertTime]

GO

创建Database Master Key,并备份master_key

USE UestDB;

GO

--数据库主密钥创建时默认使用Service Master Key和CREATE MASTER KEY中的密码加密

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'gK#3hbQKDFQY0oF';

GO

--Backup the DatabaseMasterKey

BACKUP MASTER KEY TO FILE = 'D:\SQL2012\UestDBMasterKey.bak' ENCRYPTION BY PASSWORD = 'masterkeybakpwd';

GO

创建非对称密钥，只指定了算法，没有指定PASSWORD子句，默认会使用Database Master Key加密私钥，这也是前面要创建Database Master Key的原因。接着创建对称密钥，使用前面的非对称密钥来加密，注意指定KEY_SOURCE和IDENTITY_VALUE

-- Create an asymmetric key to protect the new symmetric key

CREATE ASYMMETRIC KEY UestAsymmetricKey --名称

    --AUTHORIZATION Uest --所有者(数据库用户、数据库角色)

    WITH ALGORITHM = RSA_2048 --算法

-- 没有指定PASSWORD子句,则用数据库master key加密私钥

-- Create a symmetric key, protected by the asymmetric key

CREATE SYMMETRIC KEY UestSymmetricKey --名称

    WITH KEY_SOURCE ='pass_phrase', --derive the key

    ALGORITHM = TRIPLE_DES, --算法

    IDENTITY_VALUE = 'identity_phrase' --generate a GUID

    ENCRYPTION BY ASYMMETRIC KEY UestAsymmetricKey;--加密机制

使用对称密钥对数据进行加密

-- 打开对称密钥

OPEN SYMMETRIC KEY UestSymmetricKey

    DECRYPTION BY ASYMMETRIC KEY UestAsymmetricKey

INSERT INTO LoginsInfo(Item,LoginName,PassWords,Notes,BindingMail,ULR,CType)

VALUES ('QQ', '',EncryptByKey(Key_GUID('UestSymmetricKey'), 'Pass1')

,EncryptByKey(Key_GUID('UestSymmetricKey') ,'The first and the last letter is capitalized')

,'1234567@qq.com','http://qzone.qq.com/',1);

-- 关闭对称密钥

CLOSE SYMMETRIC KEY UestSymmetricKey;

查看原始数据和解密数据

-- 查看数据

OPEN SYMMETRIC KEY UestSymmetricKey

    DECRYPTION BY ASYMMETRIC KEY UestAsymmetricKey

SELECT Id, Item, LoginName,

    CONVERT(VARCHAR, DecryptByKey(PassWords)) AS PassWords,

    CONVERT(VARCHAR, DecryptByKey(Notes)) AS Notes,

    BindingMail,ULR,Ctype

FROM LoginsInfo;

CLOSE SYMMETRIC KEY UestSymmetricKey;

从结果可以看到存储在表中的PassWords字段已加密，需使用对应的密钥解密才能看到真实数据。
列数据加密如何迁移
假如现在需要将LoginsInfo表中的数据导入到另一个库/另一个实例下的一张数据表中，导过去后如何查看真实数据？

DROP TABLE DBA_Monitor.dbo.LoginsInfo

SELECT *

INTO DBA_Monitor.dbo.LoginsInfo

FROM LoginsInfo

可以通过导入/导出、select...into...等多种方式将数据转移到其他表。用源代码在目标数据库创建相同的非对称密钥和对称密钥

USE DBA_Monitor;

GO

--数据库主密钥创建时默认使用Service Master Key和CREATE MASTER KEY中的密码加密

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'gK#3hbQKDFQY0oF';

GO

-- Create an asymmetric key to protect the new symmetric key

CREATE ASYMMETRIC KEY UestAsymmetricKey --名称

    --AUTHORIZATION Uest --所有者(数据库用户、数据库角色)

    WITH ALGORITHM = RSA_2048 --算法

-- 没有指定PASSWORD子句,则用数据库master key加密私钥

-- Create a symmetric key, protected by the asymmetric key

CREATE SYMMETRIC KEY UestSymmetricKey --名称

    WITH KEY_SOURCE ='pass_phrase', --derive the key

    ALGORITHM = TRIPLE_DES, --算法

    IDENTITY_VALUE = 'identity_phrase' --generate a GUID

    ENCRYPTION BY ASYMMETRIC KEY UestAsymmetricKey;--加密机制

MASTER KEY中的PASSWORD不一定要和原来的一样，但是对称密钥中的KEY_SOURCE和IDENTITY_VALUE必须与原来的保持一样。查看目标表中的数据

USE DBA_Monitor;

GO

-- 查看数据

OPEN SYMMETRIC KEY UestSymmetricKey

    DECRYPTION BY ASYMMETRIC KEY UestAsymmetricKey

SELECT Id, Item, LoginName,

    CONVERT(VARCHAR, DecryptByKey(PassWords)) AS PassWords,

    CONVERT(VARCHAR, DecryptByKey(Notes)) AS Notes,

    BindingMail,ULR,Ctype

FROM LoginsInfo;

CLOSE SYMMETRIC KEY UestSymmetricKey;

至此在新环境中已经能够正常查看加密数据。
修改Database Master Key、非对称密钥和对称密钥
Database Master Key的修改和还原

--重新生成数据库主密钥

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD ='password'

GO

--删除Service Master Key的加密

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY--执行后,任何数据库主密钥的修改需要使用OPEN MASTER KEY访问

--使用密码打开数据库主密钥

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

--重新应用Service Master Key的加密

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY--执行后,数据库主密钥不再需要被显式打开或关闭,拥有足够许可(如sysadmin)的用户自动使用数据库主密钥解密

--关闭数据库主密钥

CLOSE MASTER KEY

-- Restore the DatabaseMasterKey

RESTORE MASTER KEY FROM FILE='D:\SQL2012\UestDBMasterKey.bak'

DECRYPTION BY PASSWORD ='masterkeybakpwd'--备份数据库主密钥时指定的密码

ENCRYPTION BY PASSWORD ='openpassword'--还原后数据库主密钥使用的密码(如果删除Service Master Key,则需使用此密码打开数据库主密钥)

GO

修改非对称密钥

/****修改非对称密钥****/

--修改私钥加密方式

ALTER ASYMMETRIC KEY UestAsymmetricKey--要修改的密钥名称

WITH PRIVATE KEY --私钥

(ENCRYPTION BY PASSWORD = 'AsymPass!')--指定密码加密私钥;执行后,与数据库master key就没有关联,此时drop master key不会报错

-- 查看数据

OPEN SYMMETRIC KEY UestSymmetricKey

    DECRYPTION BY ASYMMETRIC KEY UestAsymmetricKey

    WITH PASSWORD ='AsymPass!';--先用私钥密码打开对称密钥

SELECT Id, Item, LoginName,

    CONVERT(VARCHAR, DecryptByKey(PassWords)) AS PassWords,

    Notes,ULR,Ctype

FROM LoginsInfo;

CLOSE SYMMETRIC KEY UestSymmetricKey;

--修改私钥密码

ALTER ASYMMETRIC KEY UestAsymmetricKey--要修改的密钥名称

WITH PRIVATE KEY --私钥

(ENCRYPTION BY PASSWORD = 'NewAsymPass',--指定新密码

DECRYPTION BY PASSWORD = 'AsymPass!')--旧密码是用来解密的                                    

-- 查看数据

OPEN SYMMETRIC KEY UestSymmetricKey

    DECRYPTION BY ASYMMETRIC KEY UestAsymmetricKey

    WITH PASSWORD ='NewAsymPass';--先用私钥密码打开对称密钥

SELECT Id, Item, LoginName,

    CONVERT(VARCHAR, DecryptByKey(PassWords)) AS PassWords,

    Notes,ULR,Ctype

FROM LoginsInfo;

CLOSE SYMMETRIC KEY UestSymmetricKey;

修改对称密钥

/****修改对称密钥的加密方式****/

--先用私钥密码打开对称密钥

OPEN SYMMETRIC KEY UestSymmetricKey

    DECRYPTION BY ASYMMETRIC KEY UestAsymmetricKey

    WITH PASSWORD ='NewAsymPass';

--打开之后,先增加密码加密,取代原密钥

ALTER SYMMETRIC KEY UestSymmetricKey

ADD ENCRYPTION BY PASSWORD ='SysPass!'

--再删除非对称密钥加密

ALTER SYMMETRIC KEY UestSymmetricKey

DROP ENCRYPTION BY ASYMMETRIC KEY UestAsymmetricKey --执行后,与非对称密钥没有关联,此时DROP ASYMMETRIC KEY不会报错

--完成操作后，关闭对称密钥

CLOSE SYMMETRIC KEY UestSymmetricKey

-- 查看数据

OPEN SYMMETRIC KEY UestSymmetricKey

    DECRYPTION BY PASSWORD ='SysPass!';--直接用密码打开对称密钥

SELECT Id, Item, LoginName,

    CONVERT(VARCHAR, DecryptByKey(PassWords)) AS PassWords,

    Notes,ULR,Ctype

FROM LoginsInfo;

CLOSE SYMMETRIC KEY UestSymmetricKey;

-- *** Clean up ***

-- ****************

USE DBA_Monitor

GO

DROP SYMMETRIC KEY UestSymmetricKey

DROP ASYMMETRIC KEY UestAsymmetricKey

DROP MASTER KEY

GO

USE master;

GO

IF DB_ID('UestDB') IS NOT NULL DROP DATABASE UestDB;

GO

-- Can't turn off TDE in tempdb once it is on

DROP CERTIFICATE UestDBTDECert;

GO

DROP MASTER KEY;

GO

此部分主要是查看Database Master Key、非对称密钥和对称密钥的加密方式

码农公寓

相关文章