Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 42.0Mb
PDF (A4) - 42.1Mb
Man Pages (TGZ) - 267.2Kb
Man Pages (Zip) - 376.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  FIDO Pluggable Authentication

6.4.1.11 FIDO Pluggable Authentication

Note

FIDO 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 users to authenticate to MySQL Server using FIDO authentication. This authentication method is available in MySQL 8.0.27 and higher.

FIDO stands for Fast Identity Online, which provides standards for authentication that does not require use of passwords.

FIDO pluggable authentication provides these capabilities:

  • FIDO enables authentication to MySQL Server using devices such as smart cards, security keys, and biometric readers.

  • Because authentication can occur other than by providing a password, FIDO enables passwordless authentication.

  • On the other hand, device authentication is often used in conjunction with password authentication, so FIDO authentication can be used to good effect for MySQL accounts that use multifactor authentication; see Section 6.2.18, “Multifactor Authentication”.

The following table shows the plugin and library file names. The file name suffix might differ on your system. Common suffixes are .so for Unix and Unix-like systems, and .dll for Windows. The file must be located in the directory named by the plugin_dir system variable. For installation information, see Installing FIDO Pluggable Authentication.

Table 6.24 Plugin and Library Names for FIDO Authentication

Plugin or File Plugin or File Name
Server-side plugin authentication_fido
Client-side plugin authentication_fido_client
Library file authentication_fido.so, authentication_fido_client.so

Note

A libfido2 library must be available on systems where either the server-side or client-side FIDO authentication plugin is used. If a host machine has more than one FIDO device, the libfido2 library decides which device to use for registration and authentication. The libfido2 library does not provide a facility for device selection.

The server-side FIDO 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, which 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 FIDO pluggable authentication:

For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.

Installing FIDO Pluggable Authentication

This section describes how to install the server-side FIDO authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

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

The server-side plugin library file base name is authentication_fido. 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.

To load the plugin, put a line such as this in your my.cnf file, adjusting the .so suffix for your platform as necessary:

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

After modifying my.cnf, restart the server to cause the new setting 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_fido
  SONAME 'authentication_fido.so';

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

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

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME = 'authentication_fido';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| authentication_fido | ACTIVE        |
+---------------------+---------------+

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

To associate MySQL accounts with the FIDO authentication plugin, see Using FIDO Authentication.

Using FIDO Authentication

FIDO authentication typically is used in the context of multifactor authentication (see Section 6.2.18, “Multifactor Authentication”). This section shows how to incorporate FIDO device-based authentication into a multifactor account, using the authentication_fido plugin.

It is assumed in the following discussion that the server is running with the server-side FIDO authentication plugin enabled, as described in Installing FIDO Pluggable Authentication, and that the client-side FIDO plugin is available in the plugin directory on the client host.

It is also assumed that FIDO authentication is used in conjunction with non-FIDO authentication (which implies a 2FA or 3FA account). FIDO can also be used by itself to create 1FA accounts that authenticate in a passwordless manner. In this case, the setup process differs somewhat. For instructions, see FIDO Passwordless Authentication.

An account that is configured to use the authentication_fido plugin is associated with a FIDO device. Because of this, a one-time device registration step is required before FIDO authentication can occur. The device registration process has these characteristics:

  • Any FIDO device associated with an account must be registered before the account can be used.

  • Registration requires that a FIDO device be available on the client host, or registration fails.

  • The user is expected to perform the appropriate FIDO device action when prompted during registration (for example, touching the device or performing a biometric scan).

  • To perform device registration, the client user must invoke the mysql client program and specify the --fido-register-factor option to specify the factor or factors for which a device is being registered. For example, if the account is set to use FIDO as the second authentication factor, the user invokes mysql with the --fido-register-factor=2 option.

  • If the user account is configured with the authentication_fido plugin set as the second or third factor, authentication for all preceding factors must succeed before the registration step can proceed.

  • The server knows from the information in the user account whether the FIDO device requires registration or has already been registered. When the client program connects, the server places the client session in sandbox mode if the device must be registered, so that registration must occur before anything else can be done. Sandbox mode used for FIDO device registration is similar to that used for handling of expired passwords. See Section 6.2.16, “Server Handling of Expired Passwords”.

  • In sandbox mode, no statements other than ALTER USER are permitted. Registration is performed using forms of this statement. When invoked with the --fido-register-factor option, the mysql client generates the ALTER USER statements required to perform registration. After registration has been accomplished, the server switches the session out of sandbox mode, and the client can proceed normally. For information about the generated ALTER USER statements, refer to the --fido-register-factor description.

  • When device registration has been performed for the account, the server updates the mysql.user system table row for that account to update the device registration status and to store the public key and credential ID.

  • The registration step can be performed only by the user named by the account. If one user attempts to perform registration for another user, an error occurs.

  • The user should use the same FIDO device during registration and authentication. If, after registering a FIDO device on the client host, the device is reset or a different device is inserted, authentication fails. In this case, the device associated with the account must be unregistered and registration must be done again.

Suppose that you want an account to authenticate first using the caching_sha2_password plugin, then using the authentication_fido plugin. Create a multifactor account using a statement like this:

CREATE USER 'u2'@'localhost'
  IDENTIFIED WITH caching_sha2_password
    BY 'sha2_password';
  IDENTIFIED WITH authentication_fido;

To connect, supply the factor 1 password to satisfy authentication for that factor, and to initiate registration of the FIDO device, set the --fido-register-factor to factor 2.

$> mysql --user=u2 --password1 --fido-register-factor=2
Enter password: (enter factor 1 password)

