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 deprecated as of MySQL 8.0.35 and is subject to removal in a future MySQL release. For similar capabilities, consider upgrading to MySQL 8.2 (or higher) where users can authenticate to MySQL Server using WebAuthn authentication. You need to understand the release model for MySQL innovation and long-term support (LTS) versions before you proceed with an upgrade. For more information, see Section 3.2, “Upgrade Paths”.
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 8.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 8.27 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 |
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 8.2.17, “Pluggable Authentication”.
This section describes how to install the server-side FIDO authentication plugin. For general information about installing plugins, see Section 7.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 7.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.
FIDO authentication typically is used in the context of
multifactor authentication (see
Section 8.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.
On Windows, FIDO authentication only functions if the client process runs as a user with administrator privileges.
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 or MySQL Shell 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 8.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 theALTER 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 generatedALTER 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'
AND 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.
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 the 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.
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 a 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:
The user that creates a passwordless-authentication account requires the
PASSWORDLESS_USER_ADMIN
privilege in addition to theCREATE USER
privilege.The first element of the
authentication_policy
value must be an asterisk (*
) and not a plugin name. For example, the defaultauthentication_policy
value supports enabling passwordless authentication because the first element is an asterisk:authentication_policy='*,,'
For information about configuring the
authentication_policy
value, see Configuring the Multifactor Authentication Policy.
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
'
value. If the account was created as just shown, the user
executes this command and pastes in the preceding randomly
generated password (auth_string
'9XHK]M{l2rnD;VXyHzeF
)
at the prompt:
$> mysql --user=u1 --password --fido-register-factor=2
Enter password:
The option --fido-register-factor=2
is used
because the INITIAL AUTHENTICATION IDENTIFIED
BY
clause is currently acting as the first factor
authentication method. The user must therefore provide the
temporary password by using the second factor. On a successful
registration, the server removes the temporary 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
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.
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.
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:
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 isMySQL
.The client receives that information and sends it to the client-side FIDO authentication plugin, which in turn provides it to the FIDO device.
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.
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:
The server sends a random challenge, user ID, relying party ID and credentials to the client.
The client sends the same information to the FIDO device.
The FIDO device prompts the user to perform the appropriate device action, based on the selection made during registration.
This action unlocks the private key and the challenge is signed.
This signed challenge is returned to the server.
The server-side FIDO authentication plugin verifies the signature with the public key and responds to indicate authentication success or failure.