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、创建证书+重启数据库服务