随笔 - 746  文章 - 0  评论 - 39  阅读 - 79万

Mysql：6.4.2-6.4.8：Security Plugins & Component & Enterprise-Extend

6.4.2 The Connection-Control Plugins
6.4.3 The Password Validation Component
6.4.4 The MySQL Keyring
6.4.5 MySQL Enterprise Audit
6.4.6 The Audit Message Component
6.4.7 MySQL Enterprise Firewall
6.4.8 MySQL Enterprise Data Masking and De-Identification

6.4.2 The Connection-Control Plugins

6.4.2.1 Connection-Control Plugin Installation
6.4.2.2 Connection-Control System and Status Variables

MySQL Server includes a plugin library that enables administrators to introduce an increasing delay in server response to clients after a certain number of consecutive failed connection attempts. This capability provides a deterrent that slows down brute force attacks that attempt to access MySQL user accounts. The plugin library contains two plugins:

  • CONNECTION_CONTROL checks incoming connections and adds a delay to server responses as necessary. This plugin also exposes system variables that enable its operation to be configured and a status variable that provides rudimentary monitoring information.

    The CONNECTION_CONTROL plugin uses the audit plugin interface (see Section 29.2.4.8, “Writing Audit Plugins”). To collect information, it subscribes to the MYSQL_AUDIT_CONNECTION_CLASSMASK event class, and processes MYSQL_AUDIT_CONNECTION_CONNECT and MYSQL_AUDIT_CONNECTION_CHANGE_USER subevents to check whether the server should introduce a delay before responding to client connection attempts.

  • CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS implements an INFORMATION_SCHEMA table that exposes more detailed monitoring information for failed connection attempts.

The following sections provide information about connection-control plugin installation and configuration. For information about the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table, see Section 25.47.1, “The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table”.

6.4.2.1 Connection-Control Plugin Installation

This section describes how to install the connection-control plugins, CONNECTION_CONTROL and CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

The plugin library file base name is connection_control. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

To load the plugins at server startup, use the --plugin-load-add option to name the library file that contains them. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary):

[mysqld]
plugin-load-add=connection_control.so

After modifying my.cnf, restart the server to cause the new settings to take effect.

Alternatively, to load the plugins at runtime, use these statements (adjust the .so suffix for your platform as necessary):

INSTALL PLUGIN CONNECTION_CONTROL
  SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
  SONAME 'connection_control.so';

INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'connection%';

If a plugin fails to initialize, check the server error log for diagnostic messages.

If the plugins have been previously registered with INSTALL PLUGIN or are loaded with --plugin-load-add, you can use the --connection-control and --connection-control-failed-login-attempts options at server startup to control plugin activation. For example, to load the plugins at startup and prevent them from being removed at runtime, use these options:

[mysqld]
plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT

If it is desired to prevent the server from running without a given connection-control plugin, use an option value of FORCE or FORCE_PLUS_PERMANENT to force server startup to fail if the plugin does not initialize successfully.

Note

It is possible to install one plugin without the other, but both must be installed for full connection-control capability. In particular, installing only the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin is of little use because without the CONNECTION_CONTROL plugin to provide the data that populates the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table, retrievals from the table will always be empty.

Connection Delay Configuration

To enable you to configure its operation, the CONNECTION_CONTROL plugin exposes several system variables:

To entirely disable checking for failed connection attempts, set connection_control_failed_connections_threshold to zero. If connection_control_failed_connections_threshold is nonzero, the amount of delay is zero up through that many consecutive failed connection attempts. Thereafter, the amount of delay is the number of failed attempts above the threshold, multiplied by connection_control_min_connection_delay milliseconds. For example, with the default connection_control_failed_connections_threshold and connection_control_min_connection_delay values of 3 and 1000, respectively, there is no delay for the first three consecutive failed connection attempts by a client, a delay of 1000 milliseconds for the fourth failed attempt, 2000 milliseconds for the fifth failed attempt, and so on, up to the maximum delay permitted by connection_control_max_connection_delay.

You can set the CONNECTION_CONTROL system variables at server startup or runtime. Suppose that you want to permit four consecutive failed connection attempts before the server starts delaying its responses, and to increase the delay by 1500 milliseconds for each additional failure after that. To set the relevant variables at server startup, put these lines in the server my.cnf file:

[mysqld]
plugin-load-add=connection_control.so
connection_control_failed_connections_threshold=4
connection_control_min_connection_delay=1500

To set and persist the variables at runtime, use these statements:

SET PERSIST connection_control_failed_connections_threshold = 4;
SET PERSIST connection_control_min_connection_delay = 1500;

SET PERSIST sets the value for the running MySQL instance. It also saves the value, causing it to be used for subsequent server restarts. To change a value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 13.7.6.1, “SET Syntax for Variable Assignment”.

The connection_control_min_connection_delay and connection_control_max_connection_delay system variables have fixed minimum and maximum values of 1000 and 2147483647, respectively. In addition, the permitted range of values of each variable also depends on the current value of the other:

Thus, to make the changes required for some configurations, you might need to set the variables in a specific order. Suppose that the current minimum and maximum delays are 1000 and 2000, and that you want to set them to 3000 and 5000. You cannot first set connection_control_min_connection_delay to 3000 because that is greater than the current connection_control_max_connection_delay value of 2000. Instead, set connection_control_max_connection_delay to 5000, then set connection_control_min_connection_delay to 3000.

Connection Failure Assessment

When the CONNECTION_CONTROL plugin is installed, it checks connection attempts and tracks whether they fail or succeed. For this purpose, a failed connection attempt is one for which the client user and host match a known MySQL account but the provided credentials are incorrect, or do not match any known account.

Failed-connection counting is based on the user/host combination for each connection attempt. Determination of the applicable user name and host name takes proxying into account and occurs as follows:

  • If the client user proxies another user, the proxying user's information is used. For example, if external_user@example.com proxies proxy_user@example.com, connection counting uses the proxying user, external_user@example.com, rather than the proxied user, proxy_user@example.com. Both external_user@example.com and proxy_user@example.com must have valid entries in the mysql.user system table and a proxy relationship between them must be defined in the mysql.proxies_priv system table (see Section 6.2.18, “Proxy Users”).

  • If the client user does not proxy another user, but does match a mysql.user entry, counting uses the CURRENT_USER() value corresponding to that entry. For example, if a user user1 connecting from a host host1.example.com matches a user1@host1.example.com entry, counting uses user1@host1.example.com. If the user matches a user1@%.example.com, user1@%.com, or user1@% entry instead, counting uses user1@%.example.com, user1@%.com, or user1@%, respectively.

For the cases just described, the connection attempt matches some mysql.user entry, and whether the request succeeds or fails depends on whether the client provides the correct authentication credentials. For example, if the client presents an incorrect password, the connection attempt fails.

If the connection attempt matches no mysql.user entry, the attempt fails. In this case, no CURRENT_USER() value is available and connection-failure counting uses the user name provided by the client and the client host as determined by the server. For example, if a client attempts to connect as user user2 from host host2.example.com, the user name part is available in the client request and the server determines the host information. The user/host combination used for counting is user2@host2.example.com.

Note

The server maintains information about which client hosts can possibly connect to the server (essentially the union of host values for mysql.user entries). If a client attempts to connect from any other host, the server rejects the attempt at an early stage of connection setup:

ERROR 1130 (HY000): Host 'host_name' is not
allowed to connect to this MySQL server

Because this type of rejection occurs so early, CONNECTION_CONTROL does not see it, and does not count it.

Connection Failure Monitoring

To monitor failed connections, use these information sources:

Assigning a value to connection_control_failed_connections_threshold at runtime resets all accumulated failed-connection counters to zero, which has these visible effects:

6.4.2.2 Connection-Control System and Status Variables

This section describes the system and status variables that the CONNECTION_CONTROL plugin provides to enable its operation to be configured and monitored.

Connection-Control System Variables

If the CONNECTION_CONTROL plugin is installed, it exposes these system variables:

Connection-Control Status Variables

If the CONNECTION_CONTROL plugin is installed, it exposes this status variable:

6.4.3 The Password Validation Component

6.4.3.1 Password Validation Component Installation and Uninstallation
6.4.3.2 Password Validation Options and Variables
6.4.3.3 Transitioning to the Password Validation Component

The validate_password component serves to improve security by requiring account passwords and enabling strength testing of potential passwords. This component exposes system variables that enable you to configure password policy, and status variables for component monitoring.

Note

In MySQL 8.0, the validate_password plugin was reimplemented as the validate_password component. (For general information about server components, see Section 5.5, “MySQL Server Components”.) The following instructions describe how to use the component, not the plugin. For instructions on using the plugin form of validate_password, see The Password Validation Plugin in MySQL 5.7 Reference Manual.

The plugin form of validate_password is still available but is deprecated and will be removed in a future version of MySQL. MySQL installations that use the plugin should make the transition to using the component instead. See Section 6.4.3.3, “Transitioning to the Password Validation Component”.

The validate_password component implements these capabilities:

  • For SQL statements that assign a password supplied as a cleartext value, validate_password checks the password against the current password policy and rejects the password if it is weak (the statement returns an ER_NOT_VALID_PASSWORD error). This applies to the ALTER USER, CREATE USER, and SET PASSWORD statements.

  • For CREATE USER statements, validate_password requires that a password be given, and that it satisfies the password policy. This is true even if an account is locked initially because otherwise unlocking the account later would cause it to become accessible without a password that satisfies the policy.

  • validate_password implements a VALIDATE_PASSWORD_STRENGTH() SQL function that assesses the strength of potential passwords. This function takes a password argument and returns an integer from 0 (weak) to 100 (strong).

Note

For statements that assign or modify account passwords (ALTER USER, CREATE USER, and SET PASSWORD), the validate_password capabilities described here apply only to accounts that use an authentication plugin that stores credentials internally to MySQL. For accounts that use plugins that perform authentication against a credentials system external to MySQL, password management must be handled externally against that system as well. For more information about internal credentials storage, see Section 6.2.15, “Password Management”.

The preceding restriction does not apply to use of the VALIDATE_PASSWORD_STRENGTH() function because it does not affect accounts directly.

Examples:

  • validate_password checks the cleartext password in the following statement. Under the default password policy, which requires passwords to be at least 8 characters long, the password is weak and the statement produces an error:

    mysql> ALTER USER USER() IDENTIFIED BY 'abc';
ERROR 1819 (HY000): Your password does not satisfy the current
policy requirements

  • Passwords specified as hashed values are not checked because the original password value is not available for checking:

    		ALTER USER 'jeffrey'@'localhost'
    		IDENTIFIED WITH mysql_native_password
    		AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';

  • This account-creation statement fails, even though the account is locked initially, because it does not include a password that satisfies the current password policy:

    mysql> CREATE USER 'juanita'@'localhost' ACCOUNT LOCK;
ERROR 1819 (HY000): Your password does not satisfy the current
policy requirements

  • To check a password, use the VALIDATE_PASSWORD_STRENGTH() function:

    		SELECT VALIDATE_PASSWORD_STRENGTH('weak');
    		SELECT VALIDATE_PASSWORD_STRENGTH('lessweak$_@123');
    		SELECT VALIDATE_PASSWORD_STRENGTH('N0Tweak$_@123!');

To configure password checking, modify the system variables having names of the form validate_password.xxx; these are the parameters that control password policy. See Section 6.4.3.2, “Password Validation Options and Variables”.

If validate_password is not installed, the validate_password.xxx system variables are not available, passwords in statements are not checked, and the VALIDATE_PASSWORD_STRENGTH() function always returns 0. For example, without the plugin installed, accounts can be assigned passwords shorter than 8 characters, or no password at all.

Assuming that validate_password is installed, it implements three levels of password checking: LOW, MEDIUM, and STRONG. The default is MEDIUM; to change this, modify the value of validate_password.policy. The policies implement increasingly strict password tests. The following descriptions refer to default parameter values, which can be modified by changing the appropriate system variables.

In addition, validate_password supports the capability of rejecting passwords that match the user name part of the effective user account for the current session, either forward or in reverse. To provide control over this capability, validate_password exposes a validate_password.check_user_name system variable, which is enabled by default.

6.4.3.1 Password Validation Component Installation and Uninstallation

This section describes how to install and uninstall the validate_password password-validation component. For general information about installing and uninstalling components, see Section 5.5, “MySQL Server Components”.

Note

If you install MySQL 8.0 using the MySQL Yum repository, MySQL SLES Repository, or RPM packages provided by Oracle, the validate_password component is enabled by default after you start your MySQL Server for the first time.

Upgrades to MySQL 8.0 from 5.7 using Yum or RPM packages leave the validate_password plugin in place. To make the transition from the validate_password plugin to the validate_password component, see Section 6.4.3.3, “Transitioning to the Password Validation Component”.

To be usable by the server, the component library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

To install the validate_password component, use this statement:

INSTALL COMPONENT 'file://component_validate_password';

Component installation is a one-time operation that need not be done per server startup. INSTALL COMPONENT loads the component, and also registers it in the mysql.component system table to cause it to be loaded during subsequent server startups.

To uninstall the validate_password component, use this statement:

UNINSTALL COMPONENT 'file://component_validate_password';

UNINSTALL COMPONENT unloads the component, and deregisters it from the mysql.component system table to cause it not to be loaded during subsequent server startups.

6.4.3.2 Password Validation Options and Variables

This section describes the system and status variables that validate_password provides to enable its operation to be configured and monitored.

Password Validation Component System Variables

If the validate_password component is enabled, it exposes several system variables that enable configuration of password checking:

mysql> SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

To change how passwords are checked, you can set these system variables at server startup or at runtime. The following list describes the meaning of each variable.

  • validate_password.check_user_name

    PropertyValue
    Command-Line Format --validate-password.check-user-name[={OFF|ON}]
    System Variable validate_password.check_user_name
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Whether validate_password compares passwords to the user name part of the effective user account for the current session and rejects them if they match. This variable is unavailable unless validate_password is installed.

    By default, validate_password.check_user_name is enabled. This variable controls user name matching independent of the value of validate_password.policy.

    When validate_password.check_user_name is enabled, it has these effects:

    • Checking occurs in all contexts for which validate_password is invoked, which includes use of statements such as ALTER USER or SET PASSWORD to change the current user's password, and invocation of functions such as VALIDATE_PASSWORD_STRENGTH().

    • The user names used for comparison are taken from the values of the USER() and CURRENT_USER() functions for the current session. An implication is that a user who has sufficient privileges to set another user's password can set the password to that user's name, and cannot set that user's password to the name of the user executing the statement. For example, 'root'@'localhost' can set the password for 'jeffrey'@'localhost' to 'jeffrey', but cannot set the password to 'root.

    • Only the user name part of the USER() and CURRENT_USER() function values is used, not the host name part. If a user name is empty, no comparison occurs.

    • If a password is the same as the user name or its reverse, a match occurs and the password is rejected.

    • User-name matching is case sensitive. The password and user name values are compared as binary strings on a byte-by-byte basis.

    • If a password matches the user name, VALIDATE_PASSWORD_STRENGTH() returns 0 regardless of how other validate_password system variables are set.

  • validate_password.dictionary_file

    PropertyValue
    Command-Line Format --validate-password.dictionary-file=file_name
    System Variable validate_password.dictionary_file
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type File name

    The path name of the dictionary file that validate_password uses for checking passwords. This variable is unavailable unless validate_password is installed.

    By default, this variable has an empty value and dictionary checks are not performed. For dictionary checks to occur, the variable value must be nonempty. If the file is named as a relative path, it is interpreted relative to the server data directory. File contents should be lowercase, one word per line. Contents are treated as having a character set of utf8. The maximum permitted file size is 1MB.

    For the dictionary file to be used during password checking, the password policy must be set to 2 (STRONG); see the description of the validate_password.policy system variable. Assuming that is true, each substring of the password of length 4 up to 100 is compared to the words in the dictionary file. Any match causes the password to be rejected. Comparisons are not case sensitive.

    For VALIDATE_PASSWORD_STRENGTH(), the password is checked against all policies, including STRONG, so the strength assessment includes the dictionary check regardless of the validate_password.policy value.

    validate_password.dictionary_file can be set at runtime and assigning a value causes the named file to be read without a server restart.

  • validate_password.length

    PropertyValue
    Command-Line Format --validate-password.length=#
    System Variable validate_password.length
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 8
    Minimum Value 0

    The minimum number of characters that validate_password requires passwords to have. This variable is unavailable unless validate_password is installed.

    The validate_password.length minimum value is a function of several other related system variables. The value cannot be set less than the value of this expression:

    validate_password.number_count
+ validate_password.special_char_count
+ (2 * validate_password.mixed_case_count)

    If validate_password adjusts the value of validate_password.length due to the preceding constraint, it writes a message to the error log.

  • validate_password.mixed_case_count

    PropertyValue
    Command-Line Format --validate-password.mixed-case-count=#
    System Variable validate_password.mixed_case_count
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1
    Minimum Value 0

    The minimum number of lowercase and uppercase characters that validate_password requires passwords to have if the password policy is MEDIUM or stronger. This variable is unavailable unless validate_password is installed.

    For a given validate_password.mixed_case_count value, the password must have that many lowercase characters, and that many uppercase characters.

  • validate_password.number_count

    PropertyValue
    Command-Line Format --validate-password.number-count=#
    System Variable validate_password.number_count
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1
    Minimum Value 0

    The minimum number of numeric (digit) characters that validate_password requires passwords to have if the password policy is MEDIUM or stronger. This variable is unavailable unless validate_password is installed.

  • validate_password.policy

    PropertyValue
    Command-Line Format --validate-password.policy=value
    System Variable validate_password.policy
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value 1
    Valid Values

    0

    1

    2

    The password policy enforced by validate_password. This variable is unavailable unless validate_password is installed.

    validate_password.policy affects how validate_password uses its other policy-setting system variables, except for checking passwords against user names, which is controlled independently by validate_password.check_user_name.

    The validate_password.policy value can be specified using numeric values 0, 1, 2, or the corresponding symbolic values LOW, MEDIUM, STRONG. The following table describes the tests performed for each policy. For the length test, the required length is the value of the validate_password.length system variable. Similarly, the required values for the other tests are given by other validate_password.xxx variables.

    PolicyTests Performed
    0 or LOW Length
    1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
    2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

  • validate_password.special_char_count

    PropertyValue
    Command-Line Format --validate-password.special-char-count=#
    System Variable validate_password.special_char_count
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1
    Minimum Value 0

    The minimum number of nonalphanumeric characters that validate_password requires passwords to have if the password policy is MEDIUM or stronger. This variable is unavailable unless validate_password is installed.

Password Validation Component Status Variables

If the validate_password component is enabled, it exposes status variables that provide operational information:

mysql> SHOW STATUS LIKE 'validate_password.%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| validate_password.dictionary_file_last_parsed | 2019-10-03 08:33:49 |
| validate_password.dictionary_file_words_count | 1902                |
+-----------------------------------------------+---------------------+

The following list describes the meaning of each status variable.

Password Validation Plugin Options
Note

In MySQL 8.0, the validate_password plugin was reimplemented as the validate_password component. The validate_password plugin is deprecated and will be removed in a future version of MySQL. Consequently, its options are also deprecated and will be removed. MySQL installations that use the plugin should make the transition to using the component instead. See Section 6.4.3.3, “Transitioning to the Password Validation Component”.

To control activation of the validate_password plugin, use this option:

Password Validation Plugin System Variables
Note

In MySQL 8.0, the validate_password plugin was reimplemented as the validate_password component. The validate_password plugin is deprecated and will be removed in a future version of MySQL. Consequently, its system variables are also deprecated and will be removed. Use the corresponding system variables of the validate_password component; see Password Validation Component System Variables. MySQL installations that use the plugin should make the transition to using the component instead. See Section 6.4.3.3, “Transitioning to the Password Validation Component”.

Password Validation Plugin Status Variables
Note

In MySQL 8.0, the validate_password plugin was reimplemented as the validate_password component. The validate_password plugin is deprecated and will be removed in a future version of MySQL. Consequently, its status variables are also deprecated and will be removed. Use the corresponding status variables of the validate_password component; see Password Validation Component Status Variables. MySQL installations that use the plugin should make the transition to using the component instead. See Section 6.4.3.3, “Transitioning to the Password Validation Component”.

6.4.3.3 Transitioning to the Password Validation Component

Note

In MySQL 8.0, the validate_password plugin was reimplemented as the validate_password component. The validate_password plugin is deprecated and will be removed in a future version of MySQL.

MySQL installations that currently use the validate_password plugin should make the transition to using the validate_password component instead. To do so, use the following procedure. The procedure installs the component before uninstalling the plugin, to avoid having a time window during which no password validation occurs. (The component and plugin can be installed simultaneously. In this case, the server attempts to use the component, falling back to the plugin if the component is unavailable.)

  1. Install the validate_password component:

    INSTALL COMPONENT 'file://component_validate_password';

  2. Test the validate_password component to ensure that it works as expected. If you need to set any validate_password.xxx system variables, you can do so at runtime using SET GLOBAL. (Any option file changes that must be made are performed in the next step.)

  3. Adjust any references to the plugin system and status variables to refer to the corresponding component system and status variables. Suppose that you configure the plugin at startup using an option file like this:

    [mysqld]
validate-password=FORCE_PLUS_PERMANENT
validate_password_dictionary_file=/usr/share/dict/words
validate_password_length=10
validate_password_number_count=2

    To adjust the option file, omit the --validate-password option (it applies only to the plugin, not the component), and modify the system variable references:

    [mysqld]
validate_password.dictionary_file=/usr/share/dict/words
validate_password.length=10
validate_password.number_count=2

    Similar adjustments are needed for applications that refer at runtime to validate_password plugin system and status variables.

  4. Uninstall the validate_password plugin:

    UNINSTALL PLUGIN validate_password;

    If the validate_password plugin is loaded at server startup using a --plugin-load or --plugin-load-add option, omit that option from the server startup procedure. For example, if the option is listed in a server option file, remove it from the file.

  5. Restart the server.

6.4.4 The MySQL Keyring

6.4.4.1 Keyring Plugin Installation
6.4.4.2 Using the keyring_file File-Based Plugin
6.4.4.3 Using the keyring_encrypted_file Keyring Plugin
6.4.4.4 Using the keyring_okv KMIP Plugin
6.4.4.5 Using the keyring_aws Amazon Web Services Keyring Plugin
6.4.4.6 Using the HashiCorp Vault Keyring Plugin
6.4.4.7 Migrating Keys Between Keyring Keystores
6.4.4.8 Supported Keyring Key Types and Lengths
6.4.4.9 General-Purpose Keyring Key-Management Functions
6.4.4.10 Plugin-Specific Keyring Key-Management Functions
6.4.4.11 Keyring Command Options
6.4.4.12 Keyring System Variables

MySQL Server supports a keyring that enables internal server components and plugins to securely store sensitive information for later retrieval. The implementation is plugin-based:

Warning

The keyring_file and keyring_encrypted_file plugins for encryption key management are not intended as a regulatory compliance solution. 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).

Uses for the keyring within MySQL include:

  • The InnoDB storage engine uses the keyring to store its key for tablespace encryption. InnoDB can use any supported keyring plugin.

  • MySQL Enterprise Audit uses the keyring to store the audit log file encryption password. The audit log plugin can use any supported keyring plugin.

  • When binary log encryption has been activated for a MySQL server (by setting binlog_encryption=ON), the binary log encryption keys used to encrypt the file passwords for the binary log files and relay log files are stored in the keyring. Any supported keyring plugin can be used to store binary log encryption keys. Binary log encryption keys are retained as long as there are files on the server that were encrypted using them. When the binary log master key is rotated manually, all binary log encryption keys that no longer apply to any retained binary log files or relay log files are cleared from the keyring. If a retained binary log file or relay log file cannot be initialized for re-encryption, the relevant binary log encryption keys are not deleted in case the files can be recovered in the future. For example, this might be the case if a file listed in a binary log index file is currently unreadable, or if a channel fails to initialize. For more information, see Section 17.3.2, “Encrypting Binary Log Files and Relay Log Files”.

For general keyring installation instructions, see Section 6.4.4.1, “Keyring Plugin Installation”. For information specific to a given keyring plugin, see the section describing that plugin.

For information about using the keyring UDFs, see Section 6.4.4.9, “General-Purpose Keyring Key-Management Functions”.

Keyring plugins and UDFs access a keyring service that provides the interface for server components to the keyring. For information about accessing the keyring plugin service and writing keyring plugins, see Section 29.3.2, “The Keyring Service”, and Section 29.2.4.12, “Writing Keyring Plugins”.

6.4.4.1 Keyring Plugin Installation

Keyring service consumers require a keyring plugin to be installed. MySQL provides these plugin choices:

  • keyring_file: A plugin that stores keyring data in a file local to the server host. Available in all MySQL distributions.

  • keyring_encrypted_file: A plugin that stores keyring data in an encrypted file local to the server host. Available in MySQL Enterprise Edition distributions.

  • keyring_okv: A plugin that uses KMIP-compatible back end keyring storage products such as Oracle Key Vault and Gemalto SafeNet KeySecure Appliance. Available in MySQL Enterprise Edition distributions.

  • keyring_aws: A plugin that communicates with the Amazon Web Services Key Management Service as a back end for key generation and uses a local file for key storage. Available in MySQL Enterprise Edition distributions.

  • keyring_hashicorp: A plugin that communicates with HashiCorp Vault for back end storage. Available in MySQL Enterprise Edition distributions.

This section describes how to install the keyring plugin of your choosing. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

If you intend to use keyring user-defined functions (UDFs) in conjunction with the keyring plugin, install the UDFs following keyring installation using the instructions in Section 6.4.4.9, “General-Purpose Keyring Key-Management Functions”.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

Installation for each keyring plugin is similar. The following instructions use keyring_file. Users of a different keyring plugin can substitute its name for keyring_file.

The keyring_file plugin library file base name is keyring_file. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

Note

Only one keyring plugin should be enabled at a time. Enabling multiple keyring plugins is unsupported and results may not be as anticipated.

The keyring plugin must be loaded early during the server startup sequence so that server components can access it as necessary during their own initialization. For example, the InnoDB storage engine uses the keyring for tablespace encryption, so the keyring plugin must be loaded and available prior to InnoDB initialization.

To load the plugin, use the --early-plugin-load option to name the plugin library file that contains it. For example, on platforms where the plugin library file suffix is .so, use these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary):

[mysqld]
early-plugin-load=keyring_file.so

Before starting the server, check the notes for your chosen keyring plugin to see whether it permits or requires additional configuration:

After performing any plugin-specific configuration, verify plugin installation. With the MySQL server running, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'keyring%';

If the plugin fails to initialize, check the server error log for diagnostic messages.

If no keyring plugin is available when a server component tries to access the keyring service, the service cannot be used by that component. As a result, the component may fail to initialize or may initialize with limited functionality. For example, if InnoDB finds that there are encrypted tablespaces when it initializes, it attempts to access the keyring. If the keyring is unavailable, InnoDB can access only unencrypted tablespaces. To ensure that InnoDB can access encrypted tablespaces as well, use --early-plugin-load to load the keyring plugin.

Plugins can be loaded by other methods, such as the --plugin-load or --plugin-load-add option or the INSTALL PLUGIN statement. However, keyring plugins loaded using those methods may be available too late in the server startup sequence for certain server components, such as InnoDB:

  • Plugin loading using --plugin-load or --plugin-load-add occurs after InnoDB initialization.

  • Plugins installed using INSTALL PLUGIN are registered in the mysql.plugin system table and loaded automatically for subsequent server restarts. However, because mysql.plugin is an InnoDB table, any plugins named in it can be loaded during startup only after InnoDB initialization.

6.4.4.2 Using the keyring_file File-Based Plugin

The keyring_file plugin is a keyring plugin that stores keyring data in a file local to the server host.

Warning

The keyring_file plugin for encryption key management is not intended as a regulatory compliance solution. 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).

To install the keyring_file plugin, use the general keyring installation instructions found in Section 6.4.4.1, “Keyring Plugin Installation”, together with the configuration information specific to keyring_file found here.

To be usable during the server startup process, keyring_file must be loaded using the --early-plugin-load option. The keyring_file_data system variable optionally configures the location of the file used by the keyring_file plugin for data storage. The default value is platform specific. To configure the file location explicitly, set the variable value at startup. For example, use these lines in the server my.cnf file (adjust the .so suffix and file location for your platform as necessary):

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/usr/local/mysql/mysql-keyring/keyring

Keyring operations are transactional: The keyring_file plugin uses a backup file during write operations to ensure that it can roll back to the original file if an operation fails. The backup file has the same name as the value of the keyring_file_data system variable with a suffix of .backup.

For additional information about keyring_file_data, see Section 6.4.4.12, “Keyring System Variables”.

To ensure that keys are flushed only when the correct keyring storage file exists, keyring_file stores a SHA-256 checksum of the keyring in the file. Before updating the file, the plugin verifies that it contains the expected checksum.

The keyring_file plugin supports the functions that comprise the standard MySQL Keyring service interface. Keyring operations performed by those functions are accessible at two levels:

Example (using UDFs):

SELECT keyring_key_generate('MyKey', 'AES', 32);
SELECT keyring_key_remove('MyKey');

For information about the key types permitted by keyring_file, see Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

6.4.4.3 Using the keyring_encrypted_file Keyring Plugin

Note

The keyring_encrypted_file plugin is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

The keyring_encrypted_file plugin is a keyring plugin that stores keyring data in an encrypted file local to the server host.

Warning

The keyring_encrypted_file plugin for encryption key management is not intended as a regulatory compliance solution. 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).

To install the keyring_encrypted_file plugin, use the general keyring installation instructions found in Section 6.4.4.1, “Keyring Plugin Installation”, together with the configuration information specific to keyring_encrypted_file found here.

To be usable during the server startup process, keyring_encrypted_file must be loaded using the --early-plugin-load option. To specify the password for encrypting the keyring data file, set the keyring_encrypted_file_password system variable. (The password is mandatory; if not specified at server startup, keyring_encrypted_file initialization fails.) The keyring_encrypted_file_data system variable optionally configures the location of the file used by the keyring_encrypted_file plugin for data storage. The default value is platform specific. To configure the file location explicitly, set the variable value at startup. For example, use these lines in the server my.cnf file (adjust the .so suffix and file location for your platform as necessary and substitute your chosen password):

[mysqld]
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/usr/local/mysql/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=password

Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

Keyring operations are transactional: The keyring_encrypted_file plugin uses a backup file during write operations to ensure that it can roll back to the original file if an operation fails. The backup file has the same name as the value of the keyring_encrypted_file_data system variable with a suffix of .backup.

For additional information about the system variables used to configure the keyring_encrypted_file plugin, see Section 6.4.4.12, “Keyring System Variables”.

To ensure that keys are flushed only when the correct keyring storage file exists, keyring_encrypted_file stores a SHA-256 checksum of the keyring in the file. Before updating the file, the plugin verifies that it contains the expected checksum. In addition, keyring_encrypted_file encrypts file contents using AES before writing the file, and decrypts file contents after reading the file.

The keyring_encrypted_file plugin supports the functions that comprise the standard MySQL Keyring service interface. Keyring operations performed by those functions are accessible at two levels:

Example (using UDFs):

SELECT keyring_key_generate('MyKey', 'AES', 32);
SELECT keyring_key_remove('MyKey');

For information about the key types permitted by keyring_encrypted_file, see Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

6.4.4.4 Using the keyring_okv KMIP Plugin

Note

The keyring_okv plugin is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

The Key Management Interoperability Protocol (KMIP) enables communication of cryptographic keys between a key management server and its clients. The keyring_okv keyring plugin uses the KMIP 1.1 protocol to communicate securely as a client of a KMIP back end. Keyring material is generated exclusively by the back end, not by keyring_okv. The plugin works with these KMIP-compatible products:

  • Oracle Key Vault

  • Gemalto SafeNet KeySecure Appliance

The keyring_okv plugin supports the functions that comprise the standard MySQL Keyring service interface. Keyring operations performed by those functions are accessible at two levels:

Example (using UDFs):

SELECT keyring_key_generate('MyKey', 'AES', 32);
SELECT keyring_key_remove('MyKey');

For information about the key types permitted by keyring_okv, Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

To install the keyring_okv plugin, use the general keyring installation instructions found in Section 6.4.4.1, “Keyring Plugin Installation”, together with the configuration information specific to keyring_okv found here.

General keyring_okv Configuration

Regardless of which KMIP back end the keyring_okv plugin uses for keyring storage, the keyring_okv_conf_dir system variable configures the location of the directory used by keyring_okv for its support files. The default value is empty, so you must set the variable to name a properly configured directory before the plugin can communicate with the KMIP back end. Unless you do so, keyring_okv writes a message to the error log during server startup that it cannot communicate:

[Warning] Plugin keyring_okv reported: 'For keyring_okv to be
initialized, please point the keyring_okv_conf_dir variable to a directory
containing Oracle Key Vault configuration file and ssl materials'

The keyring_okv_conf_dir variable must name a directory that contains the following items:

  • okvclient.ora: A file that contains details of the KMIP back end with which keyring_okv will communicate.

  • ssl: A directory that contains the certificate and key files required to establish a secure connection with the KMIP back end: CA.pem, cert.pem, and key.pem. If the key file is password-protected, the ssl directory can contain a single-line text file named password.txt containing the password needed to decrypt the key file.

