Rechercher dans le manuel MySQL

15.6.3.9 Tablespace Encryption

Table des matières

The InnoDB tablespace encryption feature provides at-rest data encryption for file-per-table and general tablespace data files. Support for general tablespaces was introduced in MySQL 8.0.13.

About InnoDB Tablespace Encryption

Tablespace encryption uses a two tier encryption key architecture, consisting of a master encryption key and tablespace keys. When a tablespace is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user wants to access encrypted tablespace data, InnoDB uses a master encryption key to decrypt the tablespace key. The decrypted version of a tablespace key never changes, but the master encryption key can be changed as required. This action is referred to as master key rotation.

The tablespace encryption feature relies on a keyring plugin for master encryption key management.

All MySQL editions provide a keyring_file plugin, which stores keyring data in a file local to the server host.

MySQL Enterprise Edition offers additional keyring plugins:

  • The keyring_encrypted_file plugin, which stores keyring data in an encrypted file local to the server host.

  • The keyring_okv plugin, which includes a KMIP client (KMIP 1.1) that uses a KMIP-compatible product as a back end for keyring storage. Supported KMIP-compatible products include centralized key management solutions such as Oracle Key Vault, Gemalto KeySecure, Thales Vormetric key management server, and Fornetix Key Orchestration.

  • The keyring_aws plugin, which communicates with the Amazon Web Services Key Management Service (AWS KMS) as a back end for key generation and uses a local file for key storage.

Warning

The keyring_file and keyring_encrypted file plugins are not intended as regulatory compliance solutions. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

A secure and robust encryption key management solution is critical for security and for compliance with various security standards. When the tablespace encryption feature uses a centralized key management solution, the feature is referred to as “MySQL Enterprise Transparent Data Encryption (TDE)”.

Tablespace encryption supports the Advanced Encryption Standard (AES) block-based encryption algorithm. It uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption.

For frequently asked questions about the tablespace encryption feature, see Section A.16, “MySQL 8.0 FAQ: InnoDB Tablespace Encryption”.

 

InnoDB Tablespace Encryption Prerequisites

  • A keyring plugin must be installed and configured. Keyring plugin installation is performed at startup using the early-plugin-load option. Early loading ensures that the plugin is available prior to initialization of the InnoDB storage engine. For keyring plugin installation and configuration instructions, see Section 6.5.4, “The MySQL Keyring”.

    Only one keyring plugin can be enabled at a time. Enabling multiple keyring plugins is not supported.

    Important

    Once encrypted tablespaces are created in a MySQL instance, the keyring plugin that was loaded when creating the encrypted tablespace must continue to be loaded at startup using the early-plugin-load option. Failing to do so results in errors when starting the server and during InnoDB recovery.

    To verify that a keyring plugin is active, use the SHOW PLUGINS statement or query the INFORMATION_SCHEMA.PLUGINS table. For example:

    1. mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    2.        FROM INFORMATION_SCHEMA.PLUGINS
    3.        WHERE PLUGIN_NAME LIKE 'keyring%';
    4. +--------------+---------------+
    5. | PLUGIN_NAME  | PLUGIN_STATUS |
    6. +--------------+---------------+
    7. | keyring_file | ACTIVE        |
    8. +--------------+---------------+

  • Encryption is supported for file-per-table and general tablespaces. To create a table in a file-per-table tablespace, ensure that innodb_file_per_table is enabled (the default) before executing a CREATE TABLE statement. Alternatively, use the TABLESPACE='innodb_file_per_table' clause in CREATE TABLE statements. There is no similar prerequisite associated with creation of general tablespaces, which are created using CREATE TABLESPACE syntax.

  • When encrypting production data, ensure that you take steps to prevent loss of the master encryption key. If the master encryption key is lost, data stored in encrypted tablespace files is unrecoverable. If you use the keyring_file or keyring_encrypted_file plugin, create a backup of the keyring data file immediately after creating the first encrypted tablespace, before master key rotation, and after master key rotation. The keyring_file_data configuration option defines the keyring data file location for the keyring_file plugin. The keyring_encrypted_file_data configuration option defines the keyring data file location for the keyring_encrypted_file plugin. If you use the keyring_okv or keyring_aws plugin, ensure that you have performed the necessary configuration. For instructions, see Section 6.5.4, “The MySQL Keyring”.

 

