6.4 Security Plugins

MySQL includes several plugins that implement security features:

 File: manual.info.tmp, Node: authentication-plugins, Next: connection-control, Prev: security-plugins, Up: security-plugins

6.4.1 Authentication Plugins

The following sections describe pluggable authentication methods available in MySQL and the plugins that implement these methods. For general discussion of the authentication process, see *note pluggable-authentication::.

The default plugin is indicated by the value of the 'default_authentication_plugin' system variable.

 File: manual.info.tmp, Node: native-pluggable-authentication, Next: old-native-pluggable-authentication, Prev: authentication-plugins, Up: authentication-plugins

6.4.1.1 Native Pluggable Authentication .......................................

MySQL includes two plugins that implement native authentication; that is, authentication based on the password hashing methods in use from before the introduction of pluggable authentication. This section describes 'mysql_native_password', which implements authentication against the 'mysql.user' system table using the native password hashing method. For information about 'mysql_old_password', which implements authentication using the older (pre-4.1) native password hashing method, see note old-native-pluggable-authentication::. For information about these password hashing methods, see note password-hashing::.

The following table shows the plugin names on the server and client sides.

Plugin and Library Names for Native Password Authentication

Plugin or File Plugin or File Name

Server-side plugin 'mysql_native_password'

Client-side plugin 'mysql_native_password'

Library file None (plugins are built in)

The following sections provide installation and usage information specific to native pluggable authentication:

For general information about pluggable authentication in MySQL, see *note pluggable-authentication::.

Installing Native Pluggable Authentication

The 'mysql_native_password' plugin exists in server and client forms:

Using Native Pluggable Authentication

MySQL client programs use 'mysql_native_password' by default. The '--default-auth' option can be used as a hint about which client-side plugin the program can expect to use:

 $> mysql --default-auth=mysql_native_password ...

 File: manual.info.tmp, Node: old-native-pluggable-authentication, Next: account-upgrades, Prev: native-pluggable-authentication, Up: authentication-plugins

6.4.1.2 Old Native Pluggable Authentication ...........................................

MySQL includes two plugins that implement native authentication; that is, authentication based on the password hashing methods in use from before the introduction of pluggable authentication. This section describes 'mysql_old_password', which implements authentication against the 'mysql.user' system table using the older (pre-4.1) native password hashing method. For information about 'mysql_native_password', which implements authentication using the native password hashing method, see note native-pluggable-authentication::. For information about these password hashing methods, see note password-hashing::.

Note:

Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them (including the 'mysql_old_password' plugin) was removed in MySQL 5.7.5. For account upgrade instructions, see *note account-upgrades::.

The following table shows the plugin names on the server and client sides.

Plugin and Library Names for Old Native Password Authentication

Plugin or File Plugin or File Name

Server-side plugin 'mysql_old_password'

Client-side plugin 'mysql_old_password'

Library file None (plugins are built in)

The following sections provide installation and usage information specific to old native pluggable authentication:

For general information about pluggable authentication in MySQL, see *note pluggable-authentication::.

Installing Old Native Pluggable Authentication

The 'mysql_old_password' plugin exists in server and client forms:

Using Old Native Pluggable Authentication

MySQL client programs can use the '--default-auth' option to specify the 'mysql_old_password' plugin as a hint about which client-side plugin the program can expect to use:

 $> mysql --default-auth=mysql_old_password ...

 File: manual.info.tmp, Node: account-upgrades, Next: caching-sha2-pluggable-authentication, Prev: old-native-pluggable-authentication, Up: authentication-plugins

6.4.1.3 Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin ......................................................................................

The MySQL server authenticates connection attempts for each account listed in the 'mysql.user' system table using the authentication plugin named in the 'plugin' column. If the 'plugin' column is empty, the server authenticates the account as follows:

Pre-4.1 password hashes and the 'mysql_old_password' plugin are deprecated in MySQL 5.6 and support for them is removed in MySQL 5.7. They provide a level of security inferior to that offered by 4.1 password hashing and the 'mysql_native_password' plugin.

Given the requirement in MySQL 5.7 that the 'plugin' column must be nonempty, coupled with removal of 'mysql_old_password' support, DBAs are advised to upgrade accounts as follows:

The instructions in this section describe how to perform those upgrades. The result is that no account has an empty 'plugin' value and no account uses pre-4.1 password hashing or the 'mysql_old_password' plugin.

As a variant on these instructions, DBAs might offer users the choice to upgrade to the 'sha256_password' plugin, which authenticates using SHA-256 password hashes. For information about this plugin, see *note sha256-pluggable-authentication::.

The following table lists the types of 'mysql.user' accounts considered in this discussion.

'plugin' Column 'Password' Authentication Upgrade Column Result Action

Empty

Empty Implicitly Assign plugin uses
'mysql_native_password'

Empty

4.1 hash Implicitly Assign plugin uses
'mysql_native_password'

Empty

Pre-4.1 hash Implicitly Assign plugin, uses rehash password 'mysql_old_password'

'mysql_native_password'

Empty Explicitly None uses
'mysql_native_password'

'mysql_native_password'

4.1 hash Explicitly None uses
'mysql_native_password'

'mysql_old_password'

Empty Explicitly Upgrade plugin uses
'mysql_old_password'

'mysql_old_password'

Pre-4.1 hash Explicitly Upgrade plugin, uses rehash password 'mysql_old_password'

Accounts corresponding to lines for the 'mysql_native_password' plugin require no upgrade action (because no change of plugin or hash format is required). For accounts corresponding to lines for which the password is empty, consider asking the account owners to choose a password (or require it by using *note 'ALTER USER': alter-user. to expire empty account passwords).

Upgrading Accounts from Implicit to Explicit mysql_native_password Use

Accounts that have an empty plugin and a 4.1 password hash use 'mysql_native_password' implicitly. To upgrade these accounts to use 'mysql_native_password' explicitly, execute these statements:

 UPDATE mysql.user SET plugin = 'mysql_native_password'
 WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);
 FLUSH PRIVILEGES;

Before MySQL 5.7, you can execute those statements to uprade accounts proactively. As of MySQL 5.7, you can run *note 'mysql_upgrade': mysql-upgrade, which performs the same operation among its upgrade actions.

Notes:

Upgrading Accounts from mysql_old_password to mysql_native_password

Accounts that use 'mysql_old_password' (either implicitly or explicitly) should be upgraded to use 'mysql_native_password' explicitly. This requires changing the plugin and changing the password from pre-4.1 to 4.1 hash format.

For the accounts covered in this step that must be upgraded, one of these conditions is true:

To identify such accounts, use this query:

 SELECT User, Host, Password FROM mysql.user
 WHERE (plugin = '' AND LENGTH(Password) = 16)
 OR plugin = 'mysql_old_password';

The following discussion provides two methods for updating that set of accounts. They have differing characteristics, so read both and decide which is most suitable for a given MySQL installation.

Method 1.

Characteristics of this method:

You should ensure that the server is running with 'secure_auth=0'.

For all accounts that use 'mysql_old_password' explicitly, set them to the empty plugin:

 UPDATE mysql.user SET plugin = ''
 WHERE plugin = 'mysql_old_password';
 FLUSH PRIVILEGES;

To also expire the password for affected accounts, use these statements instead:

 UPDATE mysql.user SET plugin = '', password_expired = 'Y'
 WHERE plugin = 'mysql_old_password';
 FLUSH PRIVILEGES;

Now affected users can reset their password to use 4.1 hashing. Ask each user who now has an empty plugin to connect to the server and execute these statements:

 SET old_passwords = 0;
 SET PASSWORD = PASSWORD('USER-CHOSEN-PASSWORD');

Note:

The client-side '--secure-auth' option is enabled by default, so remind users to disable it; otherwise, they cannot connect:

 $> mysql -u USER_NAME -p --secure-auth=0

After an affected user has executed those statements, you can set the corresponding account plugin to 'mysql_native_password' to make the plugin explicit. Or you can periodically run these statements to find and fix any accounts for which affected users have reset their password:

 UPDATE mysql.user SET plugin = 'mysql_native_password'
 WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);
 FLUSH PRIVILEGES;

When there are no more accounts with an empty plugin, this query returns an empty result:

 SELECT User, Host, Password FROM mysql.user
 WHERE plugin = '' AND LENGTH(Password) = 16;

At that point, all accounts have been migrated away from pre-4.1 password hashing and the server no longer need be run with 'secure_auth=0'.

Method 2.

Characteristics of this method:

With this method, you update each account separately due to the need to set passwords individually. Choose a different password for each account.

Suppose that ''user1'@'localhost'' is one of the accounts to be upgraded. Modify it as follows:

Repeat for each account to be upgraded.

 File: manual.info.tmp, Node: caching-sha2-pluggable-authentication, Next: sha256-pluggable-authentication, Prev: account-upgrades, Up: authentication-plugins

6.4.1.4 Caching SHA-2 Pluggable Authentication ..............................................

MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

This section describes the caching SHA-2 authentication plugin, available as of MySQL 5.7.23. For information about the original basic (noncaching) plugin, see *note sha256-pluggable-authentication::.

Important:

In MySQL 5.7, the default authentication plugin is 'mysql_native_password'. As of MySQL 8.0, the default authentication plugin is changed to 'caching_sha2_password'. To enable MySQL 5.7 clients to connect to 8.0 and higher servers using accounts that authenticate with 'caching_sha2_password', the MySQL 5.7 client library and client programs support the 'caching_sha2_password' client-side authentication plugin. This improves MySQL 5.7 client connect-capability compatibility with respect to MySQL 8.0 and higher servers, despite the differences in default authentication plugin.

Limiting 'caching_sha2_password' support in MySQL 5.7 to the client-side plugin in the client library has these implications compared to MySQL 8.0:

In addition, there is no support for MySQL 5.7 replicas to connect to MySQL 8.0 replication source servers using accounts that authenticate with 'caching_sha2_password'. That would involve a source replicating to a replica with a version number lower than the source version, whereas sources normally replicate to replicas having a version equal to or higher than the source version.

Important:

To connect to a MySQL 8.0 or higher server using an account that authenticates with the 'caching_sha2_password' plugin, you must use either a secure connection or an unencrypted connection that supports password exchange using an RSA key pair, as described later in this section. Either way, the 'caching_sha2_password' plugin uses MySQL's encryption capabilities. See *note encrypted-connections::.

Note:

In the name 'sha256_password', 'sha256' refers to the 256-bit digest length the plugin uses for encryption. In the name 'caching_sha2_password', 'sha2' refers more generally to the SHA-2 class of encryption algorithms, of which 256-bit encryption is one instance. The latter name choice leaves room for future expansion of possible digest lengths without changing the plugin name.

The 'caching_sha2_password' plugin has these advantages, compared to 'sha256_password':

The following table shows the plugin name on the client side.

Plugin and Library Names for SHA-2 Authentication

Plugin or File Plugin or File Name

Client-side plugin 'caching_sha2_password'

Library file None (plugin is built in)

The following sections provide installation and usage information specific to caching SHA-2 pluggable authentication:

For general information about pluggable authentication in MySQL, see *note pluggable-authentication::.

Installing SHA-2 Pluggable Authentication

In MySQL 5.7, the 'caching_sha2_password' plugin exists in client form. The client-side plugin is built into the 'libmysqlclient' client library and is available to any program linked against 'libmysqlclient'.

Using SHA-2 Pluggable Authentication

In MySQL 5.7, the 'caching_sha2_password' client-side plugin enables connecting to MySQL 8.0 or higher servers using accounts that authenticate with the 'caching_sha2_password' server-side plugin. The discussion here assumes that an account named ''sha2user'@'localhost'' exists on the MySQL 8.0 or higher server. For example, the following statement creates such an account, where PASSWORD is the desired account password:

 CREATE USER 'sha2user'@'localhost'
 IDENTIFIED WITH caching_sha2_password BY 'PASSWORD';

'caching_sha2_password' supports connections over secure transport. 'caching_sha2_password' also supports encrypted password exchange using RSA over unencrypted connections if these conditions are satisfied:

RSA support has these characteristics, where all aspects that pertain to the server side require a MySQL 8.0 or higher server:

For clients that use the 'caching_sha2_password' plugin, passwords are never exposed as cleartext when connecting to the MySQL 8.0 or higher server. How password transmission occurs depends on whether a secure connection or RSA encryption is used:

As mentioned previously, RSA password encryption is available only if MySQL 5.7 was compiled using OpenSSL. The implication for clients from MySQL 5.7 distributions compiled using yaSSL is that, to use SHA-2 passwords, clients must use an encrypted connection to access the server. See *note using-encrypted-connections::.

Assuming that MySQL 5.7 has been compiled using OpenSSL, use the following procedure to enable use of an RSA key pair for password exchange during the client connection process.

Important:

Aspects of this procedure that pertain to server configuration must be done on the MySQL 8.0 or higher server to which you wish to connect using MySQL 5.7 clients, not on your MySQL 5.7 server.

  1. Create the RSA private and public key-pair files using the instructions in *note creating-ssl-rsa-files::.

  2. If the private and public key files are located in the data directory and are named 'private_key.pem' and 'public_key.pem' (the default values of the 'caching_sha2_password_private_key_path' (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_caching_sha2_password_private_key_path) and 'caching_sha2_password_public_key_path' (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_caching_sha2_password_public_key_path) system variables), the server uses them automatically at startup.

    Otherwise, to name the key files explicitly, set the system variables to the key file names in the server option file. If the files are located in the server data directory, you need not specify their full path names:

      [mysqld]
  caching_sha2_password_private_key_path=myprivkey.pem
  caching_sha2_password_public_key_path=mypubkey.pem

    If the key files are not located in the data directory, or to make their locations explicit in the system variable values, use full path names:

      [mysqld]
  caching_sha2_password_private_key_path=/usr/local/mysql/myprivkey.pem
  caching_sha2_password_public_key_path=/usr/local/mysql/mypubkey.pem

  3. Restart the server, then connect to it and check the 'Caching_sha2_password_rsa_public_key' (https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Caching_sha2_password_rsa_public_key) status variable value. The actual value differs from that shown here, but should be nonempty:

      mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G
  *************************** 1. row ***************************
  Variable_name: Caching_sha2_password_rsa_public_key
          Value: -----BEGIN PUBLIC KEY-----
  MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
  MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
  aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
  g8aV7EtKwyhHb0c30QIDAQAB
  -----END PUBLIC KEY-----

    If the value is empty, the server found some problem with the key files. Check the error log for diagnostic information.

