# Activating encryption on one table instantly encrypts all tables in all databases
**URL:** https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387
**Category:** Percona Server for MySQL 5.7
**Created:** [May 30, 2018, 3:19am UTC](https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387 "2018-05-30T03:19:23Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@dfumagalli](https://forums.percona.com/u/dfumagalli)
#### Post date: [May 30, 2018, 3:19am UTC](https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387/1 "2018-05-30T03:19:23Z")
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:
```auto
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:
```auto
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:
```auto
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
```
---
### Author:  [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)
#### Post date: [May 30, 2018, 4:15am UTC](https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387/2 "2018-05-30T04:15:26Z")
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]](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%5B/url%5D)
---
### Author:  [@Robert\_Golebiowski](https://forums.percona.com/u/Robert_Golebiowski)
#### Post date: [May 30, 2018, 5:49am UTC](https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387/3 "2018-05-30T05:49:29Z")
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-server/LATEST/management/data_at_rest_encryption.html)”][https://www.percona.com/doc/percona-...ncryption.html[/URL]](https://www.percona.com/doc/percona-...ncryption.html%5B/URL%5D)). 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
---
### Author:  [@dfumagalli](https://forums.percona.com/u/dfumagalli)
#### Post date: [May 30, 2018, 7:02am UTC](https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387/4 "2018-05-30T07:02:10Z")
> [@Robert Golebiowski;51586](#):
>
> 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 ([https://www.percona.com/doc/percona-…ncryption.html](https://www.percona.com/doc/percona-server/LATEST/management/data_at_rest_encryption.html)). 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
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:
```auto
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
```auto
innodb_encrypt_tables=ON
```
setting would just avoid me the hassle of modifying all my gzipped exported backups to add a
```auto
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](https://www.percona.com/doc/percona-server/LATEST/management/data_at_rest_encryption.html#innodb_encrypt_tables) instructions, it states:
> [@](#):
>
> This variable has 3 possible values. ON makes InnoDB tables encrypted by default
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.
---
### Author:  [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)
#### Post date: [May 30, 2018, 7:07am UTC](https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387/5 "2018-05-30T07:07:55Z")
Thanks [dfumagalli](https://www.percona.com/forums/member/42014-dfumagalli) and [Robert Golebiowski](https://www.percona.com/forums/member/42060-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!
---
### Author:  [@Robert\_Golebiowski](https://forums.percona.com/u/Robert_Golebiowski)
#### Post date: [May 30, 2018, 7:40am UTC](https://forums.percona.com/t/activating-encryption-on-one-table-instantly-encrypts-all-tables-in-all-databases/6387/6 "2018-05-30T07:40:18Z")
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