MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide CREATE USER Statement

    user [auth_option] [, user [auth_option]] ...

    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY [PASSWORD] 'auth_string'
  | IDENTIFIED WITH auth_plugin [AS 'auth_string']

The CREATE USER statement creates new MySQL accounts. An error occurs if you try to create an account that already exists.

To use CREATE USER, you must have the global CREATE USER privilege, or the INSERT privilege for the mysql system database. When the read_only system variable is enabled, CREATE USER additionally requires the SUPER privilege.


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, “Passwords and Logging”. For similar information about client-side logging, see Section, “mysql Client Logging”.

For each account, CREATE USER creates a new row in the mysql.user system 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.

An account when first created has no privileges. To assign privileges, use the GRANT statement.

Each user value naming an account may be followed by an optional auth_option value that specifies 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.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 '%'.

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

If the account has no password, the password information in the account row in the mysql.user table remains empty, which is insecure. To set the password, use SET PASSWORD. See Section, “SET PASSWORD Statement”.

For implicit plugin assignment, the default plugin becomes the value of the plugin column in the account's mysql.user system table row. The default plugin is mysql_native_password unless the --default-authentication-plugin option is set otherwise at server startup.

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:

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

CREATE USER examples:

As mentioned previously, implicit plugin assignment depends on the default authentication plugin. 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('password');

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

For additional information about setting passwords and authentication plugins, see Section 6.2.9, “Assigning Account Passwords”, and Section 6.2.11, “Pluggable Authentication”.