Both the okvclient.ora file and ssl directory with the certificate and key files are required for keyring_okv to work properly. The procedure used to populate the configuration directory with these files depends on the KMIP back end used with keyring_okv, as described elsewhere.

The configuration directory used by keyring_okv as the location for its support files should have a restrictive mode and be accessible only to the account used to run the MySQL server. For example, on Unix and Unix-like systems, to use the /usr/local/mysql/mysql-keyring-okv directory, the following commands (executed as root) create the directory and set its mode and ownership:

cd /usr/local/mysql
mkdir mysql-keyring-okv
chmod 750 mysql-keyring-okv
chown mysql mysql-keyring-okv
chgrp mysql mysql-keyring-okv

To be usable during the server startup process, keyring_okv must be loaded using the --early-plugin-load option. Also, set the keyring_okv_conf_dir system variable to tell keyring_okv where to find its configuration directory. For example, use these lines in the server my.cnf file (adjust the .so suffix and directory location for your platform as necessary):

[mysqld]
early-plugin-load=keyring_okv.so
keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv

For additional information about keyring_okv_conf_dir, see Section 6.4.4.12, “Keyring System Variables”.

Configuring keyring_okv for Oracle Key Vault

The discussion here assumes that you are familiar with Oracle Key Vault. Some pertinent information sources:

In Oracle Key Vault terminology, clients that use Oracle Key Vault to store and retrieve security objects are called endpoints. To communicate with Oracle Key Vault, it is necessary to register as an endpoint and enroll by downloading and installing endpoint support files.

The following procedure briefly summarizes the process of setting up keyring_okv for use with Oracle Key Vault:

  1. Create the configuration directory for the keyring_okv plugin to use.

  2. Register an endpoint with Oracle Key Vault to obtain an enrollment token.

  3. Use the enrollment token to obtain the okvclient.jar client software download.

  4. Install the client software to populate the keyring_okv configuration directory that contains the Oracle Key Vault support files.

Use the following procedure to configure keyring_okv and Oracle Key Vault to work together. This description only summarizes how to interact with Oracle Key Vault. For details, visit the Oracle Key Vault site and consult the Oracle Key Vault Administrator's Guide.

  1. Create the configuration directory that will contain the Oracle Key Vault support files, and make sure that the keyring_okv_conf_dir system variable is set to name that directory (for details, see General keyring_okv Configuration).

  2. Log in to the Oracle Key Vault management console as a user who has the System Administrator role.

  3. Select the Endpoints tab to arrive at the Endpoints page. On the Endpoints page, click Add.

  4. Provide the required endpoint information and click Register. The endpoint type should be Other. Successful registration results in an enrollment token.

  5. Log out from the Oracle Key Vault server.

  6. Connect again to the Oracle Key Vault server, this time without logging in. Use the endpoint enrollment token to enroll and request the okvclient.jar software download. Save this file to your system.

  7. Install the okvclient.jar file using the following command (you must have JDK 1.4 or higher):

    java -jar okvclient.jar -d dir_name [-v]

    The directory name following the -d option is the location in which to install extracted files. The -v option, if given, causes log information to be produced that may be useful if the command fails.

    When the command asks for an Oracle Key Vault endpoint password, do not provide one. Instead, press Enter. (The result is that no password will be required when the endpoint connects to Oracle Key Vault.)

  8. The preceding command produces an okvclient.ora file, which should be in this location under the directory named by the -d option in the preceding java -jar command: 

    install_dir/conf/okvclient.ora

    The file contents include lines that look something like this:

    		host_ip
    		port_num
    		host_ip
    		port_num

    The keyring_okv plugin attempts to communicate with the server running on the host named by the SERVER variable and falls back to STANDBY_SERVER if that fails:

    • For the SERVER variable, a setting in the okvclient.ora file is mandatory.

    • For the STANDBY_SERVER variable, a setting in the okvclient.ora file is optional.

  9. Go to the Oracle Key Vault installer directory and test the setup by running this command:

    okvutil/bin/okvutil list

    The output should look something like this:

    Unique ID                               Type            Identifier
255AB8DE-C97F-482C-E053-0100007F28B9	Symmetric Key	-
264BF6E0-A20E-7C42-E053-0100007FB29C	Symmetric Key	-

    For a fresh Oracle Key Vault server (a server without any key in it), the output looks like this instead, to indicate that there are no keys in the vault:

    no objects found

  10. Use this command to extract the ssl directory containing SSL materials from the okvclient.jar file:

    jar xf okvclient.jar ssl

  11. Copy the Oracle Key Vault support files (the okvclient.ora file and the ssl directory) into the configuration directory.

  12. (Optional) If you wish to password-protect the key file, use the instructions in Password-Protecting the keyring_okv Key File.

After completing the preceding procedure, restart the MySQL server. It loads the keyring_okv plugin and keyring_okv uses the files in its configuration directory to communicate with Oracle Key Vault.

Configuring keyring_okv for Gemalto SafeNet KeySecure Appliance

Gemalto SafeNet KeySecure Appliance uses the KMIP protocol (version 1.1 or 1.2). The keyring_okv keyring plugin (which supports KMIP 1.1) can use KeySecure as its KMIP back end for keyring storage.

Use the following procedure to configure keyring_okv and KeySecure to work together. The description only summarizes how to interact with KeySecure. For details, consult the section named Add a KMIP Server in the KeySecure User Guide.

  1. Create the configuration directory that will contain the KeySecure support files, and make sure that the keyring_okv_conf_dir system variable is set to name that directory (for details, see General keyring_okv Configuration).

  2. In the configuration directory, create a subdirectory named ssl to use for storing the required SSL certificate and key files.

  3. In the configuration directory, create a file named okvclient.ora. It should have following format:

    		host_ip
    		port_num
    		host_ip
    		port_num

    For example, if KeySecure is running on host 198.51.100.20 and listening on port 9002, the okvclient.ora file looks like this:

    SERVER=198.51.100.20:9002
STANDBY_SERVER=198.51.100.20:9002

  4. Connect to the KeySecure Management Console as an administrator with credentials for Certificate Authorities access.

  5. Navigate to Security >> Local CAs and create a local certificate authority (CA).

  6. Go to Trusted CA Lists. Select Default and click on Properties. Then select Edit for Trusted Certificate Authority List and add the CA just created.

  7. Download the CA and save it in the ssl directory as a file named CA.pem.

  8. Navigate to Security >> Certificate Requests and create a certificate. Then you will be able to download a compressed tar file containing certificate PEM files.

  9. Extract the PEM files from in the downloaded file. For example, if the file name is csr_w_pk_pkcs8.gz, decompress and unpack it using this command:

    tar zxvf csr_w_pk_pkcs8.gz

    Two files result from the extraction operation: certificate_request.pem and private_key_pkcs8.pem.

  10. Use this openssl command to decrypt the private key and create a file named key.pem: 

    openssl pkcs8 -in private_key_pkcs8.pem -out key.pem

  11. Copy the key.pem file into the ssl directory.

  12. Copy the certificate request in certificate_request.pem into the clipboard.

  13. Navigate to Security >> Local CAs. Select the same CA that you created earlier (the one you downloaded to create the CA.pem file), and click Sign Request. Paste the Certificate Request from the clipboard, choose a certificate purpose of Client (the keyring is a client of KeySecure), and click Sign Request. The result is a certificate signed with the selected CA in a new page.

  14. Copy the signed certificate to the clipboard, then save the clipboard contents as a file named cert.pem in the ssl directory.

  15. (Optional) If you wish to password-protect the key file, use the instructions in Password-Protecting the keyring_okv Key File.

After completing the preceding procedure, restart the MySQL server. It loads the keyring_okv plugin and keyring_okv uses the files in its configuration directory to communicate with KeySecure.

Password-Protecting the keyring_okv Key File

You can optionally protect the key file with a password and supply a file containing the password to enable the key file to be decrypted. To so do, change location to the ssl directory and perform these steps:

  1. Encrypt the key.pem key file. For example, use a command like this, and enter the encryption password at the prompts:

    shell> openssl rsa -des3 -in key.pem -out key.pem.new
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

  2. Save the encryption password in a single-line text file named password.txt in the ssl directory.

  3. Verify that the encrypted key file can be decrypted using the following command. The decrypted file should display on the console:

    shell> openssl rsa -in key.pem.new -passin file:password.txt

  4. Remove the original key.pem file and rename key.pem.new to key.pem.

  5. Change the ownership and access mode of new key.pem file and password.txt file as necessary to ensure that they have the same restrictions as other files in the ssl directory.

6.4.4.5 Using the keyring_aws Amazon Web Services Keyring Plugin

Note

The keyring_aws plugin is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

The keyring_aws plugin is a keyring plugin that 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. All keyring material is generated exclusively by the AWS server, not by keyring_aws.

keyring_aws is available on these platforms:

  • Debian 8

  • EL7

  • macOS 10.13 and 10.14

  • SLES 12

  • Ubuntu 14.04 and 16.04

  • Windows

The discussion here assumes that you are familiar with AWS in general and KMS in particular. Some pertinent information sources:

The following sections provide configuration and usage information for the keyring_aws keyring plugin:

keyring_aws Configuration

To install the keyring_aws plugin, use the general keyring installation instructions found in Section 6.4.4.1, “Keyring Plugin Installation”, together with the plugin-specific configuration information found here.

The plugin library file contains the keyring_aws plugin and two user-defined functions (UDFs), keyring_aws_rotate_cmk() and keyring_aws_rotate_keys().

To configure keyring_aws, you must obtain a secret access key that provides credentials for communicating with AWS KMS and write it to a configuration file:

  1. Create an AWS KMS account.

  2. Use AWS KMS to create a secret access key ID and secret access key. The access key serves to verify your identity and that of your applications.

  3. Use the AWS KMS account to create a customer master key (CMK) ID. At MySQL startup, set the keyring_aws_cmk_id system variable to the CMK ID value. This variable is mandatory and there is no default. (Its value can be changed at runtime if desired using SET GLOBAL.)

  4. If necessary, create the directory in which the configuration file will be located. The directory should have a restrictive mode and be accessible only to the account used to run the MySQL server. For example, on Unix and Unix-like systems, to use /usr/local/mysql/mysql-keyring/keyring_aws_conf as the file name, the following commands (executed as root) create its parent directory and set the directory mode and ownership:

    		cd /usr/local/mysql
    		mkdir mysql-keyring
    		chmod 750 mysql-keyring
    		chown mysql mysql-keyring
    		chgrp mysql mysql-keyring

    At MySQL startup, set the keyring_aws_conf_file system variable to /usr/local/mysql/mysql-keyring/keyring_aws_conf to indicate the configuration file location to the server.

  5. Prepare the keyring_aws configuration file, which should contain two lines:

    • Line 1: The secret access key ID

    • Line 2: The secret access key

    For example, if the key ID is wwwwwwwwwwwwwEXAMPLE and the key is xxxxxxxxxxxxx/yyyyyyy/zzzzzzzzEXAMPLEKEY, the configuration file looks like this:

    wwwwwwwwwwwwwEXAMPLE
xxxxxxxxxxxxx/yyyyyyy/zzzzzzzzEXAMPLEKEY

To be usable during the server startup process, keyring_aws must be loaded using the --early-plugin-load option. The keyring_aws_cmk_id system variable is mandatory and configures the customer master key (CMK) ID obtained from the AWS KMS server. The keyring_aws_conf_file and keyring_aws_data_file system variables optionally configure the locations of the files used by the keyring_aws plugin for configuration information and data storage. The file location variable default values are platform specific. To configure the locations explicitly, set the variable values at startup. For example, use these lines in the server my.cnf file (adjust the .so suffix and file locations for your platform as necessary):

[mysqld]
early-plugin-load=keyring_aws.so
keyring_aws_cmk_id='arn:aws:kms:us-west-2:111122223333:key/abcd1234-ef56-ab12-cd34-ef56abcd1234'
keyring_aws_conf_file=/usr/local/mysql/mysql-keyring/keyring_aws_conf
keyring_aws_data_file=/usr/local/mysql/mysql-keyring/keyring_aws_data

For the keyring_aws plugin to start successfully, the configuration file must exist and contain valid secret access key information, initialized as described previously. The storage file need not exist. If it does not, keyring_aws attempts to create it (as well as its parent directory, if necessary).

For additional information about the system variables used to configure the keyring_aws plugin, see Section 6.4.4.12, “Keyring System Variables”.

Start the MySQL server and install the UDFs associated with the keyring_aws plugin. This is a one-time operation, performed by executing the following statements (adjust the .so suffix for your platform as necessary):

CREATE FUNCTION keyring_aws_rotate_cmk RETURNS INTEGER
  SONAME 'keyring_aws.so';
CREATE FUNCTION keyring_aws_rotate_keys RETURNS INTEGER
  SONAME 'keyring_aws.so';

For additional information about the keyring_aws UDFs, see Section 6.4.4.10, “Plugin-Specific Keyring Key-Management Functions”.

keyring_aws Operation

At plugin startup, the keyring_aws plugin reads the AWS secret access key ID and key from its configuration file. It also reads any encrypted keys contained in its storage file into its in-memory cache.

During operation, keyring_aws maintains encrypted keys in the in-memory cache and uses the storage file as local persistent storage. Each keyring operation is transactional: keyring_aws either successfully changes both the in-memory key cache and the keyring storage file, or the operation fails and the keyring state remains unchanged.

To ensure that keys are flushed only when the correct keyring storage file exists, keyring_aws stores a SHA-256 checksum of the keyring in the file. Before updating the file, the plugin verifies that it contains the expected checksum.

The keyring_aws plugin supports the functions that comprise the standard MySQL Keyring service interface. Keyring operations performed by these functions are accessible at two levels:

Example (using UDFs):

SELECT keyring_key_generate('MyKey', 'AES', 32);
SELECT keyring_key_remove('MyKey');

In addition, the keyring_aws_rotate_cmk() and keyring_aws_rotate_keys() UDFs “extend” the keyring plugin interface to provide AWS-related capabilities not covered by the standard keyring service interface. These capabilities are accessible only by calling the UDFs. There are no corresponding C-languge key service functions.

For information about the key types permitted by keyring_aws, see Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

keyring_aws Credential Changes

Assuming that the keyring_aws plugin has initialized properly at server startup, it is possible to change the credentials used for communicating with AWS KMS:

  1. Use AWS KMS to create a new secret access key ID and secret access key.

  2. Store the new credentials in the configuration file (the file named by the keyring_aws_conf_file system variable). The file format is as described previously.

  3. Reinitialize the keyring_aws plugin so that it rereads the configuration file. Assuming that the new credentials are valid, the plugin should initialize successfully.

    There are two ways to reinitialize the plugin:

    • Restart the server. This is simpler and has no side effects, but is not suitable for installations that require minimal server downtime with as few restarts as possible.

    • Reinitialize the plugin without restarting the server by executing the following statements (adjust the .so suffix for your platform as necessary):

      UNINSTALL PLUGIN keyring_aws;
INSTALL PLUGIN keyring_aws SONAME 'keyring_aws.so';
      Note

      In addition to loading a plugin at runtime, INSTALL PLUGIN has the side effect of registering the plugin it in the mysql.plugin system table. Because of this, if you decide to stop using keyring_aws, it is not sufficient to remove the --early-plugin-load option from the set of options used to start the server. That stops the plugin from loading early, but the server still attempts to load it when it gets to the point in the startup sequence where it loads the plugins registered in mysql.plugin.

      Consequently, if you execute the UNINSTALL PLUGIN plus INSTALL PLUGIN sequence just described to change the AWS KMS credentials, then to stop using keyring_aws, it is necessary to execute UNINSTALL PLUGIN again to unregister the plugin in addition to removing the --early-plugin-load option.

6.4.4.6 Using the HashiCorp Vault Keyring Plugin

Note

The keyring_hashicorp plugin is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

The keyring_hashicorp plugin is a keyring plugin that communicates with HashiCorp Vault for back end storage. The plugin supports HashiCorp Vault AppRole authentication. All keys are stored in HashiCorp Vault. No key information is permanently stored in MySQL server local storage. (An optional in-memory key cache may be used as intermediate storage.) Random key generation is performed on the MySQL server side, and the keys are subsequently stored to Hashicorp Vault.

The keyring_hashicorp plugin supports the functions that comprise the standard MySQL Keyring service interface. Keyring operations performed by those functions are accessible at two levels:

Example (using UDFs):

SELECT keyring_key_generate('MyKey', 'AES', 32);
SELECT keyring_key_remove('MyKey');

For information about the key types permitted by keyring_hashicorp, see Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

To install the keyring_hashicorp plugin, use the general keyring installation instructions found in Section 6.4.4.1, “Keyring Plugin Installation”, together with the configuration information specific to keyring_hashicorp found here. Plugin-specific information includes preparation of the certificate and key files needed for connecting to HashiCorp Vault, as well as configuring Vault itself. The following sections provide the necessary instructions.

Certificate and Key Preparation

The keyring_hashicorp plugin requires a secure connection to the HashiCorp Vault server, employing the HTTPS protocol. A typical setup includes a set of certificate and key files:

  • company.crt: A custom CA certificate belonging to the organization. This file is used both by HashiCorp Vault server and the keyring_hashicorp plugin.

  • vault.key: The private key of the HashiCorp Vault server instance. This file is used by HashiCorp Vault server.

  • vault.crt: The certificate of the HashiCorp Vault server instance. This file must be signed by the organization CA certificate.

The following instructions describe how to create the certificate and key files using OpenSSL. (If you already have the files, proceeed to HashiCorp Vault Setup.) The instructions as shown apply to Linux platforms and may require adjustment for other platforms.

Important

Certificates generated by these instructions are self-signed, which may not be very secure. After you gain experience using such files, consider obtaining certificate/key material from a registered certificate authority.

  1. Prepare the company and HashiCorp Vault server keys.

    Use the following commands to generate the key files:

    openssl genrsa -aes256 -out company.key 4096
openssl genrsa -aes256 -out vault.key 2048

    The commands produce files holding the company private key (company.key) and the Vault server private key (vault.key). The keys are randomly generated RSA keys of 4,096 and 2,048 bits, respectively.

    Each command prompts for a password. (For testing purposes, the password is not required. To disable it, omit the -aes256 argument.)

    The key files hold sensitive information and should be stored in a secure location. The password (also sensitive) is required later, so write it down and store it in a secure location.

    (Optional) To check key file content and validity, use the following commands:

    openssl rsa -in company.key -check
openssl rsa -in vault.key -check

  2. Create the company CA certificate.

    Use the following command to create a company CA certificate file named company.crt that is valid for 365 days (enter the command on a single line):

    openssl req -x509 -new -nodes -key company.key
  -sha256 -days 365 -out company.crt

    You will be prompted for the company key password during CA certificate creation, if you used the -aes256 argument to perform key encryption during key generation. You will also be prompted for information about the certificate holder (that is, you or your company), as shown here:

    Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:

    Answer the prompts with appropriate values.

  3. Create a certificate signing request.

    To create a HashiCorp Vault server certificate, a Certificate Signing Request (CSR) must be prepared for the newly created server key. Create a configuration file named request.conf containing the following lines. If the HashiCorp Vault server does not run on the local host, substitute appropriate CN and IP values, and make any other changes required.

    [req]
distinguished_name = vault
x509_entensions = v3_req
prompt = no

[vault]
C = US
ST = CA
L = RWC
O = Company
CN = 127.0.0.1

[v3_req]
subjectAltName = @alternatives
authorityKeyIdentifier = keyid,issuer
basicConstraints = CA:TRUE

[alternatives]
IP = 127.0.0.1

    Use this command to create the signing request:

    openssl req -new -key vault.key -config request.conf -out request.csr

    The output file (request.csr) is an intermediate file that serves as input for creation of the server certificate.

  4. Create the HashiCorp Vault server certificate.

    Sign the combined information from the HashiCorp Vault server key (vault.key) and the CSR (request.csr) with the company certificate (company.crt) to create the HashiCorp Vault server certificate (vault.crt). Use the following command to do this (enter the command on a single line):

    openssl x509 -req -in request.csr
  -CA company.crt -CAkey company.key -CAcreateserial
  -out vault.crt -days 365 -sha256

    To make the vault.crt server certificate useful, append the contents of the company.crt company certificate to it. This is required so that the company certificate is delivered along with the server certificate in requests.

    cat company.crt >> vault.crt

    If you open the vault.crt file with a text editor, its content should look like this:

    		content of HashiCorp Vault server certificate
    		content of company certificate
HashiCorp Vault Setup

The following instructions describe how to create a HashiCorp Vault setup that facilitates testing the keyring_hashicorp plugin.

Important

A test setup is similar to a production setup, but production use of HashiCorp Vault entails additional security considerations such as use of non-self-signed certificates and storing the company certificate in the system trust store. It is assumed that you will implement whatever additional security steps are needed to satisfy your operational requirements.

These instructions assume availability of the certificate and key files created in Certificate and Key Preparation. See that section if you do not have the files.

  1. Fetch the HashiCorp Vault binary.

    Download the HashiCorp Vault binary appropriate for your platform from https://www.vaultproject.io/downloads.html.

    Extract the content of the archive to produce the executable vault command, which is used to perform HashiCorp Vault operations. If necessary, add the directory where you install the command to the system path.

    (Optional) HashiCorp Vault supports autocomplete options that make it easier to use. For more information, see https://learn.hashicorp.com/vault/getting-started/install#command-completion.

  2. Create the HashiCorp Vault server configuration file.

    Prepare a configuration file named config.hcl with the following content. For the tls_cert_file, tls_key_file, and path values, substitute path names appropriate for your system.

    listener "tcp" {
  address="127.0.0.1:8200"
  tls_cert_file="/home/username/certificates/vault.crt"
  tls_key_file="/home/username/certificates/vault.key"
}

storage "file" {
  path = "/home/username/vaultstorage/storage"
}

ui = true

  3. Start the HashiCorp Vault server.

    To start the Vault server, use the following command, where the -config option specifies the path to the configuration file just created:

    vault server -config=config.hcl

    During this step, you may be prompted for a password for the Vault server private key stored in the vault.key file.

    The server should start, displaying some information on the console (IP, port, and so forth).

    So that you can enter the remaining commands, put the vault server command in the background or open another terminal before continuing.

  4. Initialize the HashiCorp Vault server.

    Note

    The operations described in this step are required only when starting Vault the first time, to obtain the unseal key and root token. Subsequent Vault instance restarts require only unsealing using the unseal key.

    Issue the following commands (assuming Bourne shell syntax):

    export VAULT_SKIP_VERIFY=1
vault operator init -n 1 -t 1

    The first command enables the vault command to temporarily ignore the fact that no company certificate has been added to the system trust store. It compensates for the fact that our self-signed CA is not added to that store. (For production use, such a certificate should be added.)

    The second command creates a single unseal key with a requirement for a single unseal key to be present for unsealing. (For production use, an instance would have multiple unseal keys with up to that many keys required to be entered to unseal it. The unseal keys should be delivered to key custodians within the company. Use of a single key might be considered a security issue because that permits the vault to be unsealed by a single key custodian.)

    Vault should reply with information about the unseal key and root token, plus some additional text (the actual unseal key and root token values will differ from those shown here):

    ...
Unseal Key 1: I2xwcFQc892O0Nt2pBiRNlnkHzTUrWS+JybL39BjcOE=
Initial Root Token: s.vTvXeo3tPEYehfcd9WH7oUKz
...

    Store the unseal key and root token in a secure location.

  5. Unseal the HashiCorp Vault server.

    Use this command to unseal the Vault server:

    vault operator unseal

    When prompted to enter the unseal key, use the key obtained previously during Vault initialization.

    Vault should produce output indicating that setup is complete and the vault is unsealed.

  6. Log in to the HashiCorp Vault server and verify its status.

    Prepare the environment variables required for logging in as root:

    vault login s.vTvXeo3tPEYehfcd9WH7oUKz

    For the token value in that command, substitute the content of the root token obtained previously during Vault initialization.

    Verify the Vault server status:

    vault status

    The output should contain these lines (among others):

    ...
Initialized     true
Sealed          false
...

  7. Set up HashiCorp Vault authentication and storage.

    Note

    The operations described in this step are needed only the first time the Vault instance is run. They need not be repeated afterward.

    Enable the AppRole authentication method and verify that it is in the authentication method list:

    vault auth enable approle
vault auth list

    Enable the Vault KeyValue storage engine:

    vault secrets enable -version=1 kv

    Create and set up a role for use with the keyring_hashicorp plugin (enter the command on a single line):

    vault write auth/approle/role/mysql token_num_uses=0
  token_ttl=20m token_max_ttl=30m secret_id_num_uses=0

  8. Add an AppRole security policy.

    Note

    The operations described in this step are needed only the first time the Vault instance is run. They need not be repeated afterward.

    Prepare a policy that to permit the previously created role to access appropriate secrets. Create a new file named mysql.hcl with the following content:

    path "kv/mysql/*" {
  capabilities = ["create", "read", "update", "delete", "list"]
}

    Import the policy file to the Vault server to create a policy named mysql-policy, then assign the policy to the new role:

    vault policy write mysql-policy mysql.hcl
vault write auth/approle/role/mysql policies=mysql-policy

    Obtain the ID of the newly created role and store it in a secure location:

    vault read auth/approle/role/mysql/role-id

    Generate a secret ID for the role and store it in a secure location:

    vault write -f auth/approle/role/mysql/secret-id

    After these AppRole role ID and secret ID credentials are generated, they are expected to remain valid indefinitely. They need not be generated again and the keyring_hashicorp plugin can be configured with them for use on an ongoing basis. For more information about AuthRole authentication, visit https://www.vaultproject.io/docs/auth/approle.html.

keyring_hashicorp Configuration

The plugin library file contains the keyring_hashicorp plugin and a user-defined function (UDF), keyring_hashicorp_update_config(). When the plugin initializes and terminates, it automatically loads and unloads the UDF, so there is no need to load and unload the UDF manually.

The keyring_hashicorp plugin supports the configuration arguments shown in the following table, specified by assigning values to a set of system variables.

Configuration ParameterSystem VariableMandatory
HashiCorp Server URL keyring_hashicorp_server_url  
AppRole role ID keyring_hashicorp_role_id Yes
AppRole secret ID keyring_hashicorp_secret_id Yes
Store path keyring_hashicorp_store_path Yes
Authorization Path keyring_hashicorp_auth_path  
CA certificate file path keyring_hashicorp_ca_path  
Caching keyring_hashicorp_caching  

To be usable during the server startup process, keyring_hashicorp must be loaded using the --early-plugin-load option. As indicated by the preceding table, several plugin-related system variables are mandatory and must also be set. For example, use these lines in the server my.cnf file (adjust the .so suffix and file locations for your platform as necessary):

[mysqld]
early-plugin-load=keyring_hashicorp.so
keyring_hashicorp_role_id='ee3b495c-d0c9-11e9-8881-8444c71c32aa'
keyring_hashicorp_secret_id='0512af29-d0ca-11e9-95ee-0010e00dd718'
keyring_hashicorp_store_path='/v1/kv/mysql'

MySQL Server authenticates against HashiCorp Vault using AppRole authentication. Successful authentication requires that two secrets be provided to Vault, a role ID and a secret ID, which are similar in concept to user name and password. The role ID and secret ID values to use are those obtained during the HashiCorp Vault setup procedure performed previously. To specify the two IDs, assign their respective values to the keyring_hashicorp_role_id and keyring_hashicorp_secret_id system variables. The setup procedure also results in a store path of /v1/kv/mysql, which is the value to assign to keyring_hashicorp_commit_store_path.

At plugin initialization time, keyring_hashicorp attempts to connect to the HashiCorp Vault server using the configuration values. If the connection is successful, the plugin stores the values in corresponding system variables that have _commit_ in their name. For example, upon successful connection, the plugin stores the values of keyring_hashicorp_role_id and keyring_hashicorp_store_path in keyring_hashicorp_commit_role_id and keyring_hashicorp_commit_store_path.

Reconfiguration at runtime can be performed with the assistance of the keyring_hashicorp_update_config() UDF:

  1. Use SET statements to assign the desired new values to the configuration system variables shown in the preceding table. These assignments in themselves have no effect on ongoing plugin operation.

  2. Invoke keyring_hashicorp_update_config() to cause the plugin to reconfigure and reconnect to the HashiCorp Vault server using the new variable values.

  3. If the connection is successful, the plugin stores the updated configuration values in corresponding system variables that have _commit_ in their name.

For example, if you have reconfigured HashiCorp Vault to listen on port 8201 rather than the default 8200, reconfigure keyring_hashicorp like this:

SET GLOBAL keyring_hashicorp_server_url = 'https://127.0.0.1:8201';
SELECT keyring_hashicorp_update_config();

If the plugin is not able to connect to HashiCorp Vault during initialization or reconfiguration and there was no existing connection, the _commit_ system variables are set to 'Not committed' for string-valued variables, and OFF for Boolean-valued variables. If the plugin is not able to connect but there was an existing connection, that connection remains active and the _commit_ variables reflect the values used for it.

Note

If you do not set the mandatory system variables at server startup, or if some other plugin initialization error occurs, initialization fails. In this case, you can use the runtime reconfiguration procedure to initialize the plugin without restarting the server.

For additional information about the keyring_hashicorp plugin-specific system variables and UDF, see Section 6.4.4.12, “Keyring System Variables”, and Section 6.4.4.10, “Plugin-Specific Keyring Key-Management Functions”.

6.4.4.7 Migrating Keys Between Keyring Keystores

The MySQL server supports an operational mode that enables migration of keys between underlying keyring keystores. This enables DBAs to switch a MySQL installation from one keyring plugin to another. A migration server (that is, a server started in key migration mode) does not accept client connections. Instead, it runs only long enough to migrate keys, then exits. A migration server reports errors to the console (the standard error output).

It is possible to perform offline or online key migration:

  • If you are sure that no running server on the local host is using the source or destination keystore, an offline migration is possible. In this case, the migration server can modify the keystores without possibility of a running server modifying keystore content during the migration.

  • If a running server on the local host is using the source or destination keystore, an online migration must be performed. In this case, the migration server connects to the running server and instructs it to pause keyring operations while key migration is in progress.

The result of a key migration operation is that the destination keystore contains the keys it had prior to the migration, plus the keys from the source keystore. The source keystore is the same before and after the migration because keys are copied, not moved. If a key to be copied already exists in the destination keystore, an error occurs and the destination keystore is restored to its premigration state.

The user who invokes the server in key-migration mode must not be the root operating system user, and must have permission to read and write the keyring files.

To perform a key migration operation, determine which key migration options are needed. Migration options indicate which keyring plugins are involved, and whether to perform an offline or online migration:

  • To indicate the source and destination keyring plugins, specify these options:

    These options tell the server to run in key migration mode. Both options are mandatory for all key migration operations. The source and destination plugins must differ, and the migration server must support both plugins.

  • For an offline migration, no additional key migration options are needed.

    Warning

    Do not perform offline migration involving a keystore that is in use by a running server.

  • For an online migration, some running server currently is using the source or destination keystore. Specify the key migration options that indicate how to connect to the running server. This is necessary so that the migration server can connect to the running server and tell it to pause keyring use during the migration operation.

    Use of any of the following options signifies an online migration:

For additional details about the key migration options, see Section 6.4.4.11, “Keyring Command Options”.

Start the migration server with the key migration options determined as just described, possibly with other options. Keep the following considerations in mind:

  • Other server options might be required, such as other configuration parameters for the two keyring plugins. For example, if keyring_file is one of the plugins, you must set the keyring_file_data system variable if the keyring data file location is not the default location. Other non-keyring options may be required as well. One way to specify these options is by using --defaults-file to name an option file that contains the required options.

  • If you invoke the migration server from a system account different from that normally used to run MySQL, it might create keyring directories or files that are inaccessible to the server during normal operation. Suppose that mysqld normally runs as the mysql operating system user, but you invoke the migration server while logged in as isabel. Any new directories or files created by the migration server will be owned by isabel. Subsequent startup will fail when a server run as the mysql operating system user attempts to access file system objects owned by isabel.

    To avoid this issue, start the migration server as the root operating system user and provide a --user=user_name option, where user_name is the system account normally used to run MySQL.

  • The migration server expects path name option values to be full paths. Relative path names may not be resolved as you expect.

Example command line for offline key migration:

mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
  --keyring-migration-source=keyring_file.so
  --keyring-migration-destination=keyring_encrypted_file.so
  --keyring_encrypted_file_password=password

Example command line for online key migration:

password
root_password

The key migration server performs the migration operation as follows:

  1. (Online migration only) Connect to the running server using the connection options. The account used to connect must have the privileges required to modify the global keyring_operations system variable (ENCRYPTION_KEY_ADMIN in addition to either SYSTEM_VARIABLES_ADMIN or SUPER).

  2. (Online migration only) Disable keyring_operations on the running server. (The running server must support keyring_operations.)

  3. Load the source and destination keyring plugins.

  4. Copy keys from the source keyring to the destination keyring.

  5. Unload the keyring plugins.

  6. (Online migration only) Enable keyring_operations on the running server.

  7. (Online migration only) Disconnect from the running server.

  8. Exit.

