MySQL :: MySQL 8.4 Reference Manual :: 15.7.1.3 CREATE USER Statement (original) (raw)

15.7.1.3 CREATE USER Statement

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 Section 8.2.4, “Specifying Account Names”)

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 themysql system schema. When theread_only system variable is enabled, CREATE USER additionally requires the CONNECTION_ADMIN privilege (or the deprecatedSUPER privilege).

These additional privilege considerations also apply:

CREATE USER fails with an error if any account to be created is named as theDEFINER 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 theSET_ANY_DEFINER orALLOW_NONEXISTENT_DEFINER privilege; in this case, the statement succeeds with a warning rather than failing with an error. To perform the user-creation operation without either of these, 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 theDEFINER attribute, seeOrphan Stored 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.

Important

Under some circumstances, CREATE USER may be recorded in server logs or on the client side in a history file such as~/.mysql_history, which means that cleartext passwords may be read by anyone having read access to that information. For information about the conditions under which this occurs for the server logs and how to control it, see Section 8.1.2.3, “Passwords and Logging”. For similar information about client-side logging, seeSection 6.5.1.3, “mysql Client Logging”.

There are several aspects to the CREATE USER statement, described under the following topics:

CREATE USER Overview

For each account, CREATE USER creates a new row in the mysql.user system table. The account row reflects the properties specified in the statement. Unspecified properties are set to their default values:

An account when first created has no privileges and the default role NONE. To assign privileges or roles to this account, use one or moreGRANT statements.

Each account name uses the format described inSection 8.2.4, “Specifying Account Names”. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

The host name part of the account name, if omitted, defaults to '%'. You should be aware that, while MySQL 8.4 treats grants made to such a user as though they had been granted to'_`user`_'@'localhost', this behavior is deprecated, and thus subject to removal in a future version of MySQL.

Each user value naming an account may be followed by an optional_authoption_ value that indicates how the account authenticates. These values enable account authentication plugins and credentials (for example, a password) to be specified. Each_authoption_ value applies_only_ to the account named immediately preceding it.

Following the user specifications, the statement may include options for SSL/TLS, resource-limit, password-management, and locking properties. All such options are global to the statement and apply to_all_ accounts named in the statement.

Example: Create an account that uses the default authentication plugin and the given password. Mark the password expired so that the user must choose a new one at the first connection to the server:

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

Example: Create an account that uses thecaching_sha2_password authentication plugin and the given password. Require that a new password be chosen every 180 days, and enable failed-login tracking, such that three consecutive incorrect passwords cause temporary account locking for two days:

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

Example: Create multiple accounts, specifying some per-account properties and some global properties:

CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH caching_sha2_password
                                  BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  PASSWORD HISTORY 5
  ACCOUNT LOCK;

Each authoption value (IDENTIFIED WITH ... BY in this case) applies only to the account named immediately preceding it, so each account uses the immediately following authentication plugin and password.

The remaining properties apply globally to all accounts named in the statement, so for both accounts:

CREATE USER Authentication Options

An account name may be followed by an_authoption_ authentication option that specifies the account authentication plugin, credentials, or both.

Note

MySQL 8.4 supports multifactor authentication (MFA), such that accounts can have up to three authentication methods. That is, accounts can use two-factor authentication (2FA) or three-factor authentication (3FA). The syntax and semantics of_authoption_ remain unchanged, but_authoption_ may be followed by specifications for additional authentication methods. This section describes authoption. For details about the optional MFA-related following clauses, seeCREATE USER Multifactor Authentication Options.

Note

Clauses for random password generation apply only to accounts that use an authentication plugin that stores credentials internally to MySQL. For accounts that use a plugin that performs authentication against a credentials system that is external to MySQL, password management must be handled externally against that system as well. For more information about internal credentials storage, seeSection 8.2.15, “Password Management”.

CREATE USER permits these_authoption_ syntaxes:

Example: Specify the password as cleartext; the default plugin is used:

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'password';

Example: Specify the authentication plugin, along with a cleartext password value:

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'password';

In each case, the password value stored in the account row is the cleartext value'_`password`_' after it has been hashed by the authentication plugin associated with the account.

For additional information about setting passwords and authentication plugins, seeSection 8.2.14, “Assigning Account Passwords”, andSection 8.2.17, “Pluggable Authentication”.

CREATE USER Multifactor Authentication Options

The authoption part ofCREATE USER defines an authentication method for one-factor/single-factor authentication (1FA/SFA). CREATE USER also supports multifactor authentication (MFA), such that accounts can have up to three authentication methods. That is, accounts can use two-factor authentication (2FA) or three-factor authentication (3FA).

The authentication_policy system variable defines constraints forCREATE USER statements with multifactor authentication (MFA) clauses. For example, theauthentication_policy setting controls the number of authentication factors that accounts may have, and for each factor, which authentication methods are permitted. SeeConfiguring the Multifactor Authentication Policy.

For information about factor-specific rules that determine the default authentication plugin for authentication clauses that name no plugin, seeThe Default Authentication Plugin.

Following authoption, there may appear different optional MFA clauses:

