MySQL 5.6 Reference Manual :: 13 SQL Statement Syntax :: 13.7 Database Administration Statements :: 13.7.1 Account Management Statements :: 13.7.1.2 CREATE USER Syntax

Section Navigation [Toggle]

13.7.1.2 `CREATE USER` Syntax

CREATE USER user_specification [, user_specification] ...

user_specification:
    user
    [
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
        IDENTIFIED BY [PASSWORD] 'password'
    ]

The CREATE USER statement creates new MySQL accounts. An error occurs for accounts that already exist. To use this statement, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table with no privileges and assigns the account an authentication plugin. Depending on the syntax used, CREATE USER may also assign the account a password.

The user_specification clause consists of an account name and information about how authentication occurs for clients that use the account. This part of CREATE USER syntax is shared with GRANT, so the description here applies to GRANT as well.

Each account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:

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

If you specify only the user name part of the account name, a host name part of '%' is used.

The server assigns an authentication plugin and password to the account as follows, depending on whether the user specification clause includes IDENTIFIED WITH to specify a plugin or IDENTIFIED BY to specify a password.

With IDENTIFIED WITH, the server assigns the specified plugin and the account has no password.
With IDENTIFIED BY, the server assigns the specified password and assigns the plugin implicitly.
With neither IDENTIFIED WITH nor IDENTIFIED BY, the server assigns the plugin implicitly and the account has no password.

For implicit authentication plugin assignment, the server uses these rules:

As of MySQL 5.6.6, the server assigns the default plugin to the account. This plugin becomes the value of the plugin column in the account's mysql.user table row. The default plugin is mysql_native_password unless the --default-authentication-plugin option is set otherwise at server startup.
Before MySQL 5.6.6, the server assigns no plugin to the account. The plugin column in the account's mysql.user table row remains empty.

For client connections that use a given account, the server invokes the authentication plugin assigned to the account and the client must provide credentials as required by the authentication method that the plugin implements. If the server cannot find the plugin, either at account-creation time or connect time, an error occurs.

If an account's mysql.user table row has a nonempty plugin column:

The server authenticates client connection attempts using the named plugin.
Changes to the account password using SET PASSWORD with PASSWORD() must be made with the old_passwords system variable set to the value required by the authentication plugin, so that PASSWORD() uses the appropriate password hashing method. If the plugin is mysql_old_password, the password can also be changed using SET PASSWORD, with OLD_PASSWORD(), which uses pre-4.1 password hashing regardless of the value of old_passwords.

If an account's mysql.user table row has an empty plugin column:

The server authenticates client connection attempts using the mysql_native_password or mysql_old_password authentication plugin, depending on the hash format of the password stored in the Password column.
Changes to the account password using SET PASSWORD can be made with PASSWORD(), with old_passwords set to 0 or 1 for 4.1 or pre-4.1 password hashing, respectively, or with OLD_PASSWORD(), which uses pre-4.1 password hashing regardless of the value of old_passwords.

CREATE USER examples:

To specify an authentication plugin for an account, use IDENTIFIED WITH, where auth_plugin is the plugin name. It can be a quoted string literal or an unquoted name. 'auth_string' is an optional quoted string literal to pass to the plugin. The plugin interprets the meaning of the string, so its format is plugin specific. Consult the documentation for a given plugin for information about the authentication string values it accepts, if any.
```
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password;
```
The server assigns the given authentication plugin to the account but no password. This is insecure; to set the password, use SET PASSWORD. See Section 13.7.1.7, “SET PASSWORD Syntax”.
To ensure that an account uses a specific authentication plugin and that the password has the corresponding hash format, specify the plugin explicitly with IDENTIFIED WITH, then use SET PASSWORD to set the password.
```
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password;
SET old_passwords = 0;
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
```
Changes to the account password using SET PASSWORD with PASSWORD() must be made with the old_passwords system variable set to the value required by the account's authentication plugin, so that PASSWORD() uses the appropriate password hashing method. Therefore, to use the sha256_password or mysql_old_password plugin instead, name that plugin in the CREATE USER statement and set old_passwords to 2 or 1, respectively. (Use of mysql_old_password is not recommended. It is deprecated and support for it will be removed in a future MySQL release.)
To specify a password for an account at account-creation time, use IDENTIFIED BY with the literal plaintext password value:
```
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
```
The server assigns an authentication plugin to the account implicitly, as described previously, and assigns the given password. Clients must provide the given password when they connect.
If the implicitly assigned plugin is mysql_native_password, the old_passwords system variable must be set to 0. Otherwise, CREATE USER does not hash the password in the format required by the plugin and an error occurs.
```
mysql> SET old_passwords = 1;
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
ERROR 1827 (HY000): The password hash doesn't have the expected
format. Check if the correct password algorithm is being used with
the PASSWORD() function.

mysql> SET old_passwords = 0;
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)
```
To avoid specifying the plaintext password if you know its hash value (the value that PASSWORD() would return for the password), specify the hash value preceded by the keyword PASSWORD:
```
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
```
The server assigns an authentication plugin to the account implicitly, as described previously, and assigns the given password. The password hash must be in the format required by the assigned plugin. Clients must provide the given password when they connect.
To enable the user to connect with no password, include no IDENTIFIED BY clause:
```
CREATE USER 'jeffrey'@'localhost';
```
The server assigns an authentication plugin to the account implicitly, as described previously, but no password. Clients must provide no password when they connect. However, an account with no password is insecure. To avoid this, use SET PASSWORD to set the account password. See Section 13.7.1.7, “SET PASSWORD Syntax”.

Permitted values of --default-authentication-plugin are mysql_native_plugin and sha256_password, but not mysql_old_password. This means it is not possible to set the default plugin so as to be able to create an account that uses mysql_old_password with CREATE USER ... IDENTIFIED BY syntax. To create an account that uses mysql_old_password, use CREATE USER ... IDENTIFIED WITH to name the plugin explicitly, then set the password:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_old_password;
SET old_passwords = 1;
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');

However, mysql_old_password is deprecated, so the preceding procedure is not recommended.

For additional information about setting passwords, see Section 6.3.5, “Assigning Account Passwords”. For information about authentication plugins, see Section 6.3.7, “Pluggable Authentication”.

Important

CREATE USER may be recorded in server logs or in a history file such as ~/.mysql_history, which means that cleartext passwords may be read by anyone having read access to that information. See Section 6.1.2, “Keeping Passwords Secure”.

Important

Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. To make sure that you can take advantage of any new capabilities, update your grant tables to have the current structure whenever you update to a new version of MySQL. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

Previous / Next / Up / Table of Contents

User Comments

Add your own comment.

Top / Previous / Next / Up / Table of Contents