After the server has been configured with the RSA key files, accounts that authenticate with the 'caching_sha2_password' plugin have the option of using those key files to connect to the server. As mentioned previously, such accounts can use either a secure connection (in which case RSA is not used) or an unencrypted connection that performs password exchange using RSA. Suppose that an unencrypted connection is used. For example:

 $> mysql --ssl-mode=DISABLED -u sha2user -p
 Enter password: PASSWORD

For this connection attempt by 'sha2user', the server determines that 'caching_sha2_password' is the appropriate authentication plugin and invokes it (because that was the plugin specified at *note 'CREATE USER': create-user. time). The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. However, the server does not send the public key to the client, and the client provided no public key, so it cannot encrypt the password and the connection fails:

 ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password'
 reported error: Authentication requires secure connection.

To request the RSA public key from the server, specify the '--get-server-public-key' option:

 $> mysql --ssl-mode=DISABLED -u sha2user -p --get-server-public-key
 Enter password: PASSWORD

In this case, the server sends the RSA public key to the client, which uses it to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

Alternatively, if the client has a file containing a local copy of the RSA public key required by the server, it can specify the file using the '--server-public-key-path' option:

 $> mysql --ssl-mode=DISABLED -u sha2user -p --server-public-key-path=FILE_NAME
 Enter password: PASSWORD

In this case, the client uses the public key to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

The public key value in the file named by the '--server-public-key-path' option should be the same as the key value in the server-side file named by the 'caching_sha2_password_public_key_path' (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_caching_sha2_password_public_key_path) system variable. If the key file contains a valid public key value but the value is incorrect, an access-denied error occurs. If the key file does not contain a valid public key, the client program cannot use it.

Client users can obtain the RSA public key two ways:

Cache Operation for SHA-2 Pluggable Authentication

