WL#17354: Non-Password Authentication and MFA

Background

MySQL accounts may use up to three authentication factors, including password, external, socket, and WebAuthn methods. Workbench previously did not expose the full factor configuration or the client options needed to use these methods.

User Stories

• As a user, I want to connect to MySQL using any of the authentication plugins and policies that can be configured in the server.
• As a user, I want to be prompted when to touch my USB key in order to use WebAuthn authentication
• As a user, I want to reset my current account password when the connection and account support it.
• As a DBA, I want to reset a selected account password while optionally retaining or discarding the old password.
• As a DBA, I want to create MySQL user accounts that require any of the supported MySQL authentication plugins, in a single or multi-factor configuration.

Summary

Add MFA and non-password authentication support to connection setup, the connection editor, and the user editor. The scope includes WebAuthn/FIDO, Kerberos, LDAP, PAM, Windows, OCI, OpenID Connect, socket, no-login, and password authentication methods, together with their method-specific options. It also provides a simple current-account password reset for the MySQL-managed password in factor 1, including retaining the old password and discarding it. Expired-password connections can enter the reset flow before completing setup. Passwordless WebAuthn account creation requests a temporary initial password for device registration.

Authentication Flow

In MySQL MFA, the user needs to know the authentication factors and their types beforehand and specify the necessary factors at connection time. The protocol does not offer a way to automatically determine that.

Therefore, it must be possible to proactively configure the authentication factors and their types for each stored connection. When a connection is opened, the required factors are looked up and prompted, if necessary.

Certain authentication plugins, like WebAuthn, require a specific user action instead of a password. In such cases, the plugin will prompt the user for the action at a specific point of the authentication handshake. That prompt must be surfaced through the GUI to the user. FIDO device registration for WebAuthn has a similar flow.

Connection Editor

CE-FR1 - The connection editor must let users select up to three authentication factors in factor order and omit unused factors.

CE-FR2 - The factor pickers must show client-supported authentication methods, including built-in methods and bundled plugins.

CE-FR3 - The connection editor must expose authentication-method options when required, including plugin directories, clear-text password mode, Kerberos client mode, WebAuthn device and privacy settings, OCI configuration and profile, and an OpenID Connect token file.

CE-FR3.1 - When any selected authentication factor requires a clear-text password, the connection editor must set SSL Mode to at least Required without downgrading Verify CA or Verify Identity.

CE-FR4 - The connection editor must provide a Register Factor action for a WebAuthn factor and start the WebAuthn registration flow using the configured connection.

Opening Connections

OC-FR1 - Connection setup must prompt only for credentials required by the configured factors, route password prompts to the corresponding factor, and surface WebAuthn prompts and messages in the GUI.

User Editor

UE-FR1 - The user editor must create accounts with one to three distinct authentication factors and must allow factors of existing accounts to be added, removed, reordered, or changed. Factor 1 may include a password, and factors may include authentication strings where supported.

UE-FR2 - The user editor must expose the supported authentication methods, including password, Kerberos, LDAP simple and SASL, PAM, Windows, WebAuthn, OCI, OpenID Connect, socket, and no-login authentication.

UE-FR3 - When creating a single-factor passwordless WebAuthn account, the user editor must request a temporary initial password required for device registration.

Password Reset

PR-FR1 - The GUI must allow users to reset the current MySQL account password when SSL Mode is Required, Verify CA, or Verify Identity, or when the connection uses a Unix socket, prompting for the current password only when the server requires it.

PR-FR2 - Password reset must update only the MySQL-managed password in factor 1. MySQL does not allow internally managed passwords for authentication factors 2 and 3, so do not participate in the operation.

PR-FR3 - The GUI must expose Reset Password, Reset Password and Retain Current, and Discard Old Password for the active connection and for selected user accounts in the user editor.

PR-FR4 - When a connection fails because its password is expired, the GUI must offer password reset before completing the connection workflow.

NF1 - Password values must not be assigned to non-password authentication factors.

Connection Editor

The connection editor stores authentication hints with an ordered factors list and an optional client pluginDir. It adds method-specific connection options for Kerberos, WebAuthn, OCI, OpenID Connect, and clear-text password authentication, plus a register-factor request for WebAuthn registration.

Authentication method options

All selected methods are stored in factor order in settings.authenticationHints.factors. The connection editor takes the following additional values according to the selected methods:

The WebAuthn device number defaults to 0, and preserve privacy defaults to false. OCI configuration file and profile are required when authentication_oci is selected. The client plugin directory is stored once for the connection and applied as the MySQL Shell mysqlPluginDir option.

gui.mysql.getBundledAuthenticationPlugins() -> list[str]

• Parameters: None.
• Result: list[str] containing the client authentication plugins bundled with MySQL Shell.
• Description: The editor combines this list with authentication methods that do not require a client plugin when populating the factor pickers.

Opening Connections

The connection backend consumes the ordered factor hints, supplies credentials only to password-based factors, and forwards authentication-plugin prompts and messages, including WebAuthn device requests, to the frontend.

gui.dbConnections.testConnection

• Description: Updated to accept optional additional passwords and consume the ordered factor hints. For factors 1, 2, and 3, password credentials are represented by password, password2, and password3. A supplied password is assigned only to the first configured password-based factor and is removed from non-password factors. When the hints or factor list are absent, factor 1 defaults to password authentication. A register-factor request starts WebAuthn registration. Authentication plugin prompts and messages, including WebAuthn device requests, are returned through the existing connection event callbacks.

User Editor

The user editor exchanges an ordered authenticationFactors list. Each factor contains an authentication plugin and an optional password or authentication string. The backend maps the list to MySQL account creation and alteration statements. Creating a passwordless WebAuthn account also collects a temporary initial password before submission.

