Contact Us
How To Enable MySql “Data At Rest Encryption” CentOs/Ubuntu ?
Data Security has always an issue for DevOps, system admin and security analysts. this article will help you in enabling “Data At Rest Encryption” on your server with running MySQL.
Before getting more into this article, let us know what are possible combined approaches to secure MySQL data
- Mysql Server hardening
- Mysql Application-level hardening
- Mysql data encryption at transit
- Mysql data at rest encryption
- Mysql Disk Encryption
The concept of “Data at Rest Encryption” in MySQL was introduced in Mysql 5.7 with the initial support of InnoDB storage engine only and with the period it has evolved significantly. So let’s understand about “Data at Rest Encryption” in MySQL
What is “Data at Rest Encryption” in MySql?
The concept of “data at rest encryption” uses two-tier encryption key architecture, which used below two keys
- Tablespace keys: This is an encrypted key that is stored in the tablespace header
- Master Key: the Master key is used to decrypt the tablespace keys
So let’s Understand its working
Let’s say we have a running MySQL with InnoDB storage engine and tablespace is encrypted using a key, referred to as table space key. This key is then encrypted using a master key and stored in the tablespace header
Now when a request is made to access MySQL data, InnoDB uses the master key to decrypt the tablespace key present tablespace header. After getting decrypted tablespace key, the tablespace is decrypted and make is available to perform read/write operations
Note: The decrypted version of a tablespace key never changes, but the master key can be rotated.
Data at rest encryption implemented using keyring file plugin to manage and encrypt the master key
After understanding the concept of encryption and decryption below are a few Pros and Cons for using “Data At Rest Encryption“
Pros:
- A strong Encryption of AES 256 is used to encrypt the InnoDB tables
- It is transparent to all applications as we don’t need any application code, schema, or data type changes
- Key management is not done by DBA.
- Keys can be securely stored away from the data and key rotation is very simple.
Cons:
- Encrypts only InnoDB tables
- Can’t encrypt binary logs, redo logs, relay logs on unencrypted slaves, slow log, error log, general log, and audit log
Though we can’t encrypt binary logs, redo logs, relay logs on Mysql 5.7 but MariaDB has implemented this with a mechanism to encrypt undo/redo logs, binary logs/relay logs, etc. by enabling few flags in the MariaDB Config File.
innodb_sys_tablespace_encrypt=ON innodb_temp_tablespace_encrypt=ON innodb_parallel_dblwr_encrypt=ON innodb_encrypt_online_alter_logs=ON innodb_encrypt_tables=FORCE encrypt_binlog=ON encrypt_tmp_files=ON
However, there are some limitations
Let’s Discuss its problem/solutions and few solutions to them
- Running MySQL on a host will have access from the root user and the MySQL user and both of them may access key files (keyring file) present on the same system. For this problem, we may have our keys on the mount/unmount drive which can be unmounted after restarting MySQL.
- Data will not be in encrypted form when it will get loaded onto the RAM and can be dumped and read
- If MySQL is restarted with skip-grant-tables then again it’s havoc but this can be eliminated using an unmounted drive for keyring
- As the tablespace key remains the same so our security relies on Master key rotation which can be used to save our master key
NOTE: Do not lose the master key file, as we cant decrypt data and will suffer data loss
Doing Is Learning, so let’s try
As a prerequisite, we need a machine with a MySQL server up and running Now for data at rest encryption to work we need to enable
Enable file per table with the help of the configuration file.
cat /etc/my.cnf [mysqld]
innodb_file_per_table=ON
Along with the above parameter, enable keyring plugin and keyring path. This parameter should always be on the top in configuration so that it will get load initially when MySQL starts up. The keyring plugin is already installed in the MySQL server we just need to enable it.
vim /etc/my.cnf [mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring-data/keyring
innodb_file_per_table=ON
And save the file with a restart to MySQL
systemctl restart mysql
We can check for the enabled plugin and verify our configuration.
mysql> SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%'; +--------------+---------------+ | plugin_name | plugin_status | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+ 1 rows in set (0.00 sec)
verify that we have a running keyring plugin and its location
mysql> show global variables like '%keyring%'; +--------------------+-------------------------------------+ | Variable_name | Value | +--------------------+-------------------------------------+ | keyring_file_data | /var/lib/mysql/keyring-data/keyring | | keyring_operations | ON | +--------------------+-------------------------------------+ 2 rows in set (0.00 sec)
Verify that we have enabled file per table
MariaDB [(none)]> show global variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.33 sec)
Now we will test our set-up by creating a dummy DB with a table and insert some value to the table using the below commands
mysql> CREATE DATABASE dummy_db;
mysql> CREATE TABLE dummy_db.dummy_db_table (id int primary key auto_increment, payload varchar(256)) engine=innodb;
mysql> INSERT INTO dummy_db.dummy_db_table(payload) VALUES('top secret Data');
After successful test data creation, run the below command from the Linux shell to check whether you’re able to read the InnoDB file for your created table i.e. Before encryption
Along with that, we see that our keyring file is also empty before encryption is enabled
[root@mysql ~]# strings /var/lib/mysql/dummy_db/dummy_db_table.ibd infimum supremum top secret Data
At this point, if we try to check our keyring file we will not find anything
[root@mysql ~]# cat /var/lib/mysql/keyring
Now let’s encrypt our table with the below command and check our InnoDB file and keyring file content.
mysql> ALTER TABLE test_db.test_db_table encryption='Y'; [root@mysql ~] strings /var/lib/mysql/dummy_db/dummy_db_table.ibd 0094ca6d-7ba9-11e9-b0d0-0800275716d42QMw
The above content clear that file data is not readable and table space is encrypted. As previously oy keyring file data was absent/empty, so now it must be having some data.
Note: Please look master Key and time stamp(we will implement key rotation )
[root@mysql ~] cat /var/lib/mysql/keyring-data/keyring Keyring file version:1.0?0 INNODBKey-0094ca6d-7ba9-11e9-b0d0-0800275716d4-2AES???_gd?7m>0??nz??8M??7Yʹ:ll8@?0 INNODBKey-0094ca6d-7ba9-11e9-b0d0-0800275716d4-1AES}??x?$F?z??$???:??k?6y?YEOF [root@mysql ~] ls -ltr /var/lib/mysql/keyring-data/keyring -rw-r----- 1 mysql mysql 283 Sep 18 16:48 /var/lib/mysql/keyring-data/keyring
With known security concerns for the compromised master key, we may use the master key rotation technique from time to time to save our key.
mysql> alter instance rotate innodb master key; Query OK, 0 rows affected (0.00 sec)
After this command, we realise that our key timestamp is changed now and we have a new key.
[root@mysql ~] ls -ltr /var/lib/mysql/keyring-data/keyring -rw-r----- 1 mysql mysql 411 Sep 18 18:17 /var/lib/mysql/keyring-data/keyring
Some Useful Commands
Below are some helpful commands we may use in an encrypted system
1. List All the tables with encryption enabled
mysql> SELECT * FROM information_schema.tables WHERE create_options LIKE '%ENCRYPTION="Y"%' \G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: dummy_db TABLE_NAME: dummy_db_table TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: 2 CREATE_TIME: 2019-09-18 16:46:34 UPDATE_TIME: 2019-09-18 16:46:34 CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: ENCRYPTION="Y" TABLE_COMMENT: 1 row in set (0.02 sec)
ERROR: No query specified
2. Encrypt Tables in a Database
mysql> ALTER TABLE dummy_db.dummy_db_table ENCRYPTION='Y';
3. Disable encryption for an InnoDB table
mysql> ALTER TABLE dummy_db.dummy_db_table ENCRYPTION='N';
Conclusion :
You can encrypt data at rest by using the keyring plugin and we can control and manage it by master key rotation. Creating an encrypted Mysql data file setup is as simple as firing a few simple commands. Using an encrypted system is also transparent to services, applications, and users with minimal impact on system resources. Further with the Encryption of data at rest, we may also implement encryption in transit.
I hope you found this article informative and interesting. I’d really appreciate any feedback.
