Percona MySQL Encryption - Part 1

Percona MySQL Encryption is a powerful tool to ensure data security and privacy. It is an open source solution that provides encryption for the MySQL database. Percona provides a variety of encryption options for users to choose from, and it is easy to implement and maintain.

OS Version:?"Ubuntu 20.04.3 LTS"

Database:?8.0.30-22 Percona Server

Mysql Keyring Encryption

Before beginning the process, it is essential to understand why we are doing this.

In Mysql, If the data directory is moved to another server?and the database is started, all tables will be accessible. However, if the keyring encryption is configured, even if the data directory files are taken (or stolen), the tables will not be accessible, since the tables can only be accessed through the keyring file. If the keyring file isn't available when the database is started, the tables will be inaccessible.

MySQL generates a .idb file in the data directory for each table. If we utilize the string function of this file, the content will be visible; however, if the keyring encryption is used, the .idb file will be encrypted, thus preventing us from viewing the content.

Now Let's begin with some hands-on work.

• Edit the my.cnf file.

vi /etc/mysql/conf.d/my.cnf

[mysqld]

keyring_file_data=/var/lib/mysql-keyring/keyring

early-plugin-load=keyring_file.so        

• Login to the database and install keyring plugin

mysql> INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';

?

• Restart the Database

service mysql restart

• Now VERIFY KEYRING INSTALLATION which we installed in the previous step.

mysql> SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS?WHERE plugin_name LIKE 'keyring%';

?mysql> show global variables like '%keyring%';

• We will now create two tables in order to test the encryption; one table will be encrypted and the other will remain unencrypted so we can observe the difference. Will also insert few records in the table.

create database enc;

use enc;

CREATE TABLE UNENCRYPTED_table(Col1 VARCHAR(255));

insert into UNENCRYPTED_table values('This is Un-Encrypted Data');

insert into UNENCRYPTED_table values('This is Un-Encrypted Data1' );

insert into UNENCRYPTED_table values('This is Un-Encrypted Data3' );


CREATE TABLE ENCRYPTED_TABLE(col1 VARCHAR(255)) ENCRYPTION='Y';

insert into ENCRYPTED_TABLE?values('This is Encrypted Data1' );

insert into ENCRYPTED_TABLE?values('This is Encrypted Data2' );

insert into ENCRYPTED_TABLE?values('This is Encrypted Data3' );;        

• Now Lets verify the ibd file of both the tables.

UNENCRYPTED_table

ENCRYPTED_TABLE

• Let's now check by deleting the keyring file and restarting the database.

Before removing we have verified that the tables are accessible.

?

Let's now create a backup of the keyring file, delete the original, and then restart the database.

cd /var/lib/mysql-keyring

cp -rp keyring keyring_bkp

rm keyring

ls -ltrh/

service mysql restart        

In the database error log file you can see the errors as well.

 
Encryption can't find master key, please check the keyring is loaded

Encryption information in datafile: ./enc/ENCRYPTED_TABLE.ibd can't be de crypted, please confirm that keyring is loaded..        

Now let's look into the tables, we will find that the encrypted table is not available to us, whereas the unencrypted table is accessible.

select * from ENCRYPTED_TABLE;
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.        

Summary:

Database encryption is an important tool for protecting sensitive data. It can be used to secure data stored on disks and tapes, as well as data transmitted over networks. By encrypting data, organizations can ensure that only authorized users can access the data and that it remains safe from malicious actors. Encryption also provides an additional layer of security, as it can protect data even if the database is compromised. With the right encryption methods and strategies, organizations can ensure that their databases are secure and that their data is properly protected.

PS:?If you spot any errors or have a suggestion for a better technique, please leave a comment. Thank you!?