gui.mysql.listAuthenticationPlugins

• Description: Used by the user editor to retrieve the active authentication plugins reported by the connected server.

gui.mysql.getUser

• Description: Updated to return the account authentication plugins and authentication strings as an ordered authenticationFactors list of up to three factors.

The authentication-related API types are:

class AuthenticationFactor:
    plugin: str
    password: str | None = None
    authenticationString: str | None = None

class MySQLUser:
    user: str
    host: str
    authenticationFactors: list[AuthenticationFactor]
    plugin: str | None
    authString: str | None
    randomPassword: bool

class UserInfo:
    generatedPassword: str | None

Only fields relevant to authentication are shown. The existing TLS, resource, password-policy, account-lock, and comment fields of MySQLUser are unchanged.

authenticationFactors is ordered from factor 1 through factor 3. password and authenticationString are mutually exclusive credentials. A None value means that credential is not supplied; it is distinct from an empty string.

gui.mysql.createUser(options, session=None) -> UserInfo

• options is a MySQLUser. user, host, and one to three authenticationFactors are required.
• Every factor requires a non-empty plugin name. Plugin names must contain only ASCII letters, digits, and underscores, and the same plugin must not be used for more than one factor.
• Only factor 1 may contain password or use randomPassword=True. These inputs are accepted with caching_sha2_password, mysql_native_password, or sha256_password. A single authentication_webauthn factor is the creation-only exception: password supplies its initial authentication password, or randomPassword=True requests one from the server. One of the two is required. Factors 2 and 3 may contain authenticationString for plugins whose server configuration requires one.
• A factor must not specify both password and authenticationString. Factor 1 must not combine either credential with randomPassword=True.
• session is the MySQL session used to create the account. When omitted, the current global MySQL Shell session is used.
• The result is UserInfo. generatedPassword contains the generated password when randomPassword=True; otherwise it is None.

The API rejects an empty factor list, more than three factors, malformed or duplicate plugin names, a password on factor 2 or 3, or conflicting credential inputs. No account is created when validation fails.

gui.mysql.updateUser(options, session=None) -> UserInfo

• options is a MySQLUser. user and host identify the account to alter.
• authenticationFactors is the desired factor configuration in factor order. The API adds, removes, reorders, or changes factors to match this list.
• Factors follow the same plugin-name, uniqueness, and factor-count rules as createUser. password and randomPassword=True are accepted only for factor 1 using caching_sha2_password, mysql_native_password, or sha256_password.
• session is the MySQL session used to alter the account. When omitted, the current global MySQL Shell session is used.
• The result is UserInfo. generatedPassword contains the generated password when randomPassword=True; otherwise it is None.

Password reset, password retention, and old-password discard are separate operations described below.

Password Reset

Password reset accepts a new password and, for the current account, an optional current password. It targets the account's factor-1 MySQL-managed password. The active connection and selected user-account interfaces expose reset, retain-current, and discard-old-password operations. An expired-password connection enters the same reset flow before completing setup.

Password reset is available when the connection SSL Mode is Required, Verify CA, or Verify Identity, or when the connection uses a Unix socket.

The password-reset capability types are:

class PasswordResetAccount:
    user: str | None
    host: str | None

class PasswordResetFactor:
    plugin: str
    kind: Literal["internalPassword", "externalPassword"]
    passwordless: bool
    requiresRegistration: bool | None

class PasswordResetCurrentPassword:
    accountSetting: Literal["required", "optional", "default", "unknown"]
    requiredByPolicy: bool | None

class PasswordResetInfo:
    account: PasswordResetAccount
    factors: list[PasswordResetFactor]
    sqlResetSupported: bool | None
    retainCurrentPasswordSupported: bool
    currentPassword: PasswordResetCurrentPassword

gui.mysql.getPasswordResetInfo(user=None, host=None, session=None) -> PasswordResetInfo

• user and host identify a selected account. They must either both be supplied or both be omitted. When omitted, the API inspects CURRENT_USER.
• session is the MySQL session used to inspect the account. When omitted, the current global MySQL Shell session is used.
• account contains the canonical user and host returned by MySQL.
• factors contains the account's authentication factors in factor order. kind distinguishes MySQL-managed password plugins from external authentication plugins.
• sqlResetSupported is True when factor 1 uses a MySQL-managed password plugin and can be reset with ALTER USER ... IDENTIFIED BY. It is False for an external or passwordless factor 1. None means support could not be determined.
• retainCurrentPasswordSupported is True when SQL password reset is supported and the current session has sufficient privilege to retain the old password as a secondary password.
• currentPassword.accountSetting reports the account's PASSWORD REQUIRE CURRENT setting. requiredByPolicy resolves the account setting together with the server default; None means the requirement could not be determined.

The API raises an error when only one of user and host is supplied, the account cannot be inspected, or the session is not a MySQL session. The GUI uses the result to decide whether to offer reset, retain-current, and discard-old-password operations and whether the reset dialog must request the current password.

Password operations

The frontend performs password changes through the existing gui.db.execute(moduleSessionId, sql, params, options) API. moduleSessionId selects the SQL editor's service session. SQL values and account identifiers are passed as parameters, and password parameters are marked for log redaction.

• Resetting the current account executes ALTER USER USER() IDENTIFIED BY ?. When the current password is requested, it adds REPLACE ?; retaining the current password adds RETAIN CURRENT PASSWORD.
• Resetting a selected account executes ALTER USER ?@? IDENTIFIED BY ? and adds RETAIN CURRENT PASSWORD when selected.
• Discarding the secondary password executes ALTER USER USER() DISCARD OLD PASSWORD for the current account or ALTER USER ?@? DISCARD OLD PASSWORD for a selected account.

For an expired-password connection, the frontend executes the current-account reset on the service session before completing connection setup.