CREATE USER 'u1'@'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';  
CREATE USER 'u1'@'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;  
CREATE USER user  
  IDENTIFIED WITH authentication_webauthn  
  INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'};

For information about configuring passwordless authentication using WebAuthn pluggable authentication, SeeWebAuthn Passwordless Authentication.

CREATE USER Role Options

The DEFAULT ROLE clause defines which roles become active when the user connects to the server and authenticates, or when the user executes theSET ROLE DEFAULT statement during a session.

Each role name uses the format described inSection 8.2.5, “Specifying Role Names”. For example:

CREATE USER 'joe'@'10.0.0.1' DEFAULT ROLE administrator, developer;

The host name part of the role name, if omitted, defaults to'%'.

The DEFAULT ROLE clause permits a list of one or more comma-separated role names. These roles must exist at the time CREATE USER is executed; otherwise the statement raises an error (ER_USER_DOES_NOT_EXIST), and the user is not created.

CREATE USER SSL/TLS Options

MySQL can check X.509 certificate attributes in addition to the usual authentication that is based on the user name and credentials. For background information on the use of SSL/TLS with MySQL, see Section 8.3, “Using Encrypted Connections”.

To specify SSL/TLS-related options for a MySQL account, use aREQUIRE clause that specifies one or more_tlsoption_ values.

Order of REQUIRE options does not matter, but no option can be specified twice. TheAND keyword is optional betweenREQUIRE options.

CREATE USER permits these_tlsoption_ values:

CREATE USER 'jeffrey'@'localhost' REQUIRE NONE;

Clients attempt to establish a secure connection by default. For clients that have REQUIRE NONE, the connection attempt falls back to an unencrypted connection if a secure connection cannot be established. To require an encrypted connection, a client need specify only the--ssl-mode=REQUIRED option; the connection attempt fails if a secure connection cannot be established.
NONE is the default if no SSL-relatedREQUIRE options are specified.

CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;

Clients attempt to establish a secure connection by default. For accounts that have REQUIRE SSL, the connection attempt fails if a secure connection cannot be established.

CREATE USER 'jeffrey'@'localhost' REQUIRE X509;

For accounts with REQUIRE X509, clients must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that--ssl-ca also be specified so that the public certificate provided by the server can be verified.) This is true for ISSUER and SUBJECT as well because thoseREQUIRE options imply the requirements of X509.

CREATE USER 'jeffrey'@'localhost'  
  REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/  
    O=MySQL/CN=CA/emailAddress=ca@example.com';

Because ISSUER implies the requirements of X509, clients must specify the--ssl-key and--ssl-cert options to connect. (It is recommended but not required that--ssl-ca also be specified so that the public certificate provided by the server can be verified.)

CREATE USER 'jeffrey'@'localhost'  
  REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/  
    O=MySQL demo client certificate/  
    CN=client/emailAddress=client@example.com';

MySQL does a simple string comparison of the'_`subject`_' value to the value in the certificate, so lettercase and component ordering must be given exactly as present in the certificate.
Because SUBJECT implies the requirements of X509, clients must specify the --ssl-key and--ssl-cert options to connect. (It is recommended but not required that--ssl-ca also be specified so that the public certificate provided by the server can be verified.)

CREATE USER 'jeffrey'@'localhost'  
  REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

The SUBJECT, ISSUER, andCIPHER options can be combined in theREQUIRE clause:

CREATE USER 'jeffrey'@'localhost'
  REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL demo client certificate/
    CN=client/emailAddress=client@example.com'
  AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL/CN=CA/emailAddress=ca@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
CREATE USER Resource-Limit Options

It is possible to place limits on use of server resources by an account, as discussed in Section 8.2.21, “Setting Account Resource Limits”. To do so, use a WITH clause that specifies one or more resourceoption values.

Order of WITH options does not matter, except that if a given resource limit is specified multiple times, the last instance takes precedence.

CREATE USER permits these_resourceoption_ values:

Example:

CREATE USER 'jeffrey'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
CREATE USER Password-Management Options

CREATE USER supports several_passwordoption_ values for password management:

This section describes the syntax for password-management options. For information about establishing policy for password management, seeSection 8.2.15, “Password Management”.

If multiple password-management options of a given type are specified, the last one takes precedence. For example,PASSWORD EXPIRE DEFAULT PASSWORD EXPIRE NEVER is the same as PASSWORD EXPIRE NEVER.

Note

Except for the options that pertain to failed-login tracking, password-management options apply only to accounts that use an authentication plugin that stores credentials internally to MySQL. For accounts that use a plugin that performs authentication against a credentials system that is external to MySQL, password management must be handled externally against that system as well. For more information about internal credentials storage, seeSection 8.2.15, “Password Management”.

A client has an expired password if the account password was expired manually or the password age is considered greater than its permitted lifetime per the automatic expiration policy. In this case, the server either disconnects the client or restricts the operations permitted to it (seeSection 8.2.16, “Server Handling of Expired Passwords”). Operations performed by a restricted client result in an error until the user establishes a new account password.

