MySQL 8: Multi-Factor Authentication Overview

As part of my ongoing series around MySQL 8 user administration, I’d like to cover one of the new features introduced in MySQL 8.0.27 – multi-factor authentication. In order to establish identity, multi-factor authentication (MFA) is the use of multiple authentication values (factors) during the MySQL authentication process.

Introduction

MFA provides greater security compared to a single-factor authentication method, which has historically been based on simple methods such as password authentication. With MFA, additional authentication methods are enabled, such as requiring multiple passwords, or with devices such as smart cards, security keys, or biometric readers.

As of MySQL 8.0.27, it is now possible to require up to three authentication values to establish identity. In addition to the more common 2FA (two-factor authentication), MySQL can now also support 3FA (three-factor authentication) to complement the existing single-factor authentication that we’re all familiar with. You can mix and match different authentication methods in different combinations which makes MySQL’s MFA implementation very configurable to meet your particular security goals.

How It Works

The process of authentication is similar regardless of the number of factors required. When connecting to MySQL in the typical way (1FA using a password), the server invokes the authentication plugin indicated by the account definition and accepts or rejects the connection depending on whether the plugin reports success or failure.

With MFA authentication, the server follows the same basic process but will invoke the authentication plugins in the order listed in the account definition. If a plugin reports success and is the last plugin listed, the server will accept the connection. If a plugin reports success and is not the last plugin in the list, it will invoke the next plugin and move through the list until all of the plugins have been successfully processed. If any one of the plugins reports failure, the server will reject the connection.

MFA Elements

Authentication factors will commonly include:

• Secret passwords
• Security key (or smart card)
• Biometric data (fingerprints, facial scan, etc)

Using a password relies on that information being kept secret on both sides of the authentication process. As we all know, however, this can be subject to compromise. It is possible for your password to fall into other hands in various ways. Someone could see you entering your password, or you could be a victim of a phishing attack. Passwords are compromised during server-side security breaches, so it is possible to lose password security even when you’re not specifically at fault. While security can certainly be improved upon by using multiple passwords, the safest method would be to utilize some of the other factor types in addition to password authentication. This will give you the best security with the least risk of compromise.

• The number and type of implementation factors are controlled with the authentication_policy system variable. This variable places constraints on CREATE USER and ALTER USER statements with respect to multifactor authentication.
• CREATE USER and ALTER USER have syntax enabling multiple authentication methods to be specified for new accounts, and for adding, modifying, or dropping authentication methods for an existing account. 
  • If an account uses 2FA or 3FA, the mysql.user table stores information about the additional authentication factors in the User_attributes column. 

Authentication Policy

The authentication_policy system variable defines the multi-factor authentication policy. In a nutshell, this variable defines how many authentication factors accounts may have (or are required to have) and the authentication methods that can be used for each factor. 

The value of the authentication_policy variable is a list with one, two, or three comma-separated elements depending on your desired level of MFA. Each element in this list corresponds to an authentication factor and can be an authentication plugin name, an asterisk (*), empty, or missing. 

NOTE: There is one exception to this – the first element cannot be either empty or missing. 

As an example, see the following authentication_policy value which includes an asterisk, an authentication plugin name, and an empty element:

• The asterisk (*) indicates that an authentication method is required, but any method is permitted. 
• An empty element indicates that an authentication method is optional, and any method is permitted. 
• A missing element (no asterisk, no empty element, no authentication plugin name) indicates that an authentication method is *not* permitted. 
• When a plugin name is specified, that authentication method is required for the respective factor when creating or modifying an account. 

The default authentication_policy value is ‘*,,’ (an asterisk, and two empty elements). This default configuration requires a first factor and optionally permits a second and third factor as well. As such, the default authentication_policy is entirely backward compatible with existing 1FA accounts but gives the added flexibility of being able to use 2FA or 3FA as well. 

To enable authentication to the MySQL server using accounts that require multiple passwords (such as caching_sha2_password + authentication_ldap_simple for instance), client programs have –password1, –password2, and –password3 options that can permit up to three passwords to be specified. 

External Device(s) Authentication

The server-side authentication_fido plugin enables authentication using external devices. If this plugin is the only authentication plugin used by an account, it will also allow passwordless authentication. This plugin is only included in MySQL Enterprise distributions, however, and is not included in MySQL Community distributions. 

The client-side authentication_fido_client plugin is included in all distributions, including the MySQL Community distributions. This enables clients from any distribution to connect to accounts that use authentication_fido to authenticate on a server that has that plugin loaded. 

Note that multi-factor authentication can use non-FIDO MySQL authentication methods, the FIDO authentication method, or a combination of both. 

Exceptions

The following privileges enable users to perform certain restricted multi-factor authentication-related operations.

• If a user has the AUTHENTICATION_POLICY_ADMIN privilege, they will not be subject to the constraints imposed by the authentication_policy system variable (see above). While the constraints are not enforced in this case, a warning is given for any statements that otherwise would not be permitted. 
• If a user has the PASSWORDLESS_USER_ADMIN privilege, this will enable the creation of passwordless authentication accounts and replication of operations on those accounts. 

In Closing

Using multi-factor authentication can increase the security of your database systems, and MySQL 8.0.27 is bringing several powerful new options to the table with a robust MFA implementation. In future installments of this series, I’ll continue to expand on this topic as we dive deeper into the user administration and security aspects of MySQL. If you are interested in further exploring MFA in your database environment, let our Professional Services team help you implement the approach that is best suited for your requirements – we are here to help!