Enforce Transparent Encryption (TDE) on MySQL Tables (Windows version)

Page update date : Sunday, April 28, 2024

Page creation date : Tuesday, January 16, 2024

Operating environment

MySQL

MySQL 8.0 Community Edition

Windows

Windows 11

Prerequisites

MySQL

MySQL 8.0 Community Edition
MySQL 8.0 Enterprise Edition

Windows

Windows 11
Windows 10

Windows Server

Windows Server 2022
Windows Server 2019
Windows Server 2016
Windows Server 2012 R2

precondition

A database for MySQL is installed.

About Transparent Encryption

Enterprise Transparent Data Encryption, as the name implies, is an encryption technology that is more secure than anything that doesn't. Normally, when encrypting, it is often a burden on the implementer because it is necessary to perform encryption and decryption processing. The meaning of this name "transparent" is that it can be encrypted without the implementer making any changes to the process. Therefore, if you make a few changes to the settings at the beginning, the rest will be encrypted without permission, so it is a very effective security measure.

However, as the phrase "no need to change anything" suggests, if you actually log in to the database, you can see the data normally, and if you get the database record from the program, you can see the data normally. If the acquired data is encrypted, it will be necessary to change the process, so it will not match the wording "No need to change the process".

Then, in MySQL, what exactly is transparent encryption is encrypted, "stored database files" or "table files" are subject to encryption. As we will check later, even if you open the file directly, it only encrypts it so that you cannot see the contents, so it is necessary to operate it so that it is not logged in to the database in actual operation.

Create a table normally and check the contents

First, let's create a table and records normally. You can create it however you want, but in this case, we'll create it by executing SQL in Workbench. In this case, we plan to set transparent encryption for the table, so we will create the database normally.

CREATE DATABASE `test_database`

The table is also made normally. The contents of the column can be anything.