If an error occurs during key migration, any keys that were copied to the destination plugin are removed, leaving the destination keystore unchanged.

Important

For an online migration operation, the migration server takes care of enabling and disabling keyring_operations on the running server. However, if the migration server exits abnormally (for example, if someone forcibly terminates it), it is possible that keyring_operations will not have been re-enabled on the running server, leaving it unable to perform keyring operations. In this case, it may be necessary to connect to the running server and enable keyring_operations manually.

After a successful online key migration operation, the running server might need to be restarted:

  • If the running server was using the source keystore, it need not be restarted after the migration.

  • If the running server was using the source keystore before the migration but should use the destination keystore after the migration, it must be reconfigured to use the destination keyring plugin and restarted.

  • If the running server was using the destination keystore and will continue to use it, it should be restarted after the migration to load all keys migrated into the destination keystore.

Note

MySQL server key migration mode supports pausing a single running server. To perform a key migration if multiple key servers are using the keystores involved, use this procedure:

  1. Connect to each running server manually and set keyring_operations=OFF.

  2. Use the migration server to perform an offline key migration.

  3. Connect to each running server manually and set keyring_operations=ON.

All running servers must support the keyring_operations=ON system variable.

6.4.4.8 Supported Keyring Key Types and Lengths

MySQL Keyring supports keys of different types (encryption algorithms) and lengths:

  • The available key types depend on which keyring plugin is installed.

  • The permitted key lengths are subject to multiple factors:

    • General keyring UDF interface limits (for keys managed using one of the keyring UDFs described in Section 6.4.4.9, “General-Purpose Keyring Key-Management Functions”), or limits from back end implementations. These length limits can vary by key operation type.

    • In addition to the general limits, individual plugins may impose restrictions on key lengths per key type.

Table 6.26, “General Keyring Key Length Limits” shows the general key length limits. (The lower limits for keyring_aws are imposed by the AWS KMS interface, not the keyring UDFs.) Table 6.27, “Keyring Plugin Key Types and Lengths” shows for each keyring plugin the key types it permits, as well as any plugin-specific key-length restrictions.

Table 6.26 General Keyring Key Length Limits

Key OperationMaximum Key Length
Generate key

16,384 bytes (2,048 prior to MySQL 8.0.18); 1,024 for keyring_aws
Store key

16,384 bytes (2,048 prior to MySQL 8.0.18); 4,096 for keyring_aws
Fetch key

16,384 bytes (2,048 prior to MySQL 8.0.18); 4,096 for keyring_aws

Table 6.27 Keyring Plugin Key Types and Lengths

Plugin NamePermitted Key TypePlugin-Specific Length Restrictions
keyring_encrypted_file

AES

DSA

RSA

SECRET

None

None

None

None

keyring_file

AES

DSA

RSA

SECRET

None

None

None

None

keyring_okv

AES

SECRET

16, 24, or 32 bytes

None

keyring_aws

AES

SECRET

16, 24, or 32 bytes

None

keyring_hashicorp

AES

DSA

RSA

SECRET

None

None

None

None


The SECRET key type, available as of MySQL 8.0.19, is intended for general-purpose storage of sensitive data using the MySQL keyring, and is supported by all keyring plugins. The keyring encrypts and decrypts SECRET data as a byte stream upon storage and retrieval.

Example keyring operations involving the SECRET key type: 

SELECT keyring_key_generate('MySecret1', 'SECRET', 20);
SELECT keyring_key_remove('MySecret1');

SELECT keyring_key_store('MySecret2', 'SECRET', 'MySecretData');
SELECT keyring_key_fetch('MySecret2');
SELECT keyring_key_length_fetch('MySecret2');
SELECT keyring_key_type_fetch('MySecret2');
SELECT keyring_key_remove('MySecret2');

6.4.4.9 General-Purpose Keyring Key-Management Functions

MySQL Server supports a keyring service that enables internal server components and plugins to securely store sensitive information for later retrieval.

MySQL Server also includes an SQL interface for keyring key management, implemented as a set of general-purpose user-defined functions (UDFs) that access the functions provided by the internal keyring service. The keyring UDFs are contained in a plugin library file, which also contains a keyring_udf plugin that must be enabled prior to UDF invocation. For these UDFs to be used, a keyring plugin such as keyring_file or keyring_okv must be enabled.

The UDFs described here are general purpose and intended for use with any keyring plugin. A given keyring plugin might have UDFs of its own that are intended for use only with that plugin; see Section 6.4.4.10, “Plugin-Specific Keyring Key-Management Functions”.

The following sections provide installation instructions for the keyring UDFs and demonstrate how to use them. For information about the keyring service functions invoked by the UDFs, see Section 29.3.2, “The Keyring Service”. For general keyring information, see Section 6.4.4, “The MySQL Keyring”.

Installing or Uninstalling General-Purpose Keyring Functions

This section describes how to install or uninstall the keyring user-defined functions (UDFs), which are implemented in a plugin library file that also contains a keyring_udf plugin. For general information about installing or uninstalling plugins and UDFs, see Section 5.6.1, “Installing and Uninstalling Plugins”, and Section 5.7.1, “Installing and Uninstalling User-Defined Functions”.

The keyring UDFs enable keyring key management operations, but the keyring_udf plugin must also be installed because the UDFs will not work correctly without it. Attempts to use the UDFs without the keyring_udf plugin result in an error.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

The plugin library file base name is keyring_udf. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

To install the keyring_udf plugin and the UDFs, use the INSTALL PLUGIN and CREATE FUNCTION statements (adjust the .so suffix for your platform as necessary):

INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_generate RETURNS INTEGER
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_fetch RETURNS STRING
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_store RETURNS INTEGER
  SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_remove RETURNS INTEGER
  SONAME 'keyring_udf.so';

If the plugin and UDFs are used on a master replication server, install them on all slave servers as well to avoid replication issues.

Once installed as just described, the plugin and UDFs remain installed until uninstalled. To remove them, use the UNINSTALL PLUGIN and DROP FUNCTION statements:

UNINSTALL PLUGIN keyring_udf;
DROP FUNCTION keyring_key_generate;
DROP FUNCTION keyring_key_fetch;
DROP FUNCTION keyring_key_length_fetch;
DROP FUNCTION keyring_key_type_fetch;
DROP FUNCTION keyring_key_store;
DROP FUNCTION keyring_key_remove;
Using General-Purpose Keyring Functions

Before using the keyring user-defined functions (UDFs), install them according to the instructions provided in Installing or Uninstalling General-Purpose Keyring Functions.

The keyring UDFs are subject to these constraints:

  • To use any keyring UDF, the keyring_udf plugin must be enabled. Otherwise, an error occurs:

    ERROR 1123 (HY000): Can't initialize function 'keyring_key_generate';
This function requires keyring_udf plugin which is not installed.
Please install

    To install the keyring_udf plugin, see Installing or Uninstalling General-Purpose Keyring Functions.

  • The keyring UDFs invoke keyring service functions (see Section 29.3.2, “The Keyring Service”). The service functions in turn use whatever keyring plugin is installed (for example, keyring_file or keyring_okv). Therefore, to use any keyring UDF, some underlying keyring plugin must be enabled. Otherwise, an error occurs:

    ERROR 3188 (HY000): Function 'keyring_key_generate' failed because
underlying keyring service returned an error. Please check if a
keyring plugin is installed and that provided arguments are valid
for the keyring you are using.

    To install a keyring plugin, see Section 6.4.4.1, “Keyring Plugin Installation”.

  • To use any keyring UDF, a user must possess the global EXECUTE privilege. Otherwise, an error occurs:

    ERROR 1123 (HY000): Can't initialize function 'keyring_key_generate';
The user is not privileged to execute this function. User needs to
have EXECUTE

    To grant the global EXECUTE privilege to a user, use this statement:

    GRANT EXECUTE ON *.* TO user;

    Alternatively, should you prefer to avoid granting the global EXECUTE privilege while still permitting users to access specific key-management operations, “wrapper” stored programs can be defined (a technique described later in this section).

  • A key stored in the keyring by a given user can be manipulated later only by the same user. That is, the value of the CURRENT_USER() function at the time of key manipulation must have the same value as when the key was stored in the keyring. (This constraint rules out the use of the keyring UDFs for manipulation of instance-wide keys, such as those created by InnoDB to support tablespace encryption.)

    To enable multiple users to perform operations on the same key, “wrapper” stored programs can be defined (a technique described later in this section).

  • Keyring UDFs support the key types and lengths supported by the underlying keyring plugin. For information about keys specific to a particular keyring plugin, see Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

To create a new random key and store it in the keyring, call keyring_key_generate(), passing to it an ID for the key, along with the key type (encryption method) and its length in bytes. The following call creates a 2,048-bit DSA-encrypted key named MyKey:

mysql> SELECT keyring_key_generate('MyKey', 'DSA', 256);
+-------------------------------------------+
| keyring_key_generate('MyKey', 'DSA', 256) |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+

A return value of 1 indicates success. If the key cannot be created, the return value is NULL and an error occurs. One reason this might be is that the underlying keyring plugin does not support the specified combination of key type and key length; see Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

To be able to check the return type regardless of whether an error occurs, use SELECT ... INTO @var_name and test the variable value:

SELECT keyring_key_generate('', '', -1) INTO @x;
SELECT @x;
SELECT keyring_key_generate('x', 'AES', 16) INTO @x;
SELECT @x;

This technique also applies to other keyring UDFs that for failure return a value and an error.

The ID passed to keyring_key_generate() provides a means by which to refer to the key in subsequent UDF calls. For example, use the key ID to retrieve its type as a string or its length in bytes as an integer:

SELECT keyring_key_type_fetch('MyKey');
SELECT keyring_key_length_fetch('MyKey');

To retrieve a key value, pass the key ID to keyring_key_fetch(). The following example uses HEX() to display the key value because it may contain nonprintable characters. The example also uses a short key for brevity, but be aware that longer keys provide better security:

SELECT keyring_key_generate('MyShortKey', 'DSA', 8);
SELECT HEX(keyring_key_fetch('MyShortKey'));

Keyring UDFs treat key IDs, types, and values as binary strings, so comparisons are case-sensitive. For example, IDs of MyKey and mykey refer to different keys.

To remove a key, pass the key ID to keyring_key_remove():

mysql> SELECT keyring_key_remove('MyKey');
+-----------------------------+
| keyring_key_remove('MyKey') |
+-----------------------------+
|                           1 |
+-----------------------------+

To obfuscate and store a key that you provide, pass the key ID, type, and value to keyring_key_store():

mysql> SELECT keyring_key_store('AES_key', 'AES', 'Secret string');
+------------------------------------------------------+
| keyring_key_store('AES_key', 'AES', 'Secret string') |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

As indicated previously, a user must have the global EXECUTE privilege to call keyring UDFs, and the user who stores a key in the keyring initially must be the same user who performs subsequent operations on the key later, as determined from the CURRENT_USER() value in effect for each UDF call. To permit key operations to users who do not have the global EXECUTE privilege or who may not be the key “owner,” use this technique:

  1. Define “wrapper” stored programs that encapsulate the required key operations and have a DEFINER value equal to the key owner.

  2. Grant the EXECUTE privilege for specific stored programs to the individual users who should be able to invoke them.

  3. If the operations implemented by the wrapper stored programs do not include key creation, create any necessary keys in advance, using the account named as the DEFINER in the stored program definitions.

This technique enables keys to be shared among users and provides to DBAs more fine-grained control over who can do what with keys, without having to grant global privileges.

The following example shows how to set up a shared key named SharedKey that is owned by the DBA, and a get_shared_key() stored function that provides access to the current key value. The value can be retrieved by any user with the EXECUTE privilege for that function, which is created in the key_schema schema.

From a MySQL administrative account ('root'@'localhost' in this example), create the administrative schema and the stored function to access the key:

CREATE SCHEMA key_schema;
CREATE DEFINER = 'root'@'localhost'
FUNCTION key_schema.get_shared_key()
RETURNS BLOB READS SQL DATA
RETURN keyring_key_fetch('SharedKey');

From the administrative account, ensure that the shared key exists:

mysql> SELECT keyring_key_generate('SharedKey', 'DSA', 8);
+---------------------------------------------+
| keyring_key_generate('SharedKey', 'DSA', 8) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

From the administrative account, create an ordinary user account to which key access is to be granted:

CREATE USER 'key_user'@'localhost'
IDENTIFIED BY 'key_user_pwd';

From the key_user account, verify that, without the proper EXECUTE privilege, the new account cannot access the shared key:

mysql> SELECT HEX(key_schema.get_shared_key());
ERROR 1370 (42000): execute command denied to user 'key_user'@'localhost'
for routine 'key_schema.get_shared_key'

From the administrative account, grant EXECUTE to key_user for the stored function:

GRANT EXECUTE ON FUNCTION key_schema.get_shared_key
TO 'key_user'@'localhost';

From the key_user account, verify that the key is now accessible:

mysql> SELECT HEX(key_schema.get_shared_key());
+----------------------------------+
| HEX(key_schema.get_shared_key()) |
+----------------------------------+
| 9BAFB9E75CEEB013                 |
+----------------------------------+
General-Purpose Keyring Function Reference

For each general-purpose keyring user-defined function (UDF), this section describes its purpose, calling sequence, and return value. For information about the conditions under which these UDFs can be invoked, see Using General-Purpose Keyring Functions.

  • keyring_key_fetch(key_id)

    Given a key ID, deobfuscates and returns the key value.

    Arguments:

    • key_id: A string that specifies the key ID.

    Return value:

    Returns the key value as a string for success, NULL if the key does not exist, or NULL and an error for failure.

    Note

    Key values retrieved using keyring_key_fetch() are subject to the general keyring UDF limits described in Section 6.4.4.8, “Supported Keyring Key Types and Lengths”. A key value longer than that length can be stored using a keyring service function (see Section 29.3.2, “The Keyring Service”), but if retrieved using keyring_key_fetch() is truncated to the general keyring UDF limit.

    Example:

    		SELECT keyring_key_generate('RSA_key', 'RSA', 16);
    		SELECT HEX(keyring_key_fetch('RSA_key'));
    		SELECT keyring_key_type_fetch('RSA_key');
    		SELECT keyring_key_length_fetch('RSA_key');

    The example uses HEX() to display the key value because it may contain nonprintable characters. The example also uses a short key for brevity, but be aware that longer keys provide better security.

  • keyring_key_generate(key_id, key_type, key_length)

    Generates a new random key with a given ID, type, and length, and stores it in the keyring. The type and length values must be consistent with the values supported by the underlying keyring plugin. See Section 6.4.4.8, “Supported Keyring Key Types and Lengths”.

    Arguments:

    • key_id: A string that specifies the key ID.

    • key_type: A string that specifies the key type.

    • key_length: An integer that specifies the key length in bytes.

    Return value:

    Returns 1 for success, or NULL and an error for failure.

    Example:

    mysql> SELECT keyring_key_generate('RSA_key', 'RSA', 384);
+---------------------------------------------+
| keyring_key_generate('RSA_key', 'RSA', 384) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

  • keyring_key_length_fetch(key_id)

    Given a key ID, returns the key length.

    Arguments:

    • key_id: A string that specifies the key ID.

    Return value:

    Returns the key length in bytes as an integer for success, NULL if the key does not exist, or NULL and an error for failure.

    Example:

    See the description of keyring_key_fetch().

  • keyring_key_remove(key_id)

    Removes the key with a given ID from the keyring.

    Arguments:

    • key_id: A string that specifies the key ID.

    Return value:

    Returns 1 for success, or NULL for failure.

    Example:

    mysql> SELECT keyring_key_remove('AES_key');
+-------------------------------+
| keyring_key_remove('AES_key') |
+-------------------------------+
|                             1 |
+-------------------------------+

  • keyring_key_store(key_id, key_type, key)

    Obfuscates and stores a key in the keyring.

    Arguments:

    • key_id: A string that specifies the key ID.

    • key_type: A string that specifies the key type.

    • key: A string that specifies the key value.

    Return value:

    Returns 1 for success, or NULL and an error for failure.

    Example:

    mysql> SELECT keyring_key_store('new key', 'DSA', 'My key value');
+-----------------------------------------------------+
| keyring_key_store('new key', 'DSA', 'My key value') |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

  • keyring_key_type_fetch(key_id)

    Given a key ID, returns the key type.

    Arguments:

    • key_id: A string that specifies the key ID.

    Return value:

    Returns the key type as a string for success, NULL if the key does not exist, or NULL and an error for failure.

    Example:

    See the description of keyring_key_fetch().

6.4.4.10 Plugin-Specific Keyring Key-Management Functions

For each keyring plugin-specific user-defined function (UDF), this section describes its purpose, calling sequence, and return value. For information about general-purpose keyring UDFs, see Section 6.4.4.9, “General-Purpose Keyring Key-Management Functions”.

  • keyring_aws_rotate_cmk()

    Associated keyring plugin: keyring_aws

    keyring_aws_rotate_cmk() rotates the customer master key (CMK). Rotation changes only the key that AWS KMS uses for subsequent data key-encryption operations. AWS KMS maintains previous CMK versions, so keys generated using previous CMKs remain decryptable after rotation.

    Rotation changes the CMK value used inside AWS KMS but does not change the ID used to refer to it, so there is no need to change the keyring_aws_cmk_id system variable after calling keyring_aws_rotate_cmk().

    This UDF requires the SUPER privilege.

    Arguments:

    None.

    Return value:

    Returns 1 for success, or NULL and an error for failure.

  • keyring_aws_rotate_keys()

    Associated keyring plugin: keyring_aws

    keyring_aws_rotate_keys() rotates keys stored in the keyring_aws storage file named by the keyring_aws_data_file system variable. Rotation sends each key stored in the file to AWS KMS for re-encryption using the value of the keyring_aws_cmk_id system variable as the CMK value, and stores the new encrypted keys in the file.

    keyring_aws_rotate_keys() is useful for key re-encryption under these circumstances:

    This UDF requires the SUPER privilege.

    Arguments:

    None.

    Return value:

    Returns 1 for success, or NULL and an error for failure.

  • keyring_hashicorp_update_config()

    Associated keyring plugin: keyring_hashicorp

    When invoked, the keyring_hashicorp_update_config() UDF causes keyring_hashicorp to perform a runtime reconfiguration, as described in keyring_hashicorp Configuration.

    This UDF requires the SYSTEM_VARIABLES_ADMIN privilege because it modifies global system variables.

    Arguments:

    None.

    Return value:

    Returns the string 'Configuration update was successful.' for success, or 'Configuration update failed.' for failure.

6.4.4.11 Keyring Command Options

MySQL supports the following keyring-related command-line options:

6.4.4.12 Keyring System Variables

MySQL Keyring plugins support the following system variables. Use them to configure keyring plugin operation. These variables are unavailable unless the appropriate keyring plugin is installed (see Section 6.4.4.1, “Keyring Plugin Installation”).

  • keyring_aws_cmk_id

    PropertyValue
    Command-Line Format --keyring-aws-cmk-id=value
    System Variable keyring_aws_cmk_id
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String

    The customer master key (CMK) ID obtained from the AWS KMS server and used by the keyring_aws plugin. This variable is unavailable unless that plugin is installed, but if it is installed, a value for this variable is mandatory.

  • keyring_aws_conf_file

    PropertyValue
    Command-Line Format --keyring-aws-conf-file=file_name
    System Variable keyring_aws_conf_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type File name
    Default Value platform specific

    The location of the configuration file for the keyring_aws keyring plugin. This variable is unavailable unless that plugin is installed.

    At plugin startup, keyring_aws reads the AWS secret access key ID and key from the configuration file. For the keyring_aws plugin to start successfully, the configuration file must exist and contain valid secret access key information, initialized as described in Section 6.4.4.5, “Using the keyring_aws Amazon Web Services Keyring Plugin”.

    The default file name is keyring_aws_conf, located in the default keyring file directory. The location of this default directory is the same as for the keyring_file_data system variable. See the description of that variable for details, as well as for considerations to take into account if you create the directory manually.

  • keyring_aws_data_file

    PropertyValue
    Command-Line Format --keyring-aws-data-file
    System Variable keyring_aws_data_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type File name
    Default Value platform specific

    The location of the storage file for the keyring_aws keyring plugin. This variable is unavailable unless that plugin is installed.

    At plugin startup, if the value assigned to keyring_aws_data_file specifies a file that does not exist, the keyring_aws plugin attempts to create it (as well as its parent directory, if necessary). If the file does exist, keyring_aws reads any encrypted keys contained in the file into its in-memory cache. keyring_aws does not cache unencrypted keys in memory.

    The default file name is keyring_aws_data, located in the default keyring file directory. The location of this default directory is the same as for the keyring_file_data system variable. See the description of that variable for details, as well as for considerations to take into account if you create the directory manually.

  • keyring_aws_region

    PropertyValue
    Command-Line Format --keyring-aws-region=value
    System Variable keyring_aws_region
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value us-east-1
    Valid Values

    ap-northeast-1

    ap-northeast-2

    ap-south-1

    ap-southeast-1

    ap-southeast-2

    eu-central-1

    eu-west-1

    sa-east-1

    us-east-1

    us-west-1

    us-west-2

    The AWS region.

  • keyring_encrypted_file_data

    PropertyValue
    Command-Line Format --keyring-encrypted-file-data=file_name
    System Variable keyring_encrypted_file_data
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type File name
    Default Value platform specific

    The path name of the data file used for secure data storage by the keyring_encrypted_file plugin. This variable is unavailable unless that plugin is installed. The file location should be in a directory considered for use only by keyring plugins. For example, do not locate the file under the data directory.

    Keyring operations are transactional: The keyring_encrypted_file plugin uses a backup file during write operations to ensure that it can roll back to the original file if an operation fails. The backup file has the same name as the value of the keyring_encrypted_file_data system variable with a suffix of .backup.

    Do not use the same keyring_encrypted_file data file for multiple MySQL instances. Each instance should have its own unique data file.

    The default file name is keyring_encrypted, located in a directory that is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table. To specify the default directory for the file explicitly if you are building from source, use the INSTALL_MYSQLKEYRINGDIR CMake option.

    INSTALL_LAYOUT ValueDefault keyring_encrypted_file_data Value
    DEB, RPM, SVR4 /var/lib/mysql-keyring/keyring_encrypted
    Otherwise keyring/keyring_encrypted under the CMAKE_INSTALL_PREFIX value

    At plugin startup, if the value assigned to keyring_encrypted_file_data specifies a file that does not exist, the keyring_encrypted_file plugin attempts to create it (as well as its parent directory, if necessary).

    If you create the directory manually, it should have a restrictive mode and be accessible only to the account used to run the MySQL server. For example, on Unix and Unix-like systems, to use the /usr/local/mysql/mysql-keyring directory, the following commands (executed as root) create the directory and set its mode and ownership:

    cd /usr/local/mysql
mkdir mysql-keyring
chmod 750 mysql-keyring
chown mysql mysql-keyring
chgrp mysql mysql-keyring

    If the keyring_encrypted_file plugin cannot create or access its data file, it writes an error message to the error log. If an attempted runtime assignment to keyring_encrypted_file_data results in an error, the variable value remains unchanged.

    Important

    Once the keyring_encrypted_file plugin has created its data file and started to use it, it is important not to remove the file. Loss of the file will cause data encrypted using its keys to become inaccessible. (It is permissible to rename or move the file, as long as you change the value of keyring_encrypted_file_data to match.)

  • keyring_encrypted_file_password

    PropertyValue
    Command-Line Format --keyring-encrypted-file-password=password
    System Variable keyring_encrypted_file_password
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String

    The password used by the keyring_encrypted_file plugin. This variable is unavailable unless that plugin is installed. The password is mandatory for plugin operation; if not specified at server startup, keyring_encrypted_file initialization fails.

    If this variable is specified in an option file, the file should have a restrictive mode and be accessible only to the account used to run the MySQL server.

    Important

    Once the keyring_encrypted_file_password value has been set, changing it does not rotate the keyring password and could make the server inaccessible. If an incorrect password is provided, the keyring_encrypted_file plugin cannot load keys from the encrypted keyring file.

    The password value cannot be displayed at runtime with SHOW VARIABLES or the Performance Schema global_variables table because the display value is obfuscated.

  • keyring_file_data

    PropertyValue
    Command-Line Format --keyring-file-data=file_name
    System Variable keyring_file_data
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type File name
    Default Value platform specific

    The path name of the data file used for secure data storage by the keyring_file plugin. This variable is unavailable unless that plugin is installed. The file location should be in a directory considered for use only by keyring plugins. For example, do not locate the file under the data directory.

    Keyring operations are transactional: The keyring_file plugin uses a backup file during write operations to ensure that it can roll back to the original file if an operation fails. The backup file has the same name as the value of the keyring_file_data system variable with a suffix of .backup.

    Do not use the same keyring_file data file for multiple MySQL instances. Each instance should have its own unique data file.

    The default file name is keyring, located in a directory that is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table. To specify the default directory for the file explicitly if you are building from source, use the INSTALL_MYSQLKEYRINGDIR CMake option.

    INSTALL_LAYOUT ValueDefault keyring_file_data Value
    DEB, RPM, SVR4 /var/lib/mysql-keyring/keyring
    Otherwise keyring/keyring under the CMAKE_INSTALL_PREFIX value

    At plugin startup, if the value assigned to keyring_file_data specifies a file that does not exist, the keyring_file plugin attempts to create it (as well as its parent directory, if necessary).

    If you create the directory manually, it should have a restrictive mode and be accessible only to the account used to run the MySQL server. For example, on Unix and Unix-like systems, to use the /usr/local/mysql/mysql-keyring directory, the following commands (executed as root) create the directory and set its mode and ownership:

    cd /usr/local/mysql
mkdir mysql-keyring
chmod 750 mysql-keyring
chown mysql mysql-keyring
chgrp mysql mysql-keyring

    If the keyring_file plugin cannot create or access its data file, it writes an error message to the error log. If an attempted runtime assignment to keyring_file_data results in an error, the variable value remains unchanged.

    Important

    Once the keyring_file plugin has created its data file and started to use it, it is important not to remove the file. For example, InnoDB uses the file to store the master key used to decrypt the data in tables that use InnoDB tablespace encryption; see Section 15.13, “InnoDB Data-at-Rest Encryption”. Loss of the file will cause data in such tables to become inaccessible. (It is permissible to rename or move the file, as long as you change the value of keyring_file_data to match.) It is recommended that you create a separate backup of the keyring data file immediately after you create the first encrypted table and before and after master key rotation.

  • keyring_hashicorp_auth_path

    PropertyValue
    Command-Line Format --keyring-hashicorp-auth-path=value
    Introduced 8.0.18
    System Variable keyring_hashicorp_auth_path
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value /v1/auth/approle/login

    The authentication path where AppRole authentication is enabled within the HashiCorp Vault server.

  • keyring_hashicorp_ca_path

    PropertyValue
    Command-Line Format --keyring-hashicorp-ca-path=file_name
    Introduced 8.0.18
    System Variable keyring_hashicorp_ca_path
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type File name
    Default Value empty string

    The absolute path name of a local file accessible to the MySQL server that contains a properly formatted TLS certificate authority.

    If this variable is not set, the keyring_hashicorp plugin opens an HTTPS connection without using server certificate verification, and trusts any certificate delivered by the HashiCorp Vault server. For this to be safe, it must be assumed that the Vault server is not malicious and that no man-in-the-middle attack is possible. If those assumptions are invalid, set keyring_hashicorp_ca_path to the path of a trusted CA certificate. (For example, for the instructions in Certificate and Key Preparation, this is the company.crt file.)

  • keyring_hashicorp_caching

    PropertyValue
    Command-Line Format --keyring-hashicorp-caching[={OFF|ON}]
    Introduced 8.0.18
    System Variable keyring_hashicorp_caching
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Whether to enable the optional in-memory key cache used by the keyring_hashicorp plugin to cache keys from the HashiCorp Vault server. If the cache is enabled, the plugin populates it during initialization. Otherwise, the plugin populates only the key list during initialization.

    Enabling the cache is a compromise: It improves performance, but maintains a copy of sensitive key information in memory, which may be undesirable for security purposes.

  • keyring_hashicorp_commit_auth_path

    PropertyValue
    Introduced 8.0.18
    System Variable keyring_hashicorp_commit_auth_path
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String

    This variable is associated with keyring_hashicorp_auth_path, from which it takes its value during keyring_hashicorp plugin initialization. It reflects the “committed” value actually used for plugin operation if initialization succeeds. For additional information, see keyring_hashicorp Configuration.

  • keyring_hashicorp_commit_ca_path

    PropertyValue
    Introduced 8.0.18
    System Variable keyring_hashicorp_commit_ca_path
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String

    This variable is associated with keyring_hashicorp_ca_path. See the description of the keyring_hashicorp_commit_auth_path system variable for information about _commit_ variables.

  • keyring_hashicorp_commit_caching

    PropertyValue
    Introduced 8.0.18
    System Variable keyring_hashicorp_commit_caching
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String

    This variable is associated with keyring_hashicorp_caching. See the description of the keyring_hashicorp_commit_auth_path system variable for information about _commit_ variables.

  • keyring_hashicorp_commit_role_id

    PropertyValue
    Introduced 8.0.18
    System Variable keyring_hashicorp_commit_role_id
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String

    This variable is associated with keyring_hashicorp_role_id. See the description of the keyring_hashicorp_commit_auth_path system variable for information about _commit_ variables.

  • keyring_hashicorp_commit_server_url

    PropertyValue
    Introduced 8.0.18
    System Variable keyring_hashicorp_commit_server_url
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String

    This variable is associated with keyring_hashicorp_server_url. See the description of the keyring_hashicorp_commit_auth_path system variable for information about _commit_ variables.

  • keyring_hashicorp_commit_store_path

    PropertyValue
    Introduced 8.0.18
    System Variable keyring_hashicorp_commit_store_path
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String

    This variable is associated with keyring_hashicorp_store_path. See the description of the keyring_hashicorp_commit_auth_path system variable for information about _commit_ variables.

  • keyring_hashicorp_role_id

    PropertyValue
    Command-Line Format --keyring-hashicorp-role-id=value
    Introduced 8.0.18
    System Variable keyring_hashicorp_role_id
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value empty string

    The HashiCorp Vault AppRole authentication role ID. The value must be in UUID format.

    This variable is mandatory. If not specified, keyring_hashicorp initialization fails.

  • keyring_hashicorp_secret_id

    PropertyValue
    Command-Line Format --keyring-hashicorp-secret-id=value
    Introduced 8.0.18
    System Variable keyring_hashicorp_secret_id
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value empty string

    The HashiCorp Vault AppRole authentication secret ID. The value must be in UUID format.

    This variable is mandatory. If not specified, keyring_hashicorp initialization fails.

    The value of this variable is sensitive, so its value is masked by * characters when displayed.

  • keyring_hashicorp_server_url

    PropertyValue
    Command-Line Format --keyring-hashicorp-server-url=value
    Introduced 8.0.18
    System Variable keyring_hashicorp_server_url
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value https://127.0.0.1:8200

    The HashiCorp Vault server URL. The value must begin with https://.

  • keyring_hashicorp_store_path

    PropertyValue
    Command-Line Format --keyring-hashicorp-store-path=value
    Introduced 8.0.18
    System Variable keyring_hashicorp_store_path
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value empty string

    A store path within the HashiCorp Vault server that is writeable when appropiate AppRole AppRole credentials are provided by keyring_hashicorp. To specify the credentials, set the keyring_hashicorp_role_id and keyring_hashicorp_secret_id system variables (for example, as shown in keyring_hashicorp Configuration).

    This variable is mandatory. If not specified, keyring_hashicorp initialization fails.

  • keyring_okv_conf_dir

    PropertyValue
    Command-Line Format --keyring-okv-conf-dir=dir_name
    System Variable keyring_okv_conf_dir
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Directory name
    Default Value empty string

    The path name of the directory that stores configuration information used by the keyring_okv plugin. This variable is unavailable unless that plugin is installed. The location should be a directory considered for use only by the keyring_okv plugin. For example, do not locate the directory under the data directory.

    The default keyring_okv_conf_dir value is empty. For the keyring_okv plugin to be able to access Oracle Key Vault, the value must be set to a directory that contains Oracle Key Vault configuration and SSL materials. For instructions on setting up this directory, see Section 6.4.4.4, “Using the keyring_okv KMIP Plugin”.

    The directory should have a restrictive mode and be accessible only to the account used to run the MySQL server. For example, on Unix and Unix-like systems, to use the /usr/local/mysql/mysql-keyring-okv directory, the following commands (executed as root) create the directory and set its mode and ownership:

    cd /usr/local/mysql
