Before getting more into this article, let us know what are possible combined approaches to secure MySQL data <\/p>\n\n\n\n
- Mysql Server hardening<\/li>
- Mysql Application-level hardening<\/li>
- Mysql data encryption at transit<\/li>
- Mysql data at rest encryption<\/strong><\/li>
- Mysql Disk Encryption<\/li><\/ol>\n\n\n\n
You may explore all the approaches but in this article, we will understand the concept of Mysql data at encryption<\/strong> and hands-on too.<\/p>\n\n\n\n
The concept of \u201cData at Rest Encryption\u201d 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 \u201cData at Rest Encryption\u201d in MySQL <\/p>\n\n\n\n
What is \u201cData at Rest Encryption\u201d in MySql?<\/h3>\n\n\n\n
The concept of \u201cdata at rest encryption\u201d uses two-tier encryption key architecture, which used below two keys <\/p>\n\n\n\n
- Tablespace keys:<\/strong> This is an encrypted key which is stored in the tablespace header <\/li>
- Master Key:<\/strong> the Master key is used to decrypt the tablespace keys<\/li><\/ol>\n\n\n\n
<\/figure>\n\n\n\n
So let’s Understand its working<\/h4><\/p>\n\n\n\n
Let’s say we have a running MySQL with InnoDB storage engine and tablespace is encrypted using a key, referred as table space key. This key is then encrypted using a master key and stored in the tablespace header <\/p>\n\n\n\n
Now when a request is made to access MySQL data, InnoDB use master key to decrypt tablespace key present tablespace header. After getting decrypted tablespace key, the tablespace is decrypted and make is available to perform read\/write operations<\/p>\n\n\n\n
Note: <\/strong>The decrypted version of a tablespace key never changes, but the master key can be rotated.<\/span><\/em><\/p>\n\n\n\n
Data at rest encryption implemented using keyring file plugin to manage and encrypt the master key<\/p>\n\n\n\n
After understanding the concept of encryption and decryption below are few Pros and Cons for using DRE<\/strong><\/p>\n\n\n\n
Pros:<\/strong><\/span><\/h3><\/p>\n\n\n\n
- A strong Encryption of AES 256 is used to encrypt the InnoDB<\/em> tables<\/li>
- It is transparent to all applications as we don’t need any application code, schema, or data type changes<\/li>
- Key management is not done by DBA.<\/li>
- Keys can be securely stored away from the data and key rotation is very simple.<\/li><\/ul>\n\n\n\n
Cons:<\/strong><\/span><\/h3><\/p>\n\n\n\n
- Encrypts only InnoDB tables<\/li>
- Can\u2019t encrypt binary logs, redo logs, relay logs on unencrypted slaves, slow log, error log, general log, and audit log<\/li><\/ul>\n\n\n\n
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.\u00a0 by enabling few flags in MariaDB Config File<\/p>\r\n
innodb_sys_tablespace_encrypt=ON<\/span>
innodb_temp_tablespace_encrypt=ON<\/span>
innodb_parallel_dblwr_encrypt=ON<\/span>
innodb_encrypt_online_alter_logs=ON<\/span>
innodb_encrypt_tables=FORCE<\/span>
encrypt_binlog=ON<\/span>
encrypt_tmp_files=ON<\/span><\/pre>\r\nHowever, there are some limitations\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
Let’s Discuss its problem\/solutions and few solutions to them<\/h5>\r\n
\n\n\n\n<\/p>\r\n
- \r\n
- Running MySQL on a host will have access from root user and the MySQL user and both of them may access key file(keyring file) present on the same system.\u00a0For this problem, we may have our keys on mount\/unmount drive which can be unmounted after restarting MySQL.<\/li>\r\n
- Data will not be in encrypted form when it will get loaded onto the RAM and can be dumped and read<\/li>\r\n
- If MySQL is restarted with skip-grant-tables<\/a> then again it’s havoc but this can be eliminated using an unmounted drive for keyring<\/li>\r\n
- \u00a0As tablespace key remains the same so our security relies on Master key rotation which can be used\u00a0 to save our master key\u00a0<\/li>\r\n<\/ol>\r\n
\n\n\n\n<\/p>\r\n
NOTE<\/strong>: Do not to lose the master key file, as we cant decrypt data and will suffer data loss<\/span><\/em><\/p>\r\n
\n\n\n\n<\/p>\r\n
Doing Is Learning, so let’s try\u00a0<\/h3>\r\n
\n\n\n\n<\/p>\r\n
As a prerequisite, we need a machine with MySQL server up and running Now for data at rest encryption to work we need to enable\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
\r\n\r\nEnable file per table on with the help of the configuration file.\u00a0\u00a0<\/p>\r\n<\/div>\r\n<\/div>\r\n
\u00a0<\/div>\r\n\r\n[root@mysql ~]#\u00a0 vim \/etc\/my.cnf
[mysqld]
innodb_file_per_table=ON<\/strong><\/pre>\r\n<\/div><\/div>\r\n\n\n\n\n<\/p>\r\n
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. Keyring plugin is already installed in MySQL server we just need to enable it.\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
[root@mysql ~]#\u00a0 vim \/etc\/my.cnf
[mysqld]
early-plugin-load=keyring_file.so<\/strong>
keyring_file_data=\/var\/lib\/mysql\/keyring-data\/keyring<\/strong>
innodb_file_per_table=ON<\/pre>\r\n\n\n\n\n<\/p>\r\n
And save the file with a restart to MySQL<\/p>\r\n
\n\n\n\n<\/p>\r\n
[root@mysql ~]#\u00a0 systemctl restart mysql<\/pre>\r\n
\n\n\n\n<\/p>\r\n
We can check for the enabled plugin and verify our configuration.<\/p>\r\n
\n\n\n\n<\/p>\r\n
mysql> SELECT plugin_name, plugin_status FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name LIKE 'keyring%';
+--------------+---------------+
| plugin_name\u00a0 | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE\u00a0 \u00a0 \u00a0\u00a0\u00a0 |
+--------------+---------------+
1 rows in set (0.00 sec)
<\/pre>\r\n\n\n\n\n<\/p>\r\n
verify that we have a running keyring plugin and its location<\/p>\r\n
mysql>\u00a0 show global variables like '%keyring%';
+--------------------+-------------------------------------+
| Variable_name\u00a0 \u00a0 \u00a0 | Value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
+--------------------+-------------------------------------+
| keyring_file_data\u00a0 | \/var\/lib\/mysql\/keyring-data\/keyring |
| keyring_operations | ON\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
+--------------------+-------------------------------------+
2 rows in set (0.00 sec)<\/pre>\r\nVerify that we have enabled file per table\u00a0<\/p>\r\n
MariaDB [(none)]> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name \u00a0 \u00a0 \u00a0\u00a0\u00a0 | Value |
+-----------------------+-------+
| innodb_file_per_table | ON \u00a0 |
+-----------------------+-------+
1 row in set (0.33 sec)<\/pre>\r\nNow we will test our set up by creating a test DB with a table and insert some value to the table using below commands\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
mysql> CREATE DATABASE test_db;
mysql> CREATE TABLE test_db.test_db_table (id int primary key auto_increment, payload varchar(256)) engine=innodb;
mysql> INSERT INTO test_db.test_db_table(payload) VALUES('Confidential Data');<\/pre>\r\n\n\n\n\n<\/p>\r\n
After successful test data creation, run below command from the Linux shell to check whether you’re able to read InnoDB file for your created table i.e. Before encryption<\/p>\r\n
\n\n\n\n<\/p>\r\n
Along with that, we see that our keyring file is also empty before encryption is enabled<\/p>\r\n
\n\n\n\n<\/p>\r\n
[root@mysql ~]#\u00a0 strings \/var\/lib\/mysql\/test_db\/test_db_table.ibd
infimum
supremum
Confidential DATA<\/pre>\r\n\n\n\n\n<\/p>\r\n
<\/p>\r\n
\n\n\n\n<\/p>\r\n
At this point of time if we try to check our keyring file we will not find anything<\/p>\r\n
[root@mysql ~]#\u00a0 cat \/var\/lib\/mysql\/keyring
[root@mysql ~]#\u00a0<\/pre>\r\nNow let\u2019s encrypt our table with below command and check our InnoDB file and keyring file content.<\/p>\r\n
\n\n\n\n<\/p>\r\n
mysql> ALTER TABLE test_db.test_db_table encryption='Y';
[root@mysql ~] strings \/var\/lib\/mysql\/test_db\/test_db_table.ibd
0094ca6d-7ba9-11e9-b0d0-0800275716d42QMw<\/pre>\r\n\n\n\n\n<\/p>\r\n
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.<\/p>\r\n
\n\n\n\n<\/p>\r\n
\u00a0Note:<\/strong> Please look\u00a0 master Key and time stamp(we will implement key rotation )<\/em><\/span><\/p>\r\n
\n\n\n\n<\/p>\r\n
[root@mysql ~] \u00a0cat \/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\u02b9: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<\/pre>\r\n\n\n\n\n<\/p>\r\n
With known security concern for the compromised master key, we may use the master key rotation technique<\/strong> from time to time to save our key.<\/p>\r\n
\n\n\n\n<\/p>\r\n
mysql> alter instance rotate innodb master key;
Query OK, 0 rows affected (0.00 sec)<\/pre>\r\n\n\n\n\n<\/p>\r\n
After this command, we realise that our key timestamp is changed now and we have a new key.\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
[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<\/pre>\r\n\n\n\n\n<\/p>\r\n
Some Useful Commands<\/h3>\r\n
Below are some helpful commands we may use in an encrypted system\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
1. List All the tables with encryption enabled\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
mysql> SELECT * FROM information_schema.tables WHERE create_options LIKE '%ENCRYPTION=\"Y\"%' \\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample_db
TABLE_NAME: test_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:\u00a0
1 row in set (0.02 sec)
ERROR:\u00a0
No query specified<\/pre>\r\n\n\n\n\n<\/p>\r\n
2. Encrypt Tables in a Database\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
mysql> ALTER TABLE db.t1 ENCRYPTION='Y';<\/span><\/pre>\r\n\n\n\n\n<\/p>\r\n
3. Disable encryption for an InnoDB table<\/p>\r\n
\n\n\n\n<\/p>\r\n
mysql> ALTER TABLE t1 ENCRYPTION='N';<\/pre>\r\n
\n\n\n\n<\/p>\r\n
Conclusion :\u00a0<\/h3>\r\n
You can encrypt data at rest by using 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 of system resources. Further with Encryption of data at rest, we may also implement encryption in transit.\u00a0<\/p>\r\n
\n\n\n\n<\/p>\r\n
I hope you found this article informative and interesting. I\u2019d really appreciate any and all feedback.<\/p>\r\n
\n\n\n
<\/p>","protected":false},"excerpt":{"rendered":"
Word \u201cdata\u201d is very crucial since early 2000 and within a span of these 2 decades is it becoming more crucial. According to Forbes Google believe that in future every organisation will lead to becoming a data company. Well, when it comes to data, security is one of the major concerns that we have to … Continue reading “The Concept Of Data At Rest Encryption In MySql”<\/span><\/a><\/p>\n","protected":false},"author":172308123,"featured_media":1510,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[28070474,610,4419],"tags":[54146075,1523131,768739289,676319245],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2019\/09\/data_at_rest_encryption.png","jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pfDBOm-mX","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/1423"}],"collection":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/users\/172308123"}],"replies":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/comments?post=1423"}],"version-history":[{"count":23,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/1423\/revisions"}],"predecessor-version":[{"id":1518,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/1423\/revisions\/1518"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media\/1510"}],"wp:attachment":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media?parent=1423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/categories?post=1423"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/tags?post=1423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- \u00a0As tablespace key remains the same so our security relies on Master key rotation which can be used\u00a0 to save our master key\u00a0<\/li>\r\n<\/ol>\r\n
- Master Key:<\/strong> the Master key is used to decrypt the tablespace keys<\/li><\/ol>\n\n\n\n
- Mysql Disk Encryption<\/li><\/ol>\n\n\n\n