CREATE TABLE `plain_user` (
  `id` int NOT NULL,
  `name` varchar(32) ,
  `age` int ,
  `address` varchar(256) ,
  `phone` varchar(32) ,
  `email` varchar(128) ,
  `remarks` varchar(1024) ,
  `height` decimal(5, 2) ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I'll also put in a random record to check the encryption.

INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('1','名前1','11','宮城県仙台市1','000-0000-0001','example1@example.com','備考1','170.00');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('2','名前2','12','宮城県仙台市2','000-0000-0002','example2@example.com','備考2','171.01');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('3','名前3','13','宮城県仙台市3','000-0000-0003','example3@example.com','備考3','172.02');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('4','名前4','14','宮城県仙台市4','000-0000-0004','example4@example.com','備考4','173.03');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('5','名前5','15','宮城県仙台市5','000-0000-0005','example5@example.com','備考5','174.04');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('6','名前6','16','宮城県仙台市6','000-0000-0006','example6@example.com','備考6','175.05');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('7','名前7','17','宮城県仙台市7','000-0000-0007','example7@example.com','備考7','176.06');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('8','名前8','18','宮城県仙台市8','000-0000-0008','example8@example.com','備考8','177.07');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('9','名前9','19','宮城県仙台市9','000-0000-0009','example9@example.com','備考9','178.08');
INSERT INTO `test_database`.`plain_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('10','名前10','20','宮城県仙台市10','000-0000-0010','example10@example.com','備考10','179.09');

When you create a database or table, it is created in the following folder by default.

C:\ProgramData\MySQL\MySQL Server 8.0\Data

ProgramData The folder is hidden, so make it visible in the Explorer settings.

There is a folder with the name of the database you created in the folder, so open it.

Inside there is a table-by-table file.

Open this file in a text editor such as Notepad. Since it is a binary file, I think that there is basically a line of text that I don't understand well. If you look at the bottom, you will see the text of the registered record.

If it is not encrypted in this way, the contents of the record will be known even if the file is opened directly. You don't have to log in to the database to know what's inside.

Configuring Transparent Encryption Settings

To enable transparent encryption, you need to install a plug-in. First, check if the plug-in is included with the command.

Start it by selecting "MySQL 8.0 Command Line Client" from the Start menu.

After entering your password and logging in, enter the following command:

SELECT PLUGIN_NAME,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

Since you haven't installed it yet, it should be empty.

Now, let's set up the plug-in. First, create the following folders: In fact, it doesn't matter where you create it or what name it is. Specify it as the location where you want to save the key file later.

C:\ProgramData\MySQL\mysql-keyring

Next, open the following file in a text editor:

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Add the following text to the empty space at the bottom and save it. If you can't save with administrator privileges, you can save it to another location and then overwrite the file.

early-plugin-load=keyring_file.dll
keyring_file_data=C:\ProgramData\MySQL\mysql-keyring\keyring

Restart the MySQL service. You can also restart it with the command.

When you restart, the file is automatically created in the specified folder.

You can verify that the plug-in is installed by running the check plug-in command again.

SELECT PLUGIN_NAME,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

Creating an Encrypted Table

Now let's create an encrypted table. Create a table similar to the previous one, but this time add to the ENCRYPTION='Y' options. This encryption setting does not seem to be possible in the GUI at this time, so it must be set with a command.

CREATE TABLE `encrypt_user` (
  `id` int NOT NULL,
  `name` varchar(32) ,
  `age` int ,
  `address` varchar(256) ,
  `phone` varchar(32) ,
  `email` varchar(128) ,
  `remarks` varchar(1024) ,
  `height` decimal(5, 2) ,
PRIMARY KEY (`id`)
) ENCRYPTION='Y' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

By the way, before the plug-in is configured, even if you try to create an encrypted table, the following error will be displayed.

Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
キーリングからマスターキーが見つかりません。キーリングが正常にロードされ、初期化されているかどうかをサーバーログで確認してください。

If you don't have any data, you don't know if it's encrypted, so I'll try to put the same data.

INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('1','名前1','11','宮城県仙台市1','000-0000-0001','example1@example.com','備考1','170.00');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('2','名前2','12','宮城県仙台市2','000-0000-0002','example2@example.com','備考2','171.01');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('3','名前3','13','宮城県仙台市3','000-0000-0003','example3@example.com','備考3','172.02');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('4','名前4','14','宮城県仙台市4','000-0000-0004','example4@example.com','備考4','173.03');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('5','名前5','15','宮城県仙台市5','000-0000-0005','example5@example.com','備考5','174.04');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('6','名前6','16','宮城県仙台市6','000-0000-0006','example6@example.com','備考6','175.05');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('7','名前7','17','宮城県仙台市7','000-0000-0007','example7@example.com','備考7','176.06');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('8','名前8','18','宮城県仙台市8','000-0000-0008','example8@example.com','備考8','177.07');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('9','名前9','19','宮城県仙台市9','000-0000-0009','example9@example.com','備考9','178.08');
INSERT INTO `test_database`.`encrypt_user` (`id`,`name`,`age`,`address`,`phone`,`email`,`remarks`,`height`) VALUES ('10','名前10','20','宮城県仙台市10','000-0000-0010','example10@example.com','備考10','179.09');

encrypt_user Since the table file has been created, let's check the contents.

The file has been encrypted, and the contents of the record cannot be verified.

For the time being, you no longer have to worry about a third party reading the records inside just by logging in to the server and stealing the table file directly. There is no need to change the implementation at all, so I think it is an easy security setting.

However, if you log in to the database, you can see the data normally. If you want to increase the level of security, you need to manage the database login account well and introduce a separate encryption mechanism.

Convert to an encrypted table along the way

If you initially create an unencrypted table, you can convert it to an encrypted table later. In that case, you can convert it with the following SQL:

use <データベース名>;
ALTER TABLE <テーブル名> ENCRYPTION='Y';

example

use test_database;
ALTER TABLE plain_user ENCRYPTION='Y';

However, please note that it will take time if there are many records because all the table files are rewritten.

Backing Up and Restoring MySQL Databases (Windows Version)