mkdir mysql-keyring-okv
chmod 750 mysql-keyring-okv
chown mysql mysql-keyring-okv
chgrp mysql mysql-keyring-okv

    If the value assigned to keyring_okv_conf_dir specifies a directory that does not exist, or that does not contain configuration information that enables a connection to Oracle Key Vault to be established, keyring_okv writes an error message to the error log. If an attempted runtime assignment to keyring_okv_conf_dir results in an error, the variable value and keyring operation remain unchanged.

  • keyring_operations

    PropertyValue
    System Variable keyring_operations
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Whether keyring operations are enabled. This variable is used during key migration operations. See Section 6.4.4.7, “Migrating Keys Between Keyring Keystores”. The privileges required to modify this variable are ENCRYPTION_KEY_ADMIN in addition to either SYSTEM_VARIABLES_ADMIN or SUPER.

6.4.5 MySQL Enterprise Audit

6.4.5.1 Audit Log Components
6.4.5.2 Installing or Uninstalling MySQL Enterprise Audit
6.4.5.3 MySQL Enterprise Audit Security Considerations
6.4.5.4 Audit Log File Formats
6.4.5.5 Audit Log Logging Configuration
6.4.5.6 Audit Log Filtering
6.4.5.7 Writing Audit Log Filter Definitions
6.4.5.8 Legacy Mode Audit Log Filtering
6.4.5.9 Audit Log Reference
6.4.5.10 Audit Log Restrictions
Note

MySQL Enterprise Audit is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

MySQL Enterprise Edition includes MySQL Enterprise Audit, implemented using a server plugin named audit_log. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring, logging, and blocking of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines.

When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.

After you install the audit plugin (see Section 6.4.5.2, “Installing or Uninstalling MySQL Enterprise Audit”), it writes an audit log file. By default, the file is named audit.log in the server data directory. To change the name of the file, set the audit_log_file system variable at server startup.

By default, audit log file contents are written in new-style XML format, without compression or encryption. To select the file format, set the audit_log_format system variable at server startup. For details on file format and contents, see Section 6.4.5.4, “Audit Log File Formats”.

For more information about controlling how logging occurs, including audit log file naming and format selection, see Section 6.4.5.5, “Audit Log Logging Configuration”. To perform filtering of audited events, see Section 6.4.5.6, “Audit Log Filtering”. For descriptions of the parameters used to configure the audit log plugin, see Audit Log Options and Variables.

If the audit log plugin is enabled, the Performance Schema (see Chapter 26, MySQL Performance Schema) has instrumentation for it. To identify the relevant instruments, use this query:

SELECT NAME FROM performance_schema.setup_instruments
WHERE NAME LIKE '%/alog/%';

6.4.5.1 Audit Log Components

MySQL Enterprise Audit is based on the audit log plugin and related components:

  • A server-side plugin named audit_log examines auditable events and determines whether to write them to the audit log.

  • User-defined functions enable manipulation of filtering definitions that control logging behavior, the encryption password, and log file reading.

  • Tables in the mysql system database provide persistent storage of filter and user account data.

  • System variables enable audit log configuration and status variables provide runtime operational information.

  • An AUDIT_ADMIN privilege enable users to administer the audit log.

6.4.5.2 Installing or Uninstalling MySQL Enterprise Audit

This section describes how to install or uninstall MySQL Enterprise Audit, which is implemented using the audit log plugin and related components described in Section 6.4.5.1, “Audit Log Components”. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

Important

Read this entire section before following its instructions. Parts of the procedure differ depending on your environment.

Note

If installed, the audit_log plugin involves some minimal overhead even when disabled. To avoid this overhead, do not install MySQL Enterprise Audit unless you plan to use it.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

To install MySQL Enterprise Audit, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform. The available scripts differ in the suffix used to refer to the plugin library file:

  • audit_log_filter_win_install.sql: Choose this script for Windows systems that use .dll as the file name suffix.

  • audit_log_filter_linux_install.sql: Choose this script for Linux and similar systems that use .so as the file name suffix.

Run the script as follows. The example here uses the Linux installation script. Make the appropriate substitution for your system.

mysql -u root -p < audit_log_filter_linux_install.sql
(enter root password here)
Note

Some MySQL versions have introduced changes to the structure of the MySQL Enterprise Audit tables. To ensure that your tables are up to date for upgrades from earlier versions of MySQL 8.0, perform the MySQL upgrade procedure, making sure to use the option that forces an update (see Section 2.11, “Upgrading MySQL”). If you prefer to run the update statements only for the MySQL Enterprise Audit tables, see the following discussion.

As of MySQL 8.0.12, for new MySQL installations, the USER and HOST columns in the audit_log_user table used by MySQL Enterprise Audit have definitions that better correspond to the definitions of the User and Host columns in the mysql.user system table. For upgrades to an installation for which MySQL Enterprise Audit is already installed, it is recommended that you alter the table definitions as follows:

ALTER TABLE mysql.audit_log_user
  DROP FOREIGN KEY audit_log_user_ibfk_1;
ALTER TABLE mysql.audit_log_filter
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;
ALTER TABLE mysql.audit_log_user
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;
ALTER TABLE mysql.audit_log_user
  MODIFY COLUMN USER VARCHAR(32);
ALTER TABLE mysql.audit_log_user
ADD FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME);
Note

To use MySQL Enterprise Audit in the context of master/slave replication, Group Replication, or InnoDB cluster, you must prepare the slave or secondary nodes prior to running the installation script on the master or primary node. This is necessary because the INSTALL PLUGIN statement in the script is not replicated.

  1. On each slave or secondary node, extract the INSTALL PLUGIN statement from the installation script and execute it manually.

  2. On the master or primary node, run the installation script as described previously.

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'audit%';

If the plugin fails to initialize, check the server error log for diagnostic messages.

After MySQL Enterprise Audit is installed, you can use the --audit-log option for subsequent server startups to control audit_log plugin activation. For example, to prevent the plugin from being removed at runtime, use this option:

[mysqld]
audit-log=FORCE_PLUS_PERMANENT

If it is desired to prevent the server from running without the audit plugin, use --audit-log with a value of FORCE or FORCE_PLUS_PERMANENT to force server startup to fail if the plugin does not initialize successfully.

Important

By default, rule-based audit log filtering logs no auditable events for any users. This differs from legacy audit log behavior, which logs all auditable events for all users (see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”). Should you wish to produce log-everything behavior with rule-based filtering, create a simple filter to enable logging and assign it to the default account:

SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('%', 'log_all');

The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).

Once installed as just described, MySQL Enterprise Audit remains installed until uninstalled. To remove it, execute the following statements:

DROP TABLE IF EXISTS mysql.audit_log_user;
DROP TABLE IF EXISTS mysql.audit_log_filter;
UNINSTALL PLUGIN audit_log;
DROP FUNCTION audit_log_filter_set_filter;
DROP FUNCTION audit_log_filter_remove_filter;
DROP FUNCTION audit_log_filter_set_user;
DROP FUNCTION audit_log_filter_remove_user;
DROP FUNCTION audit_log_filter_flush;
DROP FUNCTION audit_log_encryption_password_get;
DROP FUNCTION audit_log_encryption_password_set;
DROP FUNCTION audit_log_read;
DROP FUNCTION audit_log_read_bookmark;

6.4.5.3 MySQL Enterprise Audit Security Considerations

By default, contents of audit log files produced by the audit log plugin are not encrypted and may contain sensitive information, such as the text of SQL statements. For security reasons, audit log files should be written to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log. The default file name is audit.log in the data directory. This can be changed by setting the audit_log_file system variable at server startup. Other audit log files may exist due to log rotation.

For additional security, enable audit log file encryption. See Audit Log File Encryption.

6.4.5.4 Audit Log File Formats

The MySQL server calls the audit log plugin to write an audit record to its log file whenever an auditable event occurs. Typically the first audit record written after plugin startup contains the server description and startup options. Elements following that one represent events such as client connect and disconnect events, executed SQL statements, and so forth. Only top-level statements are logged, not statements within stored programs such as triggers or stored procedures. Contents of files referenced by statements such as LOAD DATA are not logged.

To select the log format that the audit log plugin uses to write its log file, set the audit_log_format system variable at server startup. These formats are available:

  • New-style XML format (audit_log_format=NEW): An XML format that has better compatibility with Oracle Audit Vault than old-style XML format. MySQL 8.0 uses new-style XML format by default.

  • Old-style XML format (audit_log_format=OLD): The original audit log format used by default in older MySQL series.

  • JSON format (audit_log_format=JSON)

By default, audit log file contents are written in new-style XML format, without compression or encryption.

Note

For information about issues to consider when changing the log format, see Audit Log File Format.

The following sections describe the available audit logging formats:

New-Style XML Audit Log File Format

Here is a sample log file in new-style XML format (audit_log_format=NEW), reformatted slightly for readability:

<?xml version="1.0" encoding="utf-8"?>
<AUDIT>
 <AUDIT_RECORD>
  <TIMESTAMP>2019-10-03T14:06:33 UTC</TIMESTAMP>
  <RECORD_ID>1_2019-10-03T14:06:33</RECORD_ID>
  <NAME>Audit</NAME>
  <SERVER_ID>1</SERVER_ID>
  <VERSION>1</VERSION>
  <STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld
    --socket=/usr/local/mysql/mysql.sock
    --port=3306</STARTUP_OPTIONS>
  <OS_VERSION>i686-Linux</OS_VERSION>
  <MYSQL_VERSION>5.7.21-log</MYSQL_VERSION>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2019-10-03T14:09:38 UTC</TIMESTAMP>
  <RECORD_ID>2_2019-10-03T14:06:33</RECORD_ID>
  <NAME>Connect</NAME>
  <CONNECTION_ID>5</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root</USER>
  <OS_LOGIN/>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
  <CONNECTION_TYPE>SSL/TLS</CONNECTION_TYPE>
  <CONNECTION_ATTRIBUTES>
   <ATTRIBUTE>
    <NAME>_pid</NAME>
    <VALUE>42794</VALUE>
   </ATTRIBUTE>
   ...
   <ATTRIBUTE>
    <NAME>program_name</NAME>
    <VALUE>mysqladmin</VALUE>
   </ATTRIBUTE>
  </CONNECTION_ATTRIBUTES>
  <PRIV_USER>root</PRIV_USER>
  <PROXY_USER/>
  <DB>test</DB>
 </AUDIT_RECORD>

...

 <AUDIT_RECORD>
  <TIMESTAMP>2019-10-03T14:09:38 UTC</TIMESTAMP>
  <RECORD_ID>6_2019-10-03T14:06:33</RECORD_ID>
  <NAME>Query</NAME>
  <CONNECTION_ID>5</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root[root] @ localhost [127.0.0.1]</USER>
  <OS_LOGIN/>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS>drop_table</COMMAND_CLASS>
  <SQLTEXT>DROP TABLE IF EXISTS t</SQLTEXT>
 </AUDIT_RECORD>

...

 <AUDIT_RECORD>
  <TIMESTAMP>2019-10-03T14:09:39 UTC</TIMESTAMP>
  <RECORD_ID>8_2019-10-03T14:06:33</RECORD_ID>
  <NAME>Quit</NAME>
  <CONNECTION_ID>5</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root</USER>
  <OS_LOGIN/>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
  <CONNECTION_TYPE>SSL/TLS</CONNECTION_TYPE>
 </AUDIT_RECORD>

...

 <AUDIT_RECORD>
  <TIMESTAMP>2019-10-03T14:09:43 UTC</TIMESTAMP>
  <RECORD_ID>11_2019-10-03T14:06:33</RECORD_ID>
  <NAME>Quit</NAME>
  <CONNECTION_ID>6</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root</USER>
  <OS_LOGIN/>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
  <CONNECTION_TYPE>SSL/TLS</CONNECTION_TYPE>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2019-10-03T14:09:45 UTC</TIMESTAMP>
  <RECORD_ID>12_2019-10-03T14:06:33</RECORD_ID>
  <NAME>NoAudit</NAME>
  <SERVER_ID>1</SERVER_ID>
 </AUDIT_RECORD>
</AUDIT>

The audit log file is written as XML, using UTF-8 (up to 4 bytes per character). The root element is <AUDIT>. The root element contains <AUDIT_RECORD> elements, each of which provides information about an audited event. When the audit log plugin begins writing a new log file, it writes the XML declaration and opening <AUDIT> root element tag. When the plugin closes a log file, it writes the closing </AUDIT> root element tag. The closing tag is not present while the file is open.

Elements within <AUDIT_RECORD> elements have these characteristics:

  • Some elements appear in every <AUDIT_RECORD> element. Others are optional and may appear depending on the audit record type.

  • Order of elements within an <AUDIT_RECORD> element is not guaranteed.

  • Element values are not fixed length. Long values may be truncated as indicated in the element descriptions given later.

  • The <, >, ", and & characters are encoded as &lt;, &gt;, &quot;, and &amp;, respectively. NUL bytes (U+00) are encoded as the ? character.

  • Characters not valid as XML characters are encoded using numeric character references. Valid XML characters are:

    #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]

The following elements are mandatory in every <AUDIT_RECORD> element:

  • <NAME>

    A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.

    Example:

    <NAME>Query</NAME>

    Some common <NAME> values:

    Audit    When auditing starts, which may be server startup time
Connect  When a client connects, also known as logging in
Query    An SQL statement (executed directly)
Prepare  Preparation of an SQL statement; usually followed by Execute
Execute  Execution of an SQL statement; usually follows Prepare
Shutdown Server shutdown
Quit     When a client disconnects
NoAudit  Auditing has been turned off

    The possible values are Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, TableDelete, TableInsert, TableRead, TableUpdate, Time.

    Many of these values correspond to the COM_xxx command values listed in the my_command.h header file. For example, Create DB and Change user correspond to COM_CREATE_DB and COM_CHANGE_USER, respectively.

    Events having <NAME> values of TableXXX accompany Query events. For example, the following statement generates one Query event, two TableRead events, and a TableInsert events:

    INSERT INTO t3 SELECT t1.* FROM t1 JOIN t2;

    Each TableXXX event contains <DB> and <TABLE> elements to identify the table to which the event refers.

  • <RECORD_ID>

    A unique identifier for the audit record. The value is composed from a sequence number and timestamp, in the format SEQ_TIMESTAMP. When the audit log plugin opens the audit log file, it initializes the sequence number to the size of the audit log file, then increments the sequence by 1 for each record logged. The timestamp is a UTC value in YYYY-MM-DDThh:mm:ss format indicating the date and time when the audit log plugin opened the file.

    Example:

    <RECORD_ID>12_2019-10-03T14:06:33</RECORD_ID>

  • <TIMESTAMP>

    A string representing a UTC value in YYYY-MM-DDThh:mm:ss UTC format indicating the date and time when the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a <TIMESTAMP> value occurring after the statement finishes, not when it was received.

    Example:

    <TIMESTAMP>2019-10-03T14:09:45 UTC</TIMESTAMP>

The following elements are optional in <AUDIT_RECORD> elements. Many of them occur only with specific <NAME> element values.

  • <COMMAND_CLASS>

    A string that indicates the type of action performed.

    Example:

    <COMMAND_CLASS>drop_table</COMMAND_CLASS>

    The values correspond to the statement/sql/xxx command counters. For example, xxx is drop_table and select for DROP TABLE and SELECT statements, respectively. The following statement displays the possible names:

    SELECT REPLACE(EVENT_NAME, 'statement/sql/', '') AS name
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%'
ORDER BY name;

  • <CONNECTION_ATTRIBUTES>

    As of MySQL 8.0.19, events with a <COMMAND_CLASS> value of connect may include a <CONNECTION_ATTRIBUTES> element to display the connection attributes passed by the client at connect time. (For information about these attributes, which are also exposed in Performance Schema tables, see Section 26.12.9, “Performance Schema Connection Attribute Tables”.)

    The <CONNECTION_ATTRIBUTES> element contains one <ATTRIBUTE> element per attribute, each of which contains <NAME> and <VALUE> elements to indicate the attribute name and value, respectively.

    Example:

    <CONNECTION_ATTRIBUTES>
 <ATTRIBUTE>
  <NAME>_pid</NAME>
  <VALUE>42794</VALUE>
 </ATTRIBUTE>
 <ATTRIBUTE>
  <NAME>_os</NAME>
  <VALUE>osx10.14</VALUE>
 </ATTRIBUTE>
 <ATTRIBUTE>
  <NAME>_platform</NAME>
  <VALUE>x86_64</VALUE>
 </ATTRIBUTE>
 <ATTRIBUTE>
  <NAME>_client_version</NAME>
  <VALUE>8.0.19</VALUE>
 </ATTRIBUTE>
 <ATTRIBUTE>
  <NAME>_client_name</NAME>
  <VALUE>libmysql</VALUE>
 </ATTRIBUTE>
 <ATTRIBUTE>
  <NAME>program_name</NAME>
  <VALUE>mysqladmin</VALUE>
 </ATTRIBUTE>
</CONNECTION_ATTRIBUTES>

    If no connection attributes are present in the event, none are logged and no <CONNECTION_ATTRIBUTES> element appears. This can occur if the connection attempt is unsucessful, the client passes no attributes, or the connection occurs internally such as during server startup or when initiated by a plugin.

  • <CONNECTION_ID>

    An unsigned integer representing the client connection identifier. This is the same as the value returned by the CONNECTION_ID() function within the session.

    Example:

    <CONNECTION_ID>127</CONNECTION_ID>

  • <CONNECTION_TYPE>

    The security state of the connection to the server. Permitted values are TCP/IP (TCP/IP connection established without encryption), SSL/TLS (TCP/IP connection established with encryption), Socket (Unix socket file connection), Named Pipe (Windows named pipe connection), and Shared Memory (Windows shared memory connection).

    Example:

    <CONNECTION_TYPE>SSL/TLS</CONNECTION_TYPE>

  • <DB>

    A string representing the default database name.

    Example:

    <DB>test</DB>

  • <HOST>

    A string representing the client host name.

    Example:

    <HOST>localhost</HOST>

  • <IP>

    A string representing the client IP address.

    Example:

    <IP>127.0.0.1</IP>

  • <MYSQL_VERSION>

    A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable.

    Example:

    <MYSQL_VERSION>5.7.21-log</MYSQL_VERSION>

  • <OS_LOGIN>

    A string representing the external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this element is empty. The value is the same as that of the external_user system variable (see Section 6.2.18, “Proxy Users”).

    Example:

    <OS_LOGIN>jeffrey</OS_LOGIN>

  • <OS_VERSION>

    A string representing the operating system on which the server was built or is running.

    Example:

    <OS_VERSION>x86_64-Linux</OS_VERSION>

  • <PRIV_USER>

    A string representing the user that the server authenticated the client as. This is the user name that the server uses for privilege checking, and may differ from the <USER> value.

    Example:

    <PRIV_USER>jeffrey</PRIV_USER>

  • <PROXY_USER>

    A string representing the proxy user (see Section 6.2.18, “Proxy Users”). The value is empty if user proxying is not in effect.

    Example:

    <PROXY_USER>developer</PROXY_USER>

  • <SERVER_ID>

    An unsigned integer representing the server ID. This is the same as the value of the server_id system variable.

    Example:

    <SERVER_ID>1</SERVER_ID>

  • <SQLTEXT>

    A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.

    Example:

    <SQLTEXT>DELETE FROM t1</SQLTEXT>

  • <STARTUP_OPTIONS>

    A string representing the options that were given on the command line or in option files when the MySQL server was started. The first option is the path to the server executable.

    Example:

    <STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld
  --port=3306 --log_output=FILE</STARTUP_OPTIONS>

  • <STATUS>

    An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function. See the description for <STATUS_CODE> for information about how it differs from <STATUS>.

    The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3.1, “Server Error Message Reference”.

    Warnings are not logged.

    Example:

    <STATUS>1051</STATUS>

  • <STATUS_CODE>

    An unsigned integer representing the command status: 0 for success, 1 if an error occurred.

    The STATUS_CODE value differs from the STATUS value: STATUS_CODE is 0 for success and 1 for error, which is compatible with the EZ_collector consumer for Audit Vault. STATUS is the value of the mysql_errno() C API function. This is 0 for success and nonzero for error, and thus is not necessarily 1 for error.

    Example:

    <STATUS_CODE>0</STATUS_CODE>

  • <TABLE>

    A string representing a table name.

    Example:

    <TABLE>t3</TABLE>

  • <USER>

    A string representing the user name sent by the client. This may differ from the <PRIV_USER> value.

    Example:

    <USER>root[root] @ localhost [127.0.0.1]</USER>

  • <VERSION>

    An unsigned integer representing the version of the audit log file format.

    Example:

    <VERSION>1</VERSION>
Old-Style XML Audit Log File Format

Here is a sample log file in old-style XML format (audit_log_format=OLD), reformatted slightly for readability:

<?xml version="1.0" encoding="utf-8"?>
<AUDIT>
  <AUDIT_RECORD
    TIMESTAMP="2019-10-03T14:25:00 UTC"
    RECORD_ID="1_2019-10-03T14:25:00"
    NAME="Audit"
    SERVER_ID="1"
    VERSION="1"
    STARTUP_OPTIONS="--port=3306"
    OS_VERSION="i686-Linux"
    MYSQL_VERSION="5.7.21-log"/>
  <AUDIT_RECORD
    TIMESTAMP="2019-10-03T14:25:24 UTC"
    RECORD_ID="2_2019-10-03T14:25:00"
    NAME="Connect"
    CONNECTION_ID="4"
    STATUS="0"
    STATUS_CODE="0"
    USER="root"
    OS_LOGIN=""
    HOST="localhost"
    IP="127.0.0.1"
    COMMAND_CLASS="connect"
    CONNECTION_TYPE="SSL/TLS"
    PRIV_USER="root"
    PROXY_USER=""
    DB="test"/>

...

  <AUDIT_RECORD
    TIMESTAMP="2019-10-03T14:25:24 UTC"
    RECORD_ID="6_2019-10-03T14:25:00"
    NAME="Query"
    CONNECTION_ID="4"
    STATUS="0"
    STATUS_CODE="0"
    USER="root[root] @ localhost [127.0.0.1]"
    OS_LOGIN=""
    HOST="localhost"
    IP="127.0.0.1"
    COMMAND_CLASS="drop_table"
    SQLTEXT="DROP TABLE IF EXISTS t"/>

...

  <AUDIT_RECORD
    TIMESTAMP="2019-10-03T14:25:24 UTC"
    RECORD_ID="8_2019-10-03T14:25:00"
    NAME="Quit"
    CONNECTION_ID="4"
    STATUS="0"
    STATUS_CODE="0"
    USER="root"
    OS_LOGIN=""
    HOST="localhost"
    IP="127.0.0.1"
    COMMAND_CLASS="connect"
    CONNECTION_TYPE="SSL/TLS"/>
  <AUDIT_RECORD
    TIMESTAMP="2019-10-03T14:25:32 UTC"
    RECORD_ID="12_2019-10-03T14:25:00"
    NAME="NoAudit"
    SERVER_ID="1"/>
</AUDIT>

The audit log file is written as XML, using UTF-8 (up to 4 bytes per character). The root element is <AUDIT>. The root element contains <AUDIT_RECORD> elements, each of which provides information about an audited event. When the audit log plugin begins writing a new log file, it writes the XML declaration and opening <AUDIT> root element tag. When the plugin closes a log file, it writes the closing </AUDIT> root element tag. The closing tag is not present while the file is open.

Attributes of <AUDIT_RECORD> elements have these characteristics:

  • Some attributes appear in every <AUDIT_RECORD> element. Others are optional and may appear depending on the audit record type.

  • Order of attributes within an <AUDIT_RECORD> element is not guaranteed.

  • Attribute values are not fixed length. Long values may be truncated as indicated in the attribute descriptions given later.

  • The <, >, ", and & characters are encoded as &lt;, &gt;, &quot;, and &amp;, respectively. NUL bytes (U+00) are encoded as the ? character.

  • Characters not valid as XML characters are encoded using numeric character references. Valid XML characters are:

    #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]

The following attributes are mandatory in every <AUDIT_RECORD> element:

  • NAME

    A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.

    Example: NAME="Query"

    Some common NAME values:

    Audit    When auditing starts, which may be server startup time
Connect  When a client connects, also known as logging in
Query    An SQL statement (executed directly)
Prepare  Preparation of an SQL statement; usually followed by Execute
Execute  Execution of an SQL statement; usually follows Prepare
Shutdown Server shutdown
Quit     When a client disconnects
NoAudit  Auditing has been turned off

    The possible values are Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, TableDelete, TableInsert, TableRead, TableUpdate, Time.

    Many of these values correspond to the COM_xxx command values listed in the my_command.h header file. For example, "Create DB" and "Change user" correspond to COM_CREATE_DB and COM_CHANGE_USER, respectively.

    Events having NAME values of TableXXX accompany Query events. For example, the following statement generates one Query event, two TableRead events, and a TableInsert events:

    INSERT INTO t3 SELECT t1.* FROM t1 JOIN t2;

    Each TableXXX event has DB and TABLE attributes to identify the table to which the event refers.

    Connect events for old-style XML audit log format do not include connection attributes.

  • RECORD_ID

    A unique identifier for the audit record. The value is composed from a sequence number and timestamp, in the format SEQ_TIMESTAMP. When the audit log plugin opens the audit log file, it initializes the sequence number to the size of the audit log file, then increments the sequence by 1 for each record logged. The timestamp is a UTC value in YYYY-MM-DDThh:mm:ss format indicating the date and time when the audit log plugin opened the file.

    Example: RECORD_ID="12_2019-10-03T14:25:00"

  • TIMESTAMP

    A string representing a UTC value in YYYY-MM-DDThh:mm:ss UTC format indicating the date and time when the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a TIMESTAMP value occurring after the statement finishes, not when it was received.

    Example: TIMESTAMP="2019-10-03T14:25:32 UTC"

The following attributes are optional in <AUDIT_RECORD> elements. Many of them occur only for elements with specific values of the NAME attribute.

  • COMMAND_CLASS

    A string that indicates the type of action performed.

    Example: COMMAND_CLASS="drop_table"

    The values correspond to the statement/sql/xxx command counters. For example, xxx is drop_table and select for DROP TABLE and SELECT statements, respectively. The following statement displays the possible names:

    SELECT REPLACE(EVENT_NAME, 'statement/sql/', '') AS name
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%'
ORDER BY name;

  • CONNECTION_ID

    An unsigned integer representing the client connection identifier. This is the same as the value returned by the CONNECTION_ID() function within the session.

    Example: CONNECTION_ID="127"

  • CONNECTION_TYPE

    The security state of the connection to the server. Permitted values are TCP/IP (TCP/IP connection established without encryption), SSL/TLS (TCP/IP connection established with encryption), Socket (Unix socket file connection), Named Pipe (Windows named pipe connection), and Shared Memory (Windows shared memory connection).

    Example: CONNECTION_TYPE="SSL/TLS"

  • DB

    A string representing the default database name.

    Example: DB="test"

  • HOST

    A string representing the client host name.

    Example: HOST="localhost"

  • IP

    A string representing the client IP address.

    Example: IP="127.0.0.1"

  • MYSQL_VERSION

    A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable.

    Example: MYSQL_VERSION="5.7.21-log"

  • OS_LOGIN

    A string representing the external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this attribute is empty. The value is the same as that of the external_user system variable (see Section 6.2.18, “Proxy Users”).

    Example: OS_LOGIN="jeffrey"

  • OS_VERSION

    A string representing the operating system on which the server was built or is running.

    Example: OS_VERSION="x86_64-Linux"

  • PRIV_USER

    A string representing the user that the server authenticated the client as. This is the user name that the server uses for privilege checking, and it may differ from the USER value.

    Example: PRIV_USER="jeffrey"

  • PROXY_USER

    A string representing the proxy user (see Section 6.2.18, “Proxy Users”). The value is empty if user proxying is not in effect.

    Example: PROXY_USER="developer"

  • SERVER_ID

    An unsigned integer representing the server ID. This is the same as the value of the server_id system variable.

    Example: SERVER_ID="1"

  • SQLTEXT

    A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.

    Example: SQLTEXT="DELETE FROM t1"

  • STARTUP_OPTIONS

    A string representing the options that were given on the command line or in option files when the MySQL server was started.

    Example: STARTUP_OPTIONS="--port=3306 --log_output=FILE"

  • STATUS

    An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function. See the description for STATUS_CODE for information about how it differs from STATUS.

    The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3.1, “Server Error Message Reference”.

    Warnings are not logged.

    Example: STATUS="1051"

  • STATUS_CODE

    An unsigned integer representing the command status: 0 for success, 1 if an error occurred.

    The STATUS_CODE value differs from the STATUS value: STATUS_CODE is 0 for success and 1 for error, which is compatible with the EZ_collector consumer for Audit Vault. STATUS is the value of the mysql_errno() C API function. This is 0 for success and nonzero for error, and thus is not necessarily 1 for error.

    Example: STATUS_CODE="0"

  • TABLE

    A string representing a table name.

    Example: TABLE="t3"

  • USER

    A string representing the user name sent by the client. This may differ from the PRIV_USER value.

  • VERSION

    An unsigned integer representing the version of the audit log file format.

    Example: VERSION="1"

JSON Audit Log File Format

For JSON-format audit logging (audit_log_format=JSON), the log file contents form a JSON array with each array element representing an audited event as a JSON hash of key-value pairs. Examples of complete event records appear later in this section. The following is an excerpt of partial events:

[
  {
    "timestamp": "2019-10-03 13:50:01",
    "id": 0,
    "class": "audit",
    "event": "startup",
    ...
  },
  {
    "timestamp": "2019-10-03 15:02:32",
    "id": 0,
    "class": "connection",
    "event": "connect",
    ...
  },
  ...
  {
    "timestamp": "2019-10-03 17:37:26",
    "id": 0,
    "class": "table_access",
    "event": "insert",
      ...
  }
  ...
]

The audit log file is written using UTF-8 (up to 4 bytes per character). When the audit log plugin begins writing a new log file, it writes the opening [ array marker. When the plugin closes a log file, it writes the closing ] array marker. The closing marker is not present while the file is open.

Items within audit records have these characteristics:

  • Some items appear in every audit record. Others are optional and may appear depending on the audit record type.

  • Order of items within an audit record is not guaranteed.

  • Item values are not fixed length. Long values may be truncated as indicated in the item descriptions given later.

  • The " and \ characters are encoded as \" and \\, respectively.

The following examples show the JSON object formats for different event types (as indicated by the class and event items), reformatted slightly for readability:

Auditing startup event:

{ "timestamp": "2019-10-03 14:21:56",
  "id": 0,
  "class": "audit",
  "event": "startup",
  "connection_id": 0,
  "startup_data": { "server_id": 1,
                    "os_version": "i686-Linux",
                    "mysql_version": "5.7.21-log",
                    "args": ["/usr/local/mysql/bin/mysqld",
                             "--loose-audit-log-format=JSON",
                             "--log-error=log.err",
                             "--pid-file=mysqld.pid",
                             "--port=3306" ] } }

When the audit log plugin starts as a result of server startup (as opposed to being enabled at runtime), connection_id is set to 0, and account and login are not present.

Auditing shutdown event:

{ "timestamp": "2019-10-03 14:28:20",
  "id": 3,
  "class": "audit",
  "event": "shutdown",
  "connection_id": 0,
  "shutdown_data": { "server_id": 1 } }

When the audit log plugin is uninstalled as a result of server shutdown (as opposed to being disabled at runtime), connection_id is set to 0, and account and login are not present.

Connect or change-user event:

{ "timestamp": "2019-10-03 14:23:18",
  "id": 1,
  "class": "connection",
  "event": "connect",
  "connection_id": 5,
  "account": { "user": "root", "host": "localhost" },
  "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" },
  "connection_data": { "connection_type": "ssl",
                       "status": 0,
                       "db": "test",
                       "connection_attributes": {
                         "_pid": "43236",
                         ...
                         "program_name": "mysqladmin"
                       } }
}

Disconnect event:

{ "timestamp": "2019-10-03 14:24:45",
  "id": 3,
  "class": "connection",
  "event": "disconnect",
  "connection_id": 5,
  "account": { "user": "root", "host": "localhost" },
  "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" },
  "connection_data": { "connection_type": "ssl" } }

Query event:

{ "timestamp": "2019-10-03 14:23:35",
  "id": 2,
  "class": "general",
  "event": "status",
  "connection_id": 5,
  "account": { "user": "root", "host": "localhost" },
  "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" },
  "general_data": { "command": "Query",
                    "sql_command": "show_variables",
                    "query": "SHOW VARIABLES",
                    "status": 0 } }

Table access event (read, delete, insert, update):

{ "timestamp": "2019-10-03 14:23:41",
  "id": 0,
  "class": "table_access",
  "event": "insert",
  "connection_id": 5,
  "account": { "user": "root", "host": "localhost" },
  "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" },
  "table_access_data": { "db": "test",
                         "table": "t1",
                         "query": "INSERT INTO t1 (i) VALUES(1),(2),(3)",
                         "sql_command": "insert" } }