On the server side, the 'caching_sha2_password' plugin uses an in-memory cache for faster authentication of clients who have connected previously. For MySQL 5.7, which supports only the 'caching_sha2_password' client-side plugin, this server-side caching thus takes place on the MySQL 8.0 or higher server to which you connect using MySQL 5.7 clients. For information about cache operation, see Cache Operation for SHA-2 Pluggable Authentication (https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html#caching-sha2-pluggable-authentication-cache-operation), in the 'MySQL 8.0 Reference Manual'.

 File: manual.info.tmp, Node: sha256-pluggable-authentication, Next: cleartext-pluggable-authentication, Prev: caching-sha2-pluggable-authentication, Up: authentication-plugins

6.4.1.5 SHA-256 Pluggable Authentication ........................................

MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

This section describes the original noncaching SHA-2 authentication plugin. For information about the caching plugin, see *note caching-sha2-pluggable-authentication::.

Important:

To connect to the server using an account that authenticates with the 'sha256_password' plugin, you must use either a TLS connection or an unencrypted connection that supports password exchange using an RSA key pair, as described later in this section. Either way, the 'sha256_password' plugin uses MySQL' encryption capabilities. See *note encrypted-connections::.

Note:

In the name 'sha256_password', 'sha256' refers to the 256-bit digest length the plugin uses for encryption. In the name 'caching_sha2_password', 'sha2' refers more generally to the SHA-2 class of encryption algorithms, of which 256-bit encryption is one instance. The latter name choice leaves room for future expansion of possible digest lengths without changing the plugin name.

The following table shows the plugin names on the server and client sides.

Plugin and Library Names for SHA-256 Authentication

Plugin or File Plugin or File Name

Server-side plugin 'sha256_password'

Client-side plugin 'sha256_password'

Library file None (plugins are built in)

The following sections provide installation and usage information specific to SHA-256 pluggable authentication:

For general information about pluggable authentication in MySQL, see *note pluggable-authentication::.

Installing SHA-256 Pluggable Authentication

The 'sha256_password' plugin exists in server and client forms:

Using SHA-256 Pluggable Authentication

To set up an account that uses the 'sha256_password' plugin for SHA-256 password hashing, use the following statement, where PASSWORD is the desired account password:

 CREATE USER 'sha256user'@'localhost'
 IDENTIFIED WITH sha256_password BY 'PASSWORD';

The server assigns the 'sha256_password' plugin to the account and uses it to encrypt the password using SHA-256, storing those values in the 'plugin' and 'authentication_string' columns of the 'mysql.user' system table.

The preceding instructions do not assume that 'sha256_password' is the default authentication plugin. If 'sha256_password' is the default authentication plugin, a simpler *note 'CREATE USER': create-user. syntax can be used.

To start the server with the default authentication plugin set to 'sha256_password', put these lines in the server option file:

 [mysqld]
 default_authentication_plugin=sha256_password

That causes the 'sha256_password' plugin to be used by default for new accounts. As a result, it is possible to create the account and set its password without naming the plugin explicitly:

 CREATE USER 'sha256user'@'localhost' IDENTIFIED BY 'PASSWORD';

Another consequence of setting 'default_authentication_plugin' to 'sha256_password' is that, to use some other plugin for account creation, you must specify that plugin explicitly. For example, to use the 'mysql_native_password' plugin, use this statement:

 CREATE USER 'nativeuser'@'localhost'
 IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

'sha256_password' supports connections over secure transport. 'sha256_password' also supports encrypted password exchange using RSA over unencrypted connections if these conditions are satisfied:

RSA support has these characteristics:

For clients that use the 'sha256_password' plugin, passwords are never exposed as cleartext when connecting to the server. How password transmission occurs depends on whether a secure connection or RSA encryption is used:

As mentioned previously, RSA password encryption is available only if MySQL was compiled using OpenSSL. The implication for MySQL distributions compiled using yaSSL is that, to use SHA-256 passwords, clients must use an encrypted connection to access the server. See *note using-encrypted-connections::.

Note:

To use RSA password encryption with 'sha256_password', the client and server both must be compiled using OpenSSL, not just one of them.

Assuming that MySQL has been compiled using OpenSSL, use the following procedure to enable use of an RSA key pair for password exchange during the client connection process:

  1. Create the RSA private and public key-pair files using the instructions in *note creating-ssl-rsa-files::.

  2. If the private and public key files are located in the data directory and are named 'private_key.pem' and 'public_key.pem' (the default values of the 'sha256_password_private_key_path' and 'sha256_password_public_key_path' system variables), the server uses them automatically at startup.

    Otherwise, to name the key files explicitly, set the system variables to the key file names in the server option file. If the files are located in the server data directory, you need not specify their full path names:

      [mysqld]
  sha256_password_private_key_path=myprivkey.pem
  sha256_password_public_key_path=mypubkey.pem

    If the key files are not located in the data directory, or to make their locations explicit in the system variable values, use full path names:

      [mysqld]
  sha256_password_private_key_path=/usr/local/mysql/myprivkey.pem
  sha256_password_public_key_path=/usr/local/mysql/mypubkey.pem

  3. Restart the server, then connect to it and check the 'Rsa_public_key' status variable value. The actual value differs from that shown here, but should be nonempty:

      mysql> SHOW STATUS LIKE 'Rsa_public_key'\G
  *************************** 1. row ***************************
  Variable_name: Rsa_public_key
          Value: -----BEGIN PUBLIC KEY-----
  MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
  MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
  aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
  g8aV7EtKwyhHb0c30QIDAQAB
  -----END PUBLIC KEY-----

    If the value is empty, the server found some problem with the key files. Check the error log for diagnostic information.

After the server has been configured with the RSA key files, accounts that authenticate with the 'sha256_password' plugin have the option of using those key files to connect to the server. As mentioned previously, such accounts can use either a secure connection (in which case RSA is not used) or an unencrypted connection that performs password exchange using RSA. Suppose that an unencrypted connection is used. For example:

 $> mysql --ssl-mode=DISABLED -u sha256user -p
 Enter password: PASSWORD

For this connection attempt by 'sha256user', the server determines that 'sha256_password' is the appropriate authentication plugin and invokes it (because that was the plugin specified at *note 'CREATE USER': create-user. time). The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. In this case, the plugin sends the RSA public key to the client, which uses it to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

The server sends the RSA public key to the client as needed. However, if the client has a file containing a local copy of the RSA public key required by the server, it can specify the file using the '--server-public-key-path' option:

 $> mysql --ssl-mode=DISABLED -u sha256user -p --server-public-key-path=FILE_NAME
 Enter password: PASSWORD

The public key value in the file named by the '--server-public-key-path' option should be the same as the key value in the server-side file named by the 'sha256_password_public_key_path' system variable. If the key file contains a valid public key value but the value is incorrect, an access-denied error occurs. If the key file does not contain a valid public key, the client program cannot use it. In this case, the 'sha256_password' plugin sends the public key to the client as if no '--server-public-key-path' option had been specified.

Client users can obtain the RSA public key two ways:

 File: manual.info.tmp, Node: cleartext-pluggable-authentication, Next: pam-pluggable-authentication, Prev: sha256-pluggable-authentication, Up: authentication-plugins

6.4.1.6 Client-Side Cleartext Pluggable Authentication ......................................................

A client-side authentication plugin is available that enables clients to send passwords to the server as cleartext, without hashing or encryption. This plugin is built into the MySQL client library.

The following table shows the plugin name.

Plugin and Library Names for Cleartext Authentication

Plugin or File Plugin or File Name

Server-side plugin None, see discussion

Client-side plugin 'mysql_clear_password'

Library file None (plugin is built in)

Many client-side authentication plugins perform hashing or encryption of a password before the client sends it to the server. This enables clients to avoid sending passwords as cleartext.

Hashing or encryption cannot be done for authentication schemes that require the server to receive the password as entered on the client side. In such cases, the client-side 'mysql_clear_password' plugin is used, which enables the client to send the password to the server as cleartext. There is no corresponding server-side plugin. Rather, 'mysql_clear_password' can be used on the client side in concert with any server-side plugin that needs a cleartext password. (Examples are the PAM and simple LDAP authentication plugins; see note pam-pluggable-authentication::, and note ldap-pluggable-authentication::.)

The following discussion provides usage information specific to cleartext pluggable authentication. For general information about pluggable authentication in MySQL, see *note pluggable-authentication::.

Note:

Sending passwords as cleartext may be a security problem in some configurations. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MySQL Server using a method that protects the password. Possibilities include SSL (see *note encrypted-connections::), IPsec, or a private network.

To make inadvertent use of the 'mysql_clear_password' plugin less likely, MySQL clients must explicitly enable it. This can be done in several ways:

 File: manual.info.tmp, Node: pam-pluggable-authentication, Next: windows-pluggable-authentication, Prev: cleartext-pluggable-authentication, Up: authentication-plugins

6.4.1.7 PAM Pluggable Authentication ....................................

Note:

PAM pluggable authentication 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 supports an authentication method that enables MySQL Server to use PAM (Pluggable Authentication Modules) to authenticate MySQL users. PAM enables a system to use a standard interface to access various kinds of authentication methods, such as traditional Unix passwords or an LDAP directory.

PAM pluggable authentication provides these capabilities:

PAM pluggable authentication has been tested on Linux and macOS.

The following table shows the plugin and library file names. The file name suffix might differ on your system. The file must be located in the directory named by the 'plugin_dir' system variable. For installation information, see *note pam-pluggable-authentication-installation::.

Plugin and Library Names for PAM Authentication

Plugin or File Plugin or File Name

Server-side plugin 'authentication_pam'

Client-side plugin 'mysql_clear_password'

Library file 'authentication_pam.so'

The client-side 'mysql_clear_password' cleartext plugin that communicates with the server-side PAM plugin is built into the 'libmysqlclient' client library and is included in all distributions, including community distributions. Inclusion of the client-side cleartext plugin in all MySQL distributions enables clients from any distribution to connect to a server that has the server-side PAM plugin loaded.

The following sections provide installation and usage information specific to PAM pluggable authentication:

For general information about pluggable authentication in MySQL, see note pluggable-authentication::. For information about the 'mysql_clear_password' plugin, see note cleartext-pluggable-authentication::. For proxy user information, see *note proxy-users::.

How PAM Authentication of MySQL Users Works

This section provides an overview of how MySQL and PAM work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use specific PAM services, see *note pam-pluggable-authentication-usage::.

  1. The client program and the server communicate, with the client sending to the server the client user name (the operating system user name by default) and password:

    * The client user name is the external user name.

* For accounts that use the PAM server-side authentication
  plugin, the corresponding client-side plugin is
  'mysql_clear_password'.  This client-side plugin performs no
  password hashing, with the result that the client sends the
  password to the server as cleartext.

  2. The server finds a matching MySQL account based on the external user name and the host from which the client connects. The PAM plugin uses the information passed to it by MySQL Server (such as user name, host name, password, and authentication string). When you define a MySQL account that authenticates using PAM, the authentication string contains:

    * A PAM service name, which is a name that the system
  administrator can use to refer to an authentication method for
  a particular application.  There can be multiple applications
  associated with a single database server instance, so the
  choice of service name is left to the SQL application
  developer.

* Optionally, if proxying is to be used, a mapping from PAM
  groups to MySQL user names.

  3. The plugin uses the PAM service named in the authentication string to check the user credentials and returns ''Authentication succeeded, Username is USER_NAME'' or ''Authentication failed''. The password must be appropriate for the password store used by the PAM service. Examples:

    * For traditional Unix passwords, the service looks up passwords
  stored in the '/etc/shadow' file.

* For LDAP, the service looks up passwords stored in an LDAP
  directory.

    If the credentials check fails, the server refuses the connection.

  4. Otherwise, the authentication string indicates whether proxying occurs. If the string contains no PAM group mapping, proxying does not occur. In this case, the MySQL user name is the same as the external user name.

  5. Otherwise, proxying is indicated based on the PAM group mapping, with the MySQL user name determined based on the first matching group in the mapping list. The meaning of 'PAM group' depends on the PAM service. Examples:

    * For traditional Unix passwords, groups are Unix groups defined
  in the '/etc/group' file, possibly supplemented with
  additional PAM information in a file such as
  '/etc/security/group.conf'.

* For LDAP, groups are LDAP groups defined in an LDAP directory.

    If the proxy user (the external user) has the 'PROXY' privilege for the proxied MySQL user name, proxying occurs, with the proxy user assuming the privileges of the proxied user.

Installing PAM Pluggable Authentication

This section describes how to install the server-side PAM authentication plugin. For general information about installing plugins, see *note plugin-loading::.

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 'authentication_pam'. The file name suffix differs per platform (for example, '.so' for Unix and Unix-like systems, '.dll' for Windows).

To load the plugin at server startup, use the '--plugin-load-add' option to name the library file that contains it. 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, adjusting the '.so' suffix for your platform as necessary:

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

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

Alternatively, to load the plugin at runtime, use this statement, adjusting the '.so' suffix for your platform as necessary:

 INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';

*note 'INSTALL PLUGIN': 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 note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE '%pam%';
 +--------------------+---------------+
 | PLUGIN_NAME        | PLUGIN_STATUS |
 +--------------------+---------------+
 | authentication_pam | ACTIVE        |
 +--------------------+---------------+

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

To associate MySQL accounts with the PAM plugin, see *note pam-pluggable-authentication-usage::.

Uninstalling PAM Pluggable Authentication

The method used to uninstall the PAM authentication plugin depends on how you installed it:

Using PAM Pluggable Authentication

This section describes in general terms how to use the PAM authentication plugin to connect from MySQL client programs to the server. The following sections provide instructions for using PAM authentication in specific ways. It is assumed that the server is running with the server-side PAM plugin enabled, as described in *note pam-pluggable-authentication-installation::.

To refer to the PAM authentication plugin in the 'IDENTIFIED WITH' clause of a *note 'CREATE USER': create-user. statement, use the name 'authentication_pam'. For example:

 CREATE USER USER
   IDENTIFIED WITH authentication_pam
   AS 'AUTH_STRING';

The authentication string specifies the following types of information:

For example, if an account uses the 'mysql-unix' PAM service name and should map operating system users in the 'root' and 'users' PAM groups to the 'developer' and 'data_entry' MySQL users, respectively, use a statement like this:

 CREATE USER USER
   IDENTIFIED WITH authentication_pam
   AS 'mysql-unix, root=developer, users=data_entry';

Authentication string syntax for the PAM authentication plugin follows these rules:

If the plugin successfully authenticates the external user name (the name passed by the client), it looks for a PAM group mapping list in the authentication string and, if present, uses it to return a different MySQL user name to the MySQL server based on which PAM groups the external user is a member of:

The following sections describe how to set up several authentication scenarios that use the PAM authentication plugin:

Variations on these scenarios are possible:

The examples make the following assumptions. You might need to make some adjustments if your system is set up differently.

The PAM authentication plugin checks at initialization time whether the 'AUTHENTICATION_PAM_LOG' environment value is set in the server's startup environment. If so, the plugin enables logging of diagnostic messages to the standard output. Depending on how your server is started, the message might appear on the console or in the error log. These messages can be helpful for debugging PAM-related issues that occur when the plugin performs authentication. For more information, see *note pam-pluggable-authentication-debugging::.

PAM Unix Password Authentication without Proxy Users

This authentication scenario uses PAM to check external users defined in terms of operating system user names and Unix passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through traditional Unix password store.

Note:

Traditional Unix passwords are checked using the '/etc/shadow' file. For information regarding possible issues related to this file, see *note pam-authentication-unix-password-store::.

  1. Verify that Unix authentication permits logins to the operating system with the user name 'antonio' and password ANTONIO_PASSWORD.

  2. Set up PAM to authenticate MySQL connections using traditional Unix passwords by creating a 'mysql-unix' PAM service file named '/etc/pam.d/mysql-unix'. The file contents are system dependent, so check existing login-related files in the '/etc/pam.d' directory to see what they look like. On Linux, the 'mysql-unix' file might look like this:

      #%PAM-1.0
  auth            include         password-auth
  account         include         password-auth

    For macOS, use 'login' rather than 'password-auth'.

    The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead:

      @include common-auth
  @include common-account
  @include common-session-noninteractive

  3. Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the 'mysql-unix' PAM service:

      CREATE USER 'antonio'@'localhost'
    IDENTIFIED WITH authentication_pam
    AS 'mysql-unix';
  GRANT ALL PRIVILEGES
    ON mydb.*
    TO 'antonio'@'localhost';

    Here, the authentication string contains only the PAM service name, 'mysql-unix', which authenticates Unix passwords.

  4. Use the *note 'mysql': mysql. command-line client to connect to the MySQL server as 'antonio'. For example:

      $> mysql --user=antonio --password --enable-cleartext-plugin
  Enter password: ANTONIO_PASSWORD

    The server should permit the connection and the following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
  +-------------------+-------------------+--------------+
  | USER()            | CURRENT_USER()    | @@proxy_user |
  +-------------------+-------------------+--------------+
  | antonio@localhost | antonio@localhost | NULL         |
  +-------------------+-------------------+--------------+

    This demonstrates that the 'antonio' operating system user is authenticated to have the privileges granted to the 'antonio' MySQL user, and that no proxying has occurred.

Note:

The client-side 'mysql_clear_password' authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to PAM. A cleartext password is necessary to use the server-side PAM library, but may be a security problem in some configurations. These measures minimize the risk:

PAM LDAP Authentication without Proxy Users

This authentication scenario uses PAM to check external users defined in terms of operating system user names and LDAP passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through LDAP.

To use PAM LDAP pluggable authentication for MySQL, these prerequisites must be satisfied:

Note:

Another way to use LDAP for MySQL user authentication is to use the LDAP-specific authentication plugins. See *note ldap-pluggable-authentication::.

Configure MySQL for PAM LDAP authentication as follows:

  1. Verify that Unix authentication permits logins to the operating system with the user name 'antonio' and password ANTONIO_PASSWORD.

  2. Set up PAM to authenticate MySQL connections using LDAP by creating a 'mysql-ldap' PAM service file named '/etc/pam.d/mysql-ldap'. The file contents are system dependent, so check existing login-related files in the '/etc/pam.d' directory to see what they look like. On Linux, the 'mysql-ldap' file might look like this:

      #%PAM-1.0
  auth        required    pam_ldap.so
  account     required    pam_ldap.so

    If PAM object files have a suffix different from '.so' on your system, substitute the correct suffix.

    The PAM file format might differ on some systems.

  3. Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the 'mysql-ldap' PAM service:

      CREATE USER 'antonio'@'localhost'
    IDENTIFIED WITH authentication_pam
    AS 'mysql-ldap';
  GRANT ALL PRIVILEGES
    ON mydb.*
    TO 'antonio'@'localhost';

    Here, the authentication string contains only the PAM service name, 'mysql-ldap', which authenticates using LDAP.

  4. Connecting to the server is the same as described in *note pam-authentication-unix-without-proxy::.

PAM Unix Password Authentication with Proxy Users and Group Mapping

The authentication scheme described here uses proxying and PAM group mapping to map connecting MySQL users who authenticate using PAM onto other MySQL accounts that define different sets of privileges. Users do not connect directly through the accounts that define the privileges. Instead, they connect through a default proxy account authenticated using PAM, such that all the external users are mapped to the MySQL accounts that hold the privileges. Any user who connects using the proxy account is mapped to one of those MySQL accounts, the privileges for which determine the database operations permitted to the external user.

The procedure shown here uses Unix password authentication. To use LDAP instead, see the early steps of *note pam-authentication-ldap-without-proxy::.

Note:

Traditional Unix passwords are checked using the '/etc/shadow' file. For information regarding possible issues related to this file, see *note pam-authentication-unix-password-store::.

  1. Verify that Unix authentication permits logins to the operating system with the user name 'antonio' and password ANTONIO_PASSWORD.

  2. Verify that 'antonio' is a member of the 'root' or 'users' PAM group.

  3. Set up PAM to authenticate the 'mysql-unix' PAM service through operating system users by creating a file named '/etc/pam.d/mysql-unix'. The file contents are system dependent, so check existing login-related files in the '/etc/pam.d' directory to see what they look like. On Linux, the 'mysql-unix' file might look like this:

      #%PAM-1.0
  auth            include         password-auth
  account         include         password-auth

    For macOS, use 'login' rather than 'password-auth'.

    The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead:

      @include common-auth
  @include common-account
  @include common-session-noninteractive

  4. Create a default proxy user ('''@''') that maps external PAM users to the proxied accounts:

      CREATE USER ''@''
    IDENTIFIED WITH authentication_pam
    AS 'mysql-unix, root=developer, users=data_entry';

    Here, the authentication string contains the PAM service name, 'mysql-unix', which authenticates Unix passwords. The authentication string also maps external users in the 'root' and 'users' PAM groups to the 'developer' and 'data_entry' MySQL user names, respectively.

    The PAM group mapping list following the PAM service name is required when you set up proxy users. Otherwise, the plugin cannot tell how to perform mapping from external user names to the proper proxied MySQL user names.

    Note:

    If your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this issue, and ways of dealing with it, see *note proxy-users-conflicts::.

  5. Create the proxied accounts and grant to each one the privileges it should have:

      CREATE USER 'developer'@'localhost'
    IDENTIFIED WITH mysql_no_login;
  CREATE USER 'data_entry'@'localhost'
    IDENTIFIED WITH mysql_no_login;

  GRANT ALL PRIVILEGES
    ON mydevdb.*
    TO 'developer'@'localhost';
  GRANT ALL PRIVILEGES
    ON mydb.*
    TO 'data_entry'@'localhost';

    The proxied accounts use the 'mysql_no_login' authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, it is expected that users who authenticate using PAM use the 'developer' or 'data_entry' account by proxy based on their PAM group. (This assumes that the plugin is installed. For instructions, see note no-login-pluggable-authentication::.) For alternative methods of protecting proxied accounts against direct use, see note preventing-proxied-account-direct-login::.

  6. Grant to the proxy account the 'PROXY' privilege for each proxied account:

      GRANT PROXY
    ON 'developer'@'localhost'
    TO ''@'';
  GRANT PROXY
    ON 'data_entry'@'localhost'
    TO ''@'';

  7. Use the *note 'mysql': mysql. command-line client to connect to the MySQL server as 'antonio'.

      $> mysql --user=antonio --password --enable-cleartext-plugin
  Enter password: ANTONIO_PASSWORD

    The server authenticates the connection using the default '''@''' proxy account. The resulting privileges for 'antonio' depend on which PAM groups 'antonio' is a member of. If 'antonio' is a member of the 'root' PAM group, the PAM plugin maps 'root' to the 'developer' MySQL user name and returns that name to the server. The server verifies that '''@''' has the 'PROXY' privilege for 'developer' and permits the connection. The following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
  +-------------------+---------------------+--------------+
  | USER()            | CURRENT_USER()      | @@proxy_user |
  +-------------------+---------------------+--------------+
  | antonio@localhost | developer@localhost | ''@''        |
  +-------------------+---------------------+--------------+

    This demonstrates that the 'antonio' operating system user is authenticated to have the privileges granted to the 'developer' MySQL user, and that proxying occurs through the default proxy account.

    If 'antonio' is not a member of the 'root' PAM group but is a member of the 'users' PAM group, a similar process occurs, but the plugin maps 'user' PAM group membership to the 'data_entry' MySQL user name and returns that name to the server:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
  +-------------------+----------------------+--------------+
  | USER()            | CURRENT_USER()       | @@proxy_user |
  +-------------------+----------------------+--------------+
  | antonio@localhost | data_entry@localhost | ''@''        |
  +-------------------+----------------------+--------------+

    This demonstrates that the 'antonio' operating system user is authenticated to have the privileges of the 'data_entry' MySQL user, and that proxying occurs through the default proxy account.

Note:

The client-side 'mysql_clear_password' authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to PAM. A cleartext password is necessary to use the server-side PAM library, but may be a security problem in some configurations. These measures minimize the risk:

PAM Authentication Access to Unix Password Store

On some systems, Unix authentication uses a password store such as '/etc/shadow', a file that typically has restricted access permissions. This can cause MySQL PAM-based authentication to fail. Unfortunately, the PAM implementation does not permit distinguishing 'password could not be checked' (due, for example, to inability to read '/etc/shadow') from 'password does not match.' If you are using Unix password store for PAM authentication, you may be able to enable access to it from MySQL using one of the following methods:

PAM Authentication Debugging

The PAM authentication plugin checks at initialization time whether the 'AUTHENTICATION_PAM_LOG' environment value is set. In MySQL 5.7, and in MySQL NDB Cluster rrior to NDB 7.5.33 and NDB 7.6.29, the value does not matter. The plugin enables logging of diagnostic messages to the standard output, including passwords. These messages may be helpful for debugging PAM-related issues that occur when the plugin performs authentication.

In MySQL NDB Cluster, beginning with versions 7.5.33 and 7.6.29, passwords are not included if you set 'AUTHENTICATION_PAM_LOG=1' (or some other arbitrary value); you can enable logging of debugging messages, passwords included, by setting 'AUTHENTICATION_PAM_LOG=PAM_LOG_WITH_SECRET_INFO'.

Some messages include reference to PAM plugin source files and line numbers, which enables plugin actions to be tied more closely to the location in the code where they occur.

Another technique for debugging connection failures and determining what is happening during connection attempts is to configure PAM authentication to permit all connections, then check the system log files. This technique should be used only on a temporary basis, and not on a production server.

Configure a PAM service file named '/etc/pam.d/mysql-any-password' with these contents (the format may differ on some systems):

 #%PAM-1.0
 auth        required    pam_permit.so
 account     required    pam_permit.so

Create an account that uses the PAM plugin and names the 'mysql-any-password' PAM service:

 CREATE USER 'testuser'@'localhost'
   IDENTIFIED WITH authentication_pam
   AS 'mysql-any-password';

The 'mysql-any-password' service file causes any authentication attempt to return true, even for incorrect passwords. If an authentication attempt fails, that tells you the configuration problem is on the MySQL side. Otherwise, the problem is on the operating system/PAM side. To see what might be happening, check system log files such as '/var/log/secure', '/var/log/audit.log', '/var/log/syslog', or '/var/log/messages'.

After determining what the problem is, remove the 'mysql-any-password' PAM service file to disable any-password access.

 File: manual.info.tmp, Node: windows-pluggable-authentication, Next: ldap-pluggable-authentication, Prev: pam-pluggable-authentication, Up: authentication-plugins

6.4.1.8 Windows Pluggable Authentication ........................................

Note:

Windows pluggable authentication 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 for Windows supports an authentication method that performs external authentication on Windows, enabling MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password.

The client and server exchange data packets in the authentication handshake. As a result of this exchange, the server creates a security context object that represents the identity of the client in the Windows OS. This identity includes the name of the client account. Windows pluggable authentication uses the identity of the client to check whether it is a given account or a member of a group. By default, negotiation uses Kerberos to authenticate, then NTLM if Kerberos is unavailable.

Windows pluggable authentication provides these capabilities:

The following table shows the plugin and library file names. The file must be located in the directory named by the 'plugin_dir' system variable.

Plugin and Library Names for Windows Authentication

Plugin or File Plugin or File Name

Server-side plugin 'authentication_windows'

Client-side plugin 'authentication_windows_client'

Library file 'authentication_windows.dll'

The library file includes only the server-side plugin. The client-side plugin is built into the 'libmysqlclient' client library.

The server-side Windows authentication plugin is included only in MySQL Enterprise Edition. It is not included in MySQL community distributions. The client-side plugin is included in all distributions, including community distributions. This enables clients from any distribution to connect to a server that has the server-side plugin loaded.

The following sections provide installation and usage information specific to Windows pluggable authentication:

For general information about pluggable authentication in MySQL, see note pluggable-authentication::. For proxy user information, see note proxy-users::.

Installing Windows Pluggable Authentication

This section describes how to install the server-side Windows authentication plugin. For general information about installing plugins, see *note plugin-loading::.

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 load the plugin at server startup, use the '--plugin-load-add' option to name the library file that contains it. 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:

 [mysqld]
 plugin-load-add=authentication_windows.dll

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

Alternatively, to load the plugin at runtime, use this statement:

 INSTALL PLUGIN authentication_windows SONAME 'authentication_windows.dll';

*note 'INSTALL PLUGIN': 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 note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE '%windows%';
 +------------------------+---------------+
 | PLUGIN_NAME            | PLUGIN_STATUS |
 +------------------------+---------------+
 | authentication_windows | ACTIVE        |
 +------------------------+---------------+

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

To associate MySQL accounts with the Windows authentication plugin, see note windows-pluggable-authentication-usage::. Additional plugin control is provided by the 'authentication_windows_use_principal_name' and 'authentication_windows_log_level' system variables. See note server-system-variables::.

Uninstalling Windows Pluggable Authentication

The method used to uninstall the Windows authentication plugin depends on how you installed it:

In addition, remove any startup options that set Windows plugin-related system variables.

Using Windows Pluggable Authentication

The Windows authentication plugin supports the use of MySQL accounts such that users who have logged in to Windows can connect to the MySQL server without having to specify an additional password. It is assumed that the server is running with the server-side plugin enabled, as described in *note windows-pluggable-authentication-installation::. Once the DBA has enabled the server-side plugin and set up accounts to use it, clients can connect using those accounts with no other setup required on their part.

To refer to the Windows authentication plugin in the 'IDENTIFIED WITH' clause of a *note 'CREATE USER': create-user. statement, use the name 'authentication_windows'. Suppose that the Windows users 'Rafal' and 'Tasha' should be permitted to connect to MySQL, as well as any users in the 'Administrators' or 'Power Users' group. To set this up, create a MySQL account named 'sql_admin' that uses the Windows plugin for authentication:

 CREATE USER sql_admin
   IDENTIFIED WITH authentication_windows
   AS 'Rafal, Tasha, Administrators, "Power Users"';

The plugin name is 'authentication_windows'. The string following the 'AS' keyword is the authentication string. It specifies that the Windows users named 'Rafal' or 'Tasha' are permitted to authenticate to the server as the MySQL user 'sql_admin', as are any Windows users in the 'Administrators' or 'Power Users' group. The latter group name contains a space, so it must be quoted with double quote characters.

After you create the 'sql_admin' account, a user who has logged in to Windows can attempt to connect to the server using that account:

 C:\> mysql --user=sql_admin

No password is required here. The 'authentication_windows' plugin uses the Windows security API to check which Windows user is connecting. If that user is named 'Rafal' or 'Tasha', or is a member of the 'Administrators' or 'Power Users' group, the server grants access and the client is authenticated as 'sql_admin' and has whatever privileges are granted to the 'sql_admin' account. Otherwise, the server denies access.

Authentication string syntax for the Windows authentication plugin follows these rules:

When invoked by the server to authenticate a client, the plugin scans the authentication string left to right for a user or group match to the Windows user. If there is a match, the plugin returns the corresponding MYSQL_USER_NAME to the MySQL server. If there is no match, authentication fails.

A user name match takes preference over a group name match. Suppose that the Windows user named 'win_user' is a member of 'win_group' and the authentication string looks like this:

 'win_group = sql_user1, win_user = sql_user2'

When 'win_user' connects to the MySQL server, there is a match both to 'win_group' and to 'win_user'. The plugin authenticates the user as 'sql_user2' because the more-specific user match takes precedence over the group match, even though the group is listed first in the authentication string.

Windows authentication always works for connections from the same computer on which the server is running. For cross-computer connections, both computers must be registered with Microsoft Active Directory. If they are in the same Windows domain, it is unnecessary to specify a domain name. It is also possible to permit connections from a different domain, as in this example:

 CREATE USER sql_accounting
   IDENTIFIED WITH authentication_windows
   AS 'SomeDomain\\Accounting';

Here 'SomeDomain' is the name of the other domain. The backslash character is doubled because it is the MySQL escape character within strings.

MySQL supports the concept of proxy users whereby a client can connect and authenticate to the MySQL server using one account but while connected has the privileges of another account (see *note proxy-users::). Suppose that you want Windows users to connect using a single user name but be mapped based on their Windows user and group names onto specific MySQL accounts as follows:

To set this up, create a proxy account for Windows users to connect to, and configure this account so that users and groups map to the appropriate MySQL accounts ('local_wlad', 'local_dev', 'local_admin'). In addition, grant the MySQL accounts the privileges appropriate to the operations they need to perform. The following instructions use 'win_proxy' as the proxy account, and 'local_wlad', 'local_dev', and 'local_admin' as the proxied accounts.

  1. Create the proxy MySQL account:

      CREATE USER win_proxy
    IDENTIFIED WITH  authentication_windows
    AS 'local_user = local_wlad,
        MyDomain\\domain_user = local_wlad,
        MyDomain\\Developers = local_dev,
        BUILTIN\\Administrators = local_admin';

  2. For proxying to work, the proxied accounts must exist, so create them:

      CREATE USER local_wlad
    IDENTIFIED WITH mysql_no_login;
  CREATE USER local_dev
    IDENTIFIED WITH mysql_no_login;
  CREATE USER local_admin
    IDENTIFIED WITH mysql_no_login;

    The proxied accounts use the 'mysql_no_login' authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, it is expected that users who authenticate using Windows use the 'win_proxy' proxy account. (This assumes that the plugin is installed. For instructions, see note no-login-pluggable-authentication::.) For alternative methods of protecting proxied accounts against direct use, see note preventing-proxied-account-direct-login::.

    You should also execute *note 'GRANT': grant. statements (not shown) that grant each proxied account the privileges required for MySQL access.

  3. Grant to the proxy account the 'PROXY' privilege for each proxied account:

      GRANT PROXY ON local_wlad TO win_proxy;
  GRANT PROXY ON local_dev TO win_proxy;
  GRANT PROXY ON local_admin TO win_proxy;

Now the Windows users 'local_user' and 'MyDomain_user' can connect to the MySQL server as 'win_proxy' and when authenticated have the privileges of the account given in the authentication string (in this case, 'local_wlad'). A user in the 'MyDomain' group who connects as 'win_proxy' has the privileges of the 'local_dev' account. A user in the 'BUILTIN' group has the privileges of the 'local_admin' account.

To configure authentication so that all Windows users who do not have their own MySQL account go through a proxy account, substitute the default proxy account ('''@''') for 'win_proxy' in the preceding instructions. For information about default proxy accounts, see *note proxy-users::.

Note:

If your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this issue, and ways of dealing with it, see *note proxy-users-conflicts::.

To use the Windows authentication plugin with Connector/NET connection strings in Connector/NET 8.0 and higher, see Connector/NET Authentication (https://dev.mysql.com/doc/connector-net/en/connector-net-authentication.html).

 File: manual.info.tmp, Node: ldap-pluggable-authentication, Next: no-login-pluggable-authentication, Prev: windows-pluggable-authentication, Up: authentication-plugins

6.4.1.9 LDAP Pluggable Authentication .....................................

Note:

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

As of MySQL 5.7.19, MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use LDAP (Lightweight Directory Access Protocol) to authenticate MySQL users by accessing directory services such as X.500. MySQL uses LDAP to fetch user, credential, and group information.

LDAP pluggable authentication provides these capabilities:

The following tables show the plugin and library file names for simple and SASL-based LDAP authentication. The file name suffix might differ on your system. The files must be located in the directory named by the 'plugin_dir' system variable.

Plugin and Library Names for Simple LDAP Authentication

Plugin or File Plugin or File Name

Server-side plugin 'authentication_ldap_simple' name

Client-side plugin 'mysql_clear_password' name

Library file name 'authentication_ldap_simple.so'

Plugin and Library Names for SASL-Based LDAP Authentication

Plugin or File Plugin or File Name

Server-side plugin 'authentication_ldap_sasl' name

Client-side plugin 'authentication_ldap_sasl_client' name

Library file names 'authentication_ldap_sasl.so', 'authentication_ldap_sasl_client.so'

The library files include only the 'authentication_ldap_XXX' authentication plugins. The client-side 'mysql_clear_password' plugin is built into the 'libmysqlclient' client library.

Each server-side LDAP plugin works with a specific client-side plugin:

The server-side LDAP authentication plugins are included only in MySQL Enterprise Edition. They are not included in MySQL community distributions. The client-side SASL LDAP plugin is included in all distributions, including community distributions, and, as mentioned previously, the client-side 'mysql_clear_password' plugin is built into the 'libmysqlclient' client library, which also is included in all distributions. This enables clients from any distribution to connect to a server that has the appropriate server-side plugin loaded.

The following sections provide installation and usage information specific to LDAP pluggable authentication:

For general information about pluggable authentication in MySQL, see note pluggable-authentication::. For information about the 'mysql_clear_password' plugin, see note cleartext-pluggable-authentication::. For proxy user information, see *note proxy-users::.

Note:

If your system supports PAM and permits LDAP as a PAM authentication method, another way to use LDAP for MySQL user authentication is to use the server-side 'authentication_pam' plugin. See *note pam-pluggable-authentication::.

Prerequisites for LDAP Pluggable Authentication

To use LDAP pluggable authentication for MySQL, these prerequisites must be satisfied:

How LDAP Authentication of MySQL Users Works

This section provides a general overview of how MySQL and LDAP work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use specific LDAP authentication plugins, see *note ldap-pluggable-authentication-usage::.

The client connects to the MySQL server, providing the MySQL client user name and the LDAP password:

If the client user name and host name match no MySQL account, the connection is rejected.

If there is a matching MySQL account, authentication against LDAP occurs. The LDAP server looks for an entry matching the user and authenticates the entry against the LDAP password:

If the LDAP server finds no match or multiple matches, authentication fails and the client connection is rejected.

If the LDAP server finds a single match, LDAP authentication succeeds (assuming that the password is correct), the LDAP server returns the LDAP entry, and the authentication plugin determines the name of the authenticated user based on that entry:

The MySQL server compares the client user name with the authenticated user name to determine whether proxying occurs for the client session:

Installing LDAP Pluggable Authentication

This section describes how to install the server-side LDAP authentication plugins. For general information about installing plugins, see *note plugin-loading::.

To be usable by the server, the plugin library files 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 server-side plugin library file base names are 'authentication_ldap_simple' and 'authentication_ldap_sasl'. 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 '--plugin-load-add' options to name the library files that contain them. With this plugin-loading method, the options must be given each time the server starts. Also, specify values for any plugin-provided system variables you wish to configure.

Each server-side LDAP plugin exposes a set of system variables that enable its operation to be configured. Setting most of these is optional, but you must set the variables that specify the LDAP server host (so the plugin knows where to connect) and base distinguished name for LDAP bind operations (to limit the scope of searches and obtain faster searches). For details about all LDAP system variables, see *note pluggable-authentication-system-variables::.

To load the plugins and set the LDAP server host and base distinguished name for LDAP bind operations, put lines such as these in your 'my.cnf' file, adjusting the '.so' suffix for your platform as necessary:

 [mysqld]
 plugin-load-add=authentication_ldap_simple.so
 authentication_ldap_simple_server_host=127.0.0.1
 authentication_ldap_simple_bind_base_dn="dc=example,dc=com"
 plugin-load-add=authentication_ldap_sasl.so
 authentication_ldap_sasl_server_host=127.0.0.1
 authentication_ldap_sasl_bind_base_dn="dc=example,dc=com"

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, adjusting the '.so' suffix for your platform as necessary:

 INSTALL PLUGIN authentication_ldap_simple
   SONAME 'authentication_ldap_simple.so';
 INSTALL PLUGIN authentication_ldap_sasl
   SONAME 'authentication_ldap_sasl.so';

*note 'INSTALL PLUGIN': 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'.

After installing the plugins at runtime, their system variables become available and you can add settings for them to your 'my.cnf' file to configure the plugins for subsequent restarts. For example:

 [mysqld]
 authentication_ldap_simple_server_host=127.0.0.1
 authentication_ldap_simple_bind_base_dn="dc=example,dc=com"
 authentication_ldap_sasl_server_host=127.0.0.1
 authentication_ldap_sasl_bind_base_dn="dc=example,dc=com"

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

To verify plugin installation, examine the Information Schema note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE '%ldap%';
 +----------------------------+---------------+
 | PLUGIN_NAME                | PLUGIN_STATUS |
 +----------------------------+---------------+
 | authentication_ldap_sasl   | ACTIVE        |
 | authentication_ldap_simple | ACTIVE        |
 +----------------------------+---------------+

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

To associate MySQL accounts with an LDAP plugin, see *note ldap-pluggable-authentication-usage::.

Additional Notes for SELinux:

On systems running EL6 or EL that have SELinux enabled, changes to the SELinux policy are required to enable the MySQL LDAP plugins to communicate with the LDAP service:

  1. Create a file 'mysqlldap.te' with these contents:

      module mysqlldap 1.0;

  require {
          type ldap_port_t;
          type mysqld_t;
          class tcp_socket name_connect;
  }

  #============= mysqld_t ==============

  allow mysqld_t ldap_port_t:tcp_socket name_connect;

  2. Compile the security policy module into a binary representation:

      checkmodule -M -m mysqlldap.te -o mysqlldap.mod

  3. Create an SELinux policy module package:

      semodule_package -m mysqlldap.mod  -o mysqlldap.pp

  4. Install the module package:

      semodule -i mysqlldap.pp

  5. When the SELinux policy changes have been made, restart the MySQL server:

      service mysqld restart

Uninstalling LDAP Pluggable Authentication

The method used to uninstall the LDAP authentication plugins depends on how you installed them:

In addition, remove from your 'my.cnf' file any startup options that set LDAP plugin-related system variables.

LDAP Pluggable Authentication and ldap.conf

For installations that use OpenLDAP, the 'ldap.conf' file provides global defaults for LDAP clients. Options can be set in this file to affect LDAP clients, including the LDAP authentication plugins. OpenLDAP uses configuration options in this order of precedence:

If the library defaults or 'ldap.conf' values do not yield appropriate option values, an LDAP authentication plugin may be able to set related variables to affect the LDAP configuration directly. For example, LDAP plugins can override 'ldap.conf' parameters for TLS configuration: System variables are available to enable TLS and control CA configuration, such as 'authentication_ldap_simple_tls' and 'authentication_ldap_simple_ca_path' for simple LDAP authentication, and 'authentication_ldap_sasl_tls' and 'authentication_ldap_sasl_ca_path' for SASL LDAP authentication.

For more information about 'ldap.conf' consult the 'ldap.conf(5)' man page.

Using LDAP Pluggable Authentication

This section describes how to enable MySQL accounts to connect to the MySQL server using LDAP pluggable authentication. It is assumed that the server is running with the appropriate server-side plugins enabled, as described in *note ldap-pluggable-authentication-installation::, and that the appropriate client-side plugins are available on the client host.

This section does not describe LDAP configuration or administration. You are assumed to be familiar with those topics.

The two server-side LDAP plugins each work with a specific client-side plugin:

Overall requirements for LDAP authentication of MySQL users:

The instructions here assume the following scenario:

The instructions for setting up an account that uses LDAP authentication depend on which server-side LDAP plugin is used. The following sections describe several usage scenarios.

Simple LDAP Authentication (Without Proxying)

The procedure outlined in this section requires that 'authentication_ldap_simple_group_search_attr' be set to an empty string, like this:

 SET GLOBAL.authentication_ldap_simple_group_search_attr='';

Otherwise, proxying is used by default.

To set up a MySQL account for simple LDAP authentication, use a *note 'CREATE USER': create-user. statement to specify the 'authentication_ldap_simple' plugin, optionally including the LDAP user distinguished name (DN), as shown here:

 CREATE USER USER
   IDENTIFIED WITH authentication_ldap_simple
   [BY 'LDAP USER DN'];

Suppose that MySQL user 'betsy' has this entry in the LDAP directory:

 uid=betsy_ldap,ou=People,dc=example,dc=com

Then the statement to create the MySQL account for 'betsy' looks like this:

 CREATE USER 'betsy'@'localhost'
   IDENTIFIED WITH authentication_ldap_simple
   AS 'uid=betsy_ldap,ou=People,dc=example,dc=com';

The authentication string specified in the 'BY' clause does not include the LDAP password. That must be provided by the client user at connect time.

Clients connect to the MySQL server by providing the MySQL user name and LDAP password, and by enabling the client-side 'mysql_clear_password' plugin:

 $> mysql --user=betsy --password --enable-cleartext-plugin
 Enter password: BETSY_LDAP_PASSWORD

Note:

The client-side 'mysql_clear_password' authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to the LDAP server. A cleartext password is necessary to use the server-side LDAP library without SASL, but may be a security problem in some configurations. These measures minimize the risk:

The authentication process occurs as follows:

  1. The client-side plugin sends 'betsy' and BETSY_PASSWORD as the client user name and LDAP password to the MySQL server.

  2. The connection attempt matches the ''betsy'@'localhost'' account. The server-side LDAP plugin finds that this account has an authentication string of ''uid=betsy_ldap,ou=People,dc=example,dc=com'' to name the LDAP user DN. The plugin sends this string and the LDAP password to the LDAP server.

  3. The LDAP server finds the LDAP entry for 'betsy_ldap' and the password matches, so LDAP authentication succeeds.

  4. The LDAP entry has no group attribute, so the server-side plugin returns the client user name ('betsy') as the authenticated user. This is the same user name supplied by the client, so no proxying occurs and the client session uses the ''betsy'@'localhost'' account for privilege checking.

Had the *note 'CREATE USER': create-user. statement contained no 'BY' clause to specify the 'betsy_ldap' LDAP distinguished name, authentication attempts would use the user name provided by the client (in this case, 'betsy'). In the absence of an LDAP entry for 'betsy', authentication would fail.

SASL-Based LDAP Authentication (Without Proxying)

The procedure outlined in this section requires that 'authentication_ldap_sasl_group_search_attr' be set to an empty string, like this:

 SET GLOBAL.authentication_ldap_sasl_group_search_attr='';

Otherwise, proxying is used by default.

To set up a MySQL account for SALS LDAP authentication, use a *note 'CREATE USER': create-user. statement to specify the 'authentication_ldap_sasl' plugin, optionally including the LDAP user distinguished name (DN), as shown here:

 CREATE USER USER
   IDENTIFIED WITH authentication_ldap_sasl
   [BY 'LDAP USER DN'];

Suppose that MySQL user 'boris' has this entry in the LDAP directory:

 uid=boris_ldap,ou=People,dc=example,dc=com

Then the statement to create the MySQL account for 'boris' looks like this:

 CREATE USER 'boris'@'localhost'
   IDENTIFIED WITH authentication_ldap_sasl
   AS 'uid=boris_ldap,ou=People,dc=example,dc=com';

The authentication string specified in the 'BY' clause does not include the LDAP password. That must be provided by the client user at connect time.

Clients connect to the MySQL server by providing the MySQL user name and LDAP password:

 $> mysql --user=boris --password
 Enter password: BORIS_LDAP_PASSWORD

For the server-side 'authentication_ldap_sasl' plugin, clients use the client-side 'authentication_ldap_sasl_client' plugin. If a client program does not find the client-side plugin, specify a '--plugin-dir' option that names the directory where the plugin library file is installed.

The authentication process for 'boris' is similar to that previously described for 'betsy' with simple LDAP authentication, except that the client-side and server-side SASL LDAP plugins use SASL messages for secure transmission of credentials within the LDAP protocol, to avoid sending the cleartext password between the MySQL client and server.

LDAP Authentication with Proxying

LDAP authentication plugins support proxying, enabling a user to connect to the MySQL server as one user but assume the privileges of a different user. This section describes basic LDAP plugin proxy support. The LDAP plugins also support specification of group preference and proxy user mapping; see *note ldap-pluggable-authentication-usage-group-mapping::.

The proxying implementation described here is based on use of LDAP group attribute values to map connecting MySQL users who authenticate using LDAP onto other MySQL accounts that define different sets of privileges. Users do not connect directly through the accounts that define the privileges. Instead, they connect through a default proxy account authenticated with LDAP, such that all external logins are mapped to the proxied MySQL accounts that hold the privileges. Any user who connects using the proxy account is mapped to one of those proxied MySQL accounts, the privileges for which determine the database operations permitted to the external user.

The instructions here assume the following scenario:

Create the default proxy MySQL account:

 CREATE USER ''@'%'
   IDENTIFIED WITH authentication_ldap_sasl;

The proxy account definition has no 'AS 'AUTH_STRING'' clause to name an LDAP user DN. Thus:

Note:

If your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this issue, and ways of dealing with it, see *note proxy-users-conflicts::.

Create the proxied accounts and grant to each one the privileges it should have:

 CREATE USER 'accounting'@'localhost'
   IDENTIFIED WITH mysql_no_login;
 CREATE USER 'front_office'@'localhost'
   IDENTIFIED WITH mysql_no_login;

 GRANT ALL PRIVILEGES
   ON accountingdb.*
   TO 'accounting'@'localhost';
 GRANT ALL PRIVILEGES
   ON frontdb.*
   TO 'front_office'@'localhost';

The proxied accounts use the 'mysql_no_login' authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, users who authenticate using LDAP are expected to use the default '''@'%'' proxy account. (This assumes that the 'mysql_no_login' plugin is installed. For instructions, see note no-login-pluggable-authentication::.) For alternative methods of protecting proxied accounts against direct use, see note preventing-proxied-account-direct-login::.

Grant to the proxy account the 'PROXY' privilege for each proxied account:

 GRANT PROXY
   ON 'accounting'@'localhost'
   TO ''@'%';
 GRANT PROXY
   ON 'front_office'@'localhost'
   TO ''@'%';

Use the *note 'mysql': mysql. command-line client to connect to the MySQL server as 'basha'.

 $> mysql --user=basha --password
 Enter password: BASHA_PASSWORD (BASHA LDAP PASSWORD)

Authentication occurs as follows:

  1. The server authenticates the connection using the default '''@'%'' proxy account, for client user 'basha'.

  2. The matching LDAP entry is:

      uid=basha,ou=People,dc=example,dc=com,cn=accounting

  3. The matching LDAP entry has group attribute 'cn=accounting', so 'accounting' becomes the authenticated proxied user.

  4. The authenticated user differs from the client user name 'basha', with the result that 'basha' is treated as a proxy for 'accounting', and 'basha' assumes the privileges of the proxied 'accounting' account. The following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
  +-----------------+----------------------+--------------+
  | USER()          | CURRENT_USER()       | @@proxy_user |
  +-----------------+----------------------+--------------+
  | basha@localhost | accounting@localhost | ''@'%'       |
  +-----------------+----------------------+--------------+

This demonstrates that 'basha' uses the privileges granted to the proxied 'accounting' MySQL account, and that proxying occurs through the default proxy user account.

Now connect as 'basil' instead:

 $> mysql --user=basil --password
 Enter password: BASIL_PASSWORD (BASIL LDAP PASSWORD)

The authentication process for 'basil' is similar to that previously described for 'basha':

  1. The server authenticates the connection using the default '''@'%'' proxy account, for client user 'basil'.

  2. The matching LDAP entry is:

      uid=basil,ou=People,dc=example,dc=com,cn=front_office

  3. The matching LDAP entry has group attribute 'cn=front_office', so 'front_office' becomes the authenticated proxied user.

  4. The authenticated user differs from the client user name 'basil', with the result that 'basil' is treated as a proxy for 'front_office', and 'basil' assumes the privileges of the proxied 'front_office' account. The following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
  +-----------------+------------------------+--------------+
  | USER()          | CURRENT_USER()         | @@proxy_user |
  +-----------------+------------------------+--------------+
  | basil@localhost | front_office@localhost | ''@'%'       |
  +-----------------+------------------------+--------------+

This demonstrates that 'basil' uses the privileges granted to the proxied 'front_office' MySQL account, and that proxying occurs through the default proxy user account.

LDAP Authentication Group Preference and Mapping Specification

As described in *note ldap-pluggable-authentication-usage-proxying::, basic LDAP authentication proxying works by the principle that the plugin uses the first group name returned by the LDAP server as the MySQL proxied user account name. This simple capability does not enable specifying any preference about which group name to use if the LDAP server returns multiple group names, or specifying any name other than the group name as the proxied user name.

As of MySQL 5.7.25, for MySQL accounts that use LDAP authentication, the authentication string can specify the following information to enable greater proxying flexibility:

Consider the following MySQL proxy account definition:

 CREATE USER ''@'%'
   IDENTIFIED WITH authentication_ldap_sasl
   AS '+ou=People,dc=example,dc=com#grp1=usera,grp2,grp3=userc';

The authentication string has a user DN suffix 'ou=People,dc=example,dc=com' prefixed by the '+' character. Thus, as described in *note ldap-pluggable-authentication-usage-user-dn-suffix::, the full user DN is constructed from the user DN suffix as specified, plus the client user name as the 'uid' attribute.

The remaining part of the authentication string begins with '#', which signifies the beginning of group preference and mapping information. This part of the authentication string lists group names in the order 'grp1', 'grp2', 'grp3'. The LDAP plugin compares that list with the set of group names returned by the LDAP server, looking in list order for a match against the returned names. The plugin uses the first match, or if there is no match, authentication fails.

Suppose that the LDAP server returns groups 'grp3', 'grp2', and 'grp7'. The LDAP plugin uses 'grp2' because it is the first group in the authentication string that matches, even though it is not the first group returned by the LDAP server. If the LDAP server returns 'grp4', 'grp2', and 'grp1', the plugin uses 'grp1' even though 'grp2' also matches. 'grp1' has a precedence higher than 'grp2' because it is listed earlier in the authentication string.

Assuming that the plugin finds a group name match, it performs mapping from that group name to the MySQL proxied user name, if there is one. For the example proxy account, mapping occurs as follows:

If the LDAP server returns a group in DN format, the LDAP plugin parses the group DN to extract the group name from it.

To specify LDAP group preference and mapping information, these principles apply:

LDAP Authentication User DN Suffixes

As of MySQL 5.7.21, LDAP authentication plugins permit the authentication string that provides user DN information to begin with a '+' prefix character:

This account authentication string does not have '+' at the beginning, so it is taken as the full user DN:

 CREATE USER 'baldwin'
   IDENTIFIED WITH authentication_ldap_simple
   AS 'uid=admin,ou=People,dc=example,dc=com';

The client connects with the user name specified in the account ('baldwin'). In this case, that name is not used because the authentication string has no prefix and thus fully specifies the user DN.

This account authentication string does have '+' at the beginning, so it is taken as just part of the user DN:

 CREATE USER 'accounting'
   IDENTIFIED WITH authentication_ldap_simple
   AS '+ou=People,dc=example,dc=com';

The client connects with the user name specified in the account ('accounting'), which in this case is used as the 'uid' attribute together with the authentication string to construct the user DN: 'uid=accounting,ou=People,dc=example,dc=com'

The accounts in the preceding examples have a nonempty user name, so the client always connects to the MySQL server using the same name as specified in the account definition. If an account has an empty user name, such as the default anonymous '''@'%'' proxy account described in *note ldap-pluggable-authentication-usage-proxying::, clients might connect to the MySQL server with varying user names. But the principle is the same: If the authentication string begins with '+', the plugin uses the user name sent by the client together with the authentication string to construct the user DN.

LDAP Authentication Methods

The LDAP authentication plugins use a configurable authentication method. The appropriate system variable and available method choices are plugin-specific:

See the system variable descriptions for information about each permitted method.

 File: manual.info.tmp, Node: no-login-pluggable-authentication, Next: socket-pluggable-authentication, Prev: ldap-pluggable-authentication, Up: authentication-plugins

6.4.1.10 No-Login Pluggable Authentication ..........................................

The 'mysql_no_login' server-side authentication plugin prevents all client connections to any account that uses it. Use cases for this plugin include:

The following table shows the plugin and library file names. The file name suffix might differ on your system. The file must be located in the directory named by the 'plugin_dir' system variable.

Plugin and Library Names for No-Login Authentication

Plugin or File Plugin or File Name

Server-side plugin 'mysql_no_login'

Client-side plugin None

Library file 'mysql_no_login.so'

The following sections provide installation and usage information specific to no-login pluggable authentication:

For general information about pluggable authentication in MySQL, see note pluggable-authentication::. For proxy user information, see note proxy-users::.

Installing No-Login Pluggable Authentication

This section describes how to install the no-login authentication plugin. For general information about installing plugins, see *note plugin-loading::.

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 'mysql_no_login'. The file name suffix differs per platform (for example, '.so' for Unix and Unix-like systems, '.dll' for Windows).

To load the plugin at server startup, use the '--plugin-load-add' option to name the library file that contains it. 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, adjusting the '.so' suffix for your platform as necessary:

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

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

Alternatively, to load the plugin at runtime, use this statement, adjusting the '.so' suffix for your platform as necessary:

 INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';

*note 'INSTALL PLUGIN': 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 note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE '%login%';
 +----------------+---------------+
 | PLUGIN_NAME    | PLUGIN_STATUS |
 +----------------+---------------+
 | mysql_no_login | ACTIVE        |
 +----------------+---------------+

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

To associate MySQL accounts with the no-login plugin, see *note no-login-pluggable-authentication-usage::.

Uninstalling No-Login Pluggable Authentication

The method used to uninstall the no-login authentication plugin depends on how you installed it:

Using No-Login Pluggable Authentication

This section describes how to use the no-login authentication plugin to prevent accounts from being used for connecting from MySQL client programs to the server. It is assumed that the server is running with the no-login plugin enabled, as described in *note no-login-pluggable-authentication-installation::.

To refer to the no-login authentication plugin in the 'IDENTIFIED WITH' clause of a *note 'CREATE USER': create-user. statement, use the name 'mysql_no_login'.

An account that authenticates using 'mysql_no_login' may be used as the 'DEFINER' for stored program and view objects. If such an object definition also includes 'SQL SECURITY DEFINER', it executes with that account's privileges. DBAs can use this behavior to provide access to confidential or sensitive data that is exposed only through well-controlled interfaces.

The following example illustrates these principles. It defines an account that does not permit client connections, and associates with it a view that exposes only certain columns of the 'mysql.user' system table:

 CREATE DATABASE nologindb;
 CREATE USER 'nologin'@'localhost'
   IDENTIFIED WITH mysql_no_login;
 GRANT ALL ON nologindb.*
   TO 'nologin'@'localhost';
 GRANT SELECT ON mysql.user
   TO 'nologin'@'localhost';
 CREATE DEFINER = 'nologin'@'localhost'
   SQL SECURITY DEFINER
   VIEW nologindb.myview
   AS SELECT User, Host FROM mysql.user;

To provide protected access to the view to an ordinary user, do this:

 GRANT SELECT ON nologindb.myview
   TO 'ordinaryuser'@'localhost';

Now the ordinary user can use the view to access the limited information it presents:

 SELECT * FROM nologindb.myview;

Attempts by the user to access columns other than those exposed by the view result in an error, as do attempts to select from the view by users not granted access to it.

Note:

Because the 'nologin' account cannot be used directly, the operations required to set up objects that it uses must be performed by 'root' or similar account that has the privileges required to create the objects and set 'DEFINER' values.

The 'mysql_no_login' plugin is also useful in proxying scenarios. (For a discussion of concepts involved in proxying, see *note proxy-users::.) An account that authenticates using 'mysql_no_login' may be used as a proxied user for proxy accounts:

 -- create proxied account
 CREATE USER 'proxied_user'@'localhost'
   IDENTIFIED WITH mysql_no_login;
 -- grant privileges to proxied account
 GRANT ...
   ON ...
   TO 'proxied_user'@'localhost';
 -- permit proxy_user to be a proxy account for proxied account
 GRANT PROXY
   ON 'proxied_user'@'localhost'
   TO 'proxy_user'@'localhost';

This enables clients to access MySQL through the proxy account ('proxy_user') but not to bypass the proxy mechanism by connecting directly as the proxied user ('proxied_user'). A client who connects using the 'proxy_user' account has the privileges of the 'proxied_user' account, but 'proxied_user' itself cannot be used to connect.

For alternative methods of protecting proxied accounts against direct use, see *note preventing-proxied-account-direct-login::.

 File: manual.info.tmp, Node: socket-pluggable-authentication, Next: test-pluggable-authentication, Prev: no-login-pluggable-authentication, Up: authentication-plugins

6.4.1.11 Socket Peer-Credential Pluggable Authentication ........................................................

The server-side 'auth_socket' authentication plugin authenticates clients that connect from the local host through the Unix socket file. The plugin uses the 'SO_PEERCRED' socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the 'SO_PEERCRED' option, such as Linux.

The source code for this plugin can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.

The following table shows the plugin and library file names. The file must be located in the directory named by the 'plugin_dir' system variable.

Plugin and Library Names for Socket Peer-Credential Authentication

Plugin or File Plugin or File Name

Server-side plugin 'auth_socket'

Client-side plugin None, see discussion

Library file 'auth_socket.so'

The following sections provide installation and usage information specific to socket pluggable authentication:

For general information about pluggable authentication in MySQL, see *note pluggable-authentication::.

Installing Socket Pluggable Authentication

This section describes how to install the socket authentication plugin. For general information about installing plugins, see *note plugin-loading::.

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 load the plugin at server startup, use the '--plugin-load-add' option to name the library file that contains it. 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:

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

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

Alternatively, to load the plugin at runtime, use this statement:

 INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

*note 'INSTALL PLUGIN': 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 note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE '%socket%';
 +-------------+---------------+
 | PLUGIN_NAME | PLUGIN_STATUS |
 +-------------+---------------+
 | auth_socket | ACTIVE        |
 +-------------+---------------+

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

To associate MySQL accounts with the socket plugin, see *note socket-pluggable-authentication-usage::.

Uninstalling Socket Pluggable Authentication

The method used to uninstall the socket authentication plugin depends on how you installed it:

Using Socket Pluggable Authentication

The socket plugin checks whether the socket user name (the operating system user name) matches the MySQL user name specified by the client program to the server. If the names do not match, the plugin checks whether the socket user name matches the name specified in the 'authentication_string' column of the 'mysql.user' system table row. If a match is found, the plugin permits the connection. The 'authentication_string' value can be specified using an 'IDENTIFIED ...AS' clause with note 'CREATE USER': create-user. or note 'ALTER USER': alter-user.

Suppose that a MySQL account is created for an operating system user named 'valerie' who is to be authenticated by the 'auth_socket' plugin for connections from the local host through the socket file:

 CREATE USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket;

If a user on the local host with a login name of 'stefanie' invokes *note 'mysql': mysql. with the option '--user=valerie' to connect through the socket file, the server uses 'auth_socket' to authenticate the client. The plugin determines that the '--user' option value ('valerie') differs from the client user's name ('stephanie') and refuses the connection. If a user named 'valerie' tries the same thing, the plugin finds that the user name and the MySQL user name are both 'valerie' and permits the connection. However, the plugin refuses the connection even for 'valerie' if the connection is made using a different protocol, such as TCP/IP.

To permit both the 'valerie' and 'stephanie' operating system users to access MySQL through socket file connections that use the account, this can be done two ways:

To access the account, both 'valerie' and 'stephanie' specify '--user=valerie' at connect time.

 File: manual.info.tmp, Node: test-pluggable-authentication, Next: pluggable-authentication-system-variables, Prev: socket-pluggable-authentication, Up: authentication-plugins

6.4.1.12 Test Pluggable Authentication ......................................

MySQL includes a test plugin that checks account credentials and logs success or failure to the server error log. This is a loadable plugin (not built in) and must be installed prior to use.

The test plugin source code is separate from the server source, unlike the built-in native plugin, so it can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.

Note:

This plugin is intended for testing and development purposes, and is not for use in production environments or on servers that are exposed to public networks.

The following table shows the plugin and library file names. The file name suffix might differ on your system. The file must be located in the directory named by the 'plugin_dir' system variable.

Plugin and Library Names for Test Authentication

Plugin or File Plugin or File Name

Server-side plugin 'test_plugin_server'

Client-side plugin 'auth_test_plugin'

Library file 'auth_test_plugin.so'

The following sections provide installation and usage information specific to test pluggable authentication:

For general information about pluggable authentication in MySQL, see *note pluggable-authentication::.

Installing Test Pluggable Authentication

This section describes how to install the server-side test authentication plugin. For general information about installing plugins, see *note plugin-loading::.

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 load the plugin at server startup, use the '--plugin-load-add' option to name the library file that contains it. 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, adjusting the '.so' suffix for your platform as necessary:

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

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

Alternatively, to load the plugin at runtime, use this statement, adjusting the '.so' suffix for your platform as necessary:

 INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';

*note 'INSTALL PLUGIN': 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 note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE '%test_plugin%';
 +--------------------+---------------+
 | PLUGIN_NAME        | PLUGIN_STATUS |
 +--------------------+---------------+
 | test_plugin_server | ACTIVE        |
 +--------------------+---------------+

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

To associate MySQL accounts with the test plugin, see *note test-pluggable-authentication-usage::.

Uninstalling Test Pluggable Authentication

The method used to uninstall the test authentication plugin depends on how you installed it:

Using Test Pluggable Authentication

To use the test authentication plugin, create an account and name that plugin in the 'IDENTIFIED WITH' clause:

 CREATE USER 'testuser'@'localhost'
 IDENTIFIED WITH test_plugin_server
 BY 'TESTPASSWORD';

Then provide the '--user' and '--password' options for that account when you connect to the server. For example:

 $> mysql --user=testuser --password
 Enter password: TESTPASSWORD

The plugin fetches the password as received from the client and compares it with the value stored in the 'authentication_string' column of the account row in the 'mysql.user' system table. If the two values match, the plugin returns the 'authentication_string' value as the new effective user ID.

You can look in the server error log for a message indicating whether authentication succeeded (notice that the password is reported as the 'user'):

 [Note] Plugin test_plugin_server reported:
 'successfully authenticated user TESTPASSWORD'

 File: manual.info.tmp, Node: pluggable-authentication-system-variables, Prev: test-pluggable-authentication, Up: authentication-plugins

6.4.1.13 Pluggable Authentication System Variables ..................................................

These variables are unavailable unless the appropriate server-side plugin is installed:

Authentication Plugin System Variable Summary

Name Cmd-Line Option System Status Var Scope Dynamic File Var Var

authentication_ldap_sasl_auth_method_name

Yes Yes Yes Global Yes

authentication_ldap_sasl_bind_base_dn

Yes Yes Yes Global Yes

authentication_ldap_sasl_bind_root_dn

Yes Yes Yes Global Yes

authentication_ldap_sasl_bind_root_pwd

Yes Yes Yes Global Yes

authentication_ldap_sasl_ca_path

Yes Yes Yes Global Yes

authentication_ldap_sasl_group_search_attr

Yes Yes Yes Global Yes

authentication_ldap_sasl_group_search_filter

Yes Yes Yes Global Yes

authentication_ldap_sasl_init_pool_size

Yes Yes Yes Global Yes

authentication_ldap_sasl_log_status

Yes Yes Yes Global Yes

authentication_ldap_sasl_max_pool_size

Yes Yes Yes Global Yes

authentication_ldap_sasl_server_host

Yes Yes Yes Global Yes

authentication_ldap_sasl_server_port

Yes Yes Yes Global Yes

authentication_ldap_sasl_tls

Yes Yes Yes Global Yes

authentication_ldap_sasl_user_search_attr

Yes Yes Yes Global Yes

authentication_ldap_simple_auth_method_name

Yes Yes Yes Global Yes

authentication_ldap_simple_bind_base_dn

Yes Yes Yes Global Yes

authentication_ldap_simple_bind_root_dn

Yes Yes Yes Global Yes

authentication_ldap_simple_bind_root_pwd

Yes Yes Yes Global Yes

authentication_ldap_simple_ca_path

Yes Yes Yes Global Yes

authentication_ldap_simple_group_search_attr

Yes Yes Yes Global Yes

authentication_ldap_simple_group_search_filter

Yes Yes Yes Global Yes

authentication_ldap_simple_init_pool_size

Yes Yes Yes Global Yes

authentication_ldap_simple_log_status

Yes Yes Yes Global Yes

authentication_ldap_simple_max_pool_size

Yes Yes Yes Global Yes

authentication_ldap_simple_server_host

Yes Yes Yes Global Yes

authentication_ldap_simple_server_port

Yes Yes Yes Global Yes

authentication_ldap_simple_tls

Yes Yes Yes Global Yes

authentication_ldap_simple_user_search_attr

Yes Yes Yes Global Yes

authentication_windows_log_level

Yes Yes Yes Global No

authentication_windows_use_principal_name

Yes Yes Yes Global No

 File: manual.info.tmp, Node: connection-control, Next: validate-password, Prev: authentication-plugins, Up: security-plugins

6.4.2 The Connection-Control Plugins

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

The following sections provide information about connection-control plugin installation and configuration. For information about the note 'CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS': information-schema-connection-control-failed-login-attempts-table. table, see note information-schema-connection-control-failed-login-attempts-table::.

 File: manual.info.tmp, Node: connection-control-installation, Next: connection-control-variables, Prev: connection-control, Up: connection-control

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 *note plugin-loading::.

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, adjusting 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, adjusting 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';

*note 'INSTALL PLUGIN': 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 note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE 'connection%';
 +------------------------------------------+---------------+
 | PLUGIN_NAME                              | PLUGIN_STATUS |
 +------------------------------------------+---------------+
 | CONNECTION_CONTROL                       | ACTIVE        |
 | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
 +------------------------------------------+---------------+

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

If the plugins have been previously registered with *note 'INSTALL PLUGIN': 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 *note 'CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS': information-schema-connection-control-failed-login-attempts-table. table, retrievals from the table are always empty.

Connection Delay Configuration

To enable configuring its operation, the 'CONNECTION_CONTROL' plugin exposes these system variables:

If 'connection_control_failed_connections_threshold' is nonzero, failed-connection counting is enabled and has these properties:

For example, with the default 'connection_control_failed_connections_threshold' value of 3, there is no delay for the first three consecutive failed connection attempts by an account. The actual adjusted delays experienced by the account for the fourth and subsequent failed connections depend on the 'connection_control_min_connection_delay' and 'connection_control_max_connection_delay' values:

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, with a minimum delay of 2000 milliseconds. 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=2000

To set the variables at runtime, use these statements:

 SET GLOBAL connection_control_failed_connections_threshold = 4;
 SET GLOBAL connection_control_min_connection_delay = 1500;

*note 'SET GLOBAL': set-variable. sets the value for the running MySQL instance. To make the change permanent, add a line in your 'my.cnf' file, as shown previously.

The 'connection_control_min_connection_delay' and 'connection_control_max_connection_delay' system variables both have minimum and maximum values of 1000 and 2147483647. 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:

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 has these effects:

 File: manual.info.tmp, Node: connection-control-variables, Prev: connection-control-installation, Up: connection-control

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:

 File: manual.info.tmp, Node: validate-password, Next: keyring, Prev: connection-control, Up: security-plugins

6.4.3 The Password Validation Plugin

The 'validate_password' plugin serves to improve security by requiring account passwords and enabling strength testing of potential passwords. This plugin exposes a set of system variables that enable you to configure password policy.

The 'validate_password' plugin implements these capabilities:

Note:

For statements that assign, modify, or generate account passwords (note 'ALTER USER': alter-user, note 'CREATE USER': create-user, note 'GRANT': grant, and note 'SET PASSWORD': set-password.; statements that use '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 *note password-management::.

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

Examples:

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 *note validate-password-options-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, as of MySQL 5.7.15, '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.

 File: manual.info.tmp, Node: validate-password-installation, Next: validate-password-options-variables, Prev: validate-password, Up: validate-password

6.4.3.1 Password Validation Plugin Installation ...............................................

This section describes how to install the 'validate_password' password-validation plugin. For general information about installing plugins, see *note plugin-loading::.

Note:

If you installed MySQL 5.7 using the MySQL Yum repository (https://dev.mysql.com/downloads/repo/yum/), MySQL SLES Repository (https://dev.mysql.com/downloads/repo/suse/), or *note RPM packages provided by Oracle: linux-installation-rpm, 'validate_password' is enabled by default after you start your MySQL Server for the first time.

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 'validate_password'. The file name suffix differs per platform (for example, '.so' for Unix and Unix-like systems, '.dll' for Windows).

To load the plugin at server startup, use the '--plugin-load-add' option to name the library file that contains it. 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, adjusting the '.so' suffix for your platform as necessary:

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

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

Alternatively, to load the plugin at runtime, use this statement, adjusting the '.so' suffix for your platform as necessary:

 INSTALL PLUGIN validate_password SONAME 'validate_password.so';

*note 'INSTALL PLUGIN': install-plugin. loads the plugin, and also registers it in the 'mysql.plugins' system table to cause the plugin to be loaded for each subsequent normal server startup without the need for '--plugin-load-add'.

To verify plugin installation, examine the Information Schema note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE 'validate%';
 +-------------------+---------------+
 | PLUGIN_NAME       | PLUGIN_STATUS |
 +-------------------+---------------+
 | validate_password | ACTIVE        |
 +-------------------+---------------+

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

If the plugin has been previously registered with *note 'INSTALL PLUGIN': install-plugin. or is loaded with '--plugin-load-add', you can use the '--validate-password' option at server startup to control plugin activation. For example, to load the plugin at startup and prevent it from being removed at runtime, use these options:

 [mysqld]
 plugin-load-add=validate_password.so
 validate-password=FORCE_PLUS_PERMANENT

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

 File: manual.info.tmp, Node: validate-password-options-variables, Prev: validate-password-installation, Up: validate-password

6.4.3.2 Password Validation Plugin Options and Variables ........................................................

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

Password Validation Plugin Options

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

Password Validation Plugin System Variables

If the 'validate_password' plugin 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    | OFF    |
 | 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.

Password Validation Plugin Status Variables

If the 'validate_password' plugin 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.

 File: manual.info.tmp, Node: keyring, Next: audit-log, Prev: validate-password, Up: security-plugins

6.4.4 The MySQL Keyring

MySQL Server supports a keyring that enables internal server components and plugins to securely store sensitive information for later retrieval. The implementation comprises these elements:

Warning:

For encryption key management, the 'keyring_file' and 'keyring_encrypted_file' plugins 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).

Within MySQL, keyring service consumers include:

For general keyring installation instructions, see *note keyring-plugin-installation::. For installation and configuration information specific to a given keyring plugin, see the section describing that plugin.

For information about using the keyring functions, see *note keyring-functions-general-purpose::.

Keyring plugins and functions access a keyring service that provides the interface to the keyring. For information about accessing this service and writing keyring plugins, see *note keyring-service::, and Writing Keyring Plugins (https://dev.mysql.com/doc/extending-mysql/5.7/en/writing-keyring-plugins.html).

 File: manual.info.tmp, Node: keyring-plugin-installation, Next: keyring-file-plugin, Prev: keyring, Up: keyring

6.4.4.1 Keyring Plugin Installation ...................................

Keyring service consumers require that a keyring plugin be installed. This section describes how to install the keyring plugin of your choosing. Also, for general information about installing plugins, see *note plugin-loading::.

If you intend to use keyring functions in conjunction with the chosen keyring plugin, install the functions after installing that plugin, using the instructions in *note keyring-functions-general-purpose::.

Note:

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

MySQL provides these keyring plugin choices:

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 keyring plugin must be loaded early during the server startup sequence so that 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.

Installation for each keyring plugin is similar. The following instructions describe how to install 'keyring_file'. To use a different keyring plugin, 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).

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, adjusting the '.so' suffix for your platform as necessary:

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

Important:

In MySQL 5.7.11, the default '--early-plugin-load' value is the name of the 'keyring_file' plugin library file, causing that plugin to be loaded by default. In MySQL 5.7.12 and higher, the default '--early-plugin-load' value is empty; to load the 'keyring_file' plugin, you must explicitly specify the option with a value naming the 'keyring_file' plugin library file.

'InnoDB' tablespace encryption requires that the keyring plugin to be used be loaded prior to 'InnoDB' initialization, so this change of default '--early-plugin-load' value introduces an incompatibility for upgrades from 5.7.11 to 5.7.12 or higher. Administrators who have encrypted 'InnoDB' tablespaces must take explicit action to ensure continued loading of the keyring plugin: Start the server with an '--early-plugin-load' option that names the plugin library file.

Before starting the server, check the notes for your chosen keyring plugin for configuration instructions specific to that plugin:

After performing any plugin-specific configuration, start the server. Verify plugin installation by examining the Information Schema note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE 'keyring%';
 +--------------+---------------+
 | PLUGIN_NAME  | PLUGIN_STATUS |
 +--------------+---------------+
 | keyring_file | ACTIVE        |
 +--------------+---------------+

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

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

If no keyring plugin is available when a 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.

 File: manual.info.tmp, Node: keyring-file-plugin, Next: keyring-encrypted-file-plugin, Prev: keyring-plugin-installation, Up: keyring

6.4.4.2 Using the keyring_file File-Based Keyring Plugin ........................................................

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

Warning:

For encryption key management, the 'keyring_file' plugin 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 'keyring_file', use the general instructions found in *note 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, adjusting 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

If 'keyring_file_data' is set to a new location, the keyring plugin creates a new, empty file containing no keys; this means that any existing encrypted tables can no longer be accessed.

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 *note keyring-system-variables::.

As of MySQL 5.7.17, 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 the SQL interface):

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

For information about the characteristics of key values permitted by 'keyring_file', see *note keyring-key-types::.

 File: manual.info.tmp, Node: keyring-encrypted-file-plugin, Next: keyring-okv-plugin, Prev: keyring-file-plugin, Up: keyring

6.4.4.3 Using the keyring_encrypted_file Encrypted File-Based 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' keyring plugin stores keyring data in an encrypted, password-protected file local to the server host. A password must be specified for the file. This plugin is available as of MySQL 5.7.21.

Warning:

For encryption key management, the 'keyring_encrypted_file' plugin 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 'keyring_encrypted_file', use the general instructions found in *note 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, adjusting the '.so' suffix and file location for your platform as necessary and substituting 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 *note 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 the SQL interface):

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

For information about the characteristics of key values permitted by 'keyring_encrypted_file', see *note keyring-key-types::.

 File: manual.info.tmp, Node: keyring-okv-plugin, Next: keyring-aws-plugin, Prev: keyring-encrypted-file-plugin, Up: keyring

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:

Each MySQL Server instance must be registered separately as a client for KMIP. If two or more MySQL Server instances use the same set of credentials, they can interfere with each other's functioning.

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 the SQL interface):

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

