mysql 加密 tde_TDE与列级数据加密

一、测试TDE

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

--Create a test database

CREATE DATABASEUestDBGO

--Create a certificate in master to use with TDE

USEmaster;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',

ENCRYPTIONBY PASSWORD = 'RISiS9Ul%CByEk6');GO

--Must backup private key as well

View Code

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

USEUestDB;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

ENCRYPTIONBYSERVER 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

View Code

代码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 DATABASEUestDBTO 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

View Code

代码3 备份数据库

1、删除证书、删除master_key+重启数据库服务

USEmasterGO

--Oops! We lost the certificate and don't have a copy!--Or, going to restore the database to another server instance

DROPCERTIFICATE UestDBTDECert;GO

DROP MASTER KEY;GO

View Code

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

图1 UestDB不能访问

查看ERRORLOG日志如下所示：

图2 删除证书、删除master_key

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

--Create a certificate in master to use with TDE

USEmaster;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

View Code

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

图3 重新创建master_key、创建证书

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

--Clean up

USEmaster;GO

DROPCERTIFICATE 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

CREATECERTIFICATE UestDBTDECertFROM FILE = 'D:\SQL2012\UestDBTDECert'

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

DECRYPTIONBY PASSWORD = 'RISiS9Ul%CByEk6');

View Code

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

图4 重新创建master_key、还原证书

4、还原master_key、还原证书+重启数据库服务

--Clean up

USEmaster;GO

DROPCERTIFICATE UestDBTDECert;GO

DROP MASTER KEY;GO

--Restore the master_key

RESTORE MASTER KEY FROM FILE='D:\SQL2012\MasterKey.bak'DECRYPTIONBY PASSWORD ='pass'--备份数据库主密钥时指定的密码

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

GO

--打开数据库主密钥

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'newpass'

--Restore the certificate

CREATECERTIFICATE UestDBTDECertFROM FILE = 'D:\SQL2012\UestDBTDECert'

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

DECRYPTIONBY PASSWORD = 'RISiS9Ul%CByEk6');--关闭数据库主密钥

CLOSE MASTER KEY

View Code

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

USEmaster;GO

DROPCERTIFICATE UestDBTDECert;GO

DROP MASTER KEY;GO

--Restore the master_key

RESTORE MASTER KEY FROM FILE='D:\SQL2012\MasterKey.bak'DECRYPTIONBY 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

CREATECERTIFICATE UestDBTDECertFROM FILE = 'D:\SQL2012\UestDBTDECert'

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

DECRYPTIONBY PASSWORD = 'RISiS9Ul%CByEk6');

View Code

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数据库，创建测试使用的表：

USEUestDBGO

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

View Code

创建Database Master Key,并备份master_key

USEUestDB;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

View Code

创建非对称密钥，只指定了算法，没有指定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;--加密机制

View Code

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

--打开对称密钥

OPEN SYMMETRIC KEYUestSymmetricKey

DECRYPTIONBY ASYMMETRIC KEYUestAsymmetricKeyINSERT INTOLoginsInfo(Item,LoginName,PassWords,Notes,BindingMail,ULR,CType)VALUES ('QQ', '1234567',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;

View Code

查看原始数据和解密数据

--查看数据

OPEN SYMMETRIC KEYUestSymmetricKey

DECRYPTIONBY ASYMMETRIC KEYUestAsymmetricKeySELECTId, Item, LoginName,CONVERT(VARCHAR, DecryptByKey(PassWords)) ASPassWords,CONVERT(VARCHAR, DecryptByKey(Notes)) ASNotes,

BindingMail,ULR,CtypeFROMLoginsInfo;CLOSE SYMMETRIC KEY UestSymmetricKey;

View Code

从结果可以看到存储在表中的PassWords字段已加密，需使用对应的密钥解密才能看到真实数据。

列数据加密如何迁移

假如现在需要将LoginsInfo表中的数据导入到另一个库/另一个实例下的一张数据表中，导过去后如何查看真实数据？

DROP TABLEDBA_Monitor.dbo.LoginsInfoSELECT *

INTODBA_Monitor.dbo.LoginsInfoFROM LoginsInfo

View Code

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

USEDBA_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;--加密机制

View Code

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

USEDBA_Monitor;GO

--查看数据

OPEN SYMMETRIC KEYUestSymmetricKey

DECRYPTIONBY ASYMMETRIC KEYUestAsymmetricKeySELECTId, Item, LoginName,CONVERT(VARCHAR, DecryptByKey(PassWords)) ASPassWords,CONVERT(VARCHAR, DecryptByKey(Notes)) ASNotes,

BindingMail,ULR,CtypeFROMLoginsInfo;CLOSE SYMMETRIC KEY UestSymmetricKey;

View Code

至此在新环境中已经能够正常查看加密数据。

修改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'DECRYPTIONBY PASSWORD ='masterkeybakpwd'--备份数据库主密钥时指定的密码

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

GO

View Code

修改非对称密钥

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

--修改私钥加密方式

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

WITH PRIVATE KEY --私钥

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

--查看数据

OPEN SYMMETRIC KEYUestSymmetricKey

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

SELECTId, Item, LoginName,CONVERT(VARCHAR, DecryptByKey(PassWords)) ASPassWords,

Notes,ULR,CtypeFROMLoginsInfo;CLOSE SYMMETRIC KEYUestSymmetricKey;--修改私钥密码

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

WITH PRIVATE KEY --私钥

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

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

--查看数据

OPEN SYMMETRIC KEYUestSymmetricKey

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

SELECTId, Item, LoginName,CONVERT(VARCHAR, DecryptByKey(PassWords)) ASPassWords,

Notes,ULR,CtypeFROMLoginsInfo;CLOSE SYMMETRIC KEY UestSymmetricKey;

View Code

修改对称密钥

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

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

OPEN SYMMETRIC KEYUestSymmetricKey

DECRYPTIONBY ASYMMETRIC KEYUestAsymmetricKeyWITH PASSWORD ='NewAsymPass';--打开之后,先增加密码加密,取代原密钥

ALTER SYMMETRIC KEYUestSymmetricKeyADD ENCRYPTION BY PASSWORD ='SysPass!'

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

ALTER SYMMETRIC KEYUestSymmetricKeyDROP ENCRYPTION BY ASYMMETRIC KEY UestAsymmetricKey --执行后,与非对称密钥没有关联,此时DROP ASYMMETRIC KEY不会报错--完成操作后，关闭对称密钥

CLOSE SYMMETRIC KEYUestSymmetricKey--查看数据

OPEN SYMMETRIC KEYUestSymmetricKey

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

SELECTId, Item, LoginName,CONVERT(VARCHAR, DecryptByKey(PassWords)) ASPassWords,

Notes,ULR,CtypeFROMLoginsInfo;CLOSE SYMMETRIC KEY UestSymmetricKey;

View Code

--*** Clean up ***--****************

USEDBA_MonitorGO

DROP SYMMETRIC KEYUestSymmetricKeyDROP ASYMMETRIC KEYUestAsymmetricKeyDROP MASTER KEY

GO

USEmaster;GO

IF DB_ID('UestDB') IS NOT NULL DROP DATABASEUestDB;GO

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

DROPCERTIFICATE UestDBTDECert;GO

DROP MASTER KEY;GO

View Code

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