The items in the following list appear at the top level of JSON-format audit records: Each item value is either a scalar or a JSON hash. For items that have a hash value, the description lists only the item names within that hash. For more complete descriptions of second-level hash items, see later in this section.

  • account

    The MySQL account associated with the event. The value is a hash containing these items equivalent to the value of the CURRENT_USER() function within the section: user, host.

    Example:

    "account": { "user": "root", "host": "localhost" }

  • class

    A string representing the event class. The class defines the type of event, when taken together with the event item that specifies the event subclass.

    Example:

    "class": "connection"

    The following table shows the permitted combinations of class and event values.

    Table 6.28 Audit Log Class and Event Combinations

    Class ValuePermitted Event Values
    audit startup, shutdown
    connection connect, change_user, disconnect
    general status
    table_access_data read, delete, insert, update

  • connection_data

    Information about a client connection. The value is a hash containing these items: connection_type, status, db, and possibly connection_attributes. This item occurs only for audit records with a class value of connection.

    Example: 

    "connection_data": { "connection_type": "ssl",
                     "status": 0,
                     "db": "test" }

    As of MySQL 8.0.19, events with a class value of connection and event value of connect may include a connection_attributes item to display the connection attributes passed by the client at connect time. (For information about these attributes, which are also exposed in Performance Schema tables, see Section 26.12.9, “Performance Schema Connection Attribute Tables”.)

    The connection_attributes value is a hash that represents each attribute by its name and value.

    Example:

    "connection_attributes": {
  "_pid": "43236",
  "_os": "osx10.14",
  "_platform": "x86_64",
  "_client_version": "8.0.19",
  "_client_name": "libmysql",
  "program_name": "mysqladmin"
}

    If no connection attributes are present in the event, none are logged and no connection_attributes item appears. This can occur if the connection attempt is unsucessful, the client passes no attributes, or the connection occurs internally such as during server startup or when initiated by a plugin.

  • connection_id

    An unsigned integer representing the client connection identifier. This is the same as the value returned by the CONNECTION_ID() function within the session.

    Example:

    "connection_id": 5

  • event

    A string representing the subclass of the event class. The subclass defines the type of event, when taken together with the class item that specifies the event class. For more information, see the class item description.

    Example:

    "event": "connect"

  • general_data

    Information about an executed statement or command. The value is a hash containing these items: command, sql_command, query, status. This item occurs only for audit records with a class value of general.

    Example:

    "general_data": { "command": "Query",
                  "sql_command": "show_variables",
                  "query": "SHOW VARIABLES",
                  "status": 0 }

  • id

    An unsigned integer representing an event ID.

    Example:

    "id": 2

    For audit records that have the same timestamp value, their id values distinguish them and form a sequence. Within the audit log, timestamp/id pairs are unique. These pairs are bookmarks that identify event locations within the log.

  • login

    Information indicating how a client connected to the server. The value is a hash containing these items: user, os, ip, proxy.

    Example:

    "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }

  • shutdown_data

    Information pertaining to audit log plugin termination. The value is a hash containing these items: server_id This item occurs only for audit records with class and event values of audit and shutdown, respectively.

    Example:

    "shutdown_data": { "server_id": 1 }

  • startup_data

    Information pertaining to audit log plugin initialization. The value is a hash containing these items: server_id, os_version, mysql_version, args. This item occurs only for audit records with class and event values of audit and startup, respectively.

    Example:

    "startup_data": { "server_id": 1,
                  "os_version": "i686-Linux",
                  "mysql_version": "5.7.21-log",
                  "args": ["/usr/local/mysql/bin/mysqld",
                           "--loose-audit-log-format=JSON",
                           "--log-error=log.err",
                           "--pid-file=mysqld.pid",
                           "--port=3306" ] }

  • table_access_data

    Information about an access to a table. The value is a hash containing these items: db, table, query, sql_command, This item occurs only for audit records with a class value of table_access.

    Example:

    "table_access_data": { "db": "test",
                       "table": "t1",
                       "query": "INSERT INTO t1 (i) VALUES(1),(2),(3)",
                       "sql_command": "insert" }

  • timestamp

    A string representing a UTC value in YYYY-MM-DD hh:mm:ss format indicating the date and time when the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a timestamp value occurring after the statement finishes, not when it was received.

    Example:

    "timestamp": "2019-10-03 13:50:01"

    For audit records that have the same timestamp value, their id values distinguish them and form a sequence. Within the audit log, timestamp/id pairs are unique. These pairs are bookmarks that identify event locations within the log.

These items appear within hash values associated with top-level items of JSON-format audit records:

  • args

    An array of options that were given on the command line or in option files when the MySQL server was started. The first option is the path to the server executable.

    Example:

    "args": ["/usr/local/mysql/bin/mysqld",
         "--loose-audit-log-format=JSON",
         "--log-error=log.err",
         "--pid-file=mysqld.pid",
         "--port=3306" ]

  • command

    A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.

    Example:

    "command": "Query"

  • connection_type

    The security state of the connection to the server. Permitted values are tcp/ip (TCP/IP connection established without encryption), ssl (TCP/IP connection established with encryption), socket (Unix socket file connection), named_pipe (Windows named pipe connection), and shared_memory (Windows shared memory connection).

    Example:

    "connection_type": "tcp/tcp"

  • db

    A string representing a database name. For connection_data, it is the default database. For table_access_data, it is the table database.

    Example:

    "db": "test"

  • host

    A string representing the client host name.

    Example:

    "host": "localhost"

  • ip

    A string representing the client IP address.

    Example:

    "ip": "::1"

  • mysql_version

    A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable.

    Example:

    "mysql_version": "5.7.21-log"

  • os

    A string representing the external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this attribute is empty. The value is the same as that of the external_user system variable. See Section 6.2.18, “Proxy Users”.

    Example:

    "os": "jeffrey"

  • os_version

    A string representing the operating system on which the server was built or is running.

    Example:

    "os_version": "i686-Linux"

  • proxy

    A string representing the proxy user (see Section 6.2.18, “Proxy Users”). The value is empty if user proxying is not in effect.

    Example:

    "proxy": "developer"

  • query

    A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.

    Example:

    "query": "DELETE FROM t1"

  • server_id

    An unsigned integer representing the server ID. This is the same as the value of the server_id system variable.

    Example:

    "server_id": 1

  • sql_command

    A string that indicates the SQL statement type.

    Example:

    "sql_command": "insert"

    The values correspond to the statement/sql/xxx command counters. For example, xxx is drop_table and select for DROP TABLE and SELECT statements, respectively. The following statement displays the possible names:

    SELECT REPLACE(EVENT_NAME, 'statement/sql/', '') AS name
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%'
ORDER BY name;

  • status

    An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function.

    The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3.1, “Server Error Message Reference”.

    Warnings are not logged.

    Example:

    "status": 1051

  • table

    A string representing a table name.

    Example:

    "table": "t1"

  • user

    A string representing a user name. The meaning differs depending on the item within which user occurs:

    • Within account items, user is a string representing the user that the server authenticated the client as. This is the user name that the server uses for privilege checking.

    • Within login items, user is a string representing the user name sent by the client.

    Example:

    "user": "root"

6.4.5.5 Audit Log Logging Configuration

This section describes how to configure audit logging characteristics, such as the file to which the audit log plugin writes events, the format of written events, and whether to enable log file compression and encryption.

Note

Encryption capabilities described here apply as of MySQL 8.0.17, with the exception of the section that compares current encryption capabilities to the previous more-limited capabilities (see Audit Log File Encryption Prior to MySQL 8.0.17).

For additional information about the user-defined functions and system variables that affect audit logging, see Audit Log Functions, and Audit Log Options and Variables.

The audit log plugin can also control which audited events are written to the audit log file, based on event content or the account from which events originate. See Section 6.4.5.6, “Audit Log Filtering”.

Audit Log File Name

To configure the audit log file name, set the audit_log_file system variable at server startup. By default, the name is audit.log in the server data directory. For security reasons, write the audit log file to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log.

The plugin interprets the audit_log_file value as composed of a base name and an optional suffix. If compression or encryption are enabled, the effective file name (the name actually used to create the log file) differs from the configured file name because it has additional suffixes:

  • If compression is enabled, the plugin adds a suffix of .gz.

  • If encryption is enabled, the plugin adds a suffix of .pwd_id.enc, where pwd_id indicates which encryption password to use for log file operations.

The effective audit log file name is the name resulting from the addition of applicable compression and encryption suffixes to the configured file name. For example, if the configured audit_log_file value is audit.log, the effective file name is one of the values shown in the following table.

Enabled FeaturesEffective File Name
No compression or encryption audit.log
Compression audit.log.gz
Encryption audit.log.pwd_id.enc
Compression, encryption audit.log.gz.pwd_id.enc

pwd_id indicates the ID of the password used to encrypt or decrypt a file. pwd_id format is pwd_timestamp-seq, where:

  • pwd_timestamp is a UTC value in YYYYMMDDThhmmss format indicating when the password was created.

  • seq is a sequence number. Sequence numbers start at 1 and increase for passwords that have the same pwd_timestamp value.

Here are some example password ID values:

20190403T142359-1
20190403T142400-1
20190403T142400-2

The audit log plugin stores encryption passwords in the keyring (see Section 6.4.4, “The MySQL Keyring”). The IDs of audit log passwords in the keyring are based on pwd_id values, with a prefix of audit_log-. For the example password IDs just shown, the corresponding keyring IDs are:

audit_log-20190403T142359-1
audit_log-20190403T142400-1
audit_log-20190403T142400-2

The password currently used for encryption by the audit log plugin is the one having the largest pwd_timestamp value. If multiple passwords have the largest pwd_timestamp value, the current password is the one with the largest sequence number. For example, in the preceding set of password IDs, two of them have the largest timestamp, 20190403T142400, so the current password is the one with the largest sequence number (2).

The audit log plugin performs certain actions during initialization and termination based on the effective audit log file name:

  • During initialization, the plugin checks whether a file with the audit log file name already exists and renames it if so. (In this case, the plugin assumes that the previous server invocation exited unexpectedly with the audit log plugin running.) The plugin then writes to a new empty audit log file.

  • During termination, the plugin renames the audit log file.

  • File renaming (whether during plugin initialization or termination) occurs according to the usual rules for automatic log file rotation; see Automatic Audit Log File Rotation.

Audit Log File Format

To configure the audit log file format, set the audit_log_format system variable at server startup. By default, the format is NEW (new-style XML format). For details about each format, see Section 6.4.5.4, “Audit Log File Formats”.

If you change audit_log_format, it is recommended that you also change audit_log_file. Otherwise, there will be two sets of log files with the same base name but different formats.

Audit Log File Compression

Audit log file compression can be enabled for any logging format.

To configure audit log file compression, set the audit_log_compression system variable at server startup. Permitted values are NONE (no compression; the default) and GZIP (GNU Zip compression).

If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it. See Audit Log File Manual Uncompression and Decryption.

Audit Log File Encryption

Audit log file encryption can be enabled for any logging format. Encryption is based on user-defined passwords (with the exception of the initial password that the audit log plugin generates). To use this feature, the MySQL keyring must be enabled because audit logging uses it for password storage. Any keyring plugin can be used; for instructions, see Section 6.4.4, “The MySQL Keyring”.

To configure audit log file encryption, set the audit_log_encryption system variable at server startup. Permitted values are NONE (no encryption; the default) and AES (AES-256-CBC cipher encryption).

To set or get an encryption password, use these user-defined functions (UDFs):

  • To set the current encryption password, invoke audit_log_encryption_password_set(). This function stores the new password in the keyring, If encryption is enabled, it also performs a log file rotation operation that renames the current log file, and begins a new log file encrypted with the password. File renaming occurs according to the usual rules for automatic log file rotation; see Automatic Audit Log File Rotation.

  • To get the current encryption password, invoke audit_log_encryption_password_get() with no argument. To get a password by ID, pass an argument specifying the keyring ID of the current password or an archived password.

    To determine which audit log keyring IDs exist, query the Performance Schema keyring_keys table:

    		SELECT * FROM performance_schema.keyring_keys
    		WHERE KEY_ID LIKE 'audit_log%'
    		ORDER BY KEY_ID;

For additional information about audit log encryption functions, see Audit Log Functions.

When the audit log plugin initializes, if it finds that log file encryption is enabled, it checks whether the keyring contains an audit log encryption password. If not, the plugin automatically generates a random initial encryption password and stores it in the keyring. To discover this password, invoke audit_log_encryption_password_get().

If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it. See Audit Log File Manual Uncompression and Decryption.

Audit Log File Manual Uncompression and Decryption

Audit log files can be uncompressed and decrypted using standard tools. This should be done only for log files that have been closed (archived) and are no longer in use, not for the log file that the audit log plugin is currently writing. You can recognize archived log files because they have been renamed by the audit log plugin to include a timestamp in the file name just after the base name.

For this discussion, assume that audit_log_file is set to audit.log. In that case, an archived audit log file has one of the names shown in the following table.

Enabled FeaturesArchived File Name
No compression or encryption audit.timestamp.log
Compression audit.timestamp.log.gz
Encryption audit.timestamp.log.pwd_id.enc
Compression, encryption audit.timestamp.log.gz.pwd_id.enc

As discussed in Audit Log File Name, pwd_id format is pwd_timestamp-seq. Thus, the names of archived encrypted log files actually contain two timestamps. The first indicates rotation time, and the second indicates when the password was created.

Consider the following set of archived encrypted log file names:

audit.20190410T205827.log.20190403T185337-1.enc
audit.20190410T210243.log.20190403T185337-1.enc
audit.20190415T145309.log.20190414T223342-1.enc
audit.20190415T151322.log.20190414T223342-2.enc

Each file name has a unique rotation-time timestamp. By contrast, the password timestamps are not unique:

  • The first two files have the same password ID and sequence number (20190403T185337-1). They have the same encryption password.

  • The second two files have the same paswword ID (20190414T223342) but different sequence numbers (1, 2). These files have different encryption passwords.

To uncompress a compressed log file manually, use gunzip, gzip -d, or equivalent command. For example: 

timestamp
timestamp

To decrypt an encrypted log file manually, use the openssl command. For example: 

password
timestamp
pwd_id
timestamp

If both compression and encryption are enabled for audit logging, compression occurs before encryption. In this case, the file name has .gz and .pwd_id.enc suffixes added, corresponding to the order in which those operations occur. To recover the original file manually, perform the operations in reverse. That is, first decrypt the file, then uncompress it:

password
timestamp
pwd_id
timestamp
timestamp
timestamp
Audit Log File Encryption Prior to MySQL 8.0.17

This section covers the differences in audit log file encryption capabilities prior to and as of MySQL 8.0.17, which is when password archiving and expiration were implemented. It also indicates how the audit log plugin handles upgrades to MySQL 8.0.17 or higher from versions lower than 8.0.17.

FeaturePrior to MySQL 8.0.17As of MySQL 8.0.17
Number of passwords Single password only Multiple passwords permitted
Encrypted log file names .enc suffix .pwd_id.enc suffix
Password keyring ID audit_log audit_log-pwd_id
Password history No Yes
Password expiration No Yes

Prior to MySQL 8.0.17, there is no password history, so setting a new password makes the old password inaccessible, rendering MySQL Enterprise Audit unable to read log files encrypted with the old password. You must keep a record of the previous password should you need to decrypt those files manually.

If audit log file encryption is enabled when you upgrade to MySQL 8.0.17 or higher from a lower version, the audit log plugin performs these upgrade actions:

  • During plugin initialization, the plugin checks for an encryption password with a keyring ID of audit_log. If it finds one, the plugin duplicates the password using a keyring ID in audit_log-pwd_id format and uses it as the current encryption password.

  • Existing encrypted log files have a suffix of .enc. The plugin does not rename these to have a suffix of .pwd_id.enc, but can read them as long as the key with the ID of audit_log remains in the keyring.

  • When password cleanup occurs, if the plugin expires any password with a keyring ID in audit_log-pwd_id format, it also expires the password with a keyring ID of audit_log, if it exists. (At this point, encrypted log files that have a suffix of .enc rather than .pwd_id.enc become unreadable by the plugin, so it is assumed that you no longer need them.)

Audit Log File Space Management and Name Rotation

The audit log file has the potential to grow very large and consume a lot of disk space. To enable management of the space used by its log files, the audit log plugin provides for log file rotation, either manually or automatically. Rotation capabilities use the audit_log_flush and audit_log_rotate_on_size system variables:

  • By default, audit_log_rotate_on_size=0 and no log rotation occurs unless performed manually. In this case, use audit_log_flush to close and reopen the current log file after manually renaming it.

  • If audit_log_rotate_on_size is greater than 0, automatic rotation occurs when a write to the current log file causes its size to exceed this value. The audit log plugin closes the file, renames it, and opens a new log file. With automatic rotation enabled, audit_log_flush has no effect.

  • Automatic rotation also occurs under several other conditions, described later.

Note

Renamed log files are not removed automatically. For example, with size-based log file rotation, renamed log files do not rotate off the end of the name sequence. Instead, they have unique names and accumulate indefinitely. To avoid excessive space use, remove old files periodically, backing them up first as necessary. If backed-up log files are encrypted, also back up the corresponding encryption passwords to a safe place, should you need to decrypt the files later.

The following discussion describes log file rotation methods in greater detail.

Manual Audit Log File Rotation

If audit_log_rotate_on_size=0 (the default), no log rotation occurs unless performed manually. In this case, the audit log plugin closes and reopens the log file when the audit_log_flush value changes from disabled to enabled. Log file renaming must be done externally to the server. Suppose that the log file name is audit.log and you want to maintain the three most recent log files, cycling through the names audit.log.1 through audit.log.3. On Unix, perform rotation manually like this:

  1. From the command line, rename the current log files:

    mv audit.log.2 audit.log.3
mv audit.log.1 audit.log.2
mv audit.log audit.log.1

    This strategy overwrites the current audit.log.3 contents, placing a bound on the number of archived log files and the space they use.

  2. At this point, the plugin is still writing to the current log file, which has been renamed to audit.log.1. Connect to the server and flush the log file so the plugin closes it and reopens a new audit.log file:

    SET GLOBAL audit_log_flush = ON;

    audit_log_flush is special in that its value remains OFF so that you need not disable it explicitly before enabling it again to perform another flush.

Note

If compression or encryption are enabled, log file names include suffixes that signify the enabled features, as well as a password ID if encryption is enabled. If file names include a password ID, be sure to retain the ID in the name of any files you rename manually so that the password to use for decryption operations can be determined.

Note

For JSON-format logging, renaming audit log files manually makes them unavailable to the log-reading functions because the audit log plugin no longer can determine that they are part of the log file sequence (see Audit Log File Reading). Consider setting audit_log_rotate_on_size greater than 0 to use size-based rotation instead.

Automatic Audit Log File Rotation

If audit_log_rotate_on_size is greater than 0, setting audit_log_flush has no effect. Instead, whenever a write to the current log file causes its size to exceed the audit_log_rotate_on_size value, the audit log plugin closes the file, renames it, and opens a new log file.

Automatic rotation also occurs under these conditions:

  • During plugin initialization, if a file with the audit log file name already exists (see Audit Log File Name).

  • During plugin termination.

  • When the audit_log_encryption_password_set() function is called to set the encryption password, if encryption is enabled. (Rotation does not occur if encryption is disabled.)

The plugin renames the original file by inserting a timestamp just after its base name. For example, if the file name is audit.log, the plugin renames it to a value such as audit.20190115T140633.log. The timestamp is a UTC value in YYYYMMDDThhmmss format. The timestamp indicates rotation time for XML logging, and the timestamp of the last event written to the file for JSON logging.

Audit Logging Write Strategy

The audit log plugin can use any of several strategies for log writes. Regardless of strategy, logging occurs on a best-effort basis, with no guarantee of consistency.

To specify a write strategy, set the audit_log_strategy system variable at server startup. By default, the strategy value is ASYNCHRONOUS and the plugin logs asynchronously to a buffer, waiting if the buffer is full. It's possible to tell the plugin not to wait (PERFORMANCE) or to log synchronously, either using file system caching (SEMISYNCHRONOUS) or forcing output with a sync() call after each write request (SYNCHRONOUS).

For asynchronous write strategy, the audit_log_buffer_size system variable is the buffer size in bytes. Set this variable at server startup to change the buffer size. The plugin uses a single buffer, which it allocates when it initializes and removes when it terminates. The plugin does not allocate this buffer for nonasynchronous write strategies.

Asynchronous logging strategy has these characteristics:

  • Minimal impact on server performance and scalability.

  • Blocking of threads that generate audit events for the shortest possible time; that is, time to allocate the buffer plus time to copy the event to the buffer.

  • Output goes to the buffer. A separate thread handles writes from the buffer to the log file.

With asynchronous logging, the integrity of the log file may be compromised if a problem occurs during a write to the file or if the plugin does not shut down cleanly (for example, in the event that the server host exits unexpectedly). To reduce this risk, set audit_log_strategy to use synchronous logging.

A disadvantage of PERFORMANCE strategy is that it drops events when the buffer is full. For a heavily loaded server, the audit log may have events missing.

Audit Log File Reading

The audit log plugin enables bookmarking and reading of JSON-format audit log files. (These capabilities do not apply to files written in other log formats.)

When the audit log plugin initializes and is configured for JSON logging, it uses the directory containing the audit log file (determined from the audit_log_file value) as the location to search for readable audit log files. To do this, it uses the value of audit_log_file to determine the file base name and suffix values, then looks for files with names that match the following pattern, where [...] indicates optional file name parts:

basename
timestamp
suffix
pwd_id

A file is encrypted if its name ends with .enc. The plugin determines the keyring ID of the decryption password needed to read it as follows:

  • If .enc is preceded by pwd_id, the keyring ID is audit_log-pwd_id.

  • If .enc is not preceded by pwd_id, the file has an old name from before password history was implemented. The keyring ID is audit_log.

The plugin opens each file matching the pattern, checks that the file really contains JSON audit records, and sorts the files using the timestamps from the first record of each file to construct a list of files that are subject to use with the log-reading functions.

The plugin cannot include in the sequence files that were renamed manually and do not match the preceding pattern, or that were encrypted with a password no longer available in the keyring.

To read events from the audit log, use these user-defined functions (UDFs):

  • audit_log_read_bookmark() returns a JSON string representing a bookmark for the most recently written audit log event. This bookmark is suitable for passing to audit_log_read() to indicate to that function where to begin reading. Example bookmark:

    { "timestamp": "2019-10-03 21:03:44", "id": 0 }

  • audit_log_read() reads events from the audit log and returns a JSON string containing an array of audit events.

Example audit_log_read() invocation using the current bookmark:

mysql> SELECT audit_log_read(audit_log_read_bookmark());
+-----------------------------------------------------------------------+
| audit_log_read(audit_log_read_bookmark())                             |
+-----------------------------------------------------------------------+
| [ {"timestamp":"2019-10-03 22:41:24","id":0,"class":"connection", ... |
+-----------------------------------------------------------------------+

Each event in the audit_log_read() return value is a JSON hash, except that the last array element may be a JSON null value to indicate no following events are available to read. For example:

[
  { "timestamp": "2019-10-03 22:08:08", "id": 10,
    "class": "general", "event": "status",
    ...
  },
  {
    "timestamp": "2019-10-03 22:08:08", "id": 11,
    "class": "connection", "event": "disconnect",
    ...
  },
  {
    "timestamp": "2019-10-03 13:39:33", "id": 0,
    "class": "connection", "event": "connect",
    ...
  },
  {
    "timestamp": "2019-10-03 13:39:33", "id": 1,
    "class": "general", "event": "status",
    ...
  },
  {
    "timestamp": "2019-10-03 13:39:33", "id": 2,
    "class": "connection", "event": "disconnect",
    ...
  },
  null
]

Use audit_log_read() like this:

  • For the first call to audit_log_read() within a session, pass a bookmark indicating where to begin reading.

  • If the final value of the returned array is not a JSON null value, there are more events following those just read and audit_log_read() can be called without or with a bookmark argument. Without an argument, reading continues with the next unread event. With a bookmark argument, reading continues from the bookmark.

  • If the final value of the returned array is a JSON null value, there are no more events left to be read and the next call to audit_log_read() must include a bookmark argument.

A bookmark is a JSON hash that indicates where and how much to read. The following items are significant in the bookmark value (other items are ignored):

  • timestamp, id: The location within the audit log of the first event to read. Both items must be present to completely specify a position.

  • max_array_length: The maximum number of events to read from the log. If omitted, the default is to read to the end of the log or until the read buffer is full, whichever comes first.

The result returned from either log-reading function is a string, which can be manipulated as necessary. Suppose that a bookmark has this value:

SET @mark := audit_log_read_bookmark();
SELECT @mark;

Calling audit_log_read() with that bookmark can return multiple events. To limit audit_log_read() to reading a single event, add to the bookmark a max_array_length item with a value of 1. For example, convert the preceding bookmark as follows:

SET @mark := JSON_SET(@mark, '$.max_array_length', 1);
SELECT @mark;

The modified bookmark, when passed to audit_log_read(), produces a result of a single audit record.

Prior to MySQL 8.0.19, string return values from audit log UDFs are binary strings. To use a binary string with functions that require a nonbinary string (such as functions that manipulate JSON values), perform a conversion to utf8mb4. For example, before passing the bookmark to JSON_SET(), convert it as follows:

SET @mark = CONVERT(@mark USING utf8mb4);

That statement can be included even for MySQL 8.0.19 and higher because for those versions it is essentially a no-op and is harmless.

To set a limit on the number of bytes that audit_log_read() reads, set the audit_log_read_buffer_size system variable. As of MySQL 8.0.12, this variable has a default of 32KB and can be set at runtime. Each client should set its session value of audit_log_read_buffer_size appropriately for its use of audit_log_read(). Prior to MySQL 8.0.12, audit_log_read_buffer_size has a default of 1MB, affects all clients, and can be changed only at server startup.

Each call to audit_log_read() returns as many available items as fit within the buffer size, skipping items that do not fit within the buffer size. Given this behavior, consider these factors when assessing the proper buffer size for an application:

  • There is a tradeoff between number of calls to audit_log_read() and items returned per call. With a smaller buffer size, calls return fewer items, so more calls are needed. With a larger buffer size, calls return more items, so fewer calls are needed.

  • With a smaller buffer size, such as the default size of 32KB, there is a greater chance that items will exceed the buffer size and audit_log_read() will skip them. Skipped items generate warnings.

For additional information about audit log-reading functions, see Audit Log Functions.

6.4.5.6 Audit Log Filtering

Note

This section describes how audit log filtering works if the audit log plugin and the accompanying audit tables and UDFs are installed. If the plugin is installed but not the accompanying audit tables and UDFs, the plugin operates in legacy filtering mode, described in Section 6.4.5.8, “Legacy Mode Audit Log Filtering”. Legacy mode is filtering behavior as it was prior to MySQL 5.7.13; that is, before the introduction of rule-based filtering.

The audit log plugin has the capability of controlling logging of audited events by filtering them:

  • Audited events can be filtered using these characteristics:

    • User account

    • Audit event class

    • Audit event subclass

    • Value of event fields such as those that indicate operation status or SQL statement executed

  • Audit filtering is rule based:

    • A filter definition creates a set of auditing rules. Definitions can be configured to include or exclude events for logging based on the characteristics just described.

    • Filter rules have the capability of blocking (aborting) execution of qualifying events, in addition to existing capabilities for event logging.

    • Multiple filters can be defined, and any given filter can be assigned to any number of user accounts.

    • It is possible to define a default filter to use with any user account that has no explicitly assigned filter.

  • Audit filters can be defined, displayed, and modified using an SQL interface based on user-defined functions (UDFs).

  • Audit filter definitions are stored in the tables in the mysql system database.

  • Within a given session, the value of the read-only audit_log_filter_id system variable indicates whether a filter has been assigned to the session.

Note

By default, rule-based audit log filtering logs no auditable events for any users. To log all auditable events for all users, use the following statements, which create a simple filter to enable logging and assign it to the default account:

SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('%', 'log_all');

The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).

The following list briefly summarizes the UDFs that implement the SQL interface for audit filtering control:

For usage examples and complete details about the filtering functions, see Using Audit Log Filtering Functions, and Audit Log Functions.

Audit log filtering functions are subject to these constraints:

  • To use any filtering function, the audit_log plugin must be enabled or an error occurs. In addition, the audit tables must exist or an error occurs. To install the audit_log plugin and its accompanying UDFs and tables, see Section 6.4.5.2, “Installing or Uninstalling MySQL Enterprise Audit”.

  • To use any filtering function, a user must possess the SUPER privilege or an error occurs. To grant the SUPER privilege to a user account, use this statement:

    GRANT SUPER ON *.* TO user;

    Alternatively, should you prefer to avoid granting the SUPER privilege while still permitting users to access specific filtering functions, “wrapper” stored programs can be defined. This technique is described in the context of keyring UDFs in Using General-Purpose Keyring Functions; it can be adapted for use with filtering UDFs.

  • The audit_log plugin operates in legacy mode if it is installed but the accompanying audit tables and functions are not created. The plugin writes these messages to the error log at server startup:

    [Warning] Plugin audit_log reported: 'Failed to open the audit log filter tables.'
[Warning] Plugin audit_log reported: 'Audit Log plugin supports a filtering,
which has not been installed yet. Audit Log plugin will run in the legacy
mode, which will be disabled in the next release.'

    In legacy mode, filtering can be done based only on event account or status. For details, see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”.

Using Audit Log Filtering Functions

Before using the audit log user-defined functions (UDFs), install them according to the instructions provided in Section 6.4.5.2, “Installing or Uninstalling MySQL Enterprise Audit”. The SUPER privilege is required to use any of these functions.

The audit log filtering functions enable filtering control by providing an interface to create, modify, and remove filter definitions and assign filters to user accounts.

Filter definitions are JSON values. For information about using JSON data in MySQL, see Section 11.5, “The JSON Data Type”. This section shows some simple filter definitions. For more information about filter definitions, see Section 6.4.5.7, “Writing Audit Log Filter Definitions”.

When a connection arrives, the audit log plugin determines which filter to use for the new session by searching for the user account name in the current filter assignments:

  • If a filter is assigned to the user, the audit log uses that filter.

  • Otherwise, if no user-specific filter assignment exists, but there is a filter assigned to the default account (%), the audit log uses the default filter.

  • Otherwise, the audit log selects no audit events from the session for processing.

If a change-user operation occurs during a session (see Section 28.7.6.3, “mysql_change_user()”), filter assignment for the session is updated using the same rules but for the new user.

By default, no accounts have a filter assigned, so no processing of auditable events occurs for any account.

Suppose that instead you want the default to be to log only connection-related activity (for example, to see connect, change-user, and disconnect events, but not the SQL statements users execute while connected). To achieve this, define a filter (shown here named log_conn_events) that enables logging only of events in the connection class, and assign that filter to the default account, represented by the % account name:

SET @f = '{ "filter": { "class": { "name": "connection" } } }';
SELECT audit_log_filter_set_filter('log_conn_events', @f);
SELECT audit_log_filter_set_user('%', 'log_conn_events');

Now the audit log uses this default account filter for connections from any account that has no explicitly defined filter.

To assign a filter explicitly to a particular user account or accounts, define the filter, then assign it to the relevant accounts:

SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('user1@localhost', 'log_all');
SELECT audit_log_filter_set_user('user2@localhost', 'log_all');

Now full logging is enabled for user1@localhost and user2@localhost. Connections from other accounts continue to be filtered using the default account filter.

To disassociate a user account from its current filter, either unassign the filter or assign a different filter:

  • To unassign the filter from the user account:

    SELECT audit_log_filter_remove_user('user1@localhost');

    Filtering of current sessions for the account remains unaffected. Subsequent connections from the account are filtered using the default account filter if there is one, and are not logged otherwise.

  • To assign a different filter to the user account:

    SELECT audit_log_filter_set_filter('log_nothing', '{ "filter": { "log": false } }');
SELECT audit_log_filter_set_user('user1@localhost', 'log_nothing');

    Filtering of current sessions for the account remains unaffected. Subsequent connections from the account are filtered using the new filter. For the filter shown here, that means no logging for new connections from user1@localhost.

For audit log filtering, user name and host name comparisons are case-sensitive. This differs from comparisons for privilege checking, for which host name comparisons are not case-sensitive.

To remove a filter, do this:

SELECT audit_log_filter_remove_filter('log_nothing');

Removing a filter also unassigns it from any users to whom it has been assigned, including any current sessions for those users.

The filtering UDFs just described affect audit filtering immediately and update the audit log tables in the mysql system database that store filters and user accounts (see Audit Log Tables). It is also possible to modify the audit log tables directly using statements such as INSERT, UPDATE, and DELETE, but such changes do not affect filtering immediately. To flush your changes and make them operational, call audit_log_filter_flush():

SELECT audit_log_filter_flush();
Warning

audit_log_filter_flush() should be used only after modifying the audit tables directly, to force reloading all filters. Otherwise, this function should be avoided. It is, in effect, a simplified version of unloading and reloading the audit_log plugin with UNINSTALL PLUGIN plus INSTALL PLUGIN.

audit_log_filter_flush() affects all current sessions and detaches them from their previous filters. Current sessions are no longer logged unless they disconnect and reconnect, or execute a change-user operation.

To determine whether a filter has been assigned to the current session, check the session value of the read-only audit_log_filter_id system variable. If the value is 0, no filter is assigned. A nonzero value indicates the internally maintained ID of the assigned filter:

mysql> SELECT @@audit_log_filter_id;
+-----------------------+
| @@audit_log_filter_id |
+-----------------------+
|                     2 |
+-----------------------+

6.4.5.7 Writing Audit Log Filter Definitions

Filter definitions are JSON values. For information about using JSON data in MySQL, see Section 11.5, “The JSON Data Type”.

Filter definitions have this form, where actions indicates how filtering takes place:

{ "filter": actions }

The following discussion describes permitted constructs in filter definitions.

Logging All Events

To explicitly enable or disable logging of all events, use a log element in the filter:

{
  "filter": { "log": true }
}

The log value can be either true or false.

The preceding filter enables logging of all events. It is equivalent to:

{
  "filter": { }
}

Logging behavior depends on the log value and whether class or event items are specified:

  • With log specified, its given value is used.

  • Without log specified, logging is true if no class or event item is specified, and false otherwise (in which case, class or event can include their own log item).

Logging Specific Event Classes

To log events of a specific class, use a class element in the filter, with its name field denoting the name of the class to log:

{
  "filter": {
    "class": { "name": "connection" }
  }
}

The name value can be connection, general, or table_access to log connection, general, or table-access events, respectively.

The preceding filter enables logging of events in the connection class. It is equivalent to the following filter with log items made explicit:

{
  "filter": {
    "log": false,
    "class": { "log": true,
               "name": "connection" }
  }
}

To enable logging of multiple classes, define the class value as a JSON array element that names the classes:

{
  "filter": {
    "class": [
      { "name": "connection" },
      { "name": "general" },
      { "name": "table_access" }
    ]
  }
}
Note

When multiple instances of a given item appear at the same level within a filter definition, the item values can be combined into a single instance of that item within an array value. The preceding definition can be written like this:

{
  "filter": {
    "class": [
      { "name": [ "connection", "general", "table_access" ] }
    ]
  }
}
Logging Specific Event Subclasses

To select specific event subclasses, use an event item containing a name item that names the subclasses. The default action for events selected by an event item is to log them. For example, this filter enables logging for the named event subclasses:

{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect" },
          { "name": "disconnect" }
        ]
      },
      { "name": "general" },
      {
        "name": "table_access",
        "event": [
          { "name": "insert" },
          { "name": "delete" },
          { "name": "update" }
        ]
      }
    ]
  }
}

