Syntax:
CREATE USER user_specification [, user_specification] ...

user_specification:
    user [ identified_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin AS 'hash_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 database. When the read_only system
variable is enabled, CREATE USER additionally requires the SUPER
privilege.

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.

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
http://dev.mysql.com/doc/refman/5.6/en/account-names.html. 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:

o With IDENTIFIED WITH, the server assigns the specified plugin and the
  account has no password. If the optional AS 'hash_string' clause is
  also given, the string is stored as is in the authentication_string
  column (it is assumed to be already hashed in the format required by
  the plugin).

o With IDENTIFIED BY, the server assigns the plugin implicitly and
  assigns the specified password.

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

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 [HELP SET PASSWORD].

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

o 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.

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

o The server authenticates client connection attempts using the named
  plugin.

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

o 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.

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

o To specify an authentication plugin for an account, use IDENTIFIED
  WITH auth_plugin. The plugin name 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 and it is stored in the
  authentication_string column as given. (This value is meaningful only
  for plugins that use that column.) 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. Clients must provide no password when they connect.
  However, an account with no password is insecure. To ensure that an
  account uses a specific authentication plugin and has a password with
  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, before using SET PASSWORD. (Use of mysql_old_password
  is not recommended. It is deprecated and support for it will be
  removed in a future MySQL release.)

o To specify a password for an account at account-creation time, use
  IDENTIFIED BY with the literal cleartext 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)

o To avoid specifying the cleartext 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 password when they connect.

o 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.

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('mypass');

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

For additional information about setting passwords and authentication
plugins, see
http://dev.mysql.com/doc/refman/5.6/en/assigning-passwords.html, and
http://dev.mysql.com/doc/refman/5.6/en/pluggable-authentication.html.

URL: http://dev.mysql.com/doc/refman/5.6/en/create-user.html

