MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
How to use passkeys to authenticate to MySQL
Posted on Oct 28, 2024 by Georgi Kodinov
Category: Security
Tags: security

FIDO2 WebAuthN is a modern, widely supported and increasingly useful standard getting us closer to The Passwordless Future.

MySQL has supported multi-factor authentication (in the Enterprise Edition) since 8.0. And, as of 8.2, we added support for WebAuthN authentication.

But the support was primarily geared towards using hardware based FIDO2 authenticator devices (e.g. Yubikeys). While these are perfectly fine and still the most secure option, they present certain usability challenges:

  • You need to have the physical key present and communicating with your device.
  • The hardware keys can be lost or damaged.
  • They have a cost, especially if you require a backup one (recommended).

It is no surprise the industry has been hard at work trying to find a software based alternative to devices with comparable security characteristics.

Enter Passkeys.

Passkeys typically store a private key on the OS and which is protected with biometrics. Modern OSes have been ramping up their passkey support in recent years. For Windows devices, Microsoft recently added Windows Hello. Windows Hello supports unified creation, storage, protection and API use of passkeys.

This is why in 9.1, we have extended the WebAuthN plugins to support Windows Hello as a valid "security device" and allow passkeys stored in it to be used for MySQL authentication.

Here's a video on this in action showing how to create the passkey in Windows Hello and set it up to be used as a 2nd factor for a mysql user account:

And here is how to use it:

 

And, in case you are not really a video person as myself, here's a step-by-step guide on how to set up and use passkeys stored into the Windows Hello OS store on Microsoft Windows:

Make sure you have the plugin needed in your MySQL server:

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

Create an account that used WebAuthN

CREATE USER `u2`@`localhost` IDENTIFIED WITH caching_sha2_password BY 'sha2_password' AND IDENTIFIED WITH authentication_webauthn;

Register the 2nd factor: creates and stores the passkey in the Windows Hello OS device

mysql ... --user=u2 --password1=sha2_password --register-factor=2

It's ready for use now. You can check it in the Control panel (note that I use "joro.mysql.com" as my RP ID):

system settings for passkeys

Login using a passkey

mysql --plugin-dir=c:\Users\gkodinov\dev\mysql-trunk\bld\plugin_output_directory -P 20202 --user=u2 --password1=sha2_password
mysql: [Warning] Using a password on the command line interface can be insecure.
Using device 0 Product=[Windows Hello] Manufacturer=[Microsoft Corporation]

Please insert FIDO device and perform gesture action for authentication to complete.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 9.1.0-debug Source distribution

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

This is it! Passkeys feature in our MySQL Enterprise Edition so if you aren't using EE, upgrade today.

Thanks for your continued interest in MySQL!

Note: passkeys can also be used as a primary authentication method (but remember that passkey must first be registered):

create user u3 IDENTIFIED WITH authentication_webauthn INITIAL AUTHENTICATION IDENTIFIED BY 'foo';
 Search
Categories