The event item can also contain explicit log items to indicate whether to log qualifying events. This event item selects multiple events and explicitly indicates logging behavior for them:

"event": [
  { "name": "read", "log": false },
  { "name": "insert", "log": true },
  { "name": "delete", "log": true },
  { "name": "update", "log": true }
]

The event item can also indicate whether to block qualifying events, if it contains an abort item. For details, see Blocking Execution of Specific Events.

Table 6.29, “Event Class and Subclass Combinations” describes the permitted subclass values for each event class.

Table 6.29 Event Class and Subclass Combinations

Event ClassEvent SubclassDescription
connection connect Connection initiation (successful or unsuccessful)
connection change_user User re-authentication with different user/password during session
connection disconnect Connection termination
general status General operation information
message internal Internally generated message
message user Message generated by audit_api_message_emit_udf()
table_access read Table read statements, such as SELECT or INSERT INTO ... SELECT
table_access delete Table delete statements, such as DELETE or TRUNCATE TABLE
table_access insert Table insert statements, such as INSERT or REPLACE
table_access update Table update statements, such as UPDATE

Table 6.30, “Log and Abort Characteristics Per Event Class and Subclass Combination” describes for each event subclass whether it can be logged or aborted.

Table 6.30 Log and Abort Characteristics Per Event Class and Subclass Combination

Event ClassEvent SubclassCan be LoggedCan be Aborted
connection connect Yes No
connection change_user Yes No
connection disconnect Yes No
general status Yes No
message internal Yes Yes
message user Yes Yes
table_access read Yes Yes
table_access delete Yes Yes
table_access insert Yes Yes
table_access update Yes Yes
Inclusive and Exclusive Logging

A filter can be defined in inclusive or exclusive mode:

  • Inclusive mode logs only explicitly specified items.

  • Exclusive mode logs everything but explicitly specified items.

To perform inclusive logging, disable logging globally and enable logging for specific classes. This filter logs connect and disconnect events in the connection class, and events in the general class: 

{
  "filter": {
    "log": false,
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect", "log": true },
          { "name": "disconnect", "log": true }
        ]
      },
      { "name": "general", "log": true }
    ]
  }
}

To perform exclusive logging, enable logging globally and disable logging for specific classes. This filter logs everything except events in the general class:

{
  "filter": {
    "log": true,
    "class":
      { "name": "general", "log": false }
  }
}

This filter logs change_user events in the connection class, message events, and table_access events:

{
  "filter": {
    "log": true,
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect", "log": false },
          { "name": "disconnect", "log": false }
        ]
      },
      { "name": "general", "log": false }
    ]
  }
}
Testing Event Field Values

To enable logging based on specific event field values, specify a field item within the log item that indicates the field name and its expected value:

{
  "filter": {
    "class": {
    "name": "general",
      "event": {
        "name": "status",
        "log": {
          "field": { "name": "general_command.str", "value": "Query" }
        }
      }
    }
  }
}

Each event contains event class-specific fields that can be accessed from within a filter to perform custom filtering.

A connection event indicates when a connection-related activity occurs during a session, such as a user connecting to or disconnecting from the server. Table 6.31, “Connection Event Fields” indicates the permitted fields for connection events.

Table 6.31 Connection Event Fields

Field NameField TypeDescription
status integer

Event status:

0: OK

Otherwise: Failed
connection_id unsigned integer Connection ID
user.str string User name specified during authentication
user.length unsigned integer User name length
priv_user.str string Authenticated user name (account user name)
priv_user.length unsigned integer Authenticated user name length
external_user.str string External user name (provided by third-party authentication plugin)
external_user.length unsigned integer External user name length
proxy_user.str string Proxy user name
proxy_user.length unsigned integer Proxy user name length
host.str string Connected user host
host.length unsigned integer Connected user host length
ip.str string Connected user IP address
ip.length unsigned integer Connected user IP address length
database.str string Database name specified at connect time
database.length unsigned integer Database name length
connection_type integer

Connection type:

or "::undefined": Undefined

or "::tcp/ip": TCP/IP

or "::socket": Socket

or "::named_pipe": Named pipe

or "::ssl": TCP/IP with encryption

or "::shared_memory": Shared memory

The "::xxx" values are symbolic pseudo-constants that may be given instead of the literal numeric values. They must be quoted as strings and are case-sensitive.

A general event indicates the status code of an operation and its details. Table 6.32, “General Event Fields” indicates the permitted fields for general events.

Table 6.32 General Event Fields

Field NameField TypeDescription
general_error_code integer

Event status:

0: OK

Otherwise: Failed

general_thread_id unsigned integer Connection/thread ID
general_user.str string User name specified during authentication
general_user.length unsigned integer User name length
general_command.str string Command name
general_command.length unsigned integer Command name length
general_query.str string SQL statement text
general_query.length unsigned integer SQL statement text length
general_host.str string Host name
general_host.length unsigned integer Host name length
general_sql_command.str string SQL command type name
general_sql_command.length unsigned integer SQL command type name length
general_external_user.str string External user name (provided by third-party authentication plugin)
general_external_user.length unsigned integer External user name length
general_ip.str string Connected user IP address
general_ip.length unsigned integer Connection user IP address length

general_command.str indicates a command name: Query, Execute, Quit, or Change user.

A general event with the general_command.str field set to Query or Execute contains general_sql_command.str set to a value that specifies the type of SQL command: alter_db, alter_db_upgrade, admin_commands, and so forth. These values can be seen as the last components of the Performance Schema instruments displayed by this statement: 

SELECT NAME FROM performance_schema.setup_instruments
WHERE NAME LIKE 'statement/sql/%' ORDER BY NAME;

A table-access event provides information about specific table accesses. Table 6.33, “Table-Access Event Fields” indicates the permitted fields for table-access events.

Table 6.33 Table-Access Event Fields

Field NameField TypeDescription
connection_id unsigned integer Event connection ID
sql_command_id integer SQL command ID
query.str string SQL statement text
query.length unsigned integer SQL statement text length
table_database.str string Database name associated with event
table_database.length unsigned integer Database name length
table_name.str string Table name associated with event
table_name.length unsigned integer Table name length

The following list shows which statements produce which table-access events:

  • read event:

    • SELECT

    • INSERT ... SELECT (for tables referenced in SELECT clause)

    • REPLACE ... SELECT (for tables referenced in SELECT clause)

    • UPDATE ... WHERE (for tables referenced in WHERE clause)

    • HANDLER ... READ

  • delete event:

    • DELETE

    • TRUNCATE TABLE

  • insert event:

    • INSERT

    • INSERT ... SELECT (for table referenced in INSERT clause)

    • REPLACE

    • REPLACE ... SELECT (for table referenced in REPLACE clause

    • LOAD DATA

    • LOAD XML

  • update event:

    • UPDATE

    • UPDATE ... WHERE (for tables referenced in UPDATE clause)

Blocking Execution of Specific Events

event items can include an abort item that indicates whether to prevent qualifying events from executing. For example, abort enables rules to be written that block execution of specific SQL statements.

The abort item must appear within an event item. For example: 

qualifying event subclass names
condition

For event subclasses selected by the name item, the abort action is true or false, depending on condition evaluation. If the condition evaluates to true, the event is blocked. Otherwise, the event continues executing.

The condition specification can be as simple as true or false, or it can be more complex such that evaluation depends on event characteristics.

This filter blocks INSERT, UPDATE, and DELETE statements:

{
  "filter": {
    "class": {
      "name": "table_access",
      "event": {
        "name": [ "insert", "update", "delete" ],
        "abort": true
      }
    }
  }
}

This more complex filter blocks the same statements, but only for a specific table (finances.bank_account):

{
  "filter": {
    "class": {
      "name": "table_access",
      "event": {
        "name": [ "insert", "update", "delete" ],
        "abort": {
          "and": [
            { "field": { "name": "table_database.str", "value": "finances" } },
            { "field": { "name": "table_name.str", "value": "bank_account" } }
          ]
        }
      }
    }
  }
}

Statements matched and blocked by the filter return an error to the client:

ERROR 1045 (28000): Statement was aborted by an audit log filter

Not all events can be blocked (see Table 6.30, “Log and Abort Characteristics Per Event Class and Subclass Combination”). For an event that cannot, the audit log writes a warning to the error log rather than blocking it.

For attempts to define a filter in which the abort item appears elsewhere than in an event item, an error occurs.

Logical Operators

Logical operators (and, or, not) can be used in log items. This permits construction of more advanced filtering configurations:

{
  "filter": {
    "class": {
      "name": "general",
      "event": {
        "name": "status",
        "log": {
          "or": [
            {
              "and": [
                { "field": { "name": "general_command.str",    "value": "Query" } },
                { "field": { "name": "general_command.length", "value": 5 } }
              ]
            },
            {
              "and": [
                { "field": { "name": "general_command.str",    "value": "Execute" } },
                { "field": { "name": "general_command.length", "value": 7 } }
              ]
            }
          ]
        }
      }
    }
  }
}
Referencing Predefined Variables

To refer to a predefined variable in a log condition, use a variable item, which tests equality against a given value:

{
  "filter": {
    "class": {
      "name": "general",
      "event": {
        "name": "status",
        "log": {
          "variable": {
            "name": "audit_log_connection_policy_value", "value": "::none"
          }
        }
      }
    }
  }
}

Each predefined variable corresponds to a system variable. By writing a filter that tests a predefined variable, you can modify filter operation by setting the corresponding system variable, without having to redefine the filter. For example, by writing a filter that tests the value of the audit_log_connection_policy_value predefined variable, you can modify filter operation by changing the value of the audit_log_connection_policy system variable.

The audit_log_xxx_policy system variables are used for the legacy mode audit log (see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”). With rule-based audit log filtering, those variables remain visible (for example, using SHOW VARIABLES), but changes to them have no effect unless you write filters containing constructs that refer to them.

The following list describes the permitted predefined variables for variable items:

  • audit_log_connection_policy_value

    This variable corresponds to the value of the audit_log_connection_policy system variable. The value is an unsigned integer. Table 6.34, “audit_log_connection_policy_value Values” shows the permitted values and the corresponding audit_log_connection_policy values.

    Table 6.34 audit_log_connection_policy_value Values

    ValueCorresponding audit_log_connection_policy Value
    0 or "::none" NONE
    1 or "::errors" ERRORS
    2 or "::all" ALL

    The "::xxx" values are symbolic pseudo-constants that may be given instead of the literal numeric values. They must be quoted as strings and are case-sensitive.

  • audit_log_policy_value

    This variable corresponds to the value of the audit_log_policy system variable. The value is an unsigned integer. Table 6.35, “audit_log_policy_value Values” shows the permitted values and the corresponding audit_log_policy values.

    Table 6.35 audit_log_policy_value Values

    ValueCorresponding audit_log_policy Value
    0 or "::none" NONE
    1 or "::logins" LOGINS
    2 or "::all" ALL
    3 or "::queries" QUERIES

    The "::xxx" values are symbolic pseudo-constants that may be given instead of the literal numeric values. They must be quoted as strings and are case-sensitive.

  • audit_log_statement_policy_value

    This variable corresponds to the value of the audit_log_statement_policy system variable. The value is an unsigned integer. Table 6.36, “audit_log_statement_policy_value Values” shows the permitted values and the corresponding audit_log_statement_policy values.

    Table 6.36 audit_log_statement_policy_value Values

    ValueCorresponding audit_log_statement_policy Value
    0 or "::none" NONE
    1 or "::errors" ERRORS
    2 or "::all" ALL

    The "::xxx" values are symbolic pseudo-constants that may be given instead of the literal numeric values. They must be quoted as strings and are case-sensitive.

Referencing Predefined Functions

To refer to a predefined function in a log condition, use a function item, which takes name and args values to specify the function name and its arguments, respectively: 

{
  "filter": {
    "class": {
      "name": "general",
      "event": {
        "name": "status",
        "log": {
          "function": {
            "name": "find_in_include_list",
            "args": [ { "string": [ { "field": "user.str" },
                                    { "string": "@"},
                                    { "field": "host.str" } ] } ]
          }
        }
      }
    }
  }
}

The function as specified in the name item should be the function name only, without parentheses or the argument list. Arguments in the args item, if there is one, must be given in the order listed in the function description. Arguments can refer to predefined variables, event fields, or string or numeric constants.

The preceding filter determines whether to log general class status events depending on whether the current user is found in the audit_log_include_accounts system variable. That user is constructed using fields in the event.

The following list describes the permitted predefined functions for function items:

  • audit_log_exclude_accounts_is_null()

    Checks whether the audit_log_exclude_accounts system variable is NULL. This function can be helpful when defining filters that correspond to the legacy audit log implementation.

    Arguments:

    None.

  • audit_log_include_accounts_is_null()

    Checks whether the audit_log_include_accounts system variable is NULL. This function can be helpful when defining filters that correspond to the legacy audit log implementation.

    Arguments:

    None.

  • debug_sleep(millisec)

    Sleeps for the given number of milliseconds. This function is used during performance measurement.

    debug_sleep() is available for debug builds only.

    Arguments:

    • millisec: An unsigned integer that specifies the number of milliseconds to sleep.

  • find_in_exclude_list(account)

    Checks whether an account string exists in the audit log exclude list (the value of the audit_log_exclude_accounts system variable).

    Arguments:

    • account: A string that specifies the user account name.

  • find_in_include_list(account)

    Checks whether an account string exists in the audit log include list (the value of the audit_log_include_accounts system variable).

    Arguments:

    • account: A string that specifies the user account name.

  • string_find(text, substr)

    Checks whether the substr value is contained in the text value. This search is case-sensitive.

    Arguments:

    • text: The text string to search.

    • substr: The substring to search for in text.

Replacing a User Filter

In some cases, the filter definition can be changed dynamically. To do this, define a filter configuration within an existing filter. For example:

{
  "filter": {
    "id": "main",
    "class": {
      "name": "table_access",
      "event": {
        "name": [ "update", "delete" ],
        "log": false,
        "filter": {
          "class": {
            "name": "general",
            "event" : { "name": "status",
                        "filter": { "ref": "main" } }
          },
          "activate": {
            "or": [
              { "field": { "name": "table_name.str", "value": "temp_1" } },
              { "field": { "name": "table_name.str", "value": "temp_2" } }
            ]
          }
        }
      }
    }
  }
}

A new filter is activated when the activate element within a subfilter evaluates to true. Using activate in a top-level filter is not permitted.

A new filter can be replaced with the original one by using a ref item inside the subfilter to refer to the original filter id.

The filter shown operates like this:

  • The main filter waits for table_access events, either update or delete.

  • If the update or delete table_access event occurs on the temp_1 or temp_2 table, the filter is replaced with the internal one (without an id, since there is no need to refer to it explicitly).

  • If the end of the command is signalled (general / status event), an entry is written to the audit log file and the filter is replaced with the main filter.

The filter is useful to log statements that update or delete anything from the temp_1 or temp_2 tables, such as this one:

UPDATE temp_1, temp_3 SET temp_1.a=21, temp_3.a=23;

The statement generates multiple table_access events, but the audit log file will contain only general / status entries.

Note

Any id values used in the definition are evaluated with respect only to that definition. They have nothing to do with the value of the audit_log_filter_id system variable.

6.4.5.8 Legacy Mode Audit Log Filtering

Note

This section describes legacy audit log filtering, which applies if the audit_log plugin is installed but not the accompanying audit tables and UDFs needed for rule-based filtering.

The audit log plugin can filter audited events. This enables you to control whether audited events are written to the audit log file based on the account from which events originate or event status. Status filtering occurs separately for connection events and statement events.

Event Filtering by Account

To filter audited events based on the originating account, set one of these system variables at server startup or runtime:

  • audit_log_include_accounts: The accounts to include in audit logging. If this variable is set, only these accounts are audited.

  • audit_log_exclude_accounts: The accounts to exclude from audit logging. If this variable is set, all but these accounts are audited.

The value for either variable can be NULL or a string containing one or more comma-separated account names, each in user_name@host_name format. By default, both variables are NULL, in which case, no account filtering is done and auditing occurs for all accounts.

Modifications to audit_log_include_accounts or audit_log_exclude_accounts affect only connections created subsequent to the modification, not existing connections.

Example: To enable audit logging only for the user1 and user2 local host account accounts, set the audit_log_include_accounts system variable like this:

SET GLOBAL audit_log_include_accounts = 'user1@localhost,user2@localhost';

Only one of audit_log_include_accounts or audit_log_exclude_accounts can be non-NULL at a time:

value
value
value

If you inspect the value of either variable, be aware that SHOW VARIABLES displays NULL as an empty string. To avoid this, use SELECT instead:

SHOW VARIABLES LIKE 'audit_log_include_accounts';
SELECT @@audit_log_include_accounts;

If a user name or host name requires quoting because it contains a comma, space, or other special character, quote it using single quotes. If the variable value itself is quoted with single quotes, double each inner single quote or escape it with a backslash. The following statements each enable audit logging for the local root account and are equivalent, even though the quoting styles differ:

SET GLOBAL audit_log_include_accounts = 'root@localhost';
SET GLOBAL audit_log_include_accounts = '''root''@''localhost''';
SET GLOBAL audit_log_include_accounts = '\'root\'@\'localhost\'';
SET GLOBAL audit_log_include_accounts = "'root'@'localhost'";

The last statement will not work if the ANSI_QUOTES SQL mode is enabled because in that mode double quotes signify identifier quoting, not string quoting.

Event Filtering by Status

To filter audited events based on status, set the following system variables at server startup or runtime. These variables apply only for legacy audit log filtering. For JSON audit log filtering, different status variables apply; see Audit Log Options and Variables.

Each variable takes a value of ALL (log all associated events; this is the default), ERRORS (log only failed events), or NONE (do not log events). For example, to log all statement events but only failed connection events, use these settings:

SET GLOBAL audit_log_statement_policy = ALL;
SET GLOBAL audit_log_connection_policy = ERRORS;

Another policy system variable, audit_log_policy, is available but does not afford as much control as audit_log_connection_policy and audit_log_statement_policy. It can be set only at server startup. At runtime, it is a read-only variable. It takes a value of ALL (log all events; this is the default), LOGINS (log connection events), QUERIES (log statement events), or NONE (do not log events). For any of those values, the audit log plugin logs all selected events without distinction as to success or failure. Use of audit_log_policy at startup works as follows:

  • If you do not set audit_log_policy or set it to its default of ALL, any explicit settings for audit_log_connection_policy or audit_log_statement_policy apply as specified. If not specified, they default to ALL.

  • If you set audit_log_policy to a non-ALL value, that value takes precedence over and is used to set audit_log_connection_policy and audit_log_statement_policy, as indicated in the following table. If you also set either of those variables to a value other than their default of ALL, the server writes a message to the error log to indicate that their values are being overridden.

    Startup audit_log_policy ValueResulting audit_log_connection_policy ValueResulting audit_log_statement_policy Value
    LOGINS ALL NONE
    QUERIES NONE ALL
    NONE NONE NONE

6.4.5.9 Audit Log Reference

The following discussion serves as a reference to MySQL Enterprise Audit components:

To install the audit log tables and functions, use the instructions provided in Section 6.4.5.2, “Installing or Uninstalling MySQL Enterprise Audit”. Unless those components are installed, the audit_log plugin operates in legacy mode. See Section 6.4.5.8, “Legacy Mode Audit Log Filtering”.

Audit Log Tables

MySQL Enterprise Audit uses tables in the mysql system database for persistent storage of filter and user account data. The tables can be accessed only by users with privileges for that database. The tables use the InnoDB storage engine.

If these tables are missing, the audit_log plugin operates in legacy mode. See Section 6.4.5.8, “Legacy Mode Audit Log Filtering”.

The audit_log_filter table stores filter definitions. The table has these columns:

  • NAME

    The filter name.

  • FILTER

    The filter definition associated with the filter name. Definitions are stored as JSON values.

The audit_log_user table stores user account information. The table has these columns:

  • USER

    The user name part of an account. For an account user1@localhost, the USER part is user1.

  • HOST

    The host name part of an account. For an account user1@localhost, the HOST part is localhost.

  • FILTERNAME

    The name of the filter assigned to the account. The filter name associates the account with a filter defined in the audit_log_filter table.

Audit Log Functions

This section describes, for each audit log user-defined function (UDF), its purpose, calling sequence, and return value. For information about the conditions under which these UDFs can be invoked, see Section 6.4.5.6, “Audit Log Filtering”.

Each audit log UDF returns a string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

As of MySQL 8.0.19, audit log UDFs convert string arguments to utf8mb4 and string return values are utf8mb4 strings. Prior to MySQL 8.0.19, audit log UDFs treat string arguments as binary strings (which means they do not distinguish lettercase), and string return values are binary strings.

These audit log UDFs are available:

  • audit_log_encryption_password_get([keyring_id])

    This function fetches an audit log encryption password from the MySQL keyring, which must be enabled or an error occurs. Any keyring plugin can be used; for instructions, see Section 6.4.4, “The MySQL Keyring”.

    With no argument, the function retrieves the current encryption password as a binary string. An argument may be given to specify which audit log encryption password to retrieve. The argument must be the keyring ID of the current password or an archived password.

    For additional information about audit log encryption, see Audit Log File Encryption.

    Arguments:

    keyring_id: As of MySQL 8.0.17, this optional argument indicates the keyring ID of the password to retrieve. The maximum permitted length is 766 bytes. If omitted, the function retrieves the current password.

    Prior to MySQL 8.0.17, no argument is permitted. The function always retrieves the current password.

    Return value:

    The password string for success (up to 766 bytes), or NULL and an error for failure.

    Example:

    Retrieve the current password:

    mysql> SELECT audit_log_encryption_password_get();
+-------------------------------------+
| audit_log_encryption_password_get() |
+-------------------------------------+
| secret                              |
+-------------------------------------+

    To retrieve a password by ID, you can determine which audit log keyring IDs exist by querying the Performance Schema keyring_keys table:

    		SELECT * FROM performance_schema.keyring_keys
    		WHERE KEY_ID LIKE 'audit_log%'
    		ORDER BY KEY_ID;
    		SELECT audit_log_encryption_password_get('audit_log-20190416T125122-1');

  • audit_log_encryption_password_set(password)

    Sets the current audit log encryption password to the argument and stores the password in the MySQL keyring. As of MySQL 8.0.19, the password is stored as a utf8mb4 string. Prior to MySQL 8.0.19, the password is stored in binary form.

    If encryption is enabled, this function performs a log file rotation operation that renames the current log file, and begins a new log file encrypted with the password. The keyring must be enabled or an error occurs. Any keyring plugin can be used; for instructions, see Section 6.4.4, “The MySQL Keyring”.

    For additional information about audit log encryption, see Audit Log File Encryption.

    Arguments:

    password: The password string. The maximum permitted length is 766 bytes.

    Return value:

    1 for success, 0 for failure.

    Example:

    		SELECT audit_log_encryption_password_set(password);
    		password
    		password

  • audit_log_filter_flush()

    Calling any of the other filtering UDFs affects operational audit log filtering immediately and updates the audit log tables. If instead you modify the contents of those tables directly using statements such as INSERT, UPDATE, and DELETE, the changes do not affect filtering immediately. To flush your changes and make them operational, call audit_log_filter_flush().

    Warning

    audit_log_filter_flush() should be used only after modifying the audit tables directly, to force reloading all filters. Otherwise, this function should be avoided. It is, in effect, a simplified version of unloading and reloading the audit_log plugin with UNINSTALL PLUGIN plus INSTALL PLUGIN.

    audit_log_filter_flush() affects all current sessions and detaches them from their previous filters. Current sessions are no longer logged unless they disconnect and reconnect, or execute a change-user operation.

    If this function fails, an error message is returned and the audit log is disabled until the next successful call to audit_log_filter_flush().

    Arguments:

    None.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

    mysql> SELECT audit_log_filter_flush();
+--------------------------+
| audit_log_filter_flush() |
+--------------------------+
| OK                       |
+--------------------------+

  • audit_log_filter_remove_filter(filter_name)

    Given a filter name, removes the filter from the current set of filters. It is not an error for the filter not to exist.

    If a removed filter is assigned to any user accounts, those users stop being filtered (they are removed from the audit_log_user table). Termination of filtering includes any current sessions for those users: They are detached from the filter and no longer logged.

    Arguments:

    • filter_name: A string that specifies the filter name.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

    mysql> SELECT audit_log_filter_remove_filter('SomeFilter');
+----------------------------------------------+
| audit_log_filter_remove_filter('SomeFilter') |
+----------------------------------------------+
| OK                                           |
+----------------------------------------------+

  • audit_log_filter_remove_user(user_name)

    Given a user account name, cause the user to be no longer assigned to a filter. It is not an error if the user has no filter assigned. Filtering of current sessions for the user remains unaffected. New connections for the user are filtered using the default account filter if there is one, and are not logged otherwise.

    If the name is %, the function removes the default account filter that is used for any user account that has no explicitly assigned filter.

    Arguments:

    • user_name: The user account name as a string in user_name@host_name format, or % to represent the default account.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

    mysql> SELECT audit_log_filter_remove_user('user1@localhost');
+-------------------------------------------------+
| audit_log_filter_remove_user('user1@localhost') |
+-------------------------------------------------+
| OK                                              |
+-------------------------------------------------+

  • audit_log_filter_set_filter(filter_name, definition)

    Given a filter name and definition, adds the filter to the current set of filters. If the filter already exists and is used by any current sessions, those sessions are detached from the filter and are no longer logged. This occurs because the new filter definition has a new filter ID that differs from its previous ID.

    Arguments:

    • filter_name: A string that specifies the filter name.

    • definition: A JSON value that specifies the filter definition.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

    		SET @f = '{ "filter": { "log": false } }';
    		SELECT audit_log_filter_set_filter('SomeFilter', @f);

  • audit_log_filter_set_user(user_name, filter_name)

    Given a user account name and a filter name, assigns the filter to the user. A user can be assigned only one filter, so if the user was already assigned a filter, the assignment is replaced. Filtering of current sessions for the user remains unaffected. New connections are filtered using the new filter.

    As a special case, the name % represents the default account. The filter is used for connections from any user account that has no explicitly assigned filter.

    Arguments:

    • user_name: The user account name as a string in user_name@host_name format, or % to represent the default account.

    • filter_name: A string that specifies the filter name.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

    mysql> SELECT audit_log_filter_set_user('user1@localhost', 'SomeFilter');
+------------------------------------------------------------+
| audit_log_filter_set_user('user1@localhost', 'SomeFilter') |
+------------------------------------------------------------+
| OK                                                         |
+------------------------------------------------------------+

  • audit_log_read([arg])

    Reads events from the audit log and returns a binary JSON string containing an array of audit events. If the audit log format is not JSON, an error occurs.

    Each event in the return value is a JSON hash, except that the last array element may be a JSON null value to indicate no following events are available to read.

    For the first call to audit_log_read() within a session, pass a bookmark indicating where to begin reading. If the final value of the returned array is not a JSON null value, there are more events following those just read and audit_log_read() can be called without or with a bookmark argument. Without an argument, reading continues with the next unread event. With a bookmark argument, reading continues from the bookmark.

    If the final value of the returned array is a JSON null value, there are no more events left to be read and the next call to audit_log_read() must include a bookmark argument.

    To obtain a bookmark for the most recently written event, call audit_log_read_bookmark().

    For additional information about audit log-reading functions, see Audit Log File Reading.

    Arguments:

    arg: An optional bookmark, represented as a string containing a JSON hash that indicates where and how much to read. The following items are significant in the arg value (other items are ignored):

    • timestamp, id: The location within the audit log of the first event to read. Both items must be present to completely specify a position.

    • max_array_length: The maximum number of events to read from the log. If omitted, the default is to read to the end of the log or until the read buffer is full, whichever comes first.

    Return value:

    A binary JSON string containing an array of audit events for success, or NULL and an error for failure.

    Example:

    mysql> SELECT audit_log_read(audit_log_read_bookmark());
+-----------------------------------------------------------------------+
| audit_log_read(audit_log_read_bookmark())                             |
+-----------------------------------------------------------------------+
| [ {"timestamp":"2019-10-03 22:41:24","id":0,"class":"connection", ... |
+-----------------------------------------------------------------------+

  • audit_log_read_bookmark()

    Returns a binary JSON string representing a bookmark for the most recently written audit log event. If the audit log format is not JSON, an error occurs.

    The bookmark is a JSON hash with timestamp and id items indicating the event position within the audit log. It is suitable for passing to audit_log_read() to indicate to that function where to begin reading.

    For additional information about audit log-reading functions, see Audit Log File Reading.

    Arguments:

    None.

    Return value:

    A binary JSON string containing a bookmark for success, or NULL and an error for failure.

    Example:

    mysql> SELECT audit_log_read_bookmark();
+-------------------------------------------------+
| audit_log_read_bookmark()                       |
+-------------------------------------------------+
| { "timestamp": "2019-10-03 21:03:44", "id": 0 } |
+-------------------------------------------------+
Audit Log Option and Variable Reference

Table 6.37 Audit Log Option and Variable Reference

NameCmd-LineOption FileSystem VarStatus VarVar ScopeDynamic
audit-log Yes Yes        
audit_log_buffer_size Yes Yes Yes   Global No
audit_log_compression Yes Yes Yes   Global No
audit_log_connection_policy Yes Yes Yes   Global Yes
audit_log_current_session     Yes   Both No
Audit_log_current_size       Yes Global No
audit_log_encryption Yes Yes Yes   Global No
Audit_log_event_max_drop_size       Yes Global No
Audit_log_events       Yes Global No
Audit_log_events_filtered       Yes Global No
Audit_log_events_lost       Yes Global No
Audit_log_events_written       Yes Global No
audit_log_exclude_accounts Yes Yes Yes   Global Yes
audit_log_file Yes Yes Yes   Global No
audit_log_filter_id     Yes   Both No
audit_log_flush     Yes   Global Yes
audit_log_format Yes Yes Yes   Global No
audit_log_include_accounts Yes Yes Yes   Global Yes
audit_log_password_history_keep_days Yes Yes Yes   Global Yes
audit_log_policy Yes Yes Yes   Global No
audit_log_read_buffer_size Yes Yes Yes   Varies Varies
audit_log_rotate_on_size Yes Yes Yes   Global Yes
audit_log_statement_policy Yes Yes Yes   Global Yes
audit_log_strategy Yes Yes Yes   Global No
Audit_log_total_size       Yes Global No
Audit_log_write_waits       Yes Global No
Audit Log Options and Variables

This section describes the command options and system variables that configure operation of MySQL Enterprise Audit. If values specified at startup time are incorrect, the audit_log plugin may fail to initialize properly and the server does not load it. In this case, the server may also produce error messages for other audit log settings because it will not recognize them.

To configure activation of the audit log plugin, use this option:

If the audit log plugin is enabled, it exposes several system variables that permit control over logging:

mysql> SHOW VARIABLES LIKE 'audit_log%';
+-----------------------------+--------------+
| Variable_name               | Value        |
+-----------------------------+--------------+
| audit_log_buffer_size       | 1048576      |
| audit_log_connection_policy | ALL          |
| audit_log_current_session   | OFF          |
| audit_log_exclude_accounts  |              |
| audit_log_file              | audit.log    |
| audit_log_filter_id         | 0            |
| audit_log_flush             | OFF          |
| audit_log_format            | NEW          |
| audit_log_include_accounts  |              |
| audit_log_policy            | ALL          |
| audit_log_rotate_on_size    | 0            |
| audit_log_statement_policy  | ALL          |
| audit_log_strategy          | ASYNCHRONOUS |
+-----------------------------+--------------+

You can set any of these variables at server startup, and some of them at runtime. Those that are available only for legacy mode audit log filtering are so noted.

  • audit_log_buffer_size

    PropertyValue
    Command-Line Format --audit-log-buffer-size=#
    System Variable audit_log_buffer_size
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1048576
    Minimum Value 4096
    Maximum Value (64-bit platforms) 18446744073709547520
    Maximum Value (32-bit platforms) 4294967295

    When the audit log plugin writes events to the log asynchronously, it uses a buffer to store event contents prior to writing them. This variable controls the size of that buffer, in bytes. The server adjusts the value to a multiple of 4096. The plugin uses a single buffer, which it allocates when it initializes and removes when it terminates. The plugin allocates this buffer only if logging is asynchronous.

  • audit_log_compression

    PropertyValue
    Command-Line Format --audit-log-compression=value
    System Variable audit_log_compression
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value NONE
    Valid Values

    NONE

    GZIP

    The type of compression for the audit log file. Permitted values are NONE (no compression; the default) and GZIP (GNU Zip compression). For more information, see Audit Log File Compression.

  • audit_log_connection_policy

    PropertyValue
    Command-Line Format --audit-log-connection-policy=value
    System Variable audit_log_connection_policy
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value ALL
    Valid Values

    ALL

    ERRORS

    NONE
    Note

    This variable applies only to legacy mode audit log filtering (see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”).

    The policy controlling how the audit log plugin writes connection events to its log file. The following table shows the permitted values.

    ValueDescription
    ALL Log all connection events
    ERRORS Log only failed connection events
    NONE Do not log connection events
    Note

    At server startup, any explicit value given for audit_log_connection_policy may be overridden if audit_log_policy is also specified, as described in Section 6.4.5.5, “Audit Log Logging Configuration”.

  • audit_log_current_session

    PropertyValue
    System Variable audit_log_current_session
    Scope Global, Session
    Dynamic No
    SET_VAR Hint Applies No
    Type Boolean
    Default Value depends on filtering policy

    Whether audit logging is enabled for the current session. The session value of this variable is read only. It is set when the session begins based on the values of the audit_log_include_accounts and audit_log_exclude_accounts system variables. The audit log plugin uses the session value to determine whether to audit events for the session. (There is a global value, but the plugin does not use it.)

  • audit_log_encryption

    PropertyValue
    Command-Line Format --audit-log-encryption=value
    System Variable audit_log_encryption
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value NONE
    Valid Values

    NONE

    AES

    The type of encryption for the audit log file. Permitted values are NONE (no encryption; the default) and AES (AES-256-CBC cipher encryption). For more information, see Audit Log File Encryption.

  • audit_log_exclude_accounts

    PropertyValue
    Command-Line Format --audit-log-exclude-accounts=value
    System Variable audit_log_exclude_accounts
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value NULL
    Note

    This variable applies only to legacy mode audit log filtering (see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”).

    The accounts for which events should not be logged. The value should be NULL or a string containing a list of one or more comma-separated account names. For more information, see Section 6.4.5.6, “Audit Log Filtering”.

    Modifications to audit_log_exclude_accounts affect only connections created subsequent to the modification, not existing connections.

  • audit_log_file

    PropertyValue
    Command-Line Format --audit-log-file=file_name
    System Variable audit_log_file
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type File name
    Default Value audit.log

    The base name and suffix of the file to which the audit log plugin writes events. The default value is audit.log, regardless of logging format. To have the name suffix correspond to the format, set the name explicitly, choosing a different suffix (for example, audit.xml for XML format, audit.json for JSON format).

    If the value of audit_log_file is a relative path name, the plugin interprets it relative to the data directory. If the value is a full path name, the plugin uses the value as is. A full path name may be useful if it is desirable to locate audit files on a separate file system or directory. For security reasons, write the audit log file to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log.

    For details about how the audit log plugin interprets the audit_log_file value and the rules for file renaming that occurs at plugin initialization and termination, see Audit Log File Name.

    The audit log plugin uses the directory containing the audit log file (determined from the audit_log_file value) as the location to search for readable audit log files. From these log files and the current file, the plugin constructs a list of the ones that are subject to use with the audit log bookmarking and reading functions. See Audit Log File Reading.

  • audit_log_filter_id

    PropertyValue
    System Variable audit_log_filter_id
    Scope Global, Session
    Dynamic No
    SET_VAR Hint Applies No
    Type Integer

    The session value of this variable indicates the internally maintained ID of the audit filter for the current session. A value of 0 means that the session has no filter assigned.

  • audit_log_flush

    PropertyValue
    System Variable audit_log_flush
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    When this variable is set to enabled (1 or ON), the audit log plugin closes and reopens its log file to flush it. (The value remains OFF so that you need not disable it explicitly before enabling it again to perform another flush.) Enabling this variable has no effect unless audit_log_rotate_on_size is 0. For more information, see Section 6.4.5.5, “Audit Log Logging Configuration”.

  • audit_log_format

    PropertyValue
    Command-Line Format --audit-log-format=value
    System Variable audit_log_format
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value NEW
    Valid Values

    OLD

    NEW

    JSON

    The audit log file format. Permitted values are OLD (old-style XML), NEW (new-style XML; the default), and JSON. For details about each format, see Section 6.4.5.4, “Audit Log File Formats”.

    Note

    For information about issues to consider when changing the log format, see Audit Log File Format.

  • audit_log_include_accounts

    PropertyValue
    Command-Line Format --audit-log-include-accounts=value
    System Variable audit_log_include_accounts
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value NULL
    Note

    This variable applies only to legacy mode audit log filtering (see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”).

    The accounts for which events should be logged. The value should be NULL or a string containing a list of one or more comma-separated account names. For more information, see Section 6.4.5.6, “Audit Log Filtering”.

    Modifications to audit_log_include_accounts affect only connections created subsequent to the modification, not existing connections.

  • audit_log_password_history_keep_days

    PropertyValue
    Command-Line Format --audit-log-password-history-keep-days=#
    Introduced 8.0.17
    System Variable audit_log_password_history_keep_days
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 4294967295

    When a new encryption password is created, the audit log plugin archives the previous password, if one exists, for later use. The audit_log_password_history_keep_days variable controls automatic removal of archived passwords. Its value indicates the number of days after which archived audit log encryption passwords are removed. The default is 0, which disables password removal; the password retention period is forever.

    New audit log encryption passwords are created under these circumstances:

    • During plugin initialization, if the plugin finds that log file encryption is enabled, it checks whether the keyring contains an audit log encryption password. If not, the plugin automatically generates a random initial encryption password.

    • When the audit_log_encryption_password_set() function is called to set a specific password.

    In each case, the plugin stores the password in the key ring and uses it to encrypt new log files.

    Cleanup of old audit log encryption passwords occurs under these circumstances:

    • During plugin initialization.

    • When the audit_log_encryption_password_set() function is called.

    • When the runtime value of audit_log_password_history_keep_days is changed from its current value to a value greater than 0. Runtime value changes may occur for SET statements that use the GLOBAL or PERSIST keyword, but not the PERSIST_ONLY keyword. PERSIST_ONLY writes the variable setting to mysqld-auto.cnf, but has no effect on the runtime value.

    When password cleanup occurs, the current value of audit_log_password_history_keep_days determines which passwords to remove:

    • If the value is N > 0, the plugin removes passwords more than N days old.

    • If the value is 0, the plugin removes no passwords.

    Note

    Take care not to expire old passwords that are still needed to read archived encrypted log files.

    If you normally leave password expiration disabled (audit_log_password_history_keep_days=0), it is possible to perform an on-demand cleanup operation by temporarily assigning the variable a value greater than zero. For example, to expire passwords older than 365 days, do this:

    SET GLOBAL audit_log_password_history_keep_days = 365;
SET GLOBAL audit_log_password_history_keep_days = 0;

    Setting the runtime value of audit_log_password_history_keep_days requires the AUDIT_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN or SUPER privilege normally required to set a global system variable runtime value.

  • audit_log_policy

    PropertyValue
    Command-Line Format --audit-log-policy=value
    System Variable audit_log_policy
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value ALL
    Valid Values

    ALL

    LOGINS

    QUERIES

    NONE
    Note

    This variable applies only to legacy mode audit log filtering (see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”).

    The policy controlling how the audit log plugin writes events to its log file. The following table shows the permitted values.

    ValueDescription
    ALL Log all events
    LOGINS Log only login events
    QUERIES Log only query events
    NONE Log nothing (disable the audit stream)

    audit_log_policy can be set only at server startup. At runtime, it is a read-only variable. Two other system variables, audit_log_connection_policy and audit_log_statement_policy, provide finer control over logging policy and can be set either at startup or at runtime. If you use audit_log_policy at startup instead of the other two variables, the server uses its value to set those variables. For more information about the policy variables and their interaction, see Section 6.4.5.5, “Audit Log Logging Configuration”.

  • audit_log_read_buffer_size

    PropertyValue
    Command-Line Format --audit-log-read-buffer-size=#
    System Variable audit_log_read_buffer_size
    Scope (>= 8.0.12) Global, Session
    Scope (8.0.11) Global
    Dynamic (>= 8.0.12) Yes
    Dynamic (8.0.11) No
    SET_VAR Hint Applies No
    Type Integer
    Default Value (>= 8.0.12) 32768
    Default Value (8.0.11) 1048576
    Minimum Value (>= 8.0.12) 32768
    Minimum Value (8.0.11) 1024
    Maximum Value 4194304

    The buffer size for reading from the audit log file, in bytes. The audit_log_read() function reads no more than this many bytes. Log file reading is supported only for JSON log format. For more information, see Audit Log File Reading.

    As of MySQL 8.0.12, this variable has a default of 32KB and can be set at runtime. Each client should set its session value of audit_log_read_buffer_size appropriately for its use of audit_log_read(). Prior to MySQL 8.0.12, audit_log_read_buffer_size has a default of 1MB, affects all clients, and can be changed only at server startup.

  • audit_log_rotate_on_size

    PropertyValue
    Command-Line Format --audit-log-rotate-on-size=#
    System Variable audit_log_rotate_on_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 0

    If the audit_log_rotate_on_size value is 0, the audit log plugin does not perform automatic log file rotation. Instead, use audit_log_flush to close and reopen the log on demand. In this case, manually rename the file externally to the server before flushing it.

    If the audit_log_rotate_on_size value is greater than 0, automatic size-based log file rotation occurs. Whenever a write to the log file causes its size to exceed the audit_log_rotate_on_size value, the audit log plugin closes the current log file, renames it, and opens a new log file.

    For more information about audit log file rotation, see Audit Log File Space Management and Name Rotation.

    If you set this variable to a value that is not a multiple of 4096, it is truncated to the nearest multiple. (Thus, setting it to a value less than 4096 has the effect of setting it to 0 and no rotation occurs, except manually.)

  • audit_log_statement_policy

    PropertyValue
    Command-Line Format --audit-log-statement-policy=value
    System Variable audit_log_statement_policy
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value ALL
    Valid Values

    ALL

    ERRORS

    NONE
    Note

    This variable applies only to legacy mode audit log filtering (see Section 6.4.5.8, “Legacy Mode Audit Log Filtering”).

    The policy controlling how the audit log plugin writes statement events to its log file. The following table shows the permitted values.

    ValueDescription
    ALL Log all statement events
    ERRORS Log only failed statement events
    NONE Do not log statement events
    Note

    At server startup, any explicit value given for audit_log_statement_policy may be overridden if audit_log_policy is also specified, as described in Section 6.4.5.5, “Audit Log Logging Configuration”.

  • audit_log_strategy

    PropertyValue
    Command-Line Format --audit-log-strategy=value
    System Variable audit_log_strategy
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value ASYNCHRONOUS
    Valid Values

    ASYNCHRONOUS

    PERFORMANCE

    SEMISYNCHRONOUS

    SYNCHRONOUS

    The logging method used by the audit log plugin. These strategy values are permitted:

    • ASYNCHRONOUS: Log asynchronously. Wait for space in the output buffer.

    • PERFORMANCE: Log asynchronously. Drop requests for which there is insufficient space in the output buffer.

    • SEMISYNCHRONOUS: Log synchronously. Permit caching by the operating system.

    • SYNCHRONOUS: Log synchronously. Call sync() after each request.

Audit Log Status Variables

If the audit log plugin is enabled, it exposes several status variables that provide operational information. These variables are available for legacy mode audit filtering and JSON mode audit filtering.

6.4.5.10 Audit Log Restrictions

MySQL Enterprise Audit is subject to these general restrictions:

  • Only SQL statements are logged. Changes made by no-SQL APIs, such as memcached, Node.JS, and the NDB API, are not logged.

  • Only top-level statements are logged, not statements within stored programs such as triggers or stored procedures.

  • Contents of files referenced by statements such as LOAD DATA are not logged.

NDB Cluster.  It is possible to use MySQL Enterprise Audit with MySQL NDB Cluster, subject to the following conditions:

  • All changes to be logged must be done using the SQL interface. Changes using no-SQL interfaces, such as those provided by the NDB API, memcached, or ClusterJ, are not logged.

  • The plugin must be installed on each MySQL server that is used to execute SQL on the cluster.

  • Audit plugin data must be aggregated amongst all MySQL servers used with the cluster. This aggregation is the responsibility of the application or user.

6.4.6 The Audit Message Component

As of MySQL 8.0.14, the audit_api_message_emit component enables applications to add their own message events to the audit log, using the audit_api_message_emit_udf() user-defined function.

The audit_api_message_emit component cooperates with all plugins of audit type. For concreteness, examples use the audit_log plugin described in Section 6.4.5, “MySQL Enterprise Audit”.

Installing or Uninstalling the Audit Message Component

To be usable by the server, the component library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

To install the audit_api_message_emit component, use this statement:

INSTALL COMPONENT "file://component_audit_api_message_emit";

Component installation is a one-time operation that need not be done per server startup. INSTALL COMPONENT loads the component, and also registers it in the mysql.component system table to cause it to be loaded during subsequent server startups.

To uninstall the audit_api_message_emit component, use this statement:

UNINSTALL COMPONENT "file://component_audit_api_message_emit";

UNINSTALL COMPONENT unloads the component, and deregisters it from the mysql.component system table to cause it not to be loaded during subsequent server startups.

Installing or uninstalling the audit_api_message_emit component installs or uninstalls the audit_api_message_emit_udf() function that the component implements. It is not necessary to use CREATE FUNCTION or DROP FUNCTION to do so.

Audit Message Function

This section describes the audit_api_message_emit_udf() user-defined function (UDF) implemented by the audit_api_message_emit component.

Before using the audit message function, install the audit message component according to the instructions provided at Installing or Uninstalling the Audit Message Component.

  • audit_api_message_emit_udf(component, producer, message[, key, value] ...)

    Adds a message event to the audit log. Message events include component, producer, and message strings of the caller's choosing, and optionally a set of key-value pairs.

    An event posted by this UDF is sent to all enabled plugins of audit type, each of which handles the event according to its own rules. If no plugin of audit type is enabled, posting the event has no effect.

    Arguments:

    • component: A string that specifies a component name.

    • producer: A string that specifies a producer name.

    • message: A string that specifies the event message.

    • key, value: Events may include 0 or more key-value pairs that specify an arbitrary application-provided data map. Each key argument is a string that specifies a name for its immediately following value argument. Each value argument specifies a value for its immediately following key argument. Each value can be a string or numeric value, or NULL.

    Return value:

    The string OK to indicate success. An error occurs if the function fails.

    Example:

    		SELECT audit_api_message_emit_udf('component_text',
    		'producer_text',
    		'message_text',
    		'key1', 'value1',
    		'key2', 123,
    		'key3', NULL) AS 'Message';

    Additional information:

    Each audit plugin that receives an event posted by audit_api_message_emit_udf() logs the event in plugin-specific format. For example, the audit_log plugin (see Section 6.4.5, “MySQL Enterprise Audit”) logs message values as follows, depending on the log format configured by the audit_log_format system variable:

    • JSON format (audit_log_format=JSON):

      {
  ...
  "class": "message",
  "event": "user",
  ...
  "message_data": {
    "component": "component_text",
    "producer": "producer_text",
    "message": "message_text",
    "map": {
      "key1": "value1",
      "key2": 123,
      "key3": null
    }
  }
}

    • New-style XML format (audit_log_format=NEW):

      <AUDIT_RECORD>
 ...
 <NAME>Message</NAME>
 ...
 <COMMAND_CLASS>user</COMMAND_CLASS>
 <COMPONENT>component_text</COMPONENT>
 <PRODUCER>producer_text</PRODUCER>
 <MESSAGE>message_text</MESSAGE>
 <MAP>
   <ELEMENT>
     <KEY>key1</KEY>
     <VALUE>value1</VALUE>
   </ELEMENT>
   <ELEMENT>
     <KEY>key2</KEY>
     <VALUE>123</VALUE>
   </ELEMENT>
   <ELEMENT>
     <KEY>key3</KEY>
     <VALUE/>
   </ELEMENT>
 </MAP>
</AUDIT_RECORD>

    • Old-style XML format (audit_log_format=OLD):

      <AUDIT_RECORD
  ...
  NAME="Message"
  ...
  COMMAND_CLASS="user"
  COMPONENT="component_text"
  PRODUCER="producer_text"
MESSAGE="message_text"/>
      Note

      Message events logged in old-style XML format do not include the key-value map due to representational constraints imposed by this format.

    Messages posted by audit_api_message_emit_udf() have an event class of MYSQL_AUDIT_MESSAGE_CLASS and a subclass of MYSQL_AUDIT_MESSAGE_USER. (Interally generated audit messages have the same class and a subclass of MYSQL_AUDIT_MESSAGE_INTERNAL; this subclass currently is unused.) To refer to such events in audit_log filtering rules, use a class element with a name value of message. For example:

    {
  "filter": {
    "class": {
      "name": "message"
    }
  }
}

    Should it be necessary to distinguish user-generated and internally generated message events, test the subclass value against user or internal.

    Filtering based on the contents of the key-value map is not supported.

    For information about writing filtering rules, see Section 6.4.5.6, “Audit Log Filtering”.

6.4.7 MySQL Enterprise Firewall

6.4.7.1 MySQL Enterprise Firewall Components
6.4.7.2 Installing or Uninstalling MySQL Enterprise Firewall
6.4.7.3 Using MySQL Enterprise Firewall
6.4.7.4 MySQL Enterprise Firewall Reference
Note

MySQL Enterprise Firewall is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

MySQL Enterprise Edition includes MySQL Enterprise Firewall, an application-level firewall that enables database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics.

Each MySQL account registered with the firewall has its own statement whitelist, enabling protection to be tailored per account. For a given account, the firewall can operate in recording, protecting, or detecting mode, for training in the accepted statement patterns, active protection against unacceptable statements, or passive detection of unacceptable statements. The diagram illustrates how the firewall processes incoming statements in each mode.

Figure 6.1 MySQL Enterprise Firewall Operation

Flow chart showing how MySQL Enterprise Firewall processes incoming SQL statements in recording, protecting, and detecting modes.

The following sections describe the components of MySQL Enterprise Firewall, discuss how to install and use it, and provide reference information for its components.

6.4.7.1 MySQL Enterprise Firewall Components

MySQL Enterprise Firewall is based on a plugin library that implements these components:

  • A server-side plugin named MYSQL_FIREWALL examines SQL statements before they execute and, based on its in-memory cache, renders a decision whether to execute or reject each statement.

  • Server-side plugins named MYSQL_FIREWALL_USERS and MYSQL_FIREWALL_WHITELIST implement INFORMATION_SCHEMA tables that provide views into the firewall data cache.

  • System tables named firewall_users and firewall_whitelist in the mysql database provide persistent storage of firewall data.

  • Stored procedures named sp_set_firewall_mode() and sp_reload_firewall_rules() perform tasks such as registering MySQL accounts with the firewall, establishing their operational mode, and managing transfer of firewall data between the cache and the underlying system tables.

  • A set of user-defined functions provides an SQL-level API for lower-level tasks such as synchronizing the cache with the underlying system tables.

  • System variables enable firewall configuration and status variables provide runtime operational information.

  • FIREWALL_ADMIN and FIREWALL_USER privileges enable users to administer firewall rules for any user, and their own firewall rules, respectively.

6.4.7.2 Installing or Uninstalling MySQL Enterprise Firewall

MySQL Enterprise Firewall installation is a one-time operation that installs the components described in Section 6.4.7.1, “MySQL Enterprise Firewall Components”. Installation can be performed using a graphical interface or manually:

  • On Windows, MySQL Installer includes an option to enable MySQL Enterprise Firewall for you.

  • MySQL Workbench 6.3.4 or higher can install MySQL Enterprise Firewall, enable or disable an installed firewall, or uninstall the firewall.

  • Manual MySQL Enterprise Firewall installation involves running a script located in the share directory of your MySQL installation.

Important

Read this entire section before following its instructions. Parts of the procedure differ depending on your environment.

Note

If installed, MySQL Enterprise Firewall involves some minimal overhead even when disabled. To avoid this overhead, do not install the firewall unless you plan to use it.

For usage instructions, see Section 6.4.7.3, “Using MySQL Enterprise Firewall”. For reference information, see Section 6.4.7.4, “MySQL Enterprise Firewall Reference”.

Installing MySQL Enterprise Firewall

If MySQL Enterprise Firewall is already installed from an older version of MySQL, uninstall it using the instructions given later in this section and then restart your server before installing the current version. In this case, it is also necessary to register your configuration again.

On Windows, you can use MySQL Installer to install MySQL Enterprise Firewall, as shown in Figure 6.2, “MySQL Enterprise Firewall Installation on Windows”. Check the Enable MySQL Enterprise Firewall check box. (Open Firewall port for network access has a different purpose. It refers to Windows Firewall and controls whether Windows blocks the TCP/IP port on which the MySQL server listens for client connections.)

Important

There is an issue for MySQL 8.0.19 installed using MySQL Installer that prevents the server from starting if MySQL Enterprise Firewall is selected during the server configuration steps. If the server startup operation fails, click Cancel to end the configuration process and return to the dashboard. You must uninstall the server.

The workaround is to run MySQL Installer without MySQL Enterprise Firewall selected. (That is, do not select the Enable MySQL Enterprise Firewall check box.) Then install MySQL Enterprise Firewall afterward using the instructions for manual installation later in this section. This problem is corrected in MySQL 8.0.20.

Figure 6.2 MySQL Enterprise Firewall Installation on Windows

Content is described in the surrounding text.

To install MySQL Enterprise Firewall using MySQL Workbench 6.3.4 or higher, see MySQL Enterprise Firewall Interface.

To install MySQL Enterprise Firewall manually, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform. The available scripts differ in the suffix used to refer to the plugin library file:

  • win_install_firewall.sql: Choose this script for Windows systems that use .dll as the file name suffix.

  • linux_install_firewall.sql: Choose this script for Linux and similar systems that use .so as the file name suffix.

The installation script creates stored procedures in the default database, so choose a database to use. Then run the script as follows, naming the chosen database on the command line. The example here uses the mysql database and the Linux installation script. Make the appropriate substitutions for your system. 

mysql -u root -p mysql < linux_install_firewall.sql
(enter root password here)
Note

To use MySQL Enterprise Firewall in the context of master/slave replication, Group Replication, or InnoDB cluster, you must prepare the slave or secondary nodes prior to running the installation script on the master or primary node. This is necessary because the INSTALL PLUGIN statements in the script are not replicated.

  1. On each slave or secondary node, extract the INSTALL PLUGIN statements from the installation script and execute them manually.

  2. On the master or primary node, run the installation script as described previously.

Installing MySQL Enterprise Firewall either using a graphical interface or manually should enable the firewall. To verify that, connect to the server and execute this statement:

mysql> SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| mysql_firewall_mode | ON    |
+---------------------+-------+

If the plugin fails to initialize, check the server error log for diagnostic messages.

Uninstalling MySQL Enterprise Firewall

MySQL Enterprise Firewall can be uninstalled using MySQL Workbench or manually.

To uninstall MySQL Enterprise Firewall using MySQL Workbench 6.3.4 or higher, see MySQL Enterprise Firewall Interface.

To uninstall MySQL Enterprise Firewall manually, execute the following statements. It is assumed that the stored procedures were created in the mysql database. Adjust the DROP PROCEDURE statements appropriately if the procedures were created in a different database.

DROP TABLE mysql.firewall_whitelist;
DROP TABLE mysql.firewall_users;
UNINSTALL PLUGIN mysql_firewall;
UNINSTALL PLUGIN mysql_firewall_whitelist;
UNINSTALL PLUGIN mysql_firewall_users;
DROP FUNCTION set_firewall_mode;
DROP FUNCTION normalize_statement;
DROP FUNCTION read_firewall_whitelist;
DROP FUNCTION read_firewall_users;
DROP FUNCTION mysql_firewall_flush_status;
DROP PROCEDURE mysql.sp_set_firewall_mode;
DROP PROCEDURE mysql.sp_reload_firewall_rules;

6.4.7.3 Using MySQL Enterprise Firewall

Before using MySQL Enterprise Firewall, install it according to the instructions provided in Section 6.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”.

This section describes how to configure MySQL Enterprise Firewall using SQL statements. Alternatively, MySQL Workbench 6.3.4 or higher provides a graphical interface for firewall control. See MySQL Enterprise Firewall Interface.

To enable or disable the firewall, set the mysql_firewall_mode system variable. By default, this variable is enabled when the firewall is installed. To control the initial firewall state explicitly, you can set the variable at server startup. For example, to enable the firewall in an option file, use these lines:

[mysqld]
mysql_firewall_mode=ON

It is also possible to disable or enable the firewall at runtime:

SET GLOBAL mysql_firewall_mode = OFF;
SET GLOBAL mysql_firewall_mode = ON;

In addition to the global on/off firewall mode, each account registered with the firewall has its own operational mode. For an account in recording mode, the firewall learns an application's “fingerprint,” that is, the acceptable statement patterns that, taken together, form a whitelist. After training, switch the firewall to protecting mode to harden MySQL against access by statements that deviate from the fingerprint. For additional training, switch the firewall back to recording mode as necessary to update the whitelist with new statement patterns. An intrusion-detection mode is available that writes suspicious statements to the error log but does not deny access.

The firewall maintains whitelist rules on a per-account basis, enabling implementation of protection strategies such as these:

  • For an application that has unique protection requirements, configure it to use an account that is not used for any other purpose.

  • For applications that are related and share protection requirements, configure them as a group to use the same account.

Firewall operation is based on conversion of SQL statements to normalized digest form. Firewall digests are like the statement digests used by the Performance Schema (see Section 26.10, “Performance Schema Statement Digests and Sampling”). However, unlike the Performance Schema, the relevant digest-related system variable is max_digest_length.

For a connection from a registered account, the firewall converts each incoming statement to normalized form and processes it according to the account mode:

  • In recording mode, the firewall adds the normalized statement to the account whitelist rules.

  • In protecting mode, the firewall compares the normalized statement to the account whitelist rules. If there is a match, the statement passes and the server continues to process it. Otherwise, the server rejects the statement and returns an error to the client. The firewall also writes the rejected statement to the error log if the mysql_firewall_trace system variable is enabled.

  • In detecting mode, the firewall matches statements as in protecting mode, but writes nonmatching statements to the error log without denying access.

Accounts that have a mode of OFF or are not registered with the firewall are ignored by it.

To protect an account using MySQL Enterprise Firewall, follow these steps:

  1. Register the account and put it in recording mode.

  2. Connect to the MySQL server using the registered account and execute statements to be learned. This establishes the account's whitelist of accepted statements.

  3. Switch the registered account to protecting mode.

The following example shows how to register an account with the firewall, use the firewall to learn acceptable statements for that account, and protect the account against execution of unacceptable statements. The example account, 'fwuser'@'localhost', is for use by an application that accesses tables in the sakila database. (This database is available at https://dev.mysql.com/doc/index-other.html.)

Note

The user and host parts of the account name are quoted separately for statements such as CREATE USER and GRANT, whereas to specify an account for use with a firewall component, name it as a single quoted string 'fwuser@localhost'.

The convention for naming accounts as a single quoted string for firewall components means that you cannot use accounts that have embedded @ characters in the user name.

Perform the steps in the following procedure using an administrative MySQL account, except those designated for execution by the account registered with the firewall. The default database should be sakila for statements executed using the registered account.

  1. If necessary, create the account to be protected (choose an appropriate password) and grant it privileges for the sakila database:

    		CREATE USER 'fwuser'@'localhost' IDENTIFIED BY 'fWp@3sw0rd';
    		GRANT ALL ON sakila.* TO 'fwuser'@'localhost';

  2. Use the sp_set_firewall_mode() stored procedure to register the account with the firewall and place it in recording mode (if the procedure is located in a database other than mysql, adjust the statement accordingly):

    mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');

    During the course of its execution, the stored procedure invokes firewall user-defined functions, which may produce output of their own.

  3. Using the registered account, connect to the server, then execute some statements that are legitimate for it:

    		SELECT first_name, last_name FROM customer WHERE customer_id = 1;
    		UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
    		SELECT get_customer_balance(1, NOW());

    The firewall converts the statements to digest form and records them in the account whitelist.

    Note

    Until the account executes statements in recording mode, its whitelist is empty, which is equivalent to “deny all.” If switched to protecting mode, the account will be effectively prohibited from executing statements.

  4. At this point, the user and whitelist information is cached and can be seen in the firewall INFORMATION_SCHEMA tables:

    		SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS
    		WHERE USERHOST = 'fwuser@localhost';
    		SELECT RULE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST
    		WHERE USERHOST = 'fwuser@localhost';
    Note

    The @@version_comment rule comes from a statement sent automatically by the mysql client when you connect to the server as the registered user.

    It is important to train the firewall under conditions matching application use. For example, a given MySQL connector might send statements to the server at the beginning of a connection to determine server characteristics and capabilities. If an application normally is used through that connector, train the firewall that way, too. That enables those initial statements to become part of the whitelist for the account associated with the application.

  5. Use the stored procedure to switch the registered user to protecting mode:

    mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'PROTECTING');
    Important

    Switching the account out of RECORDING mode synchronizes its firewall cache data to the underlying mysql system database tables for persistent storage. If you do not switch the mode for a user who is being recorded, the cached whitelist data is not written to the system tables and will be lost when the server is restarted.

  6. Using the registered account, execute some acceptable and unacceptable statements. The firewall matches each one against the account whitelist and accepts or rejects it.

    This statement is not identical to a training statement but produces the same normalized statement as one of them, so the firewall accepts it:

    mysql> SELECT first_name, last_name FROM customer WHERE customer_id = '48';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| ANN        | EVANS     |
+------------+-----------+

    These statements do not match anything in the whitelist and each results in an error:

    		SELECT first_name, last_name FROM customer WHERE customer_id = 1 OR TRUE;
    		SHOW TABLES LIKE 'customer%';
    		TRUNCATE TABLE mysql.slow_log;

    The firewall also writes the rejected statements to the error log if the mysql_firewall_trace system variable is enabled. For example:

    [Note] Plugin MYSQL_FIREWALL reported:
'ACCESS DENIED for fwuser@localhost. Reason: No match in whitelist.
Statement: TRUNCATE TABLE `mysql` . `slow_log` '

    You can use these log messages in your efforts to identify the source of attacks.

  7. You can log nonmatching statements as suspicious without denying access. To do this, put the account in intrusion-detecting mode:

    mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'DETECTING');

  8. Using the registered account, connect to the server, then execute a statement that does not match the whitelist:

    mysql> SHOW TABLES LIKE 'customer%';
+------------------------------+
| Tables_in_sakila (customer%) |
+------------------------------+
| customer                     |
| customer_list                |
+------------------------------+

    In detecting mode, the firewall permits the nonmatching statement to execute but writes a message to the error log:

    [Note] Plugin MYSQL_FIREWALL reported:
'SUSPICIOUS STATEMENT from 'fwuser@localhost'. Reason: No match in whitelist.
Statement: SHOW TABLES LIKE ? '
    Note

    Detection mode writes messages as Notes, which are information messages. To ensure that such messages appear in the error log and are not discarded, make sure that the log_error_verbosity system variable is set to a value of 3.

  9. To assess firewall activity, examine its status variables:

    mysql> SHOW GLOBAL STATUS LIKE 'Firewall%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Firewall_access_denied     | 3     |
| Firewall_access_granted    | 4     |
| Firewall_access_suspicious | 1     |
| Firewall_cached_entries    | 4     |
+----------------------------+-------+

    The variables indicate the number of statements rejected, accepted, logged as suspicious, and added to the cache, respectively. The Firewall_access_granted count is 4 because of the @@version_comment statement sent by the mysql client each of the three time you used it to connect as the registered user, plus the SHOW TABLES statement that was not blocked in DETECTING mode.

Should additional training for an account be necessary, switch it to recording mode again, then back to protecting mode after executing statements to be added to the whitelist.

6.4.7.4 MySQL Enterprise Firewall Reference

The following discussion serves as a reference to MySQL Enterprise Firewall components:

MySQL Enterprise Firewall Tables

MySQL Enterprise Firewall maintains account and whitelist information. It uses tables in the mysql system database to store this data in persistent form, and INFORMATION_SCHEMA tables to provide views into cached data. When enabled, the firewall bases its operational decisions on the cached data.

The mysql tables can be accessed only by users with privileges for that database. The INFORMATION_SCHEMA tables are accessible by anyone.

The mysql.firewall_users table lists registered firewall accounts and their operational modes. The table has the following columns (with the corresponding INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS table having similar but not necessarily identical columns):

  • USERHOST

    An account registered with the firewall. Each account has the format user_name@host_name and represents actual user and host names as authenticated by the server. Patterns and netmasks should not be used when registering users.

  • MODE

    The current firewall operational mode for the account. The permitted mode values are OFF, DETECTING, PROTECTING, RECORDING, and RESET. For details about their meanings, see the description of sp_set_firewall_mode() in MySQL Enterprise Firewall Procedures and Functions.

The mysql.firewall_whitelist table lists registered firewall accounts and their whitelists. The table has the following columns (with the corresponding INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST table having similar but not necessarily identical columns):

  • USERHOST

    An account registered with the firewall. The format is the same as for the user account tables.

  • RULE

    A normalized statement indicating an acceptable statement pattern for the account. An account whitelist is the union of its rules.

  • ID

    An integer column that is a primary key for the table. This column was added in MySQL 8.0.12.

MySQL Enterprise Firewall Procedures and Functions

MySQL Enterprise Firewall has stored procedures that perform tasks such as registering MySQL accounts with the firewall, establishing their operational mode, and managing transfer of firewall data between the cache and the underlying system tables. It also has a set of user-defined functions (UDFs) that provides an SQL-level API for lower-level tasks such as synchronizing the cache with the underlying system tables.

Under normal operation, the stored procedures implement the user interface. The UDFs are invoked by the stored procedures, not directly by users.

To invoke a stored procedure when the default database is not the database that contains the procedure, qualify the procedure name with the database name. For example:

user
mode

The following list describes each firewall stored procedure and UDF:

  • sp_reload_firewall_rules(user)

    This stored procedure uses firewall UDFs to reset a registered account and reload the in-memory rules for it from the rules stored in the mysql.firewall_whitelist table. This procedure provides control over firewall operation for individual accounts.

    The user argument names the affected account, as a string in user_name@host_name format.

    Example:

    CALL mysql.sp_reload_firewall_rules('fwuser@localhost');
    Warning

    This procedure sets the account mode to RESET, which clears the account whitelist and sets its mode to OFF. If the account mode was not OFF prior to the sp_reload_firewall_rules() call, use sp_set_firewall_mode() to restore its previous mode after reloading the rules. For example, if the account was in PROTECTING mode, that is no longer true after calling sp_reload_firewall_rules() and you must set it to PROTECTING again explicitly.

  • sp_set_firewall_mode(user, mode)

    This stored procedure registers a MySQL account with the firewall and establishes its operational mode. The procedure also invokes firewall UDFs as necessary to transfer firewall data between the cache and the underlying system tables. This procedure may be called even if the mysql_firewall_mode system variable is OFF, although setting the mode for an account has no operational effect while the firewall is disabled.

    The user argument names the affected account, as a string in user_name@host_name format.

    The mode is the operational mode for the user, as a string. These mode values are permitted:

    • OFF: Disable the firewall for the account.

    • DETECTING: Intrusion-detection mode: Write suspicious (nonmatching) statements to the error log but do not deny access.

    • PROTECTING: Protect the account by matching incoming statements against the account whitelist.

    • RECORDING: Training mode: Record acceptable statements for the account. Incoming statements that do not immediately fail with a syntax error are recorded to become part of the account whitelist rules.

    • RESET: Clear the account whitelist and set the account mode to OFF.

    Switching the mode for an account to any mode but RECORDING synchronizes the firewall cache data to the underlying mysql system database tables for persistent storage. Switching the mode from OFF to RECORDING reloads the whitelist from the mysql.firewall_whitelist table into the cache.

    If an account has an empty whitelist, setting its mode to PROTECTING produces an error message that is returned in a result set, but not an SQL error:

    mysql> CALL mysql.sp_set_firewall_mode('a@b','PROTECTING');
+----------------------------------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode)                            |
+----------------------------------------------------------------------+
| ERROR: PROTECTING mode requested for a@b but the whitelist is empty. |
+----------------------------------------------------------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

  • mysql_firewall_flush_status()

    This UDF resets several firewall status variables to 0:

    Firewall_access_denied
Firewall_access_granted
Firewall_access_suspicious

    Example:

    SELECT mysql_firewall_flush_status();

  • normalize_statement(stmt)

    This UDF normalizes an SQL statement into the digest form used for whitelist rules.

    Example:

    SELECT normalize_statement('SELECT * FROM t1 WHERE c1 > 2');

  • read_firewall_users(user, mode)

    This aggregate UDF updates the firewall user cache through a SELECT statement on the mysql.firewall_users table.

    Example:

    SELECT read_firewall_users('fwuser@localhost', 'RECORDING')
FROM mysql.firewall_users;

  • read_firewall_whitelist(user, rule)

    This aggregate UDF updates the recorded statement cache through a SELECT statement on the mysql.firewall_whitelist table.

    Example:

    SELECT read_firewall_whitelist('fwuser@localhost', 'RECORDING')
FROM mysql.firewall_whitelist;

  • set_firewall_mode(user, mode)

    This UDF manages the user cache and establishes the user operational mode.

    Example:

    SELECT set_firewall_mode('fwuser@localhost', 'RECORDING');
MySQL Enterprise Firewall System Variables

MySQL Enterprise Firewall supports the following system variables. Use them to configure firewall operation. These variables are unavailable unless the firewall is installed (see Section 6.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”).

  • mysql_firewall_mode

    PropertyValue
    Command-Line Format --mysql-firewall-mode[={OFF|ON}]
    System Variable mysql_firewall_mode
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Whether MySQL Enterprise Firewall is enabled (the default) or disabled.

  • mysql_firewall_trace

    PropertyValue
    Command-Line Format --mysql-firewall-trace[={OFF|ON}]
    System Variable mysql_firewall_trace
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Whether the MySQL Enterprise Firewall trace is enabled or disabled (the default). When mysql_firewall_trace is enabled, for PROTECTING mode, the firewall writes rejected statements to the error log.

MySQL Enterprise Firewall Status Variables

MySQL Enterprise Firewall supports the following status variables. Use them to obtain information about firewall operational status. These variables are unavailable unless the firewall is installed (see Section 6.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”). Firewall status variables are set to 0 whenever the MYSQL_FIREWALL plugin is installed or the server is started. Many of them are reset to zero by the mysql_firewall_flush_status() UDF (see MySQL Enterprise Firewall Procedures and Functions).

6.4.8 MySQL Enterprise Data Masking and De-Identification

6.4.8.1 MySQL Enterprise Data Masking and De-Identification Components
6.4.8.2 Installing or Uninstalling MySQL Enterprise Data Masking and De-Identification
6.4.8.3 Using MySQL Enterprise Data Masking and De-Identification
6.4.8.4 MySQL Enterprise Data Masking and De-Identification User-Defined Function Reference
Note

MySQL Enterprise Data Masking and De-Identification is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, https://www.mysql.com/products/.

As of MySQL 8.0.13, MySQL Enterprise Edition provides data masking and de-identification capabilities:

  • Transformation of existing data to mask it and remove identifying characteristics, such as changing all digits of a credit card number but the last four to 'X' characters.

  • Generation of random data, such as email addresses and payment card numbers.

The way that applications use these capabilities depends on the purpose for which the data will be used and who will access it:

  • Applications that use sensitive data may protect it by performing data masking and permitting use of partially masked data for client identification. Example: A call center may ask for clients to provide their last four Social Security number digits.

  • Applications that require properly formatted data, but not necessarily the original data, can synthesize sample data. Example: An application developer who is testing data validators but has no access to original data may synthesize random data with the same format.

Example 1:

Medical research facilities can hold patient data that comprises a mix of personal and medical data. This may include genetic sequences (long strings), test results stored in JSON format, and other data types. Although the data may be used mostly by automated analysis software, access to genome data or test results of particular patients is still possible. In such cases, data masking should be used to render this information not personally identifiable.

Example 2:

A credit card processor company provides a set of services using sensitive data, such as:

  • Processing a large number of financial transactions per second.

  • Storing a large amount of transaction-related data.

  • Protecting transaction-related data with strict requirements for personal data.

  • Handling client complaints about transactions using reversible or partially masked data.

A typical transaction may include many types of sensitive information, including:

  • Credit card number.

  • Transaction type and amount.

  • Merchant type.

  • Transaction cryptogram (to confirm transaction legitimacy).

  • Geolocation of GPS-equipped terminal (for fraud detection).

Those types of information may then be joined within a bank or other card-issuing financial institution with client personal data, such as:

  • Full client name (either person or company).

  • Address.

  • Date of birth.

  • Social Security number.

  • Email address.

  • Phone number.

Various employee roles within both the card processing company and the financial institution require access to that data. Some of these roles may require access only to masked data. Other roles may require access to the original data on a case-to-case basis, which is recorded in audit logs.

Masking and de-identification are core to regulatory compliance, so MySQL Enterprise Data Masking and De-Identification can help application developers satisfy privacy requirements:

  • PCI – DSS: Payment Card Data.

  • HIPAA: Privacy of Health Data, Health Information Technology for Economic and Clinical Health Act (HITECH Act).

  • EU General Data Protection Directive (GDPR): Protection of Personal Data.

  • Data Protection Act (UK): Protection of Personal Data.

  • Sarbanes Oxley, GLBA, The USA Patriot Act, Identity Theft and Assumption Deterrence Act of 1998.

  • FERPA – Student Data, NASD, CA SB1386 and AB 1950, State Data Protection Laws, Basel II.

The following sections describe the components of MySQL Enterprise Data Masking and De-Identification, discuss how to install and use it, and provide reference information for its components.

6.4.8.1 MySQL Enterprise Data Masking and De-Identification Components

MySQL Enterprise Data Masking and De-Identification is based on a plugin library that implements these components:

  • A server-side plugin named data_masking.

  • A set of user-defined functions (UDFs) provides an SQL-level API for performing masking and de-identification operations. Some of these functions require the SUPER privilege.

6.4.8.2 Installing or Uninstalling MySQL Enterprise Data Masking and De-Identification

This section describes how to install or uninstall MySQL Enterprise Data Masking and De-Identification, which is implemented as a plugin library file containing a plugin and user-defined functions (UDFs). For general information about installing or uninstalling plugins and UDFs, see Section 5.6.1, “Installing and Uninstalling Plugins”, and Section 5.7.1, “Installing and Uninstalling User-Defined Functions”.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

The plugin library file base name is data_masking. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

To install the MySQL Enterprise Data Masking and De-Identification plugin and UDFs, use the INSTALL PLUGIN and CREATE FUNCTION statements (adjust the .so suffix for your platform as necessary):

INSTALL PLUGIN data_masking SONAME 'data_masking.so';
CREATE FUNCTION gen_blacklist RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary_drop RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary_load RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION gen_range RETURNS INTEGER
  SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_email RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_pan RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_ssn RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_us_phone RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION mask_inner RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION mask_outer RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION mask_pan RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION mask_pan_relaxed RETURNS STRING
  SONAME 'data_masking.so';
CREATE FUNCTION mask_ssn RETURNS STRING
  SONAME 'data_masking.so';

If the plugin and UDFs are used on a master replication server, install them on all slave servers as well to avoid replication issues.

Once installed as just described, the plugin and UDFs remain installed until uninstalled. To remove them, use the UNINSTALL PLUGIN and DROP FUNCTION statements:

UNINSTALL PLUGIN data_masking;
DROP FUNCTION gen_blacklist;
DROP FUNCTION gen_dictionary;
DROP FUNCTION gen_dictionary_drop;
DROP FUNCTION gen_dictionary_load;
DROP FUNCTION gen_range;
DROP FUNCTION gen_rnd_email;
DROP FUNCTION gen_rnd_pan;
DROP FUNCTION gen_rnd_ssn;
DROP FUNCTION gen_rnd_us_phone;
DROP FUNCTION mask_inner;
DROP FUNCTION mask_outer;
DROP FUNCTION mask_pan;
DROP FUNCTION mask_pan_relaxed;
DROP FUNCTION mask_ssn;

6.4.8.3 Using MySQL Enterprise Data Masking and De-Identification

Before using MySQL Enterprise Data Masking and De-Identification, install it according to the instructions provided at Section 6.4.8.2, “Installing or Uninstalling MySQL Enterprise Data Masking and De-Identification”.

To use MySQL Enterprise Data Masking and De-Identification in applications, invoke the functions that are appropriate for the operations you wish to perform. For detailed function descriptions, see Section 6.4.8.4, “MySQL Enterprise Data Masking and De-Identification User-Defined Function Reference”. This section demonstrates how to use the functions to carry out some representative tasks. It first presents an overview of the available functions, followed by some examples of how the functions might be used in real-world context:

Masking Data to Remove Identifying Characteristics

MySQL provides general-purpose masking functions that mask arbitrary strings, and special-purpose masking functions that mask specific types of values.

General-Purpose Masking Functions

mask_inner() and mask_outer() are general-purpose functions that mask parts of arbitrary strings based on position within the string:

  • mask_inner() masks the interior of its string argument, leaving the ends unmasked. Other arguments specify the sizes of the unmasked ends.

    		SELECT mask_inner('This is a string', 5, 1);
    		SELECT mask_inner('This is a string', 1, 5);

  • mask_outer() does the reverse, masking the ends of its string argument, leaving the interior unmasked. Other arguments specify the sizes of the masked ends.

    		SELECT mask_outer('This is a string', 5, 1);
    		SELECT mask_outer('This is a string', 1, 5);

By default, mask_inner() and mask_outer() use 'X' as the masking character, but permit an optional masking-character argument:

SELECT mask_inner('This is a string', 5, 1, '*');
SELECT mask_outer('This is a string', 5, 1, '#');
Special-Purpose Masking Functions

Other masking functions expect a string argument representing a specific type of value and mask it to remove identifying characteristics.

Note

The examples here supply function arguments using the random value generation functions that return the appropriate type of value. For more information about generation functions, see Generating Random Data with Specific Characteristics.

Payment card Primary Account Number masking.  Masking functions provide strict and relaxed masking of Primary Account Numbers.

  • mask_pan() masks all but the last four digits of the number:

    mysql> SELECT mask_pan(gen_rnd_pan());
+-------------------------+
| mask_pan(gen_rnd_pan()) |
+-------------------------+
| XXXXXXXXXXXX2461        |
+-------------------------+

  • mask_pan_relaxed() is similar but does not mask the first six digits that indicate the payment card issuer unmasked:

    mysql> SELECT mask_pan_relaxed(gen_rnd_pan());
+---------------------------------+
| mask_pan_relaxed(gen_rnd_pan()) |
+---------------------------------+
| 770630XXXXXX0807                |
+---------------------------------+

US Social Security number masking.  mask_ssn() masks all but the last four digits of the number:

mysql> SELECT mask_ssn(gen_rnd_ssn());
+-------------------------+
| mask_ssn(gen_rnd_ssn()) |
+-------------------------+
| XXX-XX-1723             |
+-------------------------+
Generating Random Data with Specific Characteristics

Several functions generate random values. These values can be used for testing, simulation, and so forth.

gen_range() returns a random integer selected from a given range:

mysql> SELECT gen_range(1, 10);
+------------------+
| gen_range(1, 10) |
+------------------+
|                6 |
+------------------+

gen_rnd_email() returns a random email address in the example.com domain:

mysql> SELECT gen_rnd_email();
+---------------------------+
| gen_rnd_email()           |
+---------------------------+
| ayxnq.xmkpvvy@example.com |
+---------------------------+

gen_rnd_pan() returns a random payment card Primary Account Number:

mysql> SELECT gen_rnd_pan();

(The gen_rnd_pan() function result is not shown because its return values should be used only for testing purposes, and not for publication. It cannot be guaranteed the number is not assigned to a legitimate payment account.)

gen_rnd_ssn() returns a random US Social Security number with the first and second parts each chosen from a range not used for legitimate numbers:

mysql> SELECT gen_rnd_ssn();
+---------------+
| gen_rnd_ssn() |
+---------------+
| 912-45-1615   |
+---------------+

gen_rnd_us_phone() returns a random US phone number in the 555 area code not used for legitimate numbers:

mysql> SELECT gen_rnd_us_phone();
+--------------------+
| gen_rnd_us_phone() |
+--------------------+
| 1-555-747-5627     |
+--------------------+
Generating Random Data Using Dictionaries

MySQL Enterprise Data Masking and De-Identification enables dictionaries to be used as sources of random values. To use a dictionary, it must first be loaded from a file and given a name. Each loaded dictionary becomes part of the dictionary registry. Items then can be selected from registered dictionaries and used as random values or as replacements for other values.

A valid dictionary file has these characteristics:

  • The file contents are plain text, one term per line.

  • Empty lines are ignored.

  • The file must contain at least one term.

Suppose that a file named de_cities.txt contains these city names in Germany:

Berlin
Munich
Bremen

Also suppose that a file named us_cities.txt contains these city names in the United States:

Chicago
Houston
Phoenix
El Paso
Detroit

Assume that the secure_file_priv system variable is set to /usr/local/mysql/mysql-files. In that case, copy the dictionary files to that directory so that the MySQL server can access them. Then use gen_dictionary_load() to load the dictionaries into the dictionary registry and assign them names:

SELECT gen_dictionary_load('/usr/local/mysql/mysql-files/de_cities.txt', 'DE_Cities');
SELECT gen_dictionary_load('/usr/local/mysql/mysql-files/us_cities.txt', 'US_Cities');

To select a random term from a dictionary, use gen_dictionary():

SELECT gen_dictionary('DE_Cities');
SELECT gen_dictionary('US_Cities');

To select a random term from multiple dictionaries, randomly select one of the dictionaries, then select a term from it:

SELECT gen_dictionary(ELT(gen_range(1,2), 'DE_Cities', 'US_Cities'));
SELECT gen_dictionary(ELT(gen_range(1,2), 'DE_Cities', 'US_Cities'));

The gen_blacklist() function enables a term from one dictionary to be replaced by a term from another dictionary, which effects masking by substitution. Its arguments are the term to replace, the dictionary in which the term appears, and the dictionary from which to choose a replacement. For example, to substitute a US city for a German city, or vice versa, use gen_blacklist() like this:

SELECT gen_blacklist('Munich', 'DE_Cities', 'US_Cities');
SELECT gen_blacklist('El Paso', 'US_Cities', 'DE_Cities');

If the term to replace is not in the first dictionary, gen_blacklist() returns it unchanged:

mysql> SELECT gen_blacklist('Moscow', 'DE_Cities', 'US_Cities');
+---------------------------------------------------+
| gen_blacklist('Moscow', 'DE_Cities', 'US_Cities') |
+---------------------------------------------------+
| Moscow                                            |
+---------------------------------------------------+
Using Masked Data for Customer Identification

At customer-service call centers, one common identity verification technique is to ask customers to provide their last four Social Security number (SSN) digits. For example, a customer might say her name is Joanna Bond and that her last four SSN digits are 0007.

Suppose that a customer table containing customer records has these columns:

  • id: Customer ID number.

  • first_name: Customer first name.

  • last_name: Customer last name.

  • ssn: Customer Social Security number.

For example, the table might be defined as follows:

CREATE TABLE customer
(
  id         BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name  VARCHAR(40),
  ssn        VARCHAR(11)
);

The application used by customer-service representatives to check the customer SSN might execute a query like this:

SELECT id, ssn
FROM customer
WHERE first_name = 'Joanna' AND last_name = 'Bond';

However, that exposes the SSN to the customer-service representative, who has no need to see anything but the last four digits. Instead, the application can use this query to display only the masked SSN:

SELECT id, mask_ssn(CONVERT(ssn USING binary)) AS masked_ssn
FROM customer
WHERE first_name = 'Joanna' AND last_name = 'Bond';

Now the representative sees only what is necessary, and customer privacy is preserved.

Why was the CONVERT() function used for the argument to mask_ssn()? Because mask_ssn() requires an argument of length 11. Thus, even though ssn is defined as VARCHAR(11), if the ssn column has a multibyte character set, it may appear to be longer than 11 bytes when passed to a UDF, and an error occurs. Converting the value to a binary string ensures that the UDF sees an argument of length 11.

A similar technique may be needed for other data masking functions when string arguments do not have a single-byte character set.

Creating Views that Display Masked Data

If masked data from a table is used for multiple queries, it may be convenient to define a view that produces masked data. That way, applications can select from the view without performing masking in individual queries.

For example, a masking view on the customer table from the previous section can be defined like this:

CREATE VIEW masked_customer AS
SELECT id, first_name, last_name,
mask_ssn(CONVERT(ssn USING binary)) AS masked_ssn
FROM customer;

Then the query to look up a customer becomes simpler but still returns masked data:

SELECT id, masked_ssn
FROM masked_customer
WHERE first_name = 'Joanna' AND last_name = 'Bond';

6.4.8.4 MySQL Enterprise Data Masking and De-Identification User-Defined Function Reference

The MySQL Enterprise Data Masking and De-Identification plugin library includes several user-defined functions (UDFs), which may be grouped into these categories:

As of MySQL 8.0.19, these UDFs support the single-byte latin1 character set for string arguments and return values. Prior to MySQL 8.0.19, the UDFs treat string arguments as binary strings (which means they do not distinguish lettercase), and string return values are binary strings. You can see the difference in return value character set as follows:

MySQL 8.0.19 and higher:

mysql> SELECT CHARSET(gen_rnd_email());
+--------------------------+
| CHARSET(gen_rnd_email()) |
+--------------------------+
| latin1                   |
+--------------------------+

Prior to MySQL 8.0.19:

mysql> SELECT CHARSET(gen_rnd_email());
+--------------------------+
| CHARSET(gen_rnd_email()) |
+--------------------------+
| binary                   |
+--------------------------+

For any version, if a string return value should be in a different character set, convert it. The following example shows how to convert the result of gen_rnd_email() to the utf8mb4 character set:

SET @email = CONVERT(gen_rnd_email() USING utf8mb4);

It may also be necessary to convert string arguments, as illustrated in Using Masked Data for Customer Identification.

Data Masking Functions

Each function in this section performs a masking operation on its string argument and returns the masked result.

  • mask_inner(str, margin1, margin2 [, mask_char])

    Masks the interior part of a string, leaving the ends untouched, and returns the result. An optional masking character can be specified.

    Arguments:

    • str: The string to mask.

    • margin1: A nonnegative integer that specifies the number of characters on the left end of the string to remain unmasked. If the value is 0, no left end characters remain unmasked.

    • margin2: A nonnegative integer that specifies the number of characters on the right end of the string to remain unmasked. If the value is 0, no right end characters remain unmasked.

    • mask_char: (Optional) The single character to use for masking. The default is 'X' if mask_char is not given.

      The masking character must be a single-byte character. Attempts to use a multibyte character produce an error.

    Return value:

    The masked string, or NULL if either margin is negative.

    If the sum of the margin values is larger than the argument length, no masking occurs and the argument is returned unchanged.

    Example:

    		SELECT mask_inner('abcdef', 1, 2), mask_inner('abcdef',0, 5);
    		SELECT mask_inner('abcdef', 1, 2, '*'), mask_inner('abcdef',0, 5, '#');

  • mask_outer(str, margin1, margin2 [, mask_char])

    Masks the left and right ends of a string, leaving the interior unmasked, and returns the result. An optional masking character can be specified.

    Arguments:

    • str: The string to mask.

    • margin1: A nonnegative integer that specifies the number of characters on the left end of the string to mask. If the value is 0, no left end characters are masked.

    • margin2: A nonnegative integer that specifies the number of characters on the right end of the string to mask. If the value is 0, no right end characters are masked.

    • mask_char: (Optional) The single character to use for masking. The default is 'X' if mask_char is not given.

      The masking character must be a single-byte character. Attempts to use a multibyte character produce an error.

    Return value:

    The masked string, or NULL if either margin is negative.

    If the sum of the margin values is larger than the argument length, the entire argument is masked.

    Example:

    		SELECT mask_outer('abcdef', 1, 2), mask_outer('abcdef',0, 5);
    		SELECT mask_outer('abcdef', 1, 2, '*'), mask_outer('abcdef',0, 5, '#');

  • mask_pan(str)

    Masks a payment card Primary Account Number and returns the number with all but the last four digits replaced by 'X' characters.

    Arguments:

    • str: The string to mask. The string must be a suitable length for the Primary Account Number, but is not otherwise checked.

    Return value:

    The masked payment number as a string. If the argument is shorter than required, it is returned unchanged.

    Example:

    		SELECT mask_pan(gen_rnd_pan());
    		SELECT mask_pan(gen_rnd_pan(19));
    		SELECT mask_pan('a*Z');

  • mask_pan_relaxed(str)

    Masks a payment card Primary Account Number and returns the number with all but the first six and last four digits replaced by 'X' characters. The first six digits indicate the payment card issuer.

    Arguments:

    • str: The string to mask. The string must be a suitable length for the Primary Account Number, but is not otherwise checked.

    Return value:

    The masked payment number as a string. If the argument is shorter than required, it is returned unchanged.

    Example:

    		SELECT mask_pan_relaxed(gen_rnd_pan());
    		SELECT mask_pan_relaxed(gen_rnd_pan(19));
    		SELECT mask_pan_relaxed('a*Z');

  • mask_ssn(str)

    Masks a US Social Security number and returns the number with all but the last four digits replaced by 'X' characters.

    Arguments:

    • str: The string to mask. The string must be 11 characters long, but is not otherwise checked.

    Return value:

    The masked Social Security number as a string, or NULL if the argument is not the correct length.

    Example:

    		SELECT mask_ssn('909-63-6922'), mask_ssn('abcdefghijk');
    		SELECT mask_ssn('909');
Random Data Generation Functions

The functions in this section generate random values for different types of data. When possible, generated values have characteristics reserved for demonstration or test values, to avoid having them mistaken for legitimate data. For example, gen_rnd_us_phone() returns a US phone number that uses the 555 area code, which is not assigned to phone numbers in actual use. Individual function descriptions describe any exceptions to this principle.

  • gen_range(lower, upper)

    Generates a random number chosen from a specified range.

    Arguments:

    • lower: An integer that specifies the lower boundary of the range.

    • upper: An integer that specifies the upper boundary of the range, which must not be less than the lower boundary.

    Return value:

    A random integer in the range from lower to upper, inclusive, or NULL if the upper argument is less than lower.

    Example:

    		SELECT gen_range(100, 200), gen_range(-1000, -800);
    		SELECT gen_range(1, 0);

  • gen_rnd_email()

    Generates a random email address in the example.com domain.

    Arguments:

    None.

    Return value:

    A random email address as a string.

    Example:

    mysql> SELECT gen_rnd_email();
+---------------------------+
| gen_rnd_email()           |
+---------------------------+
| ijocv.mwvhhuf@example.com |
+---------------------------+

  • gen_rnd_pan([size])

    Generates a random payment card Primary Account Number. The number passes the Luhn check (an algorithm that performs a checksum verification against a check digit).

    Warning

    Values returned from gen_rnd_pan() should be used only for test purposes, and are not suitable for publication. There is no way to guarantee that a given return value is not assigned to a legitimate payment account. Should it be necessary to publish a gen_rnd_pan() result, consider masking it with mask_pan() or mask_pan_relaxed().

    Arguments:

    • size: (Optional) An integer that specifies the size of the result. The default is 16 if size is not given. If given, size must be an integer in the range from 12 to 19.

    Return value:

    A random payment number as a string, or NULL if a size argument outside the permitted range is given.

    Example:

    		SELECT mask_pan(gen_rnd_pan());
    		SELECT mask_pan(gen_rnd_pan(19));
    		SELECT mask_pan_relaxed(gen_rnd_pan());
    		SELECT mask_pan_relaxed(gen_rnd_pan(19));
    		SELECT gen_rnd_pan(11), gen_rnd_pan(20);

  • gen_rnd_ssn()

    Generates a random US Social Security number in AAA-BB-CCCC format. The AAA part is greater than 900 and the BB part is less than 70, which are characteristics not used for legitimate Social Security numbers.

    Arguments:

    None.

    Return value:

    A random Social Security number as a string.

    Example:

    mysql> SELECT gen_rnd_ssn();
+---------------+
| gen_rnd_ssn() |
+---------------+
| 951-26-0058   |
+---------------+

  • gen_rnd_us_phone()

    Generates a random US phone number in 1-555-AAA-BBBB format. The 555 area code is not used for legitimate phone numbers.

    Arguments:

    None.

    Return value:

    A random US phone number as a string.

    Example:

    mysql> SELECT gen_rnd_us_phone();
+--------------------+
| gen_rnd_us_phone() |
+--------------------+
| 1-555-682-5423     |
+--------------------+
Random Data Dictionary-Based Functions

The functions in this section manipulate dictionaries of terms and perform generation and masking operations based on them. Some of these functions require the SUPER privilege.

When a dictionary is loaded, it becomes part of the dictionary registry and is assigned a name to be used by other dictionary functions. Dictionaries are loaded from plain text files containing one term per line. Empty lines are ignored. To be valid, a dictionary file must contain at least one nonempty line.

  • gen_blacklist(str, dictionary_name, replacement_dictionary_name)

    Replaces a term present in one dictionary with a term from a second dictionary and returns the replacement term. This masks the original term by substitution.

    Arguments:

    • str: A string that indicates the term to replace.

    • dictionary_name: A string that names the dictionary containing the term to replace.

    • replacement_dictionary_name: A string that names the dictionary from which to choose the replacement term.

    Return value:

    A string randomly chosen from replacement_dictionary_name as a replacement for str, or str if it does not appear in dictionary_name, or NULL if either dictionary name is not in the dictionary registry.

    If the term to replace appears in both dictionaries, it is possible for the return value to be the same term.

    Example:

    mysql> SELECT gen_blacklist('Berlin', 'DE_Cities', 'US_Cities');
+---------------------------------------------------+
| gen_blacklist('Berlin', 'DE_Cities', 'US_Cities') |
+---------------------------------------------------+
| Phoenix                                           |
+---------------------------------------------------+

  • gen_dictionary(dictionary_name)

    Returns a random term from a dictionary.

    Arguments:

    • dictionary_name: A string that names the dictionary from which to choose the term.

    Return value:

    A random term from the dictionary as a string, or NULL if the dictionary name is not in the dictionary registry.

    Example:

    		SELECT gen_dictionary('mydict');
    		SELECT gen_dictionary('no-such-dict');

  • gen_dictionary_drop(dictionary_name)

    Removes a dictionary from the dictionary registry.

    This function requires the SUPER privilege.

    Arguments:

    • dictionary_name: A string that names the dictionary to remove from the dictionary registry.

    Return value:

    A string that indicates whether the drop operation succeeded. Dictionary removed indicates success. Dictionary removal error indicates failure.

    Example:

    		SELECT gen_dictionary_drop('mydict');
    		SELECT gen_dictionary_drop('no-such-dict');

  • gen_dictionary_load(dictionary_path, dictionary_name)

    Loads a file into the dictionary registry and assigns the dictionary a name to be used with other functions that require a dictionary name argument.

    This function requires the SUPER privilege.

    Important

    Dictionaries are not persistent. Any dictionary used by applications must be loaded for each server startup.

    Once loaded into the registry, a dictionary is used as is, even if the underlying dictionary file changes. To reload a dictionary, first drop it with gen_dictionary_drop(), then load it again with gen_dictionary_load().

    Arguments:

    • dictionary_path: A string that specifies the path name of the dictionary file.

    • dictionary_name: A string that provides a name for the dictionary.

    Return value:

    A string that indicates whether the load operation succeeded. Dictionary load success indicates success. Dictionary load error indicates failure. Dictionary load failure can occur for several reasons, including:

    • A dictionary with the given name is already loaded.

    • The dictionary file is not found.

    • The dictionary file contains no terms.

    • The secure_file_priv system variable is set and the dictionary file is not located in the directory named by the variable.

    Example:

    		SELECT gen_dictionary_load('/usr/local/mysql/mysql-files/mydict','mydict');
    		SELECT gen_dictionary_load('/dev/null','null');

 

posted on   jinzhenshui  阅读(2364)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
历史上的今天：
2017-03-13 windows server 2003R2\2008R2\2012\2016 安装【故障转移群集】cluster

Powered by: 博客园 Copyright © 2025 jinzhenshui
Powered by .NET 9.0 on Kubernetes
点击右上角即可分享
微信分享提示