13.7.1.2 创建用户语句_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第 13 章 SQL 语句 / 13.7 数据库管理语句 / 13.7.1 账户管理报表 / 13.7.1.2 创建用户语句

13.7.1.2 创建用户语句

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section 6.2.4, “Specifying Account Names”)

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

tls_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语句创建新的 MySQL 帐户。它支持为新帐户建立身份验证、SSL/TLS、资源限制和密码管理属性，并控制帐户最初是锁定还是解锁。

要使用CREATE USER，您必须具有全局CREATE USER权限或系统数据库INSERT权限。mysql当 read_only启用系统变量时，CREATE USER额外需要SUPER权限。

如果您尝试创建一个已经存在的帐户，则会发生错误。如果IF NOT EXISTS给出该子句，则该语句会为每个已存在的命名帐户生成警告，而不是错误。

重要的

在某些情况下，CREATE USER可能会记录在服务器日志中或在客户端的历史文件中，例如 ~/.mysql_history，这意味着任何对该信息具有读取权限的人都可以读取明文密码。有关服务器日志发生这种情况的条件以及如何控制它的信息，请参阅第 6.1.2.3 节，“密码和日志记录”。有关客户端日志记录的类似信息，请参阅第 4.5.1.3 节，“mysql 客户端日志记录”。

该声明有几个方面CREATE USER，在以下主题下进行了描述：

创建用户概述

对于每个帐户，在系统表CREATE USER 中创建一个新行。mysql.user帐户行反映了报表中指定的属性。未指定的属性设置为其默认值：

Authentication：系统变量定义的认证插件 default_authentication_plugin ，凭证为空
SSL/TLS：NONE
资源限制：无限制
密码管理：PASSWORD EXPIRE DEFAULT
账户锁定：ACCOUNT UNLOCK

首次创建的帐户没有特权。要分配权限，请使用该GRANT 语句。

每个帐户名都使用第 6.2.4 节“指定帐户名”中描述的格式。例如：

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

帐户名的主机名部分（如果省略）默认为'%'.

每个user命名帐户的值后面都可以跟一个可选 auth_option值，指示帐户如何进行身份验证。这些值允许指定帐户身份验证插件和凭据（例如，密码）。每个 auth_option值仅适用于紧接其前的命名帐户。

按照user规范，该语句可能包括 SSL/TLS、资源限制、密码管理和锁定属性的选项。所有这些选项对报表都是全局的，适用于报表中指定的所有帐户。

示例：创建一个使用默认身份验证插件和给定密码的帐户。将密码标记为过期，以便用户必须在第一次连接到服务器时选择一个新密码：

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

sha256_password示例：创建一个使用身份验证插件和给定密码的帐户。要求每 180 天选择一个新密码：

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

示例：创建多个帐户，指定一些每个帐户的属性和一些全局属性：

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;

每个auth_option值（IDENTIFIED WITH ... BY在本例中）仅适用于紧接在其之前的命名帐户，因此每个帐户都使用紧随其后的身份验证插件和密码。

其余属性全局适用于声明中指定的所有帐户，因此对于这两个帐户：

必须使用有效的 X.509 证书进行连接。
每小时最多允许 60 个查询。
该帐户最初是锁定的，因此实际上它是一个占位符，在管理员解锁之前无法使用。

创建用户身份验证选项

帐户名后面可能跟有 auth_option指定帐户身份验证插件、凭据或两者的身份验证选项：

auth_plugin命名身份验证插件。插件名称可以是带引号的字符串文字或不带引号的名称。插件名称存储在系统表的plugin列中。mysql.user
对于未指定身份验证插件的语法，默认插件由系统变量auth_option的值指示。default_authentication_plugin有关每个插件的说明，请参阅第 6.4.1 节，“身份验证插件”。
凭据存储在 mysql.user系统表中。一个值指定帐户凭据，可以是明文（未加密）字符串，也可以是与帐户关联的身份验证插件所期望的格式的哈希值，分别为： 'auth_string'
- 对于使用的语法，字符串是明文并传递给身份验证插件以进行可能的哈希处理。插件返回的结果存储在表中。插件可以使用指定的值，在这种情况下不会发生散列。 BY 'auth_string'mysql.user
- 对于使用的语法，假定字符串已经采用身份验证插件所需的格式，并按原样存储在表中。如果插件需要散列值，则该值必须已经以适合插件的格式散列，否则插件无法使用该值，并且无法对客户端连接进行正确的身份验证。 AS 'auth_string'mysql.user
- 如果身份验证插件不对身份验证字符串执行散列，则和子句具有相同的效果：身份验证字符串按原样存储在系统表中。 BY 'auth_string'AS 'auth_string'mysql.user