Enabling and Disabling File-Per-Table Tablespace Encryption

To enable encryption for a new file-per-table tablespace, specify the ENCRYPTION option in a CREATE TABLE statement.

  1. mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';

To enable encryption for an existing file-per-table tablespace, specify the ENCRYPTION option in an ALTER TABLE statement.

  1. mysql> ALTER TABLE t1 ENCRYPTION='Y';

To disable encryption for a file-per-table tablespace, set ENCRYPTION='N' using ALTER TABLE.

  1. mysql> ALTER TABLE t1 ENCRYPTION='N';

 

Enabling and Disabling General Tablespace Encryption

To enable encryption for a new general tablespace, specify the ENCRYPTION option in a CREATE TABLESPACE statement.

  1. mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENCRYPTION = 'Y' Engine=InnoDB;

To enable encryption for an existing general tablespace, specify the ENCRYPTION option in an ALTER TABLESPACE statement.

  1. mysql> ALTER TABLESPACE ts1 ENCRYPTION = 'Y';

To disable encryption for general tablespace, set ENCRYPTION='N' using ALTER TABLESPACE.

  1. mysql> ALTER TABLESPACE ts1 ENCRYPTION = 'N';

 

Redo Log Data Encryption

Redo log data encryption is enabled using the innodb_redo_log_encrypt configuration option. Redo log encryption is disabled by default.

As with tablespace data, redo log data encryption occurs when redo log data is written to disk, and decryption occurs when redo log data is read from disk. Once redo log data is read into memory, it is in unencrypted form. Redo log data is encrypted and decrypted using the tablepace encryption key.

When innodb_redo_log_encrypt is enabled, unencrypted redo log pages that are present on disk remain unencrypted, and new redo log pages are written to disk in encrypted form. Likewise, when innodb_redo_log_encrypt is disabled, encrypted redo log pages that are present on disk remain encrypted, and new redo log pages are written to disk in unencrypted form.

Redo log encryption metadata, including the tablespace encryption key, is stored in the header of the first redo log file (ib_logfile0). If this file is removed, redo log encryption is disabled.

Once redo log encryption is enabled, a normal restart without the keyring plugin or without the encryption key is not possible, as InnoDB must be able to scan redo pages during startup, which is not possible if redo log pages are encrypted. Without the keyring plugin or the encryption key, only a forced startup without the redo logs (SRV_FORCE_NO_LOG_REDO) is possible. See Section 15.20.2, “Forcing InnoDB Recovery”.

 

Undo Log Data Encryption

Undo log data encryption is enabled using the innodb_undo_log_encrypt configuration option. Undo log encryption applies to undo logs that reside in undo tablespaces. See Section 15.6.3.4, “Undo Tablespaces”. Undo log data encryption is disabled by default.

As with tablespace data, undo log data encryption occurs when undo log data is written to disk, and decryption occurs when undo log data is read from disk. Once undo log data is read into memory, it is in unencrypted form. Undo log data is encrypted and decrypted using the tablepace encryption key.

When innodb_undo_log_encrypt is enabled, unencrypted undo log pages that are present on disk remain unencrypted, and new undo log pages are written to disk in encrypted form. Likewise, when innodb_undo_log_encrypt is disabled, encrypted undo log pages that are present on disk remain encrypted, and new undo log pages are written to disk in unencrypted form.

Undo log encryption metadata, including the tablespace encryption key, is stored in the header of the undo log file (undoN.ibd, where N is the space ID).

 

InnoDB Tablespace Encryption and Master Key Rotation

The master encryption key should be rotated periodically and whenever you suspect that the key has been compromised.

Master key rotation is an atomic, instance-level operation. Each time the master encryption key is rotated, all tablespace keys in the MySQL instance are re-encrypted and saved back to their respective tablespace headers. As an atomic operation, re-encryption must succeed for all tablespace keys once a rotation operation is initiated. If master key rotation is interrupted by a server failure, InnoDB rolls the operation forward on server restart. For more information, see InnoDB Tablespace Encryption and Recovery.

Rotating the master encryption key only changes the master encryption key and re-encrypts tablespace keys. It does not decrypt or re-encrypt associated tablespace data.

Rotating the master encryption key requires the ENCRYPTION_KEY_ADMIN or SUPER privilege.

To rotate the master encryption key, run:

  1. mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