For information about the characteristics of key values permitted by 'keyring_okv', *note keyring-key-types::.

To install 'keyring_okv', use the general instructions found in *note 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:

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, adjusting 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 *note 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. Note that you must register a separate endpoint for each MySQL Server instance. If two or more MySQL Server instances use the same endpoint, they can interfere with each other's functioning.

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 (http://www.oracle.com/technetwork/database/options/key-management/overview/index.html) site and consult the 'Oracle Key Vault Administrator's Guide'.

  1. Create the configuration directory that contains 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 *note 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 is required when the endpoint connects to Oracle Key Vault.)

    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 expected file contents include lines that look like this:

      SERVER=HOST_IP:PORT_NUM
  STANDBY_SERVER=HOST_IP:PORT_NUM

    Note:

    If the existing file is not in this format, then create a new file with the lines shown in the previous example. Also, consider backing up the 'okvclient.ora' file before you run the 'okvutil' command. Restore the file as needed.

    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, as of MySQL 5.7.19.  Prior
  to MySQL 5.7.19, a setting for 'STANDBY_SERVER' is mandatory;
  if 'okvclient.ora' is generated with no setting for
  'STANDBY_SERVER', 'keyring_okv' fails to initialize.  The
  workaround is to check 'oraclient.ora' and add a 'dummy'
  setting for 'STANDBY_SERVER', if one is missing.  For example:

       STANDBY_SERVER=127.0.0.1:5696

  8. 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

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

      jar xf okvclient.jar ssl

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

  11. (Optional) If you wish to password-protect the key file, use the instructions in *note keyring-okv-encrypt-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). As of MySQL 5.7.18, 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 (https://www2.gemalto.com/aws-marketplace/usage/vks/uploadedFiles/Support_and_Downloads/AWS/007-012362-001-keysecure-appliance-user-guide-v7.1.0.pdf).

  1. Create the configuration directory that contains the KeySecure support files, and make sure that the 'keyring_okv_conf_dir' system variable is set to name that directory (for details, see *note 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:

      SERVER=HOST_IP:PORT_NUM
  STANDBY_SERVER=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 can 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 *note keyring-okv-encrypt-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.

Configuring keyring_okv for Townsend Alliance Key Manager

Townsend Alliance Key Manager uses the KMIP protocol. The 'keyring_okv' keyring plugin can use Alliance Key Manager as its KMIP back end for keyring storage. For additional information, see Alliance Key Manager for MySQL (https://www.townsendsecurity.com/product/encryption-key-management-mysql).

Password-Protecting the keyring_okv Key File

As of MySQL 5.7.20, 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:

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

      $> 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.

 File: manual.info.tmp, Node: keyring-aws-plugin, Next: keyring-key-types, Prev: keyring-okv-plugin, Up: keyring

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' keyring plugin 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'.

MySQL Enterprise Edition can work with 'keyring_aws' on Red Hat Enterprise Linux, SUSE Linux Enterprise Server, Debian, Ubuntu, macOS, and Windows. MySQL Enterprise Edition does not support the use of 'keyring_aws' on these platforms:

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 'keyring_aws', use the general instructions found in *note keyring-plugin-installation::, together with the plugin-specific configuration information found here.

The plugin library file contains the 'keyring_aws' plugin and two loadable functions, '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 *note 'SET GLOBAL': set-variable.)

  4. If necessary, create the directory in which the configuration file is to 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, adjusting 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 *note keyring-system-variables::.

Start the MySQL server and install the functions associated with the 'keyring_aws' plugin. This is a one-time operation, performed by executing the following statements, adjusting 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' functions, see *note keyring-functions-plugin-specific::.

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 the SQL interface):

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

In addition, the 'keyring_aws_rotate_cmk()' and 'keyring_aws_rotate_keys()' functions '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 these functions using SQL. There are no corresponding C-languge key service functions.

For information about the characteristics of key values permitted by 'keyring_aws', see *note keyring-key-types::.

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 re-reads 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, adjusting 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, *note 'INSTALL
  PLUGIN': 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 *note 'UNINSTALL PLUGIN':
  uninstall-plugin. plus *note 'INSTALL PLUGIN': install-plugin.
  sequence just described to change the AWS KMS credentials,
  then to stop using 'keyring_aws', it is necessary to execute
  *note 'UNINSTALL PLUGIN': uninstall-plugin. again to
  unregister the plugin in addition to removing the
  '--early-plugin-load' option.

 File: manual.info.tmp, Node: keyring-key-types, Next: keyring-key-migration, Prev: keyring-aws-plugin, Up: keyring

6.4.4.6 Supported Keyring Key Types and Lengths ...............................................

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

note keyring-general-key-length-limits-table:: shows the general key-length limits. (The lower limits for 'keyring_aws' are imposed by the AWS KMS interface, not the keyring functions.) note keyring-key-types-table:: shows the key types each keyring plugin permits, as well as any plugin-specific key-length restrictions.

General Keyring Key Length Limits

Key Maximum Key Length Operation

Generate key 2,048 bytes; 1,024 for 'keyring_aws'

Store key
2,048 bytes

Fetch key
2,048 bytes

Keyring Plugin Key Types and Lengths

Plugin Name Permitted Key Plugin-Specific Length Type Restrictions

'keyring_aws'

'AES' 16, 24, or 32 bytes

'keyring_encrypted_file'

'AES' None

'DSA' None

'RSA' None

'keyring_file'

'AES' None

'DSA' None

'RSA' None

'keyring_okv'

'AES' 16, 24, or 32 bytes

 File: manual.info.tmp, Node: keyring-key-migration, Next: keyring-functions-general-purpose, Prev: keyring-key-types, Up: keyring

6.4.4.7 Migrating Keys Between Keyring Keystores ................................................

A keyring migration copies keys from one keystore to another, enabling a DBA to switch a MySQL installation to a different keystore. to another. A successful migration operation has this result:

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 following sections discuss the characteristics of offline and online migrations and describe how to perform migrations.

Offline and Online Key Migrations

A key migration is either offline or online:

When you plan a key migration, use these points to decide whether it should be offline or online:

Key Migration Using a Migration Server

As of MySQL 5.7.21, a MySQL server becomes a migration server if invoked in a special operational mode that supports key migration. A migration server 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).

To perform a key migration operation using a migration server, determine the key migration options required to specify which keyring plugins or components are involved, and whether the migration is offline or online:

For additional details about the key migration options, see *note keyring-options::.

Start the migration server with key migration options indicating the source and destination keystores and whether the migration is offline or online, possibly with other options. Keep the following considerations in mind:

Example command line for offline migration (enter the command on a single line):

 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 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
   --keyring-migration-host=127.0.0.1
   --keyring-migration-user=root
   --keyring-migration-password=ROOT_PASSWORD

The key migration server performs a migration operation as follows:

  1. (Online migration only) Connect to the running server using the connection options.

  2. (Online migration only) Disable 'keyring_operations' on the running server.

  3. Load the source and destination keyring plugins.

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

  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.

If an error occurs during key migration, the destination keystore is restored to its premigration state.

Important:

For an online migration operation, the migration server takes care of enabling and disabling 'keyring_operations' on the running server. If the migration server exits abnormally (for example, if it is forcibly terminated), it is possible for 'keyring_operations' to remain disabled 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 using this statement:

 SET GLOBAL keyring_operations = ON;

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

Key Migration Involving Multiple Running Servers

Online key migration provides for pausing keyring operations on a single running server. To perform a migration if multiple running servers are using the keystores involved, use this procedure:

  1. Connect to each running server manually and set 'keyring_operations=OFF'. This ensures that no running server is using the source or destination keystore and satisfies the required condition for offline migration.

  2. Use a migration server to perform an offline key migration for each paused server.

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

All running servers must support the 'keyring_operations' system variable. Any server that does not must be stopped before the migration and restarted after.

 File: manual.info.tmp, Node: keyring-functions-general-purpose, Next: keyring-functions-plugin-specific, Prev: keyring-key-migration, Up: keyring

6.4.4.8 General-Purpose Keyring Key-Management Functions ........................................................

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

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

The functions described here are general-purpose and intended for use with any keyring component or plugin. A given keyring component or plugin may also provide functions of its own that are intended for use only with that component or plugin; see *note keyring-functions-plugin-specific::.

The following sections provide installation instructions for the keyring functions and demonstrate how to use them. For information about the keyring service functions invoked by these functions, see note keyring-service::. For general keyring information, see note keyring::.

Installing or Uninstalling General-Purpose Keyring Functions

This section describes how to install or uninstall the keyring functions, which are implemented in a plugin library file that also contains a 'keyring_udf' plugin. For general information about installing or uninstalling plugins and loadable functions, see note plugin-loading::, and note function-loading::.

The keyring functions enable keyring key management operations, but the 'keyring_udf' plugin must also be installed because the functions do not work correctly without it. Attempts to use the functions 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 keyring functions, use the note 'INSTALL PLUGIN': install-plugin. and note 'CREATE FUNCTION': create-function. statements, adjusting 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 functions are used on a source replication server, install them on all replicas as well to avoid replication issues.

Once installed as just described, the plugin and functions remain installed until uninstalled. To remove them, use the note 'UNINSTALL PLUGIN': uninstall-plugin. and note 'DROP FUNCTION': 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 general-purpose functions, install them according to the instructions provided in *note keyring-function-installation::.

The keyring functions are subject to these constraints:

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 *note keyring-key-types::.

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

 mysql> SELECT keyring_key_generate('', '', -1) INTO @x;
 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.
 mysql> SELECT @x;
 +------+
 | @x   |
 +------+
 | NULL |
 +------+
 mysql> SELECT keyring_key_generate('x', 'AES', 16) INTO @x;
 mysql> SELECT @x;
 +------+
 | @x   |
 +------+
 |    1 |
 +------+

This technique also applies to other keyring functions 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 functions calls. For example, use the key ID to retrieve its type as a string or its length in bytes as an integer:

 mysql> SELECT keyring_key_type_fetch('MyKey');
 +---------------------------------+
 | keyring_key_type_fetch('MyKey') |
 +---------------------------------+
 | DSA                             |
 +---------------------------------+
 mysql> SELECT keyring_key_length_fetch('MyKey');
 +-----------------------------------+
 | keyring_key_length_fetch('MyKey') |
 +-----------------------------------+
 |                               256 |
 +-----------------------------------+

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:

 mysql> SELECT keyring_key_generate('MyShortKey', 'DSA', 8);
 +----------------------------------------------+
 | keyring_key_generate('MyShortKey', 'DSA', 8) |
 +----------------------------------------------+
 |                                            1 |
 +----------------------------------------------+
 mysql> SELECT HEX(keyring_key_fetch('MyShortKey'));
 +--------------------------------------+
 | HEX(keyring_key_fetch('MyShortKey')) |
 +--------------------------------------+
 | 1DB3B0FC3328A24C                     |
 +--------------------------------------+

Keyring functions 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 functions, 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 function 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:

 mysql> CREATE SCHEMA key_schema;

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

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

 mysql> 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 function, this section describes its purpose, calling sequence, and return value. For information about the conditions under which these functions can be invoked, see *note keyring-function-usage::.

 File: manual.info.tmp, Node: keyring-functions-plugin-specific, Next: keyring-metadata, Prev: keyring-functions-general-purpose, Up: keyring

6.4.4.9 Plugin-Specific Keyring Key-Management Functions ........................................................

For each keyring plugin-specific function, this section describes its purpose, calling sequence, and return value. For information about general-purpose keyring functions, see *note keyring-functions-general-purpose::.

 File: manual.info.tmp, Node: keyring-metadata, Next: keyring-options, Prev: keyring-functions-plugin-specific, Up: keyring

6.4.4.10 Keyring Metadata .........................

To see whether a keyring plugin is loaded, check the Information Schema note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE 'keyring%';
 +--------------+---------------+
 | PLUGIN_NAME  | PLUGIN_STATUS |
 +--------------+---------------+
 | keyring_file | ACTIVE        |
 +--------------+---------------+

 File: manual.info.tmp, Node: keyring-options, Next: keyring-system-variables, Prev: keyring-metadata, Up: keyring

6.4.4.11 Keyring Command Options ................................

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

 File: manual.info.tmp, Node: keyring-system-variables, Prev: keyring-options, Up: keyring

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 *note keyring-plugin-installation::).

 File: manual.info.tmp, Node: audit-log, Next: firewall, Prev: keyring, Up: security-plugins

