Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 33.8Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 198.3Kb
Man Pages (Zip) - 302.2Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

14.7.1.2 CREATE USER Syntax

CREATE USER syntax for MySQL 5.7.6 and higher:

CREATE USER [IF NOT EXISTS]
    user_specification [, user_specification] ...
    [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user_specification:
    user [ auth_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tsl_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count 
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

CREATE USER syntax before MySQL 5.7.6:

CREATE USER user_specification [, user_specification] ...

user_specification:
    user [ auth_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. It enables account authentication properties to be established. As of MySQL 5.7.6, it is also possible to establish authentication, SSL/TLS, and resource-limit properties, account password expiration, and account locking and unlocking.

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.

An error occurs if you try to create an account that already exists.

As of MySQL 5.7.8, the IF NOT EXISTS clause can be used, which causes the statement to produce a warning for each named account that already exists, rather than an error.

For each account, CREATE USER creates a new row in the mysql.user table. The row reflects the properties specified in the statement. Unspecified properties are set to their default values.

Example 1: Create an account that uses the default authentication plugin and the given password. Mark the password expired so that the user must choose a new one at the first connection to the server:

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

Example 2: Create an account that uses the sha256_password authentication plugin and the given password. Require that a new password be chosen every 180 days:

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED WITH sha256_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY;

Because the capabilities of this statement were expanded considerably in MySQL 5.7.6, this section first describes current syntax, and then the more limited pre-5.7.6 syntax.

Important

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

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

CREATE USER as of MySQL 5.7.6

Each account name uses the format described in Section 7.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.

Each user_specification clause consists of an account name and an optional auth_option value that specifies how the account authenticates. These values enable account authentication plugins and credentials (passwords) to be specified. Each auth_option value applies only to the user named immediately preceding it.

Following the user specifications, the statement may include options for SSL/TLS, resource-limit, password-expiration, and locking properties. All these options are global to the statement and apply to all named users.

Example: This statement creates two accounts, each with the default authentication plugin and named password. For both accounts, connections must be made using a valid X509 certificate and up to 60 queries per hour are permitted. Both accounts are locked initially, so effectively they are placeholders and cannot be used until an administrator unlocks them:

CREATE USER
  'jeffrey'@'localhost' IDENTIFIED BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  ACCOUNT LOCK;

For omitted options, these default values are used:

  • Authentication: The authentication plugin defined by the default_authentication_plugin system variable, and empty credentials

  • SSL/TLS: NONE

  • Resource limits: Unlimited

  • Password expiration: PASSWORD EXPIRE DEFAULT

  • Account locking: ACCOUNT UNLOCK

Authentication Options

An account name may be followed by an authentication option that specifies the account authentication plugin, credentials, or both:

  • auth_plugin names an authentication plugin. The plugin name can be a quoted string literal or an unquoted name. Plugin names are stored in the plugin column of the mysql.user table.

  • 'auth_string' or 'hash_string' specifiy account credentials, either as cleartext or hashed in the format expected by the authentication plugin, respectively. Credentials are stored in the authentication_string column of the mysql.user table.

CREATE USER permits these auth_option syntaxes:

  • IDENTIFIED BY 'auth_string'

    Sets the account authentication plugin to the default plugin, hashes the cleartext 'auth_string' value, and stores the result in the mysql.user account row.

  • IDENTIFIED BY PASSWORD 'hash_string'

    Sets the account authentication plugin to the default plugin, takes the hashed 'hash_string' value as is, and stores the result in the mysql.user account row. The string is assumed to be already hashed in the format required by the plugin.

    Note

    This syntax is deprecated and will be removed in a future MySQL release.

  • IDENTIFIED WITH auth_plugin

    Sets the account authentication plugin to auth_plugin, clears the credentials to the empty string, and stores the result in the mysql.user account row.

  • IDENTIFIED WITH auth_plugin BY 'auth_string'

    Sets the account authentication plugin to auth_plugin, hashes the cleartext 'auth_string' value, and stores the result in the mysql.user account row.

  • IDENTIFIED WITH auth_plugin AS 'hash_string'

    Sets the account authentication plugin to auth_plugin, takes the 'hash_string' value as is, and stores the result in the mysql.user account row. The string is assumed to be already hashed in the format required by the plugin.

The default plugin is mysql_native_password unless the default_authentication_plugin system variable is set otherwise. For descriptions of each plugin, see Section 7.4.1, “Authentication Plugins”.

Example 1: Specify the password as cleartext; the default plugin is used:

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

Example 2: Specify the authentication plugin, along with a cleartext password value:

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

SSL/TLS Options

MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the user name and credentials. For background information on the use of SSL/TLS with MySQL, see Section 7.3.11, “Using Secure Connections”.

To specify SSL/TLS-related options for a MySQL account, use a REQUIRE clause that specifies one or more tsl_option values:

CREATE USER permits these tsl_option values:

  • NONE

    Indicates that the account has no SSL or X509 requirements. Unencrypted connections are permitted if the user name and password are valid. However, encrypted connections can also be used, at the client's option, if the client has the proper certificate and key files.

    As of MySQL 5.7.3, a client need specify only the --ssl option to obtain an encrypted connection. The connection attempt fails if an encrypted connection cannot be established. Before MySQL 5.7.3, the client must specify either the --ssl-ca option, or all three of the --ssl-ca, --ssl-key, and --ssl-cert options.

  • SSL

    Tells the server to permit only encrypted connections for the account.

    CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;
    

    As of MySQL 5.7.3, a client need specify only the --ssl option to obtain an encrypted connection. The connection attempt fails if an encrypted connection cannot be established. Before MySQL 5.7.3, the client must specify the --ssl-ca option to authenticate the server certificate, and may additionally specify the --ssl-key and --ssl-cert options. If neither the --ssl-ca option nor --ssl-capath option is specified, the client does not authenticate the server certificate.

  • X509

    Requires that the client must have a valid certificate but the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    CREATE USER 'jeffrey'@'localhost' REQUIRE X509;
    

    The client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.) This is true for ISSUER and SUBJECT as well because those REQUIRE options imply the requirements of X509.

  • ISSUER 'issuer'

    Places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA 'issuer'. If the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    Because ISSUER implies the requirements of X509, the client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.)

    CREATE USER 'jeffrey'@'localhost'
      REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
        O=MySQL/CN=CA/emailAddress=ca@example.com';
    
  • SUBJECT 'subject'

    Places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject subject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    Because SUBJECT implies the requirements of X509, the client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.)

    CREATE USER 'jeffrey'@'localhost'
      REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
        O=MySQL demo client certificate/
        CN=client/emailAddress=client@example.com';
    

    MySQL does a simple string comparison of the 'subject' value to the value in the certificate, so lettercase and component ordering must be given exactly as present in the certificate.

    Note

    Regarding emailAddress, see the note in the description of REQUIRE ISSUER.

  • CIPHER 'cipher'

    Requests a specific cipher method for encrypting connections. This option is needed to ensure that ciphers and key lengths of sufficient strength are used. SSL itself can be weak if old algorithms using short encryption keys are used.

    CREATE USER 'jeffrey'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
    

The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause:

CREATE USER 'jeffrey'@'localhost'
  REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL demo client certificate/
    CN=client/emailAddress=client@example.com'
  AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL/CN=CA/emailAddress=ca@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The order of the options does not matter, but no option can be specified twice. The AND keyword is optional between REQUIRE options.

Resource-Limit Options

It is possible to place limits on use of server resources by an account, as discussed in Section 7.3.4, “Setting Account Resource Limits”. To do so, use a WITH clause that specifies one or more resource_option values.

CREATE USER permits these resource_option values:

  • MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, MAX_CONNECTIONS_PER_HOUR count

    These options restrict the number of queries, updates, and connections to the server permitted to this account during any given one-hour period. (Queries for which results are served from the query cache do not count against the MAX_QUERIES_PER_HOUR limit.) If count is 0 (the default), this means that there is no limitation for the account.

  • MAX_USER_CONNECTIONS count

    Restricts the maximum number of simultaneous connections to the server by the account. A nonzero count specifies the limit for the account explicitly. If count is 0 (the default), the server determines the number of simultaneous connections for the account from the global value of the max_user_connections system variable. If max_user_connections is also zero, there is no limit for the account.

Example:

CREATE USER 'jeffrey'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

If a given resource limit is specified multiple times, the last instance takes precedence.

Password-Expiration Options

CREATE USER supports several password_option values for password expiration management, to either expire an account password or establish its password expiration policy. Policy options do not expire the password; instead, they determine how the server applies automatic expiration to the account (see Section 7.3.6, “Password Expiration Policy”).

CREATE USER permits these password_option values:

  • PASSWORD EXPIRE

    Expires the account password.

    CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
    
  • PASSWORD EXPIRE DEFAULT

    Sets the account so that the global expiration policy applies, as specified by the default_password_lifetime system variable.

    CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
    
  • PASSWORD EXPIRE NEVER

    Disables password expiration for the account so that its password never expires.

    CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
    
  • PASSWORD EXPIRE INTERVAL N DAY

    Sets the account password lifetime to N days. This statement requires the password to be changed every 180 days:

    CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
    

If multiple password-expiration options are specified, the last one takes precedence.

A client session operates in restricted mode if the account password was expired manually or if the password is considered past its lifetime per the automatic expiration policy. In restricted mode, operations performed within the session result in an error until the user establishes a new account password. See Section 7.3.6, “Password Expiration Policy”.

Account-Locking Options

MySQL supports account locking and unlocking using the ACCOUNT LOCK and ACCOUNT UNLOCK options, which specify the locking state for an account. For additional discussion, see Section 7.3.10, “User Account Locking”.

If multiple account-locking options are specified, the last one takes precedence.

CREATE USER Before MySQL 5.7.6

For each account, CREATE USER creates a new row in the mysql.user table with no privileges and assigns the account an authentication plugin and credentials (such as a password). If the statement specifies no credentials, the empty string is assigned.

Each user_specification clause consists of an account name and information about how authentication occurs for clients that use the account.

Each account name uses the format described in Section 7.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:

  • 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).

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

  • 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 credentials in the account's mysql.user table row remain empty, which is insecure. To set the password, use SET PASSWORD. See Section 14.7.1.7, “SET PASSWORD Syntax”.

For implicit plugin assignment, the default 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 system variable is set otherwise.

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. (Use of mysql_old_password is not recommended. It is deprecated and support for it is removed in MySQL 5.7.5.)

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

  • As of MySQL 5.7.2, the server disables any account with an empty plugin until the DBA assigns a nonempty one. Before MySQL 5.7.2, 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 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 is removed in MySQL 5.7.5.)

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

However, the preceding procedure is not recommended because mysql_old_password is deprecated and support for it is removed in MySQL 5.7.5.


User Comments
  Posted by Andrew Bikadorov on March 19, 2015
-- Create Admin user allow access from anywhere, remove WITH GRANT OPTION and change ALL PRIVILEGES to SELECT,INSERT,UPDATE,DELETE or/and other if not admin user is needed
GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'%' IDENTIFIED BY 'UnencriptedPa55w0RdHeRe' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Sign Up Login You must be logged in to post a comment.