MySQL :: 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

$ CREATE SCHEMA db1; 
 
$ SHOW CREATE SCHEMA db1;
|Database |Create Database
|-
|db1      |CREATE DATABASE `db1`
           /*!40100 DEFAULT CHARACTER SET utf8mb4
           COLLATE utf8mb4_0900_ai_ci */
           /*!80016 DEFAULT ENCRYPTION='Y' */

$ CREATE SCHEMA db1;

$ SHOW CREATE SCHEMA db1;

|Database |Create Database

|db1 |CREATE DATABASE `db1`

/*!40100 DEFAULT CHARACTER SET utf8mb4

COLLATE utf8mb4_0900_ai_ci */

/*!80016 DEFAULT ENCRYPTION='Y' */

$ CREATE TABLESPACE ts2 ADD DATAFILE 'df1.ibd'; 

$ SELECT NAME, ENCRYPTION
    FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
    WHERE NAME LIKE 'ts2'; 
|NAME   |ENCRYPTION 
|-
|ts2    |Y

$ CREATE TABLESPACE ts2 ADD DATAFILE 'df1.ibd';

$ SELECT NAME, ENCRYPTION

FROM INFORMATION_SCHEMA.INNODB_TABLESPACES

WHERE NAME LIKE 'ts2';

|NAME |ENCRYPTION

|ts2 |Y

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.,:

CREATE SCHEMA db1 DEFAULT ENCRYPTION='y';

1	CREATE SCHEMA db1 DEFAULT ENCRYPTION='y';

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

$ CREATE TABLE db1.t1 (f1 int);

$ SHOW CREATE TABLE db1.t1;
|Table |Create Table
|-
|t1    |CREATE TABLE `t1` (
        `f1` int DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'

$ SELECT TABLE_NAME, CREATE_OPTIONS
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME='t1';
|TABLE_NAME    |CREATE_OPTIONS
|-
|t1            |ENCRYPTION='Y'

$ CREATE TABLE db1.t1 (f1 int);

$ SHOW CREATE TABLE db1.t1;

|Table |Create Table

|t1 |CREATE TABLE `t1` (

`f1` int DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'

$ SELECT TABLE_NAME, CREATE_OPTIONS

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME='t1';

|TABLE_NAME |CREATE_OPTIONS

|t1 |ENCRYPTION='Y'

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.,:

$ CREATE TABLE db1.t4 (f1 int) ENCRYPTION='n';
# Warnings:
# Warning 3824 Creating an unencrypted table in
#   a database with default encryption enabled.

$ CREATE TABLE db1.t4 (f1 int) ENCRYPTION='n';

# Warnings:

# Warning 3824 Creating an unencrypted table in

# a database with default encryption enabled.

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.,:

$ CREATE TABLESPACE ts2
  ADD DATAFILE 'df1.ibd' ENCRYPTION='y';
 
$ CREATE SCHEMA db1 DEFAULT ENCRYPTION='y';
 
$ CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts2;

$ SHOW CREATE TABLE db1.t2;
|Table |Create Table
|-
|t2    |CREATE TABLE `t2` (
        `f1` int DEFAULT NULL
        ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB
        DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
        /*!80016 ENCRYPTION='Y' *

$ SELECT NAME, ENCRYPTION
   FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
   WHERE NAME LIKE 'ts2';
|NAME |ENCRYPTION
|-
|ts2  |Y

$ CREATE TABLESPACE ts2

ADD DATAFILE 'df1.ibd' ENCRYPTION='y';

$ CREATE SCHEMA db1 DEFAULT ENCRYPTION='y';

$ CREATE TABLE db1.t2 (f1 int) TABLESPACE=ts2;

$ SHOW CREATE TABLE db1.t2;

|Table |Create Table

|t2 |CREATE TABLE `t2` (

`f1` int DEFAULT NULL

) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

/*!80016 ENCRYPTION='Y' *

$ SELECT NAME, ENCRYPTION

FROM INFORMATION_SCHEMA.INNODB_TABLESPACES

WHERE NAME LIKE 'ts2';

|NAME |ENCRYPTION

|ts2 |Y

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.,:

$ CREATE TABLESPACE ts4 ADD DATAFILE 'df1.ibd' ENCRYPTION='n';
 
$ CREATE SCHEMA db1 DEFAULT ENCRYPTION='y';

$ CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts4 ENCRYPTION='n';
# Warnings:
# Warning 3824 Creating an unencrypted table in
#   a database with default encryption enabled.

$ CREATE TABLESPACE ts4 ADD DATAFILE 'df1.ibd' ENCRYPTION='n';

$ CREATE SCHEMA db1 DEFAULT ENCRYPTION='y';

$ CREATE TABLE db1.t1 (f1 int) TABLESPACE=ts4 ENCRYPTION='n';

# Warnings:

# Warning 3824 Creating an unencrypted table in

# a database with default encryption enabled.

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.

$ CREATE SCHEMA db1 DEFAULT ENCRYPTION='n';
# ERROR HY000: Database default encryption differ
#   from 'default_table_encryption' setting, and
#   user doesn't have enough privilege.

$ CREATE SCHEMA db1 DEFAULT ENCRYPTION='n';

# ERROR HY000: Database default encryption differ

# from 'default_table_encryption' setting, and

# user doesn't have enough privilege.

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

$ CREATE SCHEMA db1;
-- Uses DEFAULT ENCRYPTION='y' because
-- 'default_table_encryption' is true
 
$ CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n'; 
# ERROR HY000: Table encryption differ from its
#   database default encryption, and user doesn't
#   have enough privilege.

$ CREATE SCHEMA db1;

-- Uses DEFAULT ENCRYPTION='y' because

-- 'default_table_encryption' is true

$ CREATE TABLE db1.t1 (f1 int) ENCRYPTION='n';

# ERROR HY000: Table encryption differ from its

# database default encryption, and user doesn't

# have enough privilege.

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

$ CREATE TABLESPACE ts1
  ADD DATAFILE 'ts1.ibd' ENCRYPTION='n'; 
# ERROR HY000: Tablespace encryption differ from
#   'default_table_encryption' setting, and user
#   doesn't have enough privilege.

$ CREATE TABLESPACE ts1

ADD DATAFILE 'ts1.ibd' ENCRYPTION='n';

# ERROR HY000: Tablespace encryption differ from

# 'default_table_encryption' setting, and user

# doesn't have enough privilege.

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!