Rechercher dans le manuel MySQL
15.6.3.9 InnoDB Data-at-Rest Encryption
InnoDB
supports data-at-rest encryption for
file-per-table
tablespaces,
general
tablespaces, the mysql
system tablespace, redo
logs, and undo logs.
As of MySQL 8.0.16, setting an encryption default for schemas and general tablespaces is also supported, which permits DBAs to control whether tables created in those schemas and tablespaces are encrypted.
InnoDB
data-at-rest encryption features and
capabilities are described under the following topics in this
section.
About Data-at-Rest Encryption
InnoDB
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 data-at-rest 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.
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 data-at-rest encryption feature uses a centralized key management solution, the feature is referred to as “MySQL Enterprise Transparent Data Encryption (TDE)”.
The data-at-rest encryption feature 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 data-at-rest encryption feature, see Section A.16, “MySQL 8.0 FAQ: InnoDB Data-at-Rest Encryption”.
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 theInnoDB
storage engine. For keyring plugin installation and configuration instructions, see Section 6.4.4, “The MySQL Keyring”.Only one keyring plugin can be enabled at a time. Enabling multiple keyring plugins is not supported.
ImportantOnce 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 duringInnoDB
recovery.To verify that a keyring plugin is active, use the
SHOW PLUGINS
statement or query theINFORMATION_SCHEMA.PLUGINS
table. For example: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
orkeyring_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. Thekeyring_file_data
configuration option defines the keyring data file location for thekeyring_file
plugin. Thekeyring_encrypted_file_data
configuration option defines the keyring data file location for thekeyring_encrypted_file
plugin. If you use thekeyring_okv
orkeyring_aws
plugin, ensure that you have performed the necessary configuration. For instructions, see Section 6.4.4, “The MySQL Keyring”.
As of MySQL 8.0.16, the
default_table_encryption
variable defines the default encryption setting for schemas and
general tablespaces. CREATE
TABLESPACE
and
CREATE
SCHEMA
operations apply the
default_table_encryption
setting when an ENCRYPTION
clause is not
specified explicitly.
ALTER
SCHEMA
and ALTER
TABLESPACE
operations do not apply the
default_table_encryption
setting. An ENCRYPTION
clause must be
specified explicitly to alter the encryption of an existing
schema or general tablespace.
The default_table_encryption
variable can be set for an individual client connection or
globally using
SET
syntax. For example, the following statement enables default
schema and tablespace encryption globally:
The default encryption setting for a schema can also be defined
using the DEFAULT ENCRYPTION
clause when
creating or altering a schema, as in this example:
If the DEFAULT ENCRYPTION
clause is not
specified when creating a schema, the
default_table_encryption
setting is applied. The DEFAULT ENCRYPTION
clause must be specified to alter the default encryption of an
existing schema. Otherwise, the schema retains its current
encryption setting.
By default, a table inherits the encryption setting of the schema or general tablespace it is created in. For example, a table created in an encryption-enabled schema is encrypted by default. This behavior enables a DBA to control table encryption usage by defining and enforcing schema and general tablespace encryption defaults.
Encryption defaults are enforced by enabling the
table_encryption_privilege_check
variable. When
table_encryption_privilege_check
is enabled, a privilege check occurs when creating or altering a
schema or general tablespace with an encryption setting that
differs from the
default_table_encryption
setting, or when creating or altering a table with an encryption
setting that differs from the default schema encryption. When
table_encryption_privilege_check
is disabled (the default), the privilege check does not occur
and the previously mentioned operations are permitted to proceed
with a warning.
The TABLE_ENCRYPTION_ADMIN
privilege is required to override default encryption settings
when
table_encryption_privilege_check
is enabled. A DBA can grant this privilege to enable a user to
deviate from the
default_table_encryption
setting when creating or altering a schema or general
tablespace, or to deviate from the default schema encryption
when creating or altering a table. This privilege does not
permit deviating from the encryption of a general tablespace
when creating or altering a table. A table must have the same
encryption setting as the general tablespace it resides in.
As of MySQL 8.0.16, a file-per-table tablespace inherits the
default encryption of the schema in which the table is created
unless an ENCRYPTION
clause is specified
explcitly in the CREATE TABLE
statement. Prior to MySQL 8.0.16, the
ENCRYPTION
clause must be specified to enable
encryption.
To alter the encryption of an existing file-per-table
tablespace, an ENCRYPTION
clause must be
specified.
As of MySQL 8.0.16, if the
table_encryption_privilege_check
variable is enabled, specifying an ENCRYPTION
clause with a setting that differs from the default schema
encryption requires the
TABLE_ENCRYPTION_ADMIN
privilege.
See
Defining an Encryption Default for Schemas and General Tablespaces.
As of MySQL 8.0.16, the
default_table_encryption
variable determines the encryption of a newly created general
tablespace unless an ENCRYPTION
clause is
specified explicitly in the CREATE
TABLESPACE
statement. Prior to MySQL 8.0.16, an
ENCRYPTION
clause must be specified to enable
encryption.
To alter the encryption of an existing general tablespace, an
ENCRYPTION
clause must be specified.
As of MySQL 8.0.16, if the
table_encryption_privilege_check
variable is enabled, specifying an ENCRYPTION
clause with a setting that differs from the
default_table_encryption
setting requires the
TABLE_ENCRYPTION_ADMIN
privilege.
See
Defining an Encryption Default for Schemas and General Tablespaces.
Encryption support for the mysql
system
tablespace is available as of MySQL 8.0.16.
The mysql
system tablespace contains the
mysql
system database and MySQL data
dictionary tables. It is unencrypted by default. To enable
encryption for the mysql
system tablespace,
specify the tablespace name and the
ENCRYPTION
option in an
ALTER TABLESPACE
statement.
To disable encryption for the mysql
system
tablespace, set ENCRYPTION = 'N'
using an
ALTER TABLESPACE
statement.
Enabling or disabling encryption for the
mysql
system tablespace requires the
CREATE TABLESPACE
privilege on
all tables in the instance (CREATE TABLESPACE on
*.*)
.
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 tablespace 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 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 tablespace 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.
When undo log encryption is disabled, the server continues to require the keyring plugin that was used to encrypt undo log data until the undo tablespaces that contained the encrypted undo log data are truncated. (An encryption header is only removed from an undo tablespace when the undo tablespace is truncated.) For information about truncating undo tablespaces, see Truncating Undo Tablespaces.
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 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:
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.
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
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 tablespace 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.
Tablespace export is only supported for file-per-table tablespaces.
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
file. This file together with the encrypted tablespace file is
required to perform an import operation. On import,
tablespace_name
.cfpInnoDB
uses the transfer key to decrypt the
tablespace key in the
file. For related information, see
Section 15.6.3.7, “Copying Tablespaces to Another Instance”.
tablespace_name
.cfp
The
ALTER INSTANCE ROTATE INNODB MASTER KEY
statement is only supported in replication environments where the master and slaves run a version of MySQL that supports tablespace encryption.Successful
ALTER INSTANCE ROTATE INNODB MASTER KEY
statements are written to the binary log for replication on slaves.If an
ALTER INSTANCE ROTATE INNODB MASTER KEY
statement fails, it is not logged to the binary log and is not replicated on slaves.Replication of an
ALTER INSTANCE ROTATE INNODB MASTER KEY
operation fails if the keyring plugin is installed on the master but not on the slave.If the
keyring_file
orkeyring_encrypted_file
plugin is installed on both the master and a slave but the slave does not have a keyring data file, the replicatedALTER INSTANCE ROTATE INNODB MASTER KEY
statement creates the keyring data file on the slave, assuming the keyring file data is not cached in memory.ALTER INSTANCE ROTATE INNODB MASTER KEY
uses keyring file data that is cached in memory, if available.
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. row ***************************
- SPACE: 4294967294
- NAME: mysql
- SPACE_TYPE: General
- ENCRYPTION: Y
- *************************** 2. row ***************************
- SPACE: 2
- NAME: test/t1
- SPACE_TYPE: Single
- ENCRYPTION: Y
- *************************** 3. row ***************************
- SPACE: 3
- NAME: ts1
- SPACE_TYPE: General
- 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.
- +--------------+------------+----------------+
- | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
- +--------------+------------+----------------+
- | test | t1 | ENCRYPTION="Y" |
- +--------------+------------+----------------+
Query
INFORMATION_SCHEMA.INNODB_TABLESPACES
to retrieve information about the tablespace associated with a
particular schema and table.
- +-------+---------+------------+
- +-------+---------+------------+
- | 3 | test/t1 | Single |
- +-------+---------+------------+
You can identify encryption-enabled schemas by querying the
INFORMATION_SCHEMA.SCHEMATA
table.
- +-------------+--------------------+
- | SCHEMA_NAME | DEFAULT_ENCRYPTION |
- +-------------+--------------------+
- | test | YES |
- +-------------+--------------------+
SHOW
CREATE SCHEMA
also shows the DEFAULT
ENCRYPTION
clause.
You can monitor general tablespace and mysql
system 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 or mysql
system tablespace
encryption progress. For information about Performance Schema
stage event instruments and related consumers, see
Section 26.12.5, “Performance Schema Stage Event Tables”.
Enable the
stage/innodb/alter tablespace (encryption)
instrument:Enable the stage event consumer tables, which include
events_stages_current
,events_stages_history
, andevents_stages_history_long
.Run a tablespace encryption operation. In this example, a general tablespace named
ts1
is encrypted.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.- +--------------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +--------------------------------------------+----------------+----------------+
- +--------------------------------------------+----------------+----------------+
The
events_stages_current
table returns an empty set if the encryption operation has completed. In this case, you can check theevents_stages_history
table to view event data for the completed operation. For example:- +--------------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +--------------------------------------------+----------------+----------------+
- +--------------------------------------------+----------------+----------------+
Plan appropriately when altering an existing file-per-table tablespace with the
ENCRYPTION
option. Tables residing in file-per-table tablespaces are rebuilt using theCOPY
algorithm. TheINPLACE
algorithm is used when altering theENCRYPTION
attribute of a general tablespace or themysql
system tablespace. TheINPLACE
algorithm permits concurrent DML on tables that reside in the general tablespace. Concurrent DDL is blocked.When a general tablespace or the
mysql
system tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted 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 and themysql
system tablespace, it is anALGORITHM=INPLACE
operation, which does not require rebuilding tables that reside in the tablespace.If a table is created with both the
COMPRESSION
andENCRYPTION
options, compression is performed before tablespace data is encrypted.If a keyring data file (the file named by
keyring_file_data
orkeyring_encrypted_file_data
) is empty or missing, the first execution ofALTER INSTANCE ROTATE INNODB MASTER KEY
creates a master encryption key.Uninstalling the
keyring_file
orkeyring_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
orkeyring_encrypted_file_data
setting at runtime or when restarting the server can cause previously encrypted tablespaces to become inaccessible, resulting in lost data.
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 tablespaces, general tablespaces, and the
mysql
system tablespace. Encryption support for general tablespaces was introduced in MySQL 8.0.13. Encryption support for themysql
system tablespace is available as of MySQL 8.0.16. Encryption is not supported for other tablespace types including theInnoDB
system tablespace.You cannot move or copy a table from an encrypted file-per-table tablespace, general tablespace, or the
mysql
system 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
andinnodb_undo_log_encrypt
. See Redo Log Encryption, and Undo Log Encryption. Binary log data is not encrypted.It is not permitted to change the storage engine of a table that resides in, or previously resided in, an encrypted tablespace.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-tablespace-encryption.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.