Movatterモバイル変換

MySQL 5.7 Reference Manual

13.7.1.2 CREATE USER Statement

CREATE USER [IF NOT EXISTS]user [auth_option] [,user [auth_option]] ...    [REQUIRE {NONE |tls_option [[AND]tls_option] ...}]    [WITHresource_option [resource_option] ...]    [password_option |lock_option] ...user:    (see Section 6.2.4, “Specifying Account Names”)auth_option: {    IDENTIFIED BY 'auth_string'  | IDENTIFIED WITHauth_plugin  | IDENTIFIED WITHauth_plugin BY 'auth_string'  | IDENTIFIED WITHauth_plugin AS 'auth_string'  | IDENTIFIED BY PASSWORD 'auth_string'}tls_option: {   SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'}resource_option: {    MAX_QUERIES_PER_HOURcount  | MAX_UPDATES_PER_HOURcount  | MAX_CONNECTIONS_PER_HOURcount  | MAX_USER_CONNECTIONScount}password_option: {    PASSWORD EXPIRE  | PASSWORD EXPIRE DEFAULT  | PASSWORD EXPIRE NEVER  | PASSWORD EXPIRE INTERVALN DAY}lock_option: {    ACCOUNT LOCK  | ACCOUNT UNLOCK}

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

To useCREATE USER, you must have the globalCREATE USER privilege, or theINSERT privilege for themysql system database. When theread_only system variable is enabled,CREATE USER additionally requires theSUPER privilege.

An error occurs if you try to create an account that already exists. If theIF NOT EXISTS clause is given, the statement produces a warning for each named account 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, seeSection 6.1.2.3, “Passwords and Logging”. For similar information about client-side logging, seeSection 4.5.1.3, “mysql Client Logging”.

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

CREATE USER Overview

For each account,CREATE USER creates a new row in themysql.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 thedefault_authentication_plugin system variable, and empty credentials
SSL/TLS:NONE
Resource limits: Unlimited
Password management:PASSWORD EXPIRE DEFAULT
Account locking:ACCOUNT UNLOCK

An account when first created has no privileges. To assign privileges to this account, use one or moreGRANT statements.

Each account name uses the format described inSection 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'%'.

Eachuser value naming an account may be followed by an optionalauth_option value that indicates how the account authenticates. These values enable account authentication plugins and credentials (for example, a password) to be specified. Eachauth_option value appliesonly to the account named immediately preceding it.

Following theuser specifications, the statement may include options for SSL/TLS, resource-limit, password-management, and locking properties. All such options areglobal to the statement and apply toall 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:

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

Example: Create an account that uses thesha256_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;

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

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

Eachauth_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.
The account is locked initially, so effectively it is a placeholder and cannot be used until an administrator unlocks it.

CREATE USER Authentication Options

An account name may be followed by anauth_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 theplugin column of themysql.user system table.
Forauth_option syntax that does not specify an authentication plugin, the default plugin is indicated by the value of thedefault_authentication_plugin system variable. For descriptions of each plugin, seeSection 6.4.1, “Authentication Plugins”.
Credentials are stored in themysql.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 syntax that usesBY '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 themysql.user table. A plugin may use the value as specified, in which case no hashing occurs.
- For syntax that usesAS 'auth_string', the string is assumed to be already in the format the authentication plugin requires, and is stored as is in themysql.user table. If a plugin requires a hashed value, the value must be already hashed in a format appropriate for the plugin, or the value cannot be used by the plugin and correct authentication of client connections cannot occur.
- If an authentication plugin performs no hashing of the authentication string, theBY 'auth_string' andAS 'auth_string' clauses have the same effect: The authentication string is stored as is in themysql.user system table.

CREATE USER permits theseauth_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 themysql.user system table.
IDENTIFIED WITHauth_plugin
Sets the account authentication plugin toauth_plugin, clears the credentials to the empty string, and stores the result in the account row in themysql.user system table.
IDENTIFIED WITHauth_plugin BY 'auth_string'
Sets the account authentication plugin toauth_plugin, passes the cleartext'auth_string' value to the plugin for possible hashing, and stores the result in the account row in themysql.user system table.
IDENTIFIED WITHauth_plugin AS 'auth_string'
Sets the account authentication plugin toauth_plugin and stores the'auth_string' value as is in themysql.user account row. If the plugin requires a hashed string, the string is assumed to be already hashed in the format the plugin requires.
IDENTIFIED BY PASSWORD 'auth_string'
Sets the account authentication plugin to the default plugin and stores the'auth_string' value as is in themysql.user account row. If the plugin requires a hashed string, the string is assumed to be already hashed in the format the plugin requires.
Note
IDENTIFIED BY PASSWORD syntax is deprecated; expect it to be removed in a future MySQL release.

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

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

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

CREATE USER 'jeffrey'@'localhost'  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, seeSection 6.2.10, “Assigning Account Passwords”, andSection 6.2.13, “Pluggable Authentication”.

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, seeSection 6.3, “Using Encrypted Connections”.

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

Order ofREQUIRE options does not matter, but no option can be specified twice. TheAND keyword is optional betweenREQUIRE options.

CREATE USER permits thesetls_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.
```
CREATE USER 'jeffrey'@'localhost' REQUIRE NONE;
```
Clients attempt to establish a secure connection by default. For clients that haveREQUIRE 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-relatedREQUIRE options are specified.
SSL
Tells the server to permit only encrypted connections for all accounts named by the statement.
```
CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;
```
Clients attempt to establish a secure connection by default. For accounts that haveREQUIRE 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 theSSL option is unnecessary in this case.
```
CREATE USER 'jeffrey'@'localhost' REQUIRE X509;
```
For accounts withREQUIRE 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 forISSUER andSUBJECT as well because thoseREQUIRE options imply the requirements ofX509.
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 theSSL option is unnecessary in this case.
```
CREATE USER 'jeffrey'@'localhost'  REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/    O=MySQL/CN=CA/emailAddress=ca@example.com';
```
BecauseISSUER implies the requirements ofX509, 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 subjectsubject. 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 theSSL option is unnecessary in this case.
```
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.
BecauseSUBJECT implies the requirements ofX509, 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.
```
CREATE USER 'jeffrey'@'localhost'  REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
```

TheSUBJECT,ISSUER, andCIPHER options can be combined in theREQUIRE 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';

CREATE USER Resource-Limit Options

It is possible to place limits on use of server resources by an account, as discussed inSection 6.2.16, “Setting Account Resource Limits”. To do so, use aWITH clause that specifies one or moreresource_option values.

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

CREATE USER permits theseresource_option values:

MAX_QUERIES_PER_HOURcount,MAX_UPDATES_PER_HOURcount,MAX_CONNECTIONS_PER_HOURcount
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. (Queries for which results are served from the query cache do not count against theMAX_QUERIES_PER_HOUR limit.) Ifcount is0 (the default), this means that there is no limitation for the account.
MAX_USER_CONNECTIONScount
For all accounts named by the statement, restricts the maximum number of simultaneous connections to the server by each account. A nonzerocount specifies the limit for the account explicitly. Ifcount is0 (the default), the server determines the number of simultaneous connections for the account from the global value of themax_user_connections system variable. Ifmax_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;

CREATE USER Password-Management Options

Account passwords have an age, assessed from the date and time of the most recent password change.

CREATE USER supports severalpassword_option values for password expiration management, to either expire an account password manually 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 based on account password age. For a given account, its password age is assessed from the date and time of the most recent password change.

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

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

These options apply only to accounts that use an authentication plugin that stores credentials internally to MySQL. For accounts that use a plugin that performs authentication against a credentials system that is external to MySQL, password management must be handled externally against that system as well. For more information about internal credentials storage, seeSection 6.2.11, “Password Management”.

A client session operates in restricted mode if the account password was expired manually or if the password age is considered greater than its permitted 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. For information about restricted mode, seeSection 6.2.12, “Server Handling of Expired Passwords”.

CREATE USER permits thesepassword_option values for controlling password expiration:

PASSWORD EXPIRE
Immediately marks the password expired for all accounts named by the statement.
```
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 thedefault_password_lifetime system variable.
```
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.
```
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
```
PASSWORD EXPIRE INTERVALN DAY
This expiration option overrides the global policy for all accounts named by the statement. For each, it sets the password lifetime toN days. The following statement requires the password to be changed every 180 days:
```
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
```

CREATE USER Account-Locking Options

MySQL supports account locking and unlocking using theACCOUNT LOCK andACCOUNT UNLOCK options, which specify the locking state for an account. For additional discussion, seeSection 6.2.15, “Account Locking”.

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

PREV HOME UP NEXT