MySQL Blog Archive
For the latest blogs go to
Controlling table encryption in MySQL 8.0

MySQL 5.7.11 introduced InnoDB transparent tablespace encryption, which enabled support for file-per-table tablespaces, and this feature is discussed in this blog.

Later in MySQL 8.0.13, encryption for general tablespace  was introduced.

To improve usability of encryption handling, MySQL 8.0.16 added several features to enable, disable and enforce table encryption for tables within a schema, general tablespace or entire MySQL system. This allows more granular control for database administrators. The sections below discusses some of these features with examples

1. Encryption happens at tablespace level

MySQL encrypts tables at the storage level, by encrypting the contents of filesystem blocks. A tablespace can not have a mix of encrypted an unencrypted blocks. So a tablespace is either unencrypted or encrypted. For user tables, MySQL supports two types of tablespace. The default is file-per-table tablespaces, where each table is stored in a separate tablespace. The second type is a general tablespace, where multiple tables can be stored in one tablespace. It then follows that general tablespaces can not have a mix of unencrypted and encrypted tables.

Encryption can be controlled at multiple levels in the MySQL system. The encryption happens at the storage layer.


Usecase 1: I want all tables in my system to be encrypted

I want all tables in my system to be encrypted. This can easily be achieved with a system wide configuration setting, default_table_encryption. If you configure default_table_encryption=’y’ , all new tables will be encrypted.  In the figure above this will be configures at the system level. See section 2 for how to inspect this.

Usecase 2: I want all tables in my schema to be encrypted

I have a schema where I want all my tables to be encrypted. When creating the schema, a default encryption attribute can be set. All tables that are created in the schema will then inherit this schema default encryption setting. Looking at the figure above, if encryption is configured for ‘db1’,  all tables will be encrypted both for file-per-table tablespaces like for ‘ts1’ and for general tablespaces like ‘ts2’.  See sections 3 and 4 for more details.

Usecase 3: I have a general tablespace and want it to be encrypted

I am using general tablespaces, and I want all tables in a tablespace to be encrypted. We know that a general tablespace can only hold tables that are encrypted or not encrypted. Looking at the figure above we wants ‘ts2’ to be encrypted. So we create the tablespace and configure it to be encrypted. Table ‘t2’ from schema ‘db1’ will then be created and stored in ‘ts2’ encrypted as ‘db1’ has is created with default encryption set. When ‘ts1’ in ‘db2’ is created, an encryption clause must be added to successfully create the table which will be stored in ‘ts2’. See section 4 for more details.

Usecase 4: I want to block overriding default encryption settings

There are clauses to override default encryption settings. I want to prohibit any overriding of these settings. Looking at the figure above, and ‘db1’ has default encryption set, and ‘db2’ has default encryption not set, and ‘ts2’ is created as an encrypted tablespace, I want to  prohibit ‘t1’ from ‘db2’ to be created in ‘ts2’.  We can achieve this with the configuration setting  —table_encryption_privilege_check=true which will ask the server to prohibit overriding the encryption setting.  Users with the TABLE_ENCRYPTION_ADMIN privilege can always override any check.  See section 5 for more details.

2. MySQL system wide encryption default

MySQL 8.0.16 provide a server system variable default_table_encryption which is set to ‘n’ by default upon server startup. Creating a schema or a general tablespace will inherit this setting. E.g., with —default_table_encryption=true

This variable enables a way to create encrypted tables in MySQL system wide. Of course, we can override inheriting the setting by explicitly setting the encryption type as shown in section 3. and 4. below.

Note: The variables ‘default_table_encryption’ settings can be changed at runtime by the users who own the SYSTEM_VARIABLES_ADMIN and the TABLE_ENCRYPTION_ADMIN privileges, or the SUPER privilege.

3. Schema wide encryption default

The default encryption for a schema is set using a new DEFAULT ENCRYPTION clause introduced in MySQL 8.0.16 when creating or altering a schema. E.g.,:

Any table that is created under the schema ‘db1’ would inherit the schema DEFAULT ENCRYPTION clause setting.

We can override the default table ENCRYPTION by explicitly setting it in your CREATE statement. Attempts to create an  unencrypted table under the schema with DEFAULT ENCRYPTION = ‘Y’ will generate a warning. E.g.,:

4. General tablespaces and schema default encryption

Let us suppose we have schema ‘db1’ with DEFAULT ENCRYPTION set to ‘Y’ and we want to create a table using a general tablespace in ‘db1’. MySQL expects users to use an encrypted general tablespace. E.g.,:

One can create a table using unencrypted general tablespace by explicitly providing an ENCRYPTION clause in CREATE TABLE. This will generate a warning. E.g.,:

5. Enforcing table encryption

You may have noticed above that we allow overriding the default encryption setting by explicitly providing DEFAULT ENCRYPTION clause for schema and ENCRYPTION clause while creating tables. However, some users/administrators want a strict enforcement to avoid overriding the default settings. MySQL 8.0.16 provides a new server system variable called table_encryption_privilege_check which can be used to enforce the default encryption settings.

MySQL 8.0.16 provides a new privilege called TABLE_ENCRYPTION_ADMIN, which must be GRANTed to users who need to override default encryption settings when table_encryption_privilege_check is enabled.

The variable ‘table_encryption_privilege_check’ can be changed at runtime by the users who own SUPER privilege.

Let us assume the server is started with —table_encryption_privilege_check=true and —default_table_encryption=true. And the user is not granted with TABLE_ENCRYPTION_ADMIN privilege.

a) Trying to create a schema with DEFAULT ENCRYPTION = ‘N’ will throw an error.

b) Trying to create a table with encryption type which does not match the schema encryption will also result in similar error. E.g.,:

c) Trying to create a general tablespace with ENCRYPTION=’N’ will throw an error.

With —table_encryption_privilege_check=true and —default_table_encryption=false, we will see similar enforcement’s disallowing encrypted tables in MySQL. E.g., if a user attempts to create a database with DEFAULT ENCRYPTION=’Y’ or create a table with ENCRYPTION=’Y’,  the command will fail with corresponding error messages.  A user who owns TABLE_ENCRYPTION_ADMIN privilege would be able to execute above statements overriding the privilege checks.

That is all for now!  Please refer to the following documentation for more information.

Thank you for using MySQL!