ALTER INSTANCE ROTATE INNODB MASTER KEY supports concurrent DML. However, it cannot be run concurrently with tablespace encryption operations, and locks are taken to prevent conflicts that could arise from concurrent execution. If an ALTER INSTANCE ROTATE INNODB MASTER KEY operation is running, it must finish before a tablespace encryption operation can proceed, and vice versa.

 

InnoDB Tablespace Encryption and Recovery

If a server failure occurs during an encryption operation, the operation is rolled forward when the server is restarted. For general tablespaces, the encryption operation is resumed in a background thread from the last processed page.

If a server failure occurs during master key rotation, InnoDB continues the operation on server restart.

The keyring plugin must be loaded prior to storage engine initialization so that the information necessary to decrypt tablespace data pages can be retrieved from tablespace headers before InnoDB initialization and recovery activities access tablespace data. (See InnoDB Tablespace Encryption Prerequisites.)

When InnoDB initialization and recovery begin, the master key rotation operation resumes. Due to the server failure, some tablespace keys may already be encrypted using the new master encryption key. InnoDB reads the encryption data from each tablespace header, and if the data indicates that the tablespace key is encrypted using the old master encryption key, InnoDB retrieves the old key from the keyring and uses it to decrypt the tablepace key. InnoDB then re-encrypts the tablespace key using the new master encryption key and saves the re-encrypted tablespace key back to the tablespace header.

 

Exporting Encrypted Tablespaces

Tablespace export is only supported for file-per-table tabelspaces.

When an encrypted tablespace is exported, InnoDB generates a transfer key that is used to encrypt the tablespace key. The encrypted tablespace key and transfer key are stored in a tablespace_name.cfp file. This file together with the encrypted tablespace file is required to perform an import operation. On import, InnoDB uses the transfer key to decrypt the tablespace key in the tablespace_name.cfp file. For related information, see Section 15.6.3.7, “Copying Tablespaces to Another Instance”.

 

InnoDB Tablespace Encryption and Replication

 

Identifying Encrypted Tablespaces

The INFORMATION_SCHEMA.INNODB_TABLESPACES table, introduced in MySQL 8.0.13, includes an ENCRYPTION column that can be used to identify encrypted tablespaces.

  1. mysql> SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
  2.        WHERE ENCRYPTION='Y'\G
  3. *************************** 1. row ***************************
  4.      SPACE: 2
  5.       NAME: ts1
  6. SPACE_TYPE: General
  7. ENCRYPTION: Y
  8. *************************** 2. row ***************************
  9.      SPACE: 3
  10.       NAME: test/t1
  11. SPACE_TYPE: Single
  12. ENCRYPTION: Y

When the ENCRYPTION option is specified in a CREATE TABLE or ALTER TABLE statement, it is recorded in the CREATE_OPTIONS column of INFORMATION_SCHEMA.TABLES. This column can be queried to identify tables that reside in encrypted file-per-table tablespaces.

  1. mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
  2.        WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
  3. +--------------+------------+----------------+
  4. | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
  5. +--------------+------------+----------------+
  6. | test         | t1         | ENCRYPTION="Y" |
  7. +--------------+------------+----------------+

Query INFORMATION_SCHEMA.INNODB_TABLESPACES to retrieve information about the tablespace associated with a particular schema and table.

  1. mysql> SELECT SPACE, NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='test/t1';
  2. +-------+---------+------------+
  3. | SPACE | NAME    | SPACE_TYPE |
  4. +-------+---------+------------+
  5. |     3 | test/t1 | Single     |
  6. +-------+---------+------------+

 

Monitoring Tablespace Encryption Progress

You can monitor general tablespace encryption progress using Performance Schema.

The stage/innodb/alter tablespace (encryption) stage event instrument reports WORK_ESTIMATED and WORK_COMPLETED information for general tablespace encryption operations.

