MySQL 8.2.0 introduces a new authentication plugin: authentication_webauthn. The plugin allows usage of a FIDO U2F/FIDO2 compatible device to achieve Multi-factor Authentication OR Passwordless Authentication with MySQL server. This plugin supersedes existing authentication_fido plugin which is marked as deprecated in 8.2.0.

authentication_webauthn supports both FIDO U2F and FIDO2 devices. FIDO2 devices have discoverable credentials support - allowing multiple private keys to be stored on the device itself and thus eliminating need to send private key (in encrypted form, of course!) on network.

Prerequisites

Rest of the sections of this post assume following:

• User has a functioning FIDO/FIDO2 device and knowledge of its passcode.
• If device is to be used on Windows, user must use an administrative console. This is due to the fact that MySQL uses libfido2 library to access the device and Windows mandate usage of administrative session for such access using 3rd party libraries.
• Only one FIDO/FIDO2 device is plugged in. If more than one devices are plugged in MySQL client will return an error.
• When using a FIDO device (i.e. one with only FIDO U2F capability), device must be in unlocked state. For example, if you are using a v4 yubikey, make sure to execute: ykman fido access verify-pin -P <PIN> before using  the key with MySQL client.

Setting Up MySQL Server to Use authentication_webauthn

In order to enable authentication_webauthn, add following in server's cnf file:

[mysqld]
...
...
--plugin-load-add=authentication_webauthn.so
--authentication_webauthn_rp_id=mysql.com

You should replace value for authentication_webauthn_rp_id as per your requirement. If you are using Windows, authentication_webauth library has .dll extension. Once done, server is ready to use the plugin. You can verify it by executing following:

SHOW PLUGINS LIKE '%webauthn';

OR

SELECT @@global.authentication_webauthn_rp_id;

Setting Up MFA Using a FIDO2 Device

To use a FIDO2 device as additional authentication factor, create a user that uses authentication_webauthn as 2nd factor authentication

CREATE USER u1 IDENTIFIED BY '<redacted>' AND IDENTIFIED WITH authentication_webauthn;

User u1 uses:

• caching_sha2_password as 1st factor authentication
• authentication_webauthn as 2nd factor authentication

To complete the setup, user can perform registration step in following manner:

mysql --plugin-dir=<path_to_plugin_directory> --user=u1 --password1 --register-factor=2

This would require user to enter device's passcode and perform the gesture (touch etc) action.

mysql --plugin-dir=<path_to_plugin_directory> --user=u1 --password1 --register-factor=2
Enter password:
Please insert FIDO device and follow the instruction.Depending on the device, you may have to perform gesture action multiple times.
1. Perform gesture action (Skip this step if you are prompted to enter device PIN).
2. Enter PIN for token device:
3. Perform gesture action for registration to complete.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8

Behind the scene following would happen:

1. Client will execute ALTER USER USER() 2 FACTOR INITIATE REGISTRATION;
2. Client will then receive challenege from server and pass the same to authentication_webauth_client plugin
3. The plugin will interact with FIDO2 device to register a new key and use the same to sign the challenge
4. Client will execute ALTER USER USER() 2 FACTOR FINISH REGISTRATION SET CHALLENGE RESPOSE AS <blob>;

Now, user can use FIDO2 device to complete 2nd factor authentication.

mysql.exe --plugin-dir=<path_to_plugin_directory> --user=u1 --password1 -e "SELECT CURRENT_USER();"
Enter password:
Please insert FIDO device and perform gesture action for authentication to complete.
+----------------+
| CURRENT_USER() |
+----------------+
| u1@%           |
+----------------+

You can inspect discoverable credentials stored using FIDO2 device utility program. See following example for a v5 yubikey.

ykman fido credentials list
Enter your PIN:
Credential ID  RP ID      Username  Display name
cea4f7dc...    mysql.com  `u1`@`%`

Setting Up Passwordless Login Using a FIDO2 Device

To use a FIDO2 device passwordless authentication, create a user in following manner:

mysql> CREATE user u2 IDENTIFIED WITH 'authentication_webauthn' INITIAL AUTHENTICATION IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+-------------+
| user | host | generated password   | auth_factor |
+------+------+----------------------+-------------+
| u2   | %    | 1o-F/Bpr>f->hgsnzpJO |           1 |
+------+------+----------------------+-------------+
1 row in set (0.02 sec)

This would create user u2 and set authentication_webauthn as 1st factor authentication. It would also create a random temporary password that can be used in registration phase and return the to the client.

To complete the setup, user can perform registration step in following manner:

mysql --plugin-dir=<path_to_plugin_directory> --user=u2 --password1 --register-factor=2
Enter password:
Please insert FIDO device and follow the instruction.Depending on the device, you may have to perform gesture action multiple times.
1. Perform gesture action (Skip this step if you are prompted to enter device PIN).
2. Enter PIN for token device:
3. Perform gesture action for registration to complete.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11

Now, user can use FIDO2 device to login to MySQL server without needing any password.

mysql --plugin-dir=<path_to_plugin_directory> --user=u2 -e "SELECT CURRENT_USER();"
mysql: [Warning] Using a password on the command line interface can be insecure.
Please insert FIDO device and perform gesture action for authentication to complete.
+----------------+
| CURRENT_USER() |
+----------------+
| u2@%           |
+----------------+

You can inspect discoverable credentials stored using FIDO2 device utility program. See following example for a v5 yubikey.

ykman fido credentials list
Enter your PIN:
Credential ID  RP ID      Username  Display name
690ee7a3...    mysql.com  `u2`@`%`

Selecting a Discoverable Credential for Authentication

It is possible that a user uses same FIDO2 device for multiple database accounts on a given MySQL server and they may only differ in hostname part of the account. When user attempts in such cases, server will determine account to be used based client's host. This allows user to assume different role depending on the host from which user connects to database. See following example of a yubikey holding mutliple discoverable credentials.

ykman fido credentials list
Enter your PIN:
Credential ID  RP ID      Username          Display name
fff5101f...    mysql.com  `u3`@`127.0.0.1`
ebc363cf...    mysql.com  `u3`@`%`

When multiple credentials exists for given RP ID, by default MySQL client will sign challenge sent by server with all such credentials and send multiple assertions to server. Server will try to verify each response using the public key it has for given account and stop when it finds the correct response or when all signed challenges are exhausted.

While this is completely secure - because private keys *NEVER* leaves user's FIDO2 device, if user is concerned that it would still allow server to enumerate number of observable credentials stored on user's device (note that MySQL server DOES NOT log this information anywhere), there is a client side configuration to limit keys used for signing server's challenge.  User can use --plugin-authentication-webauthn-client-preserve-privacy option of mysql program and choose a key to be used for signing challenge.

--plugin-authentication-webauthn-client-preserve-privacy
                    Allows selection of discoverable credential to be used
                    for signing challenge. default is false - implies
                    challenge is signed by all credentials for given relying
                    party.

When set to TRUE for an interactive client program such as mysql, user will be presented with a list of discoverable credentials for given RP ID. User can select a particular credential and server's challenge will be signed using it.

mysql --plugin-dir=<path_to_plugin_directory> - --user=u3 --password1 --plugin-authentication-webauthn-client-preserve-privacy -e "SELECT CURRENT_USER();"
Enter password:
Enter PIN for token device: 
Found following credentials for RP ID: mysql.com
[1]`u3`@`127.0.0.1`
[2]`u3`@`%`
Please select one(1...N):
1
Please insert FIDO device and perform gesture action for authentication to complete.
+----------------+
| CURRENT_USER() |
+----------------+
| u3@127.0.0.1   |
+----------------+

Notice that to be able to select one of the available credentials, user must enter device PIN.

FIDO U2F Support

Plugin also supports FIDO U2F devices. When such a device is used, private key is sent to server in encrypted form and stored in mysql.user table. When such a device is plugged in, client detects it at the time of registration/authentication and performs required additional steps to send additional details to server. There is an additional network roundtrip involved at the time of authentication when FIDO U2F device is used.

Supported Platforms

authentication_webauthn relies on libfido2. As of writing of this post, the latest version of libfido2 library is 1.13.0 and MySQL uses the same. Starting from libfido2 v1.9.0, libfido2 has stopped supporting OpenSSL v1.0 and earlier. Starting with MySQL 8.0.35 and 8.2.0, MySQL has upgraded libfido2 from v1.8.0 to 1.13.0. Thus, support for authentication_webauthn is limited to MySQL packages that rely on OpenSSL v1.1.1 and above. MySQL packages for OL6, OL7, Solaris 11 and SLES 12 would not support the new plugin.This restriction also extends to now deprecated authentication_fido plugin in 8.0.35 and 8.2.0 onward and the library will no longer be available on above mentioned platform due to libfido2 upgrade.

To conclude, authentication_webauthn adds supports for FIDO CTAP and allows users to use FIDO2 devices for MFA or passwordless login. Please give it a go and let us know your feedback.