Rechercher dans le manuel MySQL

13.7.1.3 CREATE USER Syntax

Contents
  1. CREATE USER [IF NOT EXISTS]
  2.     user [auth_option] [, user [auth_option]] ...
  3.     DEFAULT ROLE role [, role ] ...
  4.     [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
  5.     [WITH resource_option [resource_option] ...]
  6.     [password_option | lock_option] ...
  7.  
  8. user:
  9.     (see Section 6.2.4, “Specifying Account Names”)
  10.  
  11. auth_option: {
  12.     IDENTIFIED BY 'auth_string'
  13.   | IDENTIFIED WITH auth_plugin
  14.   | IDENTIFIED WITH auth_plugin BY 'auth_string'
  15.   | IDENTIFIED WITH auth_plugin AS 'auth_string'
  16. }
  17.  
  18. tls_option: {
  19.    SSL
  20.  | X509
  21.  | CIPHER 'cipher'
  22.  | ISSUER 'issuer'
  23.  | SUBJECT 'subject'
  24. }
  25.  
  26. resource_option: {
  27.     MAX_QUERIES_PER_HOUR count
  28.   | MAX_UPDATES_PER_HOUR count
  29.   | MAX_CONNECTIONS_PER_HOUR count
  30.   | MAX_USER_CONNECTIONS count
  31. }
  32.  
  33. password_option: {
  34.     PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  35.   | PASSWORD HISTORY {DEFAULT | N}
  36.   | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  37.   | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  38. }
  39.  
  40. lock_option: {
  41.     ACCOUNT LOCK
  42.   | ACCOUNT UNLOCK
  43. }

The CREATE USER statement creates new MySQL accounts. It enables authentication, role, SSL/TLS, resource-limit, and password-management properties to be established for new accounts. It also controls whether accounts are initially locked or unlocked.

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 CONNECTION_ADMIN or SUPER privilege.

CREATE USER either succeeds for all named users or rolls back and has no effect if any error occurs. By default, an error occurs if you try to create a user that already exists. If the IF NOT EXISTS clause is given, the statement produces a warning for each named user that already exists, rather than an error.

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

There are several aspects to the CREATE USER statement, described under the following topics:

CREATE USER Overview

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

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

  • Default role: NONE

  • SSL/TLS: NONE

  • Resource limits: Unlimited

  • Password management: PASSWORD EXPIRE DEFAULT PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

  • Account locking: ACCOUNT UNLOCK

An account when first created has no privileges and a default role of NONE. To assign privileges or roles, use the GRANT statement.

Each account name uses the format described in Section 6.2.4, “Specifying Account Names”. For example:

  1. CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

The host name part of the account name, if omitted, defaults to '%'.

Each user value naming an account may be followed by an optional auth_option value that indicates how the account authenticates. These values enable account authentication plugins and credentials (for example, a password) to be specified. Each auth_option value applies only to the account named immediately preceding it.

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

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

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

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

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

Example: Create multiple accounts, specifying some per-account properties and some global properties:

  1. CREATE USER
  2.   'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
  3.                                    BY 'new_password1',
  4.   'jeanne'@'localhost' IDENTIFIED WITH sha256_password
  5.                                   BY 'new_password2'
  6.   REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  7.   PASSWORD HISTORY 5
  8.   ACCOUNT LOCK;

Each auth_option value (IDENTIFIED WITH ... BY in this case) applies only to the account named immediately preceding it, so each account uses the immediately following authentication plugin and password.

The remaining properties apply globally to all accounts named in the statement, so for both accounts:

  • Connections must be made using a valid X.509 certificate.

  • Up to 60 queries per hour are permitted.

  • Password changes cannot reuse any of the five most recent passwords.

  • The account is locked initially, so effectively it is a placeholder and cannot be used until an administrator unlocks it.

Contents Haut

CREATE USER Authentication Options

An account name may be followed by an auth_option 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 system table.

    For auth_option syntaxes that do not specify an authentication plugin, the default plugin is indicated by the value of the default_authentication_plugin system variable. For descriptions of each plugin, see Section 6.4.1, “Authentication Plugins”.

  • Credentials are stored in the mysql.user system table. An 'auth_string' value specifies account credentials, either as a cleartext (unencrypted) string or hashed in the format expected by the authentication plugin associated with the account, respectively:

    • For syntaxes that use BY 'auth_string', the string is cleartext and is passed to the authentication plugin for possible hashing. The result returned by the plugin is stored in the mysql.user table. A plugin may use the value as specified, in which case no hashing occurs.

    • For syntaxes that use AS 'auth_string', the string is assumed to be already in the format the authentication plugin requires, and is stored as is in the mysql.user table. If a plugin requires a hashed value, the value must be hashed in a format appropriate for the plugin. Otherwise, the value is not usable by the plugin and correct authentication of client connections will not occur.

      As of MySQL 8.0.17, a hashed string can be either a string literal or a hexadecimal value. The latter corresponds to the type of value displayed by SHOW CREATE USER for password hashes containing unprintable characters when the print_identified_with_as_hex system variable is enabled.

    • If an authentication plugin does not perform hashing of the authentication string, the BY 'auth_string' and AS 'auth_string' clauses have the same effect: Storage of the authentication string as is in the mysql.user table.

CREATE USER permits these auth_option syntaxes:

  • IDENTIFIED BY 'auth_string'

    Sets the account authentication plugin to the default plugin, passes the cleartext 'auth_string' value to the plugin for possible hashing, and stores the result in the account row in the mysql.user system table.

  • 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 account row in the mysql.user system table.

  • IDENTIFIED WITH auth_plugin BY 'auth_string'

    Sets the account authentication plugin to auth_plugin, passes the cleartext 'auth_string' value to the plugin for possible hashing, and stores the result in the account row in the mysql.user system table.

  • IDENTIFIED WITH auth_plugin AS 'auth_string'

    Sets the account authentication plugin to auth_plugin and stores the 'auth_string' value as is in the mysql.user account row. If the plugin requires a hashed string, the string is assumed to be already hashed in the format the plugin requires.

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

  1. CREATE USER 'jeffrey'@'localhost'
  2.   IDENTIFIED BY 'password';

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

  1. CREATE USER 'jeffrey'@'localhost'
  2.   IDENTIFIED WITH mysql_native_password BY 'password';

In each case, the password value stored in the account row is the cleartext value 'password' after it has been hashed by the authentication plugin associated with the account.

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

Contents Haut

CREATE USER Role Options

The DEFAULT ROLE clause defines which roles become active when the user connects to the server and authenticates, or when the user executes the SET ROLE DEFAULT statement during a session.

Each role name uses the format described in Section 6.2.5, “Specifying Role Names”. For example:

  1. CREATE USER 'joe'@'10.0.0.1' DEFAULT ROLE administrator, developer;

The host name part of the role name, if omitted, defaults to '%'.

The DEFAULT ROLE clause permits a list of one or more comma-separated role names. These roles need not exist at the time CREATE USER is executed.

Contents Haut

CREATE USER SSL/TLS Options

MySQL can check X.509 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 6.3, “Using Encrypted Connections”.

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

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

CREATE USER permits these tls_option values:

  • NONE

    Indicates that all accounts named by the statement have no SSL or X.509 requirements. Unencrypted connections are permitted if the user name and password are valid. Encrypted connections can be used, at the client's option, if the client has the proper certificate and key files.

    1. CREATE USER 'jeffrey'@'localhost' REQUIRE NONE;

    Clients attempt to establish a secure connection by default. For clients that have REQUIRE NONE, the connection attempt falls back to an unencrypted connection if a secure connection cannot be established. To require an encrypted connection, a client need specify only the --ssl-mode=REQUIRED option; the connection attempt fails if a secure connection cannot be established.

    NONE is the default if no SSL-related REQUIRE options are specified.

  • SSL

    Tells the server to permit only encrypted connections for all accounts named by the statement.

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

    Clients attempt to establish a secure connection by default. For accounts that have REQUIRE SSL, the connection attempt fails if a secure connection cannot be established.

  • X509

    For all accounts named by the statement, requires that clients present 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 X.509 certificates always implies encryption, so the SSL option is unnecessary in this case.

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

    For accounts with REQUIRE X509, clients 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'

    For all accounts named by the statement, requires that clients present a valid X.509 certificate issued by CA 'issuer'. If a client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X.509 certificates always implies encryption, so the SSL option is unnecessary in this case.

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

    Because ISSUER implies the requirements of X509, clients 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.)

  • SUBJECT 'subject'

    For all accounts named by the statement, requires that clients present a valid X.509 certificate containing the subject subject. If a client presents a certificate that is valid but has a different subject, the server rejects the connection. Use of X.509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    1. CREATE USER 'jeffrey'@'localhost'
    2.   REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
    3.    O=MySQL demo client certificate/
    4.    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.

    Because SUBJECT implies the requirements of X509, clients 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.)

  • CIPHER 'cipher'

    For all accounts named by the statement, requires a specific cipher method for encrypting connections. This option is needed to ensure that ciphers and key lengths of sufficient strength are used. Encryption can be weak if old algorithms using short encryption keys are used.

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

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

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

Contents Haut

CREATE USER Resource-Limit Options

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

Order of WITH options does not matter, except that if a given resource limit is specified multiple times, the last instance takes precedence.

CREATE USER permits these resource_option values:

  • MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, MAX_CONNECTIONS_PER_HOUR count

    For all accounts named by the statement, these options restrict how many queries, updates, and connections to the server are permitted to each account during any given one-hour period. If count is 0 (the default), this means that there is no limitation for the account.

  • MAX_USER_CONNECTIONS count

    For all accounts named by the statement, restricts the maximum number of simultaneous connections to the server by each 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:

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

Contents Haut

CREATE USER Password-Management Options

CREATE USER supports several password_option values for password management:

  • Password expiration options: You can expire an account password manually and establish its password expiration policy. Policy options do not expire the password. Instead, they determine how the server applies automatic expiration to the account based on password age, which is assessed from the date and time of the most recent account password change.

  • Password reuse options: You can restrict password reuse based on number of password changes, time elapsed, or both.

  • Password verification-required options: You can indicate whether attempts to change an account password must specify the current password, as verification that the user attempting to make the change actually knows the current password.

This section describes the syntax for password-management options. For information about establishing policy for password management, see Section 6.2.15, “Password Management”.

If multiple password-management options of a given type (PASSWORD EXPIRE, PASSWORD HISTORY, PASSWORD REUSE INTERVAL, PASSWORD REQUIRE) are specified, the last one takes precedence.

