13.7.1.1 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 (as of MySQL 5.5.7) assigns the account an authentication plugin. Depending on the syntax used, CREATE USER may also assign the account a password.

Each 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 each account as follows, depending on whether the user specification clause includes IDENTIFIED WITH to specify a plugin or IDENTIFIED BY to specify a password:

Note

IDENTIFIED WITH is available as of MySQL 5.5.7. Before 5.5.7, authentication plugins are not used, so only the remarks about IDENTIFIED BY apply.

If the account has no password, the Password column in the account's mysql.user table row remains empty, which is insecure. To set the password, use SET PASSWORD. See Section 13.7.1.6, “SET PASSWORD Syntax”.

If 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:

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

CREATE USER examples:

For additional information about setting passwords and authentication plugins, see Section 6.3.5, “Assigning Account Passwords”, and Section 6.3.6, “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”.