Movatterモバイル変換

• Elements of Multifactor Authentication Support

• Configuring the Multifactor Authentication Policy

• Getting Started with Multifactor Authentication

Authentication factors commonly include these types of information:

The“something you know” factor type relies on information that is kept secret on both sides of the authentication process. Unfortunately, secrets may be subject to compromise: Someone might see you enter your password or fool you with a phishing attack, a password stored on the server side might be exposed by a security breach, and so forth. Security can be improved by using multiple passwords, but each may still be subject to compromise. Use of the other factor types enables improved security with less risk of compromise.

Implementation of multifactor authentication in MySQL comprises these elements:

Theauthentication_policy system variable defines the multifactor authentication policy. Specifically, it 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 ofauthentication_policy is a list of 1, 2, or 3 comma-separated elements. Each element in the list corresponds to an authentication factor and can be an authentication plugin name, an asterisk (*), empty, or missing. (Exception: Element 1 cannot be empty or missing.) The entire list is enclosed in single quotes. For example, the followingauthentication_policy value includes an asterisk, an authentication plugin name, and an empty element:

authentication_policy = '*,authentication_webauthn,'

An 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, empty element, or 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 defaultauthentication_policy value is'*,,' (an asterisk and two empty elements), which requires a first factor, and optionally permits second and third factors. The defaultauthentication_policy value is thus backward compatible with existing 1FA accounts, but also permits creation or modification of accounts to use 2FA or 3FA.

A user who has theAUTHENTICATION_POLICY_ADMIN privilege is not subject to the constraints imposed by theauthentication_policy setting. (A warning occurs for statements that otherwise would not be permitted.)

authentication_policy values can be defined in an option file or specified using aSET GLOBAL statement:

SET GLOBAL authentication_policy='*,*,';

There are several rules that govern how theauthentication_policy value can be defined. Refer to theauthentication_policy system variable description for a compete account of those rules. The following table provides severalauthentication_policy example values and the policy established by each.

By default, MySQL uses a multifactor authentication policy that permits any authentication plugin for the first factor, and optionally permits second and third authentication factors. This policy is configurable; for details, seeConfiguring the Multifactor Authentication Policy.

Suppose that you want an account to authenticate first using thecaching_sha2_password plugin, then using theauthentication_ldap_sasl SASL LDAP plugin. (This assumes that LDAP authentication is already set up as described inSection 8.4.1.6, “LDAP Pluggable Authentication”, and that the user has an entry in the LDAP directory corresponding to the authentication string shown in the example.) Create the account using a statement like this:

CREATE USER 'alice'@'localhost'  IDENTIFIED WITH caching_sha2_password    BY 'sha2_password'  AND IDENTIFIED WITH authentication_ldap_sasl    AS 'uid=u1_ldap,ou=People,dc=example,dc=com';

To connect, the user must supply two passwords. To enable authentication to the MySQL server using accounts that require multiple passwords, client programs have--password1,--password2, and--password3 options that permit up to three passwords to be specified. These options are similar to the--password option in that they can take a password value following the option on the command line (which is insecure) or if given without a password value cause the user to be prompted for one. For the account just created, factors 1 and 2 take passwords, so invoke themysql client with the--password1 and--password2 options.mysql prompts for each password in turn:

$> mysql --user=alice --password1 --password2Enter password:(enter factor 1 password)Enter password:(enter factor 2 password)

Suppose you want to add a third authentication factor. This can be achieved by dropping and recreating the user with a third factor or by usingALTER USERuser ADDfactor syntax. Both methods are shown below:

DROP USER 'alice'@'localhost';  CREATE USER 'alice'@'localhost'  IDENTIFIED WITH caching_sha2_password    BY 'sha2_password'  AND IDENTIFIED WITH authentication_ldap_sasl    AS 'uid=u1_ldap,ou=People,dc=example,dc=com'  AND IDENTIFIED WITH authentication_webauthn;

ADDfactor syntax includes the factor number andFACTOR keyword:

ALTER USER 'alice'@'localhost' ADD 3 FACTOR IDENTIFIED WITH authentication_webauthn;

ALTER USERuser DROPfactor syntax permits dropping a factor. The following example drops the third factor (authentication_webauthn) that was added in the previous example:

ALTER USER 'alice'@'localhost' DROP 3 FACTOR;

ALTER USERuser MODIFYfactor syntax permits changing the plugin or authentication string for a particular factor, provided that the factor exists. The following example modifies the second factor, changing the authentication method fromauthentication_ldap_sasl toauthetication_webauthn:

ALTER USER 'alice'@'localhost' MODIFY 2 FACTOR IDENTIFIED WITH authentication_webauthn;

UseSHOW CREATE USER to view the authentication methods defined for an account:

SHOW CREATE USER 'u1'@'localhost'\G*************************** 1. row ***************************CREATE USER for u1@localhost: CREATE USER `u1`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 'sha2_password' AND IDENTIFIED WITH 'authentication_authn' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT