Activating encryption on one table instantly encrypts all tables in all databases

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello,

after reading the relevant information, I installed the key vault plugin and easily made it work over SSL with Hashicorp Vault. I am running with innodb_file_per_table=ON.

Then I proceeded to encrypt one table to see what happened:

ALTER TABLE `clients` ENCRYPTION='Y'

To my big surprise, this encrypted all tables on almost all databases! Only the MySQL databases were left “untouched”.

Why is that? Here is what I see now:

mysql> SELECT space, name, flag, (flag & 8192) != 0 AS encrypted FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
+-------+-----------------------------------+------+-----------+
| space | name | flag | encrypted |
+-------+-----------------------------------+------+-----------+
| 2 | mysql/plugin | 33 | 0 |
| 3 | mysql/servers | 33 | 0 |
| 4 | mysql/help_topic | 33 | 0 |
| 5 | mysql/help_category | 33 | 0 |
| 6 | mysql/help_relation | 33 | 0 |
| 7 | mysql/help_keyword | 33 | 0 |
| 8 | mysql/time_zone_name | 33 | 0 |
| 9 | mysql/time_zone | 33 | 0 |
| 10 | mysql/time_zone_transition | 33 | 0 |
| 11 | mysql/time_zone_transition_type | 33 | 0 |
| 12 | mysql/time_zone_leap_second | 33 | 0 |
| 13 | mysql/innodb_table_stats | 33 | 0 |
| 14 | mysql/innodb_index_stats | 33 | 0 |
| 15 | mysql/slave_relay_log_info | 33 | 0 |
| 16 | mysql/slave_master_info | 33 | 0 |
| 17 | mysql/slave_worker_info | 33 | 0 |
| 18 | mysql/gtid_executed | 33 | 0 |
| 19 | mysql/server_cost | 33 | 0 |
| 20 | mysql/engine_cost | 33 | 0 |
| 21 | sys/sys_config | 33 | 0 |
| 41 | phpmyadmin/pma__bookmark | 8225 | 1 |
| 42 | phpmyadmin/pma__column_info | 8225 | 1 |
| 43 | phpmyadmin/pma__history | 8225 | 1 |
| 44 | phpmyadmin/pma__pdf_pages | 8225 | 1 |
| 45 | phpmyadmin/pma__recent | 8225 | 1 |
| 46 | phpmyadmin/pma__favorite | 8225 | 1 |
| 47 | phpmyadmin/pma__table_uiprefs | 8225 | 1 |
| 48 | phpmyadmin/pma__relation | 8225 | 1 |
| 49 | phpmyadmin/pma__table_coords | 8225 | 1 |
| 50 | phpmyadmin/pma__table_info | 8225 | 1 |
| 51 | phpmyadmin/pma__tracking | 8225 | 1 |
| 52 | phpmyadmin/pma__userconfig | 8225 | 1 |
| 53 | phpmyadmin/pma__users | 8225 | 1 |
| 54 | phpmyadmin/pma__usergroups | 8225 | 1 |
| 55 | phpmyadmin/pma__navigationhiding | 8225 | 1 |
| 56 | phpmyadmin/pma__savedsearches | 8225 | 1 |
| 57 | phpmyadmin/pma__central_columns | 8225 | 1 |
| 58 | phpmyadmin/pma__designer_settings | 8225 | 1 |
| 59 | phpmyadmin/pma__export_templates | 8225 | 1 |
| 61 | roundcube/session | 8225 | 1 |
| 62 | roundcube/users | 8225 | 1 |
| 63 | roundcube/cache | 8225 | 1 |
| 64 | roundcube/cache_shared | 8225 | 1 |
| 65 | roundcube/cache_index | 8225 | 1 |
| 66 | roundcube/cache_thread | 8225 | 1 |
| 67 | roundcube/cache_messages | 8225 | 1 |
| 68 | roundcube/contacts | 8225 | 1 |
| 69 | roundcube/contactgroups | 8225 | 1 |
| 70 | roundcube/contactgroupmembers | 8225 | 1 |
| 71 | roundcube/identities | 8225 | 1 |
| 72 | roundcube/dictionary | 8225 | 1 |
| 73 | roundcube/searches | 8225 | 1 |
| 74 | roundcube/system | 8225 | 1 |
+-------+-----------------------------------+------+-----------+

Funny thing is, I found out the “clients” table is MyISAM and has not been touched.

Configuration file relevant snippet:


innodb_file_per_table=ON
...
# Database encryption at rest section
early-plugin-load="keyring_vault=keyring_vault.so"
keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf"
keyring-vault-timeout=30
innodb_encrypt_tables=ON
innodb_temp_tablespace_encrypt=ON
binlog_checksum=CRC32
master_verify_checksum=ON
encrypt_binlog=ON
2

Just saw this too, I will see if I can get someone to take a look at both your questions together in case they are related.
Link to other post [url]https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/percona-server-5-7/51579-very-undesiderable-side-effects-in-case-of-key-vault-plugin-startup-failure[/url]

3

Hi dfumagalli,

You have innodb_encrypt_tables set to ON, which means all InnoDB tables in your server should be encrypted unless you specify otherwise ([URL=“https://www.percona.com/doc/percona-server/LATEST/management/data_at_rest_encryption.html”]https://www.percona.com/doc/percona-...ncryption.html[/URL]). I do not think ALTER TABLE made all those tables encrypted and that they were already encrypted before you issued “ALTER TABLE”. I suspect that mysql database was created before innodb_encrypt_tables was ON. Could you please check that this is the case ?

Thanks,
Robert

4

Thank you and Lorraine!

The MySQL database has been created during installation and then I edited the configuration file and added the various encryption settings. Your hypothesis seems sound.
What’s not clear is the documentation.

It says:

General tablespace encryption is enabled by the following syntax extension:
mysql> CREATE TABLESPACE tablespace_name ... ENCRYPTION='Y'

By reading the above I’d have expected to have to create such a tablespace and then move into it the tables I wanted to encrypt.

Furthermore, as shown in the example below those instructions, I understood the

innodb_encrypt_tables=ON

setting would just avoid me the hassle of modifying all my gzipped exported backups to add a

ENCRYPTION="Y";

at the end of the CREATE TABLE statements.

The example gave me the impression that table encryption would be a “voluntary” decision and that - in any case - would only encrypt newly created (or manually ALTERed) tables, not every pre-existing InnoDB table in every database.

If I go read the innodb_encrypt_tables instructions, it states:

Please excuse me, but I would understand this as: “makes InnoDB tables encrypted by default… from now on”. Not everything and in a retroactive way.

It’s all ok and nice with me, because I am experimenting on research & development servers.
Lesson learned.

However, you might want to adjust the instructions so that they are more explicit about what is going to happen.

Why? Because with the advent of European GDPR privacy law, you’ll soon get a lot of people (starting from us) installing Percona’s file or key vault encryption plugins and I am sure that some of them will do that on live servers and will get both this “unexpected” automatic encryption behavior on their legacy databases and the issue I filed on the other thread Lorraine linked above.

5

Thanks dfumagalli and Robert Golebiowski for this useful forum discussion, and for your feedback.

I’ll take an action to ask the team to review the documentation taking your comments into account to see if we can make the instructions more explicit.

Much appreciated, thank you!

6

Hi dfumagalli,

“Please excuse me, but I would understand this as: “makes InnoDB tables encrypted by default… from now on”. Not everything and in a retroactive way.”

But this is exactly how it works - encrypted by default from now on. This is why all tables in your mysql database remained un-encrypted as I explained before.

Kind regards,
Robert