{"id":2248,"date":"2018-04-10T15:06:53","date_gmt":"2018-04-10T14:06:53","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2248"},"modified":"2020-05-27T07:48:59","modified_gmt":"2020-05-27T06:48:59","slug":"mysql-security-mysql-enterprise-transparent-data-encryption","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2018\/04\/10\/mysql-security-mysql-enterprise-transparent-data-encryption\/","title":{"rendered":"MySQL Security &#8211; MySQL Enterprise Transparent Data Encryption"},"content":{"rendered":"<p>When thinking about security within a MySQL installation, you should consider a wide range of possible procedures \/ best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools \/ features \/ plugins in order to protect your data including some advanced features like\u00a0 <a href=\"http:\/\/dasini.net\/blog\/2018\/04\/04\/mysql-security-mysql-enterprise-audit\/\" target=\"_blank\" rel=\"noopener noreferrer\">Audit<\/a>, <a href=\"http:\/\/dasini.net\/blog\/2019\/03\/19\/mysql-security-mysql-enterprise-data-masking-and-de-identification\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Data Masking &amp; De-Identification (opens in a new tab)\">Data Masking &amp; De-Identification<\/a>, <a href=\"http:\/\/dasini.net\/blog\/2018\/04\/16\/mysql-security-mysql-enterprise-firewall\/\" target=\"_blank\" rel=\"noopener noreferrer\">Firewall<\/a>, <a href=\"http:\/\/dasini.net\/blog\/2018\/03\/07\/mysql-security-password-management\/\" target=\"_blank\" rel=\"noopener noreferrer\">Password Management<\/a>, <a href=\"http:\/\/dasini.net\/blog\/2018\/03\/01\/mysql-security-password-validation-plugin\/\" target=\"_blank\" rel=\"noopener noreferrer\">Password Validation Plugin<\/a>, <a href=\"http:\/\/dasini.net\/blog\/2018\/03\/14\/mysql-security-user-account-locking\/\" target=\"_blank\" rel=\"noopener noreferrer\">User Account Locking<\/a>, etc&#8230;<\/p>\n<p><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=169%2C179\" alt=\"MySQL Security\" width=\"169\" height=\"179\" \/><\/p>\n<p>In this sixth episode of the\u00a0<strong>MySQL Security<\/strong> series, we will see\u00a0how <strong><a href=\"https:\/\/en.wikipedia.org\/wiki\/Data_at_rest\" target=\"_blank\" rel=\"noopener noreferrer\">data-at-rest<\/a> encryption<\/strong>\u00a0helps organizations implement stronger<strong> security controls<\/strong> and satisfy <strong>regulatory compliance<\/strong>.<\/p>\n<p>You will be able to protect the <strong>privacy<\/strong> of your information, prevent <strong>data breaches<\/strong> and help meet popular regulatory requirements including <a href=\"https:\/\/www.eugdpr.org\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>GDPR<\/strong><\/a>, <a href=\"https:\/\/en.wikipedia.org\/wiki\/Payment_Card_Industry_Data_Security_Standard\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>PCI DSS<\/strong><\/a>, <a href=\"https:\/\/en.wikipedia.org\/wiki\/Health_Insurance_Portability_and_Accountability_Act\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>HIPAA<\/strong><\/a> with <a href=\"https:\/\/www.mysql.com\/products\/enterprise\/tde.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL Enterprise Transparent Data Encryption aka TDE<\/a>.<\/p>\n<h2>MySQL Keyring<\/h2>\n<p>MySQL Enterprise Transparent Data Encryption relies on a <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring.html\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>MySQL Keyring plugin<\/strong><\/a> for master encryption key management. MySQL Server provides many plugin choices including :<\/p>\n<ul>\n<li><strong><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring-file-plugin.html\" target=\"_blank\" rel=\"noopener noreferrer\">keyring_file<\/a><\/strong> : that stores keyring data in a file local to the server host. Available in all MySQL distributions.<\/li>\n<li><strong><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring-encrypted-file-plugin.html\" target=\"_blank\" rel=\"noopener noreferrer\">keyring_encrypted_file<\/a><\/strong> : that stores keyring data in an encrypted file local to the server host. Available in MySQL Enterprise Edition.<\/li>\n<li><strong><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring-okv-plugin.html\" target=\"_blank\" rel=\"noopener noreferrer\">keyring_okv<\/a><\/strong> : a <a href=\"https:\/\/en.wikipedia.org\/wiki\/Key_Management_Interoperability_Protocol\" target=\"_blank\" rel=\"noopener noreferrer\">KMIP<\/a> 1.1 plugin for use with KMIP-compatible backend keyring storage products such as <a href=\"http:\/\/www.oracle.com\/technetwork\/database\/options\/key-management\/overview\/index.html\" target=\"_blank\" rel=\"noopener noreferrer\">Oracle Key Vault<\/a> and Gemalto <a href=\"https:\/\/safenet.gemalto.com\/data-encryption\/enterprise-key-management\/key-secure\/\" target=\"_blank\" rel=\"noopener noreferrer\">SafeNet KeySecure<\/a> Appliance. Available in MySQL Enterprise Edition.<\/li>\n<li><strong><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring-aws-plugin.html\" target=\"_blank\" rel=\"noopener noreferrer\">keyring_aws<\/a><\/strong> : that communicates with the <a href=\"https:\/\/aws.amazon.com\/kms\/\" target=\"_blank\" rel=\"noopener noreferrer\">Amazon Web Services Key Management Service<\/a> for key generation and uses a local file for key storage. Available in MySQL Enterprise Edition.<\/li>\n<\/ul>\n<p>In this article I&rsquo;ll use the\u00a0<strong><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring-encrypted-file-plugin.html\" target=\"_blank\" rel=\"noopener noreferrer\">keyring_encrypted_file<\/a><\/strong>\u00a0plugin with MySQL Enterprise Edition <strong>5.7.21<\/strong>.<\/p>\n<p><em>Updated on 22nd of August 2018<span style=\"text-decoration: underline;\"><br \/>Note<\/span>: MySQL Enterprise Transparent Data Encryption <span style=\"color: #ff0000;\"><strong>works with MySQL 8.0<\/strong><\/span> as well. In other words examples below could be done with <strong>MySQL 8.0.12<\/strong>+<\/em><\/p>\n<p><strong><em><span style=\"text-decoration: underline;\">Warning<\/span><\/em><\/strong><br \/><em>The <strong>keyring_file<\/strong> and <strong>keyring_encrypted_file<\/strong> plugins for encryption key management are not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).<\/em><\/p>\n<p>These keyring plugins would be use with\u00a0<strong>MySQL Enterprise Audit<\/strong>\u00a0to store the audit log file encryption password (you can read my <a href=\"http:\/\/dasini.net\/blog\/2018\/04\/04\/mysql-security-mysql-enterprise-audit\/\" target=\"_blank\" rel=\"noopener noreferrer\">previous article<\/a>) and\u00a0InnoDB tables when encrypted with\u00a0 <strong>MySQL Enterprise\u00a0Transparent Data Encryption<\/strong>, actually the topic of this article but you already know that, right? :).<\/p>\n<h3>Keyring Plugin Installation :\u00a0keyring_encrypted_file<\/h3>\n<p>Like I stated before many keyring plugins are <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.7\/en\/keyring.html\" target=\"_blank\" rel=\"noopener noreferrer\">available<\/a>\u00a0and I&rsquo;ll use the keyring plugin : <strong><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.7\/en\/keyring-encrypted-file-plugin.html\" target=\"_blank\" rel=\"noopener noreferrer\">keyring_encrypted_file<\/a><\/strong>, that stores keyring data in an encrypted file local to the server host.<\/p>\n<p>Only one keyring plugin should be enabled at a time. So let&rsquo;s check that we have none so far :<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Checks keyring plugin\">mysql&gt;\nSELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION \nFROM INFORMATION_SCHEMA.PLUGINS \nWHERE PLUGIN_NAME = 'keyring_encrypted_file'\\G\nEmpty set (0.01 sec)<\/pre>\n<p>To be usable by the server, the plugin library file must be located in the MySQL plugin directory :<\/p>\n<pre class=\"lang:mysql decode:true \" title=\"Plugin keyring list\">mysql&gt;\nSHOW VARIABLES LIKE 'plugin_dir';\n+---------------+------------------+\n| Variable_name | Value            |\n+---------------+------------------+\n| plugin_dir    | \/var\/lib\/plugin\/ |\n+---------------+------------------+\n\n\nsystem ls  \/var\/lib\/plugin\/ | grep keyring\nkeyring_encrypted_file.so\nkeyring_file.so\nkeyring_okv.so\nkeyring_udf.so<\/pre>\n<p>Update the MySQL configuration file and restart the MySQL server :<\/p>\n<pre class=\"lang:vim decode:true\" title=\"install keyring plugin\">[mysqld]\nearly-plugin-load=keyring_encrypted_file.so\nkeyring_encrypted_file_data=\/var\/lib\/mysql-keyring\/keyring-encrypted\nkeyring_encrypted_file_password=V&amp;rySec4eT\n<\/pre>\n<ul>\n<li>To be usable during the server startup process, <em>keyring_encrypted_file<\/em> must be loaded using the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/server-options.html#option_mysqld_early-plugin-load\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>early-plugin-load<\/strong><\/a> option.<\/li>\n<li>To specify the password for encrypting the keyring data file, set the <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.7\/en\/keyring-system-variables.html#sysvar_keyring_encrypted_file_password\" target=\"_blank\" rel=\"noopener noreferrer\"><em>keyring_encrypted_file_password<\/em><\/a> system variable.<\/li>\n<li>The <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.7\/en\/keyring-system-variables.html#sysvar_keyring_encrypted_file_data\" target=\"_blank\" rel=\"noopener noreferrer\"><em>keyring_encrypted_file_data<\/em><\/a> system variable optionally configures the location of the file used by the <em>keyring_encrypted_file<\/em> plugin for data storage.<\/li>\n<\/ul>\n<p><em><strong><span style=\"text-decoration: underline;\">Warning<\/span><\/strong><\/em><br \/><em>Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.<\/em><\/p>\n<pre class=\"lang:mysql decode:true\" title=\"keyring_encrypted_file details\">mysql&gt; \nSELECT PLUGIN_NAME, PLUGIN_TYPE, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION \nFROM INFORMATION_SCHEMA.PLUGINS \nWHERE PLUGIN_NAME = 'keyring_encrypted_file'\\G\n*************************** 1. row ***************************\n       PLUGIN_NAME: keyring_encrypted_file\n       PLUGIN_TYPE: KEYRING\n     PLUGIN_STATUS: ACTIVE\n    PLUGIN_LIBRARY: keyring_encrypted_file.so\nPLUGIN_DESCRIPTION: store\/fetch authentication data to\/from an encrypted file<\/pre>\n<p>We can have a look at the Keyring file. Its location could be found with\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring-system-variables.html#sysvar_keyring_file_data\" target=\"_blank\" rel=\"noopener noreferrer\">keyring_file_data<\/a> variable :<\/p>\n<pre class=\"lang:mysql decode:true \" title=\"SHOW VARIABLES LIKE 'keyring_file_data'\">mysql&gt;\nSHOW VARIABLES LIKE 'keyring_encrypted_file_data'\\G\n*************************** 1. row ***************************\nVariable_name: keyring_encrypted_file_data\n        Value: \/var\/lib\/mysql-keyring\/keyring-encrypted\n\n\nsystem ls -lh \/var\/lib\/mysql-keyring\/keyring-encrypted\n... 0 Apr 5 16:36 \/var\/lib\/mysql-keyring\/keyring-encrypted<\/pre>\n<p>After a keyring plugin is configured, InnoDB tables may be encrypted.<\/p>\n<h2>InnoDB Transparent Encryption<\/h2>\n<p><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/mysql_enterprise_tde.png?resize=622%2C164\" alt=\"MySQL Enterprise Transparent Data Encryption\" width=\"622\" height=\"164\" \/><\/p>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-storage-engine.html\">InnoDB<\/a> supports data encryption for InnoDB tables stored in <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_file_per_table\" target=\"_blank\" rel=\"noopener noreferrer\">file-per-table<\/a> <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/glossary.html#glos_tablespace\" target=\"_blank\" rel=\"noopener noreferrer\">tablespaces<\/a>.<\/p>\n<p>I&rsquo;m using\u00a0<strong><a href=\"https:\/\/dev.mysql.com\/doc\/sakila\/en\/\" target=\"_blank\" rel=\"noopener noreferrer\">Sakila<\/a><\/strong>\u00a0Database, available <a href=\"https:\/\/dev.mysql.com\/doc\/index-other.html\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/p>\n<p>Below a couple of random queries :<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Sakila database\">mysql&gt; \nUSE sakila;\n\nSELECT rental_id\nFROM rental\nWHERE inventory_id = 3043\n    AND customer_id = 53\n    AND return_date IS NULL;\n+-----------+\n| rental_id |\n+-----------+\n|     11657 |\n+-----------+\n1 row in set (0,00 sec)\n\n\n-- Find Overdue DVDs\nSELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title\nFROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id\n  INNER JOIN address ON customer.address_id = address.address_id\n  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id\n  INNER JOIN film ON inventory.film_id = film.film_id\nWHERE rental.return_date IS NULL\n  AND rental_date + INTERVAL film.rental_duration DAY &lt; CURRENT_DATE()\n  LIMIT 5;\n+------------------+--------------+------------------------+\n| customer         | phone        | title                  |\n+------------------+--------------+------------------------+\n| KNIGHT, GAIL     | 904253967161 | HYDE DOCTOR            |\n| MAULDIN, GREGORY | 80303246192  | HUNGER ROOF            |\n| JENKINS, LOUISE  | 800716535041 | FRISCO FORREST         |\n| HOWELL, WILLIE   | 991802825778 | TITANS JERK            |\n| DIAZ, EMILY      | 333339908719 | CONNECTION MICROCOSMOS |\n+------------------+--------------+------------------------+\n1 row in set (0,01 sec)<\/pre>\n<h3>Data-at-Rest Encryption<\/h3>\n<p>Usually it is not necessary to encrypt all the tables. After a deep data analysis let&rsquo;s assume 2 relevant InnoDB tables must be encrypted : <em>customer<\/em> and <em>staff<\/em>.<\/p>\n<p>So let&rsquo;s encrypted these tables!<\/p>\n<p>We can do it easily with a CREATE or ALTER TABLE statement using\u00a0<strong>ENCRYPTION=\u00a0\u00bbY\u00a0\u00bb<\/strong>\u00a0:<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Tables encryption\">mysql&gt; \nALTER TABLE sakila.customer ENCRYPTION=\"Y\";\nQuery OK, 599 rows affected (0,19 sec)\nRecords: 599  Duplicates: 0  Warnings: 0\n\n\nALTER TABLE sakila.staff ENCRYPTION=\"Y\";\nQuery OK, 2 rows affected (0,15 sec)\nRecords: 2  Duplicates: 0  Warnings: 0\n<\/pre>\n<p>There we go!<\/p>\n<p><em><strong><span style=\"text-decoration: underline;\">Note<\/span><\/strong><\/em><br \/><em>Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE &#8230; ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITHM=INPLACE is not supported.<\/em><\/p>\n<h3>Identify Tables that Use InnoDB Tablespace Encryption<\/h3>\n<p>This information is available in\u00a0INFORMATION_SCHEMA.TABLES :<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Find encrypted tables\">mysql&gt;\nSELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS \nFROM INFORMATION_SCHEMA.TABLES \nWHERE CREATE_OPTIONS = 'ENCRYPTION=\"Y\"';\n+--------------+------------+----------------+\n| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |\n+--------------+------------+----------------+\n| sakila       | customer   | ENCRYPTION=\"Y\" |\n| sakila       | staff      | ENCRYPTION=\"Y\" |\n+--------------+------------+----------------+\n<\/pre>\n<h3>Query encrypted Tables<\/h3>\n<p>Transparent Data Encryption means transparent for the clients ie no change in your applications.<\/p>\n<p>In other words, you <strong>don&rsquo;t have to modify your queries<\/strong>\u00a0and sames queries with same datasets will give me the same result :<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; \nUSE sakila;\n\nSELECT rental_id\nFROM rental\nWHERE inventory_id = 3043\n    AND customer_id = 53\n    AND return_date IS NULL;\n+-----------+\n| rental_id |\n+-----------+\n|     11657 |\n+-----------+\n1 row in set (0,00 sec)\n\n\n-- Find Overdue DVDs\nSELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title\nFROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id\n  INNER JOIN address ON customer.address_id = address.address_id\n  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id\n  INNER JOIN film ON inventory.film_id = film.film_id\nWHERE rental.return_date IS NULL\n  AND rental_date + INTERVAL film.rental_duration DAY &lt; CURRENT_DATE()\n  LIMIT 5;\n+------------------+--------------+------------------------+\n| customer         | phone        | title                  |\n+------------------+--------------+------------------------+\n| KNIGHT, GAIL     | 904253967161 | HYDE DOCTOR            |\n| MAULDIN, GREGORY | 80303246192  | HUNGER ROOF            |\n| JENKINS, LOUISE  | 800716535041 | FRISCO FORREST         |\n| HOWELL, WILLIE   | 991802825778 | TITANS JERK            |\n| DIAZ, EMILY      | 333339908719 | CONNECTION MICROCOSMOS |\n+------------------+--------------+------------------------+\n1 row in set (0,01 sec)<\/pre>\n<h2>Master Key Rotation<\/h2>\n<p>Legal and regulatory requirements require a periodically rotation of the master encryption key. It&rsquo;s also more than recommended to rotate the master key whenever you suspect a security breach.\u00a0Rotating the master encryption key only changes the master encryption key and re-encrypts tablespace keys.<\/p>\n<p>To rotate the master encryption key use\u00a0<strong>ALTER INSTANCE ROTATE INNODB MASTER KEY<\/strong> :<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Key rotation\">mysql&gt; \nsystem ls -lh \/var\/lib\/mysql-keyring\/keyring-encrypted\n... 197 Apr  5 16:44 \/var\/lib\/mysql-keyring\/keyring-encrypted\n\n\nALTER INSTANCE ROTATE INNODB MASTER KEY;\nQuery OK, 0 rows affected (0,01 sec)\n\n\nsystem ls -lh \/var\/lib\/mysql-keyring\/keyring-encrypted\n... 325 Apr  5 17:18 \/var\/lib\/mysql-keyring\/keyring-encrypted\n<\/pre>\n<p>Et voil\u00e0!<\/p>\n<p>Now you know the MySQL Enterprise Transparent Encryption basics \\o\/<\/p>\n<p>I would like to address a last item, regarding a\u00a0common customer question about the disk footprint of an encrypted table.<\/p>\n<p>So let&rsquo;s play a little bit more with TDE.<\/p>\n<p>We have a table,\u00a0encryptedTable, <strong>not<\/strong> yet encrypted with 5 millions rows<\/p>\n<p>Table size is <strong>722<\/strong> MB<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Table stats\">mysql&gt; \nSHOW TABLE STATUS LIKE 'encryptedTable'\\G\n*************************** 1. row ***************************\n           Name: encryptedTable\n         Engine: InnoDB\n        Version: 10\n     Row_format: Dynamic\n           Rows: 4986035\n Avg_row_length: 61\n    Data_length: 304922624\nMax_data_length: 0\n   Index_length: 449921024\n      Data_free: 2097152\n Auto_increment: NULL\n    Create_time: 2018-04-10 12:02:35\n    Update_time: NULL\n     Check_time: NULL\n      Collation: utf8_general_ci\n       Checksum: NULL\n Create_options: \n        Comment: \n\n\n-- Rows = 5 million\nSELECT count(*) FROM encryptedTable;\n+----------+\n| count(*) |\n+----------+\n|  5000000 |\n+----------+\n\n\n-- Table size = 722MB\nSELECT   \n    CONCAT(sum(ROUND(data_length \/ ( 1024 * 1024 ), 4)), 'MB') DATA,   \n    CONCAT(sum(ROUND(index_length \/ ( 1024 * 1024 ),4)), 'MB') INDEXES, \n    CONCAT(sum(ROUND(data_free \/ ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, \n    CONCAT(sum(ROUND(( data_length + index_length+data_free) \/ ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' \nFROM information_schema.TABLES\nWHERE TABLE_SCHEMA = 'test'\n    AND TABLE_NAME = ('encryptedTable')\\G\n*************************** 1. row ***************************\n     DATA: 290.7969MB\n  INDEXES: 429.0781MB\nDATA_FREE: 2.0000MB\n    TOTAL: 721.8750MB<\/pre>\n<p>Table is now encrypted<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Table encryption\">ALTER TABLE encryptedTable ENCRYPTION=\"Y\";\nQuery OK, 5000000 rows affected (6 min 42,58 sec)\nRecords: 5000000  Duplicates: 0  Warnings: 0<\/pre>\n<p>Table size is now : <strong>870<\/strong> MB<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Encrypted table size\">-- Table size = 870MB\nSELECT   \n    CONCAT(sum(ROUND(data_length \/ ( 1024 * 1024 ), 4)), 'MB') DATA,   \n    CONCAT(sum(ROUND(index_length \/ ( 1024 * 1024 ),4)), 'MB') INDEXES, \n    CONCAT(sum(ROUND(data_free \/ ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, \n    CONCAT(sum(ROUND(( data_length + index_length+data_free) \/ ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' \nFROM information_schema.TABLES\nWHERE TABLE_SCHEMA = 'test'\n    AND TABLE_NAME = ('encryptedTable')\\G\n*************************** 1. row ***************************\n     DATA: 249.7813MB\n  INDEXES: 614.6563MB\nDATA_FREE: 6.0000MB\n    TOTAL: 870.4375MB<\/pre>\n<p>Is there a way to reclaim space\u00a0(and optionally improve I\/O efficiency)?\u00a0 Yes of course, <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/optimize-table.html\" target=\"_blank\" rel=\"noopener noreferrer\">OPTIMIZE TABLE<\/a> :<\/p>\n<pre class=\"lang:mysql decode:true \" title=\"OPTIMIZE TABLE\">OPTIMIZE TABLE test.encrypTable\\G\n*************************** 1. row ***************************\n   Table: test.encrypTable\n      Op: optimize\nMsg_type: note\nMsg_text: Table does not support optimize, doing recreate + analyze instead\n*************************** 2. row ***************************\n   Table: test.encrypTable\n      Op: optimize\nMsg_type: status\nMsg_text: OK\n2 rows in set (1 min 32,52 sec)<\/pre>\n<p>Table size is again\u00a0<strong>722<\/strong> MB<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Table size\">-- Table size = 722MB\nSELECT   \n    CONCAT(sum(ROUND(data_length \/ ( 1024 * 1024 ), 4)), 'MB') DATA,   \n    CONCAT(sum(ROUND(index_length \/ ( 1024 * 1024 ),4)), 'MB') INDEXES, \n    CONCAT(sum(ROUND(data_free \/ ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, \n    CONCAT(sum(ROUND(( data_length + index_length+data_free) \/ ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' \nFROM information_schema.TABLES\nWHERE TABLE_SCHEMA = 'test'\n    AND TABLE_NAME = ('encryptedTable')\\G\n*************************** 1. row ***************************\n     DATA: 290.7969MB\n  INDEXES: 429.0781MB\nDATA_FREE: 2.0000MB\n    TOTAL: 721.8750MB<\/pre>\n<h2>MySQL Enterprise Edition<\/h2>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL Enterprise Edition<\/a> includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.<\/p>\n<p>It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.<\/p>\n<p><a href=\"https:\/\/edelivery.oracle.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL Enterprise Edition server\u00a0Trial Download<\/a>\u00a0(Note &#8211; Select Product Pack: MySQL Database).<\/p>\n<p><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/\" target=\"_blank\" rel=\"noopener noreferrer\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_Enterprise_Edition_600x313.png?resize=600%2C313\" alt=\"MySQL Enterprise Edition\" width=\"600\" height=\"313\" \/><\/a><\/p>\n<h2>In order to go further<\/h2>\n<h3>MySQL Security Series<\/h3>\n<ol>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/03\/01\/mysql-security-password-validation-plugin\/\" target=\"_blank\" rel=\"noopener noreferrer\">Password Validation Plugin<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/03\/07\/mysql-security-password-management\/\" target=\"_blank\" rel=\"noopener noreferrer\">Password Management<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/03\/14\/mysql-security-user-account-locking\/\" target=\"_blank\" rel=\"noopener noreferrer\">User Account Locking<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/03\/29\/mysql-security-the-connection-control-plugins\/\" target=\"_blank\" rel=\"noopener noreferrer\">The Connection-Control Plugins<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/04\/04\/mysql-security-mysql-enterprise-audit\/\" target=\"_blank\" rel=\"noopener noreferrer\">Enterprise Audit<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/04\/10\/mysql-security-mysql-enterprise-transparent-data-encryption\/\" target=\"_blank\" rel=\"noopener noreferrer\">Enterprise Transparent Data Encryption (TDE)<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2018\/04\/16\/mysql-security-mysql-enterprise-firewall\/\" target=\"_blank\" rel=\"noopener noreferrer\">Enterprise Firewall<\/a><\/li>\n<li><a href=\"http:\/\/dasini.net\/blog\/2019\/03\/19\/mysql-security-mysql-enterprise-data-masking-and-de-identification\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Enterprise Data Masking and De-Identification (opens in a new tab)\">Enterprise Data Masking and De-Identification<\/a><\/li>\n<\/ol>\n<h3>Reference Manual<\/h3>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/keyring.html\" target=\"_blank\" rel=\"noopener noreferrer\">The MySQL Keyring<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/innodb-tablespace-encryption.html\" target=\"_blank\" rel=\"noopener noreferrer\">Encrypting InnoDB Tables<\/a><\/li>\n<li><a href=\"https:\/\/www.mysql.com\/products\/enterprise\/tde.html\" target=\"_blank\" rel=\"noopener noreferrer\">Presentation of MySQL Enterprise Transparent Data Encryption (TDE)<\/a><\/li>\n<li><a href=\"https:\/\/mysqlserverteam.com\/mysql-innodb-transparent-tablespace-encryption\/\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL : InnoDB Transparent Tablespace Encryption<\/a><\/li>\n<\/ul>\n<h3>MySQL Security<\/h3>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-security-excerpt\/5.7\/en\/\" target=\"_blank\" rel=\"noopener noreferrer\">Security in MySQL<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-secure-deployment-guide\/5.7\/en\/\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL 5.7 Secure Deployment Guide<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/security.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL 5.7 Reference Manual &#8211; Security<\/a><\/li>\n<\/ul>\n\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<p><strong><span style=\"text-decoration: underline;\">MySQL Security Serie &#8211; Password Management<\/span><\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a aria-label=\"Random Password Generation? (opens in a new tab)\" href=\"http:\/\/dasini.net\/blog\/2020\/04\/15\/mysql-security-random-password-generation\/\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"aioseop-link\">Random Password Generation<\/a><\/li><li><a aria-label=\"Password Expiration Policy? (opens in a new tab)\" href=\"http:\/\/dasini.net\/blog\/2020\/04\/21\/mysql-security-password-expiration-policy\/\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"aioseop-link\">Password Expiration Policy<\/a><\/li><li><a aria-label=\"Password Reuse Policy? (opens in a new tab)\" href=\"http:\/\/dasini.net\/blog\/2020\/04\/28\/mysql-security-password-reuse-policy\/\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"aioseop-link\">Password Reuse Policy<\/a><\/li><li><a aria-label=\"Password Verification-Required Policy? (opens in a new tab)\" href=\"http:\/\/dasini.net\/blog\/2020\/05\/05\/mysql-security-password-verification-required-policy\/\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"aioseop-link\">Password Verification-Required Policy<\/a><\/li><li><a aria-label=\"Failed-Login Tracking and Temporary Account Locking? (opens in a new tab)\" href=\"http:\/\/dasini.net\/blog\/2020\/05\/12\/mysql-security-failed-login-tracking-and-temporary-account-locking\/\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"aioseop-link\">Failed-Login Tracking and Temporary Account Locking<\/a><\/li><li><a href=\"http:\/\/dasini.net\/blog\/2020\/05\/19\/mysql-security-dual-password-support\/\" target=\"_blank\" aria-label=\"Dual Password Support (opens in a new tab)\" rel=\"noreferrer noopener\" class=\"aioseop-link\">Dual Password Support<\/a><\/li><\/ul>\n\n\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<p><strong><a rel=\"noreferrer noopener\" href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\" target=\"_blank\" class=\"aioseop-link\"><span style=\"text-decoration: underline;\">Olivier&rsquo;s MySQL Channel<\/span><\/a><\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/youtu.be\/k4K-scd4oI0\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Establish a policy for password expiration with MySQL\">Establish a policy for password expiration with MySQL<\/a><\/li><li><a href=\"https:\/\/youtu.be\/VND0KvuX7bc\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Create users with a random password in MySQL\">Create users with a random password in MySQL<\/a><\/li><li><a href=\"https:\/\/www.youtube.com\/watch?v=4z6-dlGT-Mc\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Enable restrictions on reuse of previous passwords with MySQL\">Enable restrictions on reuse of previous passwords with MySQL<\/a><\/li><li><a href=\"https:\/\/www.youtube.com\/watch?v=1RwlU14TDWk\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Require MySQL users to provide their current password to change it\">Require MySQL users to provide their current password to change it<\/a><\/li><li><a href=\"https:\/\/www.youtube.com\/watch?v=6HO_ciRbiXw\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Temporary Account Locking in MySQL\">Temporary Account Locking in MySQL<\/a><\/li><\/ul>\n\n\n<\/div><\/div>\n\n\n\n<div style=\"height:75px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-vivid-red-color has-text-color\"><strong>Thanks for using MySQL!<\/strong><\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p><a href=\"https:\/\/www.linkedin.com\/groups\/12524512\/\" target=\"_blank\" rel=\"noopener\" title=\"Olivier DASINI on Linkedin\">Follow me on Linkedin<\/a><\/p>\n\n\n\n<p>Watch my videos on my <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier's MySQL Channel\">YouTube channel<\/a> and <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\/?sub_confirmation=1\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Subscribe\">subscribe<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/www.slideshare.net\/freshdaz\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Slideshare\">Slideshare account<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/speakerdeck.com\/freshdaz\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Speaker Deck\">Speaker Deck account<\/a>.<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-vivid-red-color has-text-color\"><strong>Thanks for using HeatWave &amp; MySQL!<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this sixth episode of the\u00a0MySQL Security series, we will see\u00a0how data-at-rest encryption\u00a0helps organizations implement stronger security controls and satisfy regulatory compliance.<br \/>\nYou will be able to protect the privacy of your information, prevent data breaches and help meet popular regulatory requirements including GDPR, PCI DSS, HIPAA with MySQL Enterprise Transparent Data Encryption aka TDE.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[203,365,339],"tags":[387,383,367],"class_list":["post-2248","post","type-post","status-publish","format-standard","hentry","category-mysql-en","category-security","category-tuto-en","tag-encryption","tag-mysql-enterprise","tag-security"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-Ag","jetpack-related-posts":[{"id":3557,"url":"https:\/\/dasini.net\/blog\/2020\/04\/15\/mysql-security-random-password-generation\/","url_meta":{"origin":2248,"position":0},"title":"MySQL Security &#8211; Random Password Generation","author":"Olivier DASINI","date":"15 avril 2020","format":false,"excerpt":"MySQL has the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/img.youtube.com\/vi\/VND0KvuX7bc\/0.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":2177,"url":"https:\/\/dasini.net\/blog\/2018\/03\/14\/mysql-security-user-account-locking\/","url_meta":{"origin":2248,"position":1},"title":"MySQL Security &#8211; User Account Locking","author":"Olivier DASINI","date":"14 mars 2018","format":false,"excerpt":"For security reasons some context require you to setup a user account locking policy. Thus an unauthorized user is not able (anymore) to login to the MySQL server. In this 3rd article of the\u00a0MySQL 5.7 Security series, we\u00a0will see how to [un]lock a user account.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":2778,"url":"https:\/\/dasini.net\/blog\/2019\/03\/19\/mysql-security-mysql-enterprise-data-masking-and-de-identification\/","url_meta":{"origin":2248,"position":2},"title":"MySQL Security &#8211; MySQL Enterprise Data Masking and De-Identification","author":"Olivier DASINI","date":"19 mars 2019","format":false,"excerpt":"MySQL Enterprise Data Masking and De-Identification hides sensitive information by replacing real values with substitutes in order to protect sensitive data while they are still look real and consistent.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2136,"url":"https:\/\/dasini.net\/blog\/2018\/03\/01\/mysql-security-password-validation-plugin\/","url_meta":{"origin":2248,"position":3},"title":"MySQL Security \u2013 Password Validation Plugin","author":"Olivier DASINI","date":"1 mars 2018","format":false,"excerpt":"In this article, 1st of a MySQL 5.7 Security series, we will see how to enforce Strong Passwords with Password Validation Plugin when using MySQL 5.7. Authentication with ID and password is a very simple and common (because it\u2019s simple) way to secure the access to a resource, however the\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":3567,"url":"https:\/\/dasini.net\/blog\/2020\/05\/19\/mysql-security-dual-password-support\/","url_meta":{"origin":2248,"position":4},"title":"MySQL Security &#8211; Dual Password Support","author":"Olivier DASINI","date":"19 mai 2020","format":false,"excerpt":"Dual-password capability makes it possible to seamlessly perform credential changes without downtime.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2156,"url":"https:\/\/dasini.net\/blog\/2018\/03\/07\/mysql-security-password-management\/","url_meta":{"origin":2248,"position":5},"title":"MySQL Security \u2013 Password Management","author":"Olivier DASINI","date":"7 mars 2018","format":false,"excerpt":"Some regulations required\u00a0that the password is renewed in a timely and appropriate manner (e.g. every 90 days). In this article, 2nd of the MySQL 5.7 Security series, we will see how to\u00a0to establish a policy for password expiration\u00a0with MySQL 5.7\u00a0Password\u00a0Management.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2248","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=2248"}],"version-history":[{"count":41,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2248\/revisions"}],"predecessor-version":[{"id":5232,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/2248\/revisions\/5232"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=2248"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=2248"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=2248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}