Note

Password-management options apply only to accounts that store credentials internally in the mysql.user system table (mysql_native_password, sha256_password, or caching_sha2_password). For accounts that use plugins that perform authentication against an external credential system, password management must be handled externally against that system as well.

A client has an expired password if the account password was expired manually or the password age is considered greater than its permitted lifetime per the automatic expiration policy. In this case, the server either disconnects the client or restricts the operations permitted to it (see Section 6.2.16, “Server Handling of Expired Passwords”). Operations performed by a restricted client result in an error until the user establishes a new account password.

CREATE USER permits these password_option values for controlling password expiration:

  • PASSWORD EXPIRE

    Immediately marks the password expired for all accounts named by the statement.

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

  • PASSWORD EXPIRE DEFAULT

    Sets all accounts named by the statement so that the global expiration policy applies, as specified by the default_password_lifetime system variable.

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

  • PASSWORD EXPIRE NEVER

    This expiration option overrides the global policy for all accounts named by the statement. For each, it disables password expiration so that the password never expires.

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

  • PASSWORD EXPIRE INTERVAL N DAY

    This expiration option overrides the global policy for all accounts named by the statement. For each, it sets the password lifetime to N days. The following statement requires the password to be changed every 180 days:

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

CREATE USER permits these password_option values for controlling reuse of previous passwords based on required minimum number of password changes:

  • PASSWORD HISTORY DEFAULT

    Sets all accounts named by the statement so that the global policy about password history length applies, to prohibit reuse of passwords before the number of changes specified by the password_history system variable.

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY DEFAULT;

  • PASSWORD HISTORY N

    This history-length option overrides the global policy for all accounts named by the statement. For each, it sets the password history length to N passwords, to prohibit reusing any of the N most recently chosen passwords. The following statement prohibits reuse of any of the previous 6 passwords:

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;

CREATE USER permits these password_option values for controlling reuse of previous passwords based on time elapsed:

  • PASSWORD REUSE INTERVAL DEFAULT

    Sets all statements named by the account so that the global policy about time elapsed applies, to prohibit reuse of passwords newer than the number of days specified by the password_reuse_interval system variable.

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL DEFAULT;

  • PASSWORD REUSE INTERVAL N DAY

    This time-elapsed option overrides the global policy for all accounts named by the statement. For each, it sets the password reuse interval to N days, to prohibit reuse of passwords newer than that many days. The following statement prohibits password reuse for 360 days:

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 360 DAY;

CREATE USER permits these password_option values for controlling whether attempts to change an account password must specify the current password, as verification that the user attempting to make the change actually knows the current password:

  • PASSWORD REQUIRE CURRENT

    This verification option overrides the global policy for all accounts named by the statement. For each, it requires that password changes specify the current password.

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

  • PASSWORD REQUIRE CURRENT OPTIONAL

    This verification option overrides the global policy for all accounts named by the statement. For each, it does not require that password changes specify the current password. (The current password may but need not be given.)

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;

  • PASSWORD REQUIRE CURRENT DEFAULT

    Sets all statements named by the account so that the global policy about password verification applies, as specified by the password_require_current system variable.

    1. CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

Contents Haut

CREATE USER 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 6.2.19, “Account Locking”.

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

Contents Haut

CREATE USER Binary Logging

CREATE USER is written to the binary log if it succeeds, but not if it fails; in that case, rollback occurs and no changes are made. A statement written to the binary log includes all named users. If the IF NOT EXISTS clause is given, this includes even users that already exist and were not created.

The statement written to the binary log specifies an authentication plugin for each user, determined as follows:

  • The plugin named in the original statement, if one was specified.

  • Otherwise, the default authentication plugin. In particular, if a user u1 already exists and uses a nondefault authentication plugin, the statement written to the binary log for CREATE USER IF NOT EXISTS u1 names the default authentication plugin. (If the statement written to the binary log must specify a nondefault authentication plugin for a user, include it in the original statement.)

If the server adds the default authentication plugin for any nonexisting users in the statement written to the binary log, it writes a warning to the error log naming those users.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-create-user.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut