GeezerVison

Purpose

MySQL 8.4 multi-factor authentication adds clauses to CREATE USER and ALTER USER that the parser rejects today. This PR parses them into the existing account model.

CREATE USER u1 IDENTIFIED WITH caching_sha2_password BY 'p'
    AND IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u'
    AND IDENTIFIED WITH authentication_webauthn;
CREATE USER u2 IDENTIFIED WITH authentication_webauthn INITIAL AUTHENTICATION IDENTIFIED BY 'p';
ALTER USER u1 ADD 2 FACTOR IDENTIFIED WITH authentication_ldap_sasl AS 'uid=u' ADD 3 FACTOR IDENTIFIED BY 'p';
ALTER USER u1 MODIFY 2 FACTOR IDENTIFIED WITH authentication_webauthn;
ALTER USER u1 DROP 2 FACTOR DROP 3 FACTOR;
ALTER USER u1 2 FACTOR INITIATE REGISTRATION;
ALTER USER u1 2 FACTOR FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'x';
ALTER USER USER() 2 FACTOR UNREGISTER;

References: CREATE USER, ALTER USER, Multifactor Authentication.

Current design (As-is)

• UserAccount holds one UserAuthentication (IDENTIFIED BY ... / IDENTIFIED WITH ..., and for ALTER USER also REPLACE, RETAIN CURRENT PASSWORD, DISCARD OLD PASSWORD).
• CREATE USER reads account [IDENTIFIED ...]; AND IDENTIFIED ... and INITIAL AUTHENTICATION are parse errors.
• ALTER USER reads an authentication change, the shared UserOptions, DEFAULT ROLE, or USER() with a password change. ADD/MODIFY/DROP of a factor and the registration steps are parse errors.

Target design (To-be)

The clauses extend the existing model; no statement class or visitor method is added.

The grammar follows sql_yacc.yy of MySQL 8.4:

• CREATE USER: at most two AND IDENTIFIED ... per account, with any of the six IDENTIFIED forms; the server grammar also accepts them without a first factor (CREATE USER u AND IDENTIFIED BY 'q'). INITIAL AUTHENTICATION follows only IDENTIFIED WITH plugin without credentials, takes IDENTIFIED BY 'auth', IDENTIFIED BY RANDOM PASSWORD or IDENTIFIED WITH plugin AS 'hash', and cannot be combined with AND.
• ALTER USER: one or two changes of the same kind per account (ADD ... ADD ..., MODIFY ... MODIFY ..., DROP ... DROP ...; the second DROP repeats the keyword), in account lists and before the account options, not with USER(), DEFAULT ROLE, REPLACE or RETAIN CURRENT PASSWORD.
• Registration steps apply to one account or USER() (also after IF EXISTS) and end the statement.
• The factor is an integer literal followed by FACTOR. As on the server, a value other than 2 or 3 is not a syntax error (MySQL reports Incorrect nth factor value when it runs the statement); getFactor() returns the number, so leading zeros are not kept.

FACTOR, INITIAL, AUTHENTICATION, INITIATE, REGISTRATION, FINISH, UNREGISTER and CHALLENGE_RESPONSE are read as contextual keywords and stay ordinary identifiers; no token is added. As for the existing MySQL account clauses, the forms are also read without a dialect and in the MariaDB dialect; PostgreSQL ALTER USER (the ALTER ROLE alias) is unchanged.

toString() and StatementDeParser share the rendering through UserAccount.toString(). The change is additive: existing getters, setters and constructors are unchanged.

Validation

• New MySqlMultiFactorAuthenticationTest with 93 tests: AST fields, toString/StatementDeParser/reparse round trips, default mode and MariaDB, statement boundaries in a script, builders, features, TablesNamesFinder, validation, 68 rejected forms, and the new words as identifiers.

• ./gradlew spotlessApply cleanTest check jar on Java 17: 13,427 tests, 0 failures, 25 skipped.

• Compared with master (e27929f76) on the DDL/DCL and other-statement survey corpora for MySQL and PostgreSQL (dialect and default mode, keyword and script corpora): 0 regressions and no changed result. The corpora contain no multi-factor form; a separate list of 78 accepted and 68 rejected forms covers them, and all 78 fail on master.

• MySQL 8.4.11 accepts the syntax of all 78 statements the tests accept, and master rejects every one of them. None of them runs to completion in a stock server: they stop at execution with a plugin, policy or unknown-user error. MySQL reports a syntax error (1064) for all 68 statements the tests reject, including DROP 2 FACTOR 3 FACTOR (the second DROP must be repeated), mixed ADD/MODIFY/DROP changes, a third change, a factor of 2147483648, 2.0 or '2', INITIAL AUTHENTICATION after a plugin with credentials, factor changes with USER(), and a registration step in an account list.

AI assistance: prepared with Claude Code.