MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
CREATE USERuser
[auth_option
] [,user
[auth_option
]] ...user
: (see Section 6.2.4, “Specifying Account Names”)auth_option
: { IDENTIFIED BY [PASSWORD] 'auth_string
' | IDENTIFIED WITHauth_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 6.1.2.3, “Passwords and Logging”. For similar
information about client-side logging, see
Section 4.5.1.3, “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:
With IDENTIFIED BY
, the server assigns
the plugin implicitly. The
'
value is cleartext and is passed to the plugin for possible
hashing. The result returned by the plugin is stored as the
password. A plugin may use the value as specified, in which
case no hashing occurs. If the optional
auth_string
'PASSWORD
keyword is given, the behavior
is the same except that if the plugin requires a hashed
string, the string is assumed to be already hashed in the
format the plugin requires, and is stored as is as the
password.
With IDENTIFIED WITH
, the server assigns
the specified plugin and the account has no password. If the
optional AS
'
clause is
given, the string is stored as is as the password. If the
plugin requires a hashed string, the string is assumed to be
already hashed in the format the plugin requires.
auth_string
'
With neither IDENTIFIED BY
nor
IDENTIFIED WITH
, the server assigns the
plugin implicitly and the account has no password.
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 13.7.1.7, “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:
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
. The plugin
name can be a quoted string literal or an unquoted name.
auth_plugin
'
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
auth_string
'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('password
');
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; expect support for it to removed in a future
MySQL release.)
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 'password
';
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 '
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>password
';SET old_passwords = 0;
mysql>CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY '
Query OK, 0 rows affected (0.00 sec)password
';
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.
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('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”.