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

user:
    (see )

auth_option: {
    IDENTIFIED BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [initial_auth_option]
}

2fa_auth_option: {
    IDENTIFIED BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 3fa_auth_option]
}

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

initial_auth_option: {
    INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'}
  | INITIAL AUTHENTICATION IDENTIFIED WITH auth_plugin AS 'auth_string'
}

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 CREATE USER statement creates new MySQL accounts. It enables
authentication, role, SSL/TLS, resource-limit, password-management,
comment, and attribute properties to be established for new accounts.
It also controls whether accounts are initially locked or unlocked.

To use CREATE USER, you must have the global CREATE USER privilege, or
the INSERT privilege for the mysql system schema. When the read_only
system variable is enabled, CREATE 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 CREATE 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 create an account that uses passwordless authentication, you must
  have the PASSWORDLESS_USER_ADMIN privilege.

As of MySQL 8.0.22, CREATE USER fails with an error if any account to
be created is named as the DEFINER attribute for any stored object.
(That is, the statement fails if creating an account would cause the
account to adopt a currently orphaned stored object.) To perform the
operation anyway, you must have the SET_USER_ID privilege; in this
case, the statement succeeds with a warning rather than failing with an
error. Without SET_USER_ID, to perform the user-creation operation,
drop the orphan objects, create the account and grant its privileges,
and then re-create the dropped objects. For additional information,
including how to identify which objects name a given account as the
DEFINER attribute, see
https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#st
ored-objects-security-orphan-objects.

CREATE USER either succeeds for all named users or rolls back and has
no effect if any error occurs. By default, an error occurs if you try
to create a user that already exists. If the IF NOT EXISTS clause is
given, the statement produces a warning for each named user that
already exists, rather than an error.

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