CREATE USER permits these_passwordoption_ values for controlling password expiration:

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;  
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;  
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;  
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;

CREATE USER permits these_passwordoption_ values for controlling reuse of previous passwords based on required minimum number of password changes:

CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY DEFAULT;  
CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;

CREATE USER permits these_passwordoption_ values for controlling reuse of previous passwords based on time elapsed:

CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL DEFAULT;  
CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 360 DAY;

CREATE USER permits these_passwordoption_ values for controlling whether attempts to change an account password must specify the current password, as verification that the user attempting to make the change actually knows the current password:

CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;  
CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;  
CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

CREATE USER permits these_passwordoption_ values for controlling failed-login tracking:

For failed-login tracking and temporary locking to occur, an account's FAILED_LOGIN_ATTEMPTS andPASSWORD_LOCK_TIME options both must be nonzero. The following statement creates an account that remains locked for two days after four consecutive password failures:

CREATE USER 'jeffrey'@'localhost'
  FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 2;
CREATE USER Comment and Attribute Options

You can also include an optional comment or attribute when creating a user, as described here:

CREATE USER 'jon'@'localhost' COMMENT 'Some information about Jon';  
CREATE USER 'jim'@'localhost'  
    ATTRIBUTE '{"fname": "James", "lname": "Scott", "phone": "123-456-7890"}';

User comments and user attributes are stored together in theATTRIBUTE column of the Information SchemaUSER_ATTRIBUTES table. This query displays the row in this table inserted by the statement just shown for creating the user jim@localhost:

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->    WHERE USER = 'jim' AND HOST = 'localhost'\G
*************************** 1. row ***************************
     USER: jim
     HOST: localhost
ATTRIBUTE: {"fname": "James", "lname": "Scott", "phone": "123-456-7890"}
1 row in set (0.00 sec)

The COMMENT option in actuality provides a shortcut for setting a user attribute whose only element hascomment as its key and whose value is the argument supplied for the option. You can see this by executing the statement CREATE USER 'jon'@'localhost' COMMENT 'Some information about Jon', and observing the row which it inserts into theUSER_ATTRIBUTES table:

mysql> CREATE USER 'jon'@'localhost' COMMENT 'Some information about Jon';
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->    WHERE USER = 'jon' AND HOST = 'localhost';
+------+-----------+-------------------------------------------+
| USER | HOST      | ATTRIBUTE                                 |
+------+-----------+-------------------------------------------+
| jon  | localhost | {"comment": "Some information about Jon"} |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

You cannot use COMMENT andATTRIBUTE together in the sameCREATE USER statement; attempting to do so causes a syntax error. To set a user comment concurrently with setting a user attribute, use ATTRIBUTE and include in its argument a value with acomment key, like this:

mysql> CREATE USER 'bill'@'localhost'
    ->        ATTRIBUTE '{"fname":"William", "lname":"Schmidt",
    ->        "comment":"Website developer"}';
Query OK, 0 rows affected (0.16 sec)

Since the content of the ATTRIBUTE row is a JSON object, you can employ any appropriate MySQL JSON functions or operators to manipulate it, as shown here:

mysql> SELECT
    ->   USER AS User,
    ->   HOST AS Host,
    ->   CONCAT(ATTRIBUTE->>"$.fname"," ",ATTRIBUTE->>"$.lname") AS 'Full Name',
    ->   ATTRIBUTE->>"$.comment" AS Comment
    -> FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    -> WHERE USER='bill' AND HOST='localhost';
+------+-----------+-----------------+-------------------+
| User | Host      | Full Name       | Comment           |
+------+-----------+-----------------+-------------------+
| bill | localhost | William Schmidt | Website developer |
+------+-----------+-----------------+-------------------+
1 row in set (0.00 sec)

To set or to make changes in the user comment or user attribute for an existing user, you can use aCOMMENT or ATTRIBUTE option with an ALTER USER statement.

Because the user comment and user attribute are stored together internally in a singleJSON column, this sets an upper limit on their maximum combined size; seeJSON Storage Requirements, for more information.

See also the description of the Information SchemaUSER_ATTRIBUTES table for more information and examples.

CREATE USER Account-Locking Options

MySQL supports account locking and unlocking using theACCOUNT LOCK and ACCOUNT UNLOCK options, which specify the locking state for an account. For additional discussion, seeSection 8.2.20, “Account Locking”.

If multiple account-locking options are specified, the last one takes precedence.

CREATE USER Binary Logging

CREATE USER is written to the binary log if it succeeds, but not if it fails; in that case, rollback occurs and no changes are made. A statement written to the binary log includes all named users. If the IF NOT EXISTS clause is given, this includes even users that already exist and were not created.

The statement written to the binary log specifies an authentication plugin for each user, determined as follows:

If the server adds the default authentication plugin for any nonexisting users in the statement written to the binary log, it writes a warning to the error log naming those users.

If the original statement specifies theFAILED_LOGIN_ATTEMPTS orPASSWORD_LOCK_TIME option, the statement written to the binary log includes the option.

CREATE USER statements with clauses that support multifactor authentication (MFA) are written to the binary log.