Once the factor 1 password is accepted, the client session enters sandbox mode so that device registration can be performed for factor 2. During registration, you are prompted to perform the appropriate FIDO device action, such as touching the device or performing a biometric scan.

When the registration process is complete, the connection to the server is permitted.

Note

The connection to the server is permitted following registration regardless of additional authentication factors in the account's authentication chain. For example, if the account in preceding example was defined with a third authentication factor (using non-FIDO authentication), the connection would be permitted after a successful registration without authenticating the third factor. However, subsequent connections would require authenticating all three factors.

FIDO Passwordless Authentication

This section describes how FIDO can be used by itself to create 1FA accounts that authenticate in a passwordless manner. In this context, passwordless means that authentication occurs but uses a method other than a password, such as security key or biometric scan. It does not refer to an account that uses a password-based authentication plugin for which the password is empty. That kind of passwordless is completely insecure and is not recommended.

The following prerequisites apply when using the authentication_fido plugin to achieve passwordless authentication:

To use authentication_fido as a passwordless authentication method, the account must be created with authentication_fido as the first factor authentication method. The INITIAL AUTHENTICATION IDENTIFIED BY clause must also be specified for the first factor (it is not supported with 2nd or 3rd factors). This clause specifies whether a randomly generated or user-specified password will be used for FIDO device registration. After device registration, the server deletes the password and modifies the account to make authentication_fido the sole authentication method (the 1FA method).

The required CREATE USER syntax is as follows:

CREATE USER user
  IDENTIFIED WITH authentication_fido
  INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'};

The following example uses the RANDOM PASSWORD syntax:

mysql> CREATE USER 'u1'@'localhost'
         IDENTIFIED WITH authentication_fido
         INITIAL AUTHENTICATION IDENTIFIED BY RANDOM PASSWORD;
+------+-----------+----------------------+-------------+
| user | host      | generated password   | auth_factor |
+------+-----------+----------------------+-------------+
| u1   | localhost | 9XHK]M{l2rnD;VXyHzeF |           1 |
+------+-----------+----------------------+-------------+

To perform registration, the user must authenticate to the server with the password associated with the INITIAL AUTHENTICATION IDENTIFIED BY clause, either the randomly generated password, or the 'auth_string' value. If the account was created as just shown, the user executes this command and pastes in the preceding randomly generated password (9XHK]M{l2rnD;VXyHzeF) at the prompt:

$> mysql --user=u1 --password
Enter password:

The server accepts the password and revises the account entry in the mysql.user system table to list authentication_fido as the sole (1FA) authentication method.

When creating a passwordless-authentication account, it is important to include the INITIAL AUTHENTICATION IDENTIFIED BY clause in the CREATE USER statement. The server will accept a statement without the clause, but the resulting account is unusable because there is no way to connect to the server to register the device. Suppose that you execute a statement like this:

CREATE USER 'u2'@'localhost'
  IDENTIFIED WITH authentication_fido;

Subsequent attempts to use the account to connect fail like this:

$> mysql --user=u2 --skip-password
Failed to open FIDO device.
ERROR 1 (HY000): Unknown MySQL error
Note

Passwordless authentication is achieved using the Universal 2nd Factor (U2F) protocol, which does not support additional security measures such as setting a PIN on the device to be registered. It is therefore the responsibility of the device holder to ensure the device is handled in a secure manner.

FIDO Device Unregistration

It is possible to unregister FIDO devices associated with a MySQL account. This might be desirable or necessary under multiple circumstances:

  • A FIDO device is to be replaced with a different device. The previous device must be unregistered and the new device registered.

    In this case, the account owner or any user who has the CREATE USER privilege can unregister the device. The account owner can register the new device.

  • A FIDO device is reset or lost. Authentication attempts will fail until the current device is unregistered and a new registration is performed.

    In this case, the account owner, being unable to authenticate, cannot unregister the current device and must contact the DBA (or any user who has the CREATE USER privilege) to do so. Then the account owner can reregister the reset device or register a new device.

Unregistering a FIDO device can be done by the account owner or by any user who has the CREATE USER privilege. Use this syntax:

ALTER USER user {2 | 3} FACTOR UNREGISTER;

To re-register a device or perform a new registration, refer to the instructions in Using FIDO Authentication.

How FIDO Authentication of MySQL Users Works

This section provides an overview of how MySQL and FIDO work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use the FIDO authentication plugins, see Using FIDO Authentication.

An account that uses FIDO authentication must perform an initial device registration step before it can connect to the server. After the device has been registered, authentication can proceed. FIDO device registration process is as follows:

  1. The server sends a random challenge, user ID, and relying party ID (which uniquely identifies a server) to the client. The relying party ID is defined by the authentication_fido_rp_id system variable. The default value is MySQL.

  2. The client receives that information and sends it to the client-side FIDO authentication plugin, which in turn provides it to the FIDO device.

  3. After the user has performed the appropriate device action (for example, touching the device or performing a biometric scan) the FIDO device generates a public/private key pair, a key handle, an X.509 certificate, and a signature, which is returned to the server.

  4. The server-side FIDO authentication plugin verifies the signature. Upon successful verification, the server stores the credential ID and public key in the mysql.user system table.

After registration has been performed successfully, FIDO authentication follows this process:

  1. The server sends a random challenge, user ID, relying party ID and credentials to the client.

  2. The client sends the same information to the FIDO device.

  3. The FIDO device prompts the user to perform the appropriate device action, based on the selection made during registration.

  4. This action unlocks the private key and the challenge is signed.

  5. This signed challenge is returned to the server.

  6. The server-side FIDO authentication plugin verifies the signature with the public key and responds to indicate authentication success or failure.