CREATE USER允许这些 auth_option语法：

IDENTIFIED BY 'auth_string'
将帐户身份验证插件设置为默认插件，将明文值传递给插件以进行可能的哈希处理，并将结果存储在系统表的帐户行中。 'auth_string'mysql.user
IDENTIFIED WITH auth_plugin
将帐户认证插件设置为 auth_plugin，将凭据清除为空字符串，并将结果存储在mysql.user 系统表的帐户行中。
IDENTIFIED WITH auth_plugin BY 'auth_string'
将帐户身份验证插件设置为 auth_plugin，将明文值传递给插件以进行可能的哈希处理，并将结果存储在系统表的帐户行中。 'auth_string'mysql.user
IDENTIFIED WITH auth_plugin AS 'auth_string'
将帐户身份验证插件设置为 auth_plugin并将值按原样存储在帐户行中。如果插件需要哈希字符串，则假定该字符串已经按照插件所需的格式进行了哈希处理。 'auth_string'mysql.user
IDENTIFIED BY PASSWORD 'auth_string'
将帐户身份验证插件设置为默认插件并将值按原样存储在帐户行中。如果插件需要哈希字符串，则假定该字符串已经按照插件所需的格式进行了哈希处理。 'auth_string'mysql.user

笔记

IDENTIFIED BY PASSWORD语法已弃用；希望在未来的 MySQL 版本中将其删除。

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, see Section 6.2.10, “Assigning Account Passwords”, and Section 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, 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
表示该语句命名的所有帐户都没有 SSL 或 X.509 要求。如果用户名和密码有效，则允许未加密的连接。如果客户端具有适当的证书和密钥文件，则可以根据客户端的选择使用加密连接。
```
CREATE USER 'jeffrey'@'localhost' REQUIRE NONE;
```
默认情况下，客户端会尝试建立安全连接。对于具有REQUIRE NONE的客户端，如果无法建立安全连接，连接尝试将退回到未加密的连接。要要求加密连接，客户端只需要指定 --ssl-mode=REQUIRED 选项；如果无法建立安全连接，则连接尝试失败。
NONE如果未指定与 SSL 相关的 REQUIRE选项，则为默认值。
SSL
告诉服务器只允许语句指定的所有帐户的加密连接。
```
CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;
```
默认情况下，客户端会尝试建立安全连接。对于具有REQUIRE SSL的帐户，如果无法建立安全连接，连接尝试将失败。
X509
对于声明命名的所有帐户，要求客户出示有效证书，但确切的证书、发行人和主题无关紧要。唯一的要求是应该可以使用其中一个 CA 证书来验证其签名。使用 X.509 证书总是意味着加密，因此 SSL在这种情况下不需要该选项。
```
CREATE USER 'jeffrey'@'localhost' REQUIRE X509;
```
对于具有的帐户REQUIRE X509，客户端必须指定--ssl-key 和--ssl-cert选项才能连接。（建议但不要求 --ssl-ca也指定，以便可以验证服务器提供的公共证书。）这也是正确的ISSUER ，SUBJECT因为这些 REQUIRE选项暗示了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.
```
CREATE USER 'jeffrey'@'localhost'
  REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
    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.
```
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.
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.
```
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';

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.16, “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. (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
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:

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 several password_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, see Section 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, see Section 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, see Section 6.2.12, “Server Handling of Expired Passwords”.

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.
```
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.
```
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 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:
```
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
```

CREATE USER Account-Locking Options

MySQL 支持使用 ACCOUNT LOCK和ACCOUNT UNLOCK选项来锁定和解锁帐户，这些选项指定帐户的锁定状态。有关其他讨论，请参阅第 6.2.15 节，“帐户锁定”。

如果指定了多个帐户锁定选项，则最后一个优先。