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'
  | 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'
}

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, and password-management
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.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