The following example demonstrates how to enable the stage/innodb/alter tablespace (encryption) stage event instrument and related consumer tables to monitor general tablespace encryption progress. For information about Performance Schema stage event instruments and related consumers, see Section 26.12.5, “Performance Schema Stage Event Tables”.

  1. Enable the stage/innodb/alter tablespace (encryption) instrument:

    1. mysql> USE performance_schema;
    2. mysql> UPDATE setup_instruments SET ENABLED = 'YES'
    3.        WHERE NAME LIKE 'stage/innodb/alter tablespace (encryption)';

  2. Enable the stage event consumer tables, which include events_stages_current, events_stages_history, and events_stages_history_long.

    1. mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';

  3. Run a general tablespace encryption operation.

    1. mysql> ALTER TABLESPACE ts1 ENCRYPTION = 'Y';

  4. Check the progress of the encryption operation by querying the Performance Schema events_stages_current table. WORK_ESTIMATED reports the total number of pages in the tablespace. WORK_COMPLETED reports the number of pages processed.

    1. mysql> SELECT EVENT_NAME, WORK_ESTIMATED, WORK_COMPLETED FROM events_stages_current;
    2. +--------------------------------------------+----------------+----------------+
    3. | EVENT_NAME                                 | WORK_COMPLETED | WORK_ESTIMATED |
    4. +--------------------------------------------+----------------+----------------+
    5. | stage/innodb/alter tablespace (encryption) |           1056 |           1407 |
    6. +--------------------------------------------+----------------+----------------+

    The events_stages_current table returns an empty set if the encryption operation has completed. In this case, you can check the events_stages_history table to view event data for the completed operation. For example:

    1. mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history;
    2. +--------------------------------------------+----------------+----------------+
    3. | EVENT_NAME                                 | WORK_COMPLETED | WORK_ESTIMATED |
    4. +--------------------------------------------+----------------+----------------+
    5. | stage/innodb/alter tablespace (encryption) |           1407 |           1407 |
    6. +--------------------------------------------+----------------+----------------+

 

InnoDB Tablespace Encryption Usage Notes

  • Plan appropriately when altering an existing file-per-table tablespace with the ENCRYPTION option. Tables residing in file-per-table tablespaces are rebuilt using the COPY algorithm. The INPLACE algorithm is used when altering the ENCRYPTION attribute of a general tablespace. The INPLACE algorithm permits concurrent DML on tables that reside in the general tablespace. Concurrent DDL is blocked.

  • When a general tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted general tablespace is encrypted.

  • If the server exits or is stopped during normal operation, it is recommended to restart the server using the same encryption settings that were configured previously.

  • The first master encryption key is generated when the first new or existing tablespace is encrypted.

  • Master key rotation re-encrypts tablespaces keys but does not change the tablespace key itself. To change a tablespace key, you must disable and re-enable encryption. For file-per-table tablespaces, re-encrypting the tablespace is an ALGORITHM=COPY operation that rebuilds the table. For general tablespaces, it is an ALGORITHM=INPLACE operation, which does not require rebuilding tables that reside in the tablespace.

  • If a table is created with both the COMPRESSION and ENCRYPTION options, compression is performed before tablespace data is encrypted.

  • If a keyring data file (the file named by keyring_file_data or keyring_encrypted_file_data) is empty or missing, the first execution of ALTER INSTANCE ROTATE INNODB MASTER KEY creates a master encryption key.

  • Uninstalling the keyring_file or keyring_encrypted_file plugin does not remove an existing keyring data file.

  • It is recommended that you not place a keyring data file under the same directory as tablespace data files.

  • Modifying the keyring_file_data or keyring_encrypted_file_data setting at runtime or when restarting the server can cause previously encrypted tablespaces to become inaccessible, resulting in lost data.

 

InnoDB Tablespace Encryption Limitations

  • Advanced Encryption Standard (AES) is the only supported encryption algorithm. InnoDB tablespace encryption uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption.

  • Encryption is only supported for file-per-table and general tablespaces. Encryption support for general tablespaces was introduced in MySQL 8.0.13. Encryption is not supported for other tablespace types including the system tablespace.

  • You cannot move or copy a table from an encrypted file-per-table tablespace or general tablespace to a tablespace type that does not support encryption.

  • You cannot move or copy a table from an encrypted tablespace to an unencrypted tablespace. However, moving a table from an unencrypted tablespace to an encrypted one is permitted. For example, you can move or copy a table from a unencrypted file-per-table or general tablespace to an encrypted general tablespace.

  • By default, tablespace encryption only applies to data in the tablespace. Redo log and undo log data can be encrypted by enabling innodb_redo_log_encrypt and innodb_undo_log_encrypt. See Redo Log Data Encryption, and Undo Log Data Encryption. Binary log data is not encrypted.

  • It is not permitted to change the storage engine of a table that resides or previously resided in an encrypted tablespace.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-tablespace-encryption.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut