Information Schema INNODB_TABLESPACES_ENCRYPTION Table

The Information Schema INNODB_TABLESPACES_ENCRYPTION table contains metadata about encrypted InnoDB tablespaces. When you enable encryption for an InnoDB tablespace, an entry for the tablespace is added to this table. If you later disable encryption for the InnoDB tablespace, then the row still remains in this table, but the ENCRYPTION_SCHEME and CURRENT_KEY_VERSION columns will be set to 0.

Viewing this table requires the PROCESS privilege, although a bug in versions before MariaDB 10.1.46, 10.2.33, 10.3.24, 10.4.14 and 10.5.5 mean the SUPER privilege was required (MDEV-23003).

It has the following columns:

Column Description Added
SPACE InnoDB tablespace ID.
NAME Path to the InnoDB tablespace file, without the extension.
ENCRYPTION_SCHEME Key derivation algorithm. Only 1 is currently used to represent an algorithm. If this value is 0, then the tablespace is unencrypted.
KEYSERVER_REQUESTS Number of times InnoDB has had to request a key from the encryption key management plugin. The three most recent keys are cached internally.
MIN_KEY_VERSION Minimum key version used to encrypt a page in the tablespace. Different pages may be encrypted with different key versions.
CURRENT_KEY_VERSION Key version that will be used to encrypt pages. If this value is 0, then the tablespace is unencrypted.
KEY_ROTATION_PAGE_NUMBER Page that a background encryption thread is currently rotating. If key rotation is not enabled, then the value will be NULL.
KEY_ROTATION_MAX_PAGE_NUMBER When a background encryption thread starts rotating a tablespace, the field contains its current size. If key rotation is not enabled, then the value will be NULL.
CURRENT_KEY_ID Key ID for the encryption key currently in use. MariaDB 10.1.13
ROTATING_OR_FLUSHING Current key rotation status. If this value is 1, then the background encryption threads are working on the tablespace. See MDEV-11738. MariaDB 10.2.5, MariaDB 10.1.23

When the InnoDB system tablespace is encrypted, it is represented in this table with the special name: innodb_system.

Example

SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION 
WHERE NAME LIKE 'db_encrypt%';
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
| SPACE | NAME                                         | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
|    18 | db_encrypt/t_encrypted_existing_key          |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
|    19 | db_encrypt/t_not_encrypted_existing_key      |                 1 |                  0 |               1 |                   1 |                     NULL |                         NULL |
|    20 | db_encrypt/t_not_encrypted_non_existing_key  |                 1 |                  0 |      4294967295 |          4294967295 |                     NULL |                         NULL |
|    21 | db_encrypt/t_default_encryption_existing_key |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
|    22 | db_encrypt/t_encrypted_default_key           |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
|    23 | db_encrypt/t_not_encrypted_default_key       |                 1 |                  0 |               1 |                   1 |                     NULL |                         NULL |
|    24 | db_encrypt/t_defaults                        |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
7 rows in set (0.00 sec)

See Also

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/information-schema-innodb_tablespaces_encryption-table/