Syntax:
ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

ALTER USER [IF EXISTS]
    USER() user_func_auth_option

ALTER USER [IF EXISTS]
    user [registration_option]

ALTER USER [IF EXISTS]
    USER() [registration_option]

ALTER USER [IF EXISTS]
    user DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}

user:
    (see )

auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | DISCARD OLD PASSWORD
  | ADD factor factor_auth_option [ADD factor factor_auth_option]
  | MODIFY factor factor_auth_option [MODIFY factor factor_auth_option]
  | DROP factor [DROP factor]
}

user_func_auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | DISCARD OLD PASSWORD
}

factor_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

registration_option: {
    factor INITIATE REGISTRATION
  | factor FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'auth_string'
  | factor UNREGISTER
}

factor: {2 | 3} FACTOR

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

The ALTER USER statement modifies MySQL accounts. It enables
authentication, role, SSL/TLS, resource-limit, password-management,
comment, and attribute properties to be modified for existing accounts.
It can also be used to lock and unlock accounts.

In most cases, ALTER USER requires the global CREATE USER privilege, or
the UPDATE privilege for the mysql system schema. The exceptions are:

o Any client who connects to the server using a nonanonymous account
  can change the password for that account. (In particular, you can
  change your own password.) To see which account the server
  authenticated you as, invoke the CURRENT_USER() function:

SELECT CURRENT_USER();

o For DEFAULT ROLE syntax, ALTER USER requires these privileges:

  o Setting the default roles for another user requires the global
    CREATE USER privilege, or the UPDATE privilege for the
    mysql.default_roles system table.

  o Setting the default roles for yourself requires no special
    privileges, as long as the roles you want as the default have been
    granted to you.

o Statements that modify secondary passwords require these privileges:

  o The APPLICATION_PASSWORD_ADMIN privilege is required to use the
    RETAIN CURRENT PASSWORD or DISCARD OLD PASSWORD clause for ALTER
    USER statements that apply to your own account. The privilege is
    required to manipulate your own secondary password because most
    users require only one password.

  o If an account is to be permitted to manipulate secondary passwords
    for all accounts, it requires the CREATE USER privilege rather than
    APPLICATION_PASSWORD_ADMIN.

When the read_only system variable is enabled, ALTER USER additionally
requires the CONNECTION_ADMIN privilege (or the deprecated SUPER
privilege).

As of MySQL 8.0.27, these additional privilege considerations apply:

o The authentication_policy system variable places certain constraints
  on how the authentication-related clauses of ALTER USER statements
  may be used; for details, see the description of that variable. These
  constraints do not apply if you have the AUTHENTICATION_POLICY_ADMIN
  privilege.

o To modify an account that uses passwordless authentication, you must
  have the PASSWORDLESS_USER_ADMIN privilege.

By default, an error occurs if you try to modify a user that does not
exist. If the IF EXISTS clause is given, the statement produces a
warning for each named user that does not exist, rather than an error.

URL: https://dev.mysql.com/doc/refman/8.0/en/alter-user.html