6.4.5 MySQL Enterprise Audit

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 *note audit-log-installation::), 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 *note audit-log-file-formats::.

For more information about controlling how logging occurs, including audit log file naming and format selection, see note audit-log-logging-configuration::. To perform filtering of audited events, see note audit-log-filtering::. For descriptions of the parameters used to configure the audit log plugin, see *note audit-log-options-variables::.

If the audit log plugin is enabled, the Performance Schema (see *note 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/%';

 File: manual.info.tmp, Node: audit-log-elements, Next: audit-log-installation, Prev: audit-log, Up: audit-log

6.4.5.1 Elements of MySQL Enterprise Audit ..........................................

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

Note:

Prior to MySQL 5.7.13, MySQL Enterprise Audit consists only of the 'audit_log' plugin and operates in legacy mode. See *note audit-log-legacy-filtering::.

 File: manual.info.tmp, Node: audit-log-installation, Next: audit-log-security, Prev: audit-log-elements, Up: 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 elements described in note audit-log-elements::. For general information about installing plugins, see note plugin-loading::.

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.

Note:

The instructions here apply to MySQL 5.7.13 and later.

Also, prior to MySQL 5.7.13, MySQL Enterprise Audit consists only of the 'audit_log' plugin and includes none of the other elements described in *note audit-log-elements::. As of MySQL 5.7.13, if the 'audit_log' plugin is already installed from a version of MySQL prior to 5.7.13, uninstall it using the following statement and restart the server before installing the current version:

 UNINSTALL PLUGIN audit_log;

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:

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 password: (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 5.7, run *note 'mysql_upgrade --force': mysql-upgrade. (which also performs any other needed updates). If you prefer to run the update statements only for the MySQL Enterprise Audit tables, see the following discussion.

As of MySQL 5.7.23, 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 5.7.23 or higher of 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
   ENGINE=InnoDB;
 ALTER TABLE mysql.audit_log_filter
   CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 ALTER TABLE mysql.audit_log_user
   ENGINE=InnoDB;
 ALTER TABLE mysql.audit_log_user
   CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
 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);

As of MySQL 5.7.21, for a new installation of MySQL Enterprise Audit, 'InnoDB' is used instead of 'MyISAM' for the audit log tables. For upgrades to 5.7.21 or higher of an installation for which MySQL Enterprise Audit is already installed, it is recommended that you alter the audit log tables to use 'InnoDB':

 ALTER TABLE mysql.audit_log_user ENGINE=InnoDB;
 ALTER TABLE mysql.audit_log_filter ENGINE=InnoDB;

Note:

To use MySQL Enterprise Audit in the context of source/replica replication, Group Replication, or InnoDB Cluster, you must use MySQL 5.7.21 or higher, and ensure that the audit log tables use 'InnoDB' as just described. Then you must prepare the replica nodes prior to running the installation script on the source node. This is necessary because the *note 'INSTALL PLUGIN': install-plugin. statement in the script is not replicated.

  1. On each replica node, extract the *note 'INSTALL PLUGIN': install-plugin. statement from the installation script and execute it manually.

  2. On the source node, run the installation script as described previously.

To verify plugin installation, examine the Information Schema note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE 'audit%';
 +-------------+---------------+
 | PLUGIN_NAME | PLUGIN_STATUS |
 +-------------+---------------+
 | audit_log   | ACTIVE        |
 +-------------+---------------+

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 (prior to MySQL 5.7.13), which logs all auditable events for all users (see *note audit-log-legacy-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;

 File: manual.info.tmp, Node: audit-log-security, Next: audit-log-file-formats, Prev: audit-log-installation, Up: audit-log

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 *note audit-log-file-encryption::.

 File: manual.info.tmp, Node: audit-log-file-formats, Next: audit-log-logging-configuration, Prev: audit-log-security, Up: audit-log

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 *note 'LOAD DATA': 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:

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 *note 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>
   <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 ''. The root element contains '' 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 '' root element tag. When the plugin closes a log file, it writes the closing '' root element tag. The closing tag is not present while the file is open.

Elements within '' elements have these characteristics:

The following elements are mandatory in every '' element: