14.7.1.2 MySQL创建用户句法

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

14.7.1.2 CREATE USER Syntax

单击此处查看原文

以下是MySQL 5.7.6及以上版本的CREATE USER句法

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: '详情参见: Section 7.2.3, “Specifying Account Names”https://dev.mysql.cm/doc/refman/5.7/en/account-names.html'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' }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 }

以下是MySQL 5.7.6以前版本的CREATE USER句法

CREATE USER user [auth_option] [, user [auth_option]] ...user: '详情参见： Section 7.2.3, “Specifying Account Names”) https://dev.mysql.com/doc/refman/5.7/en/account-names.html'auth_option: { IDENTIFIED BY 'auth_string' | IDENTIFIED BY PASSWORD 'hash_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin AS 'hash_string' }

CREATE USER 语句将会创建一个新的MySQL账户。它允许账户认证属性被建立。在MySQL 5.7.6中，它也可能建立认证、SSL/TLS、资源限制、账户密码过期时间、账户锁定和解锁等属性。

一个账户在建立之初是没有权限的。

使用 CREATE USER 你必须有全局的 CREATE USER 权限，或者有对mysql数据库进行 INSERT 的权限。当设置了 read only 系统变量时， CREATE USER 还需要 SUPER 权限。

如果你尝试创建一个已经存在的账户将会触发异常。

在 MySQL 5.7.8 中，可以使用IF NOT EXISTS 语句。这样当尝试创建已有用户时，会抛出警告而非异常。

每个账户, CREATE USER 语句会在mysql.user表中创建一条新纪录。这条记录会reflects（反射？还是要翻译为反映？）在语句中指定的账户属性。没有被指定的账户属性将会得到一个默认值。

例1：使用默认的认证插件来创建一个账户并设置默认密码。如果设置密码已经过期，那么用户必须在首次连接mysql服务器的时候重新设置密码。

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

例2：使用 sha256_password 认证插件创建一个账户并设置默认密码。设置每180天必须更新密码。

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

要点

在某些情况下， CREATE USER 的使用可能会被记录在服务器日志中，或者记录在客户端的历史文件中，比如：~/.mysql_history。这意味着用户的明文密码会被任何有权阅读日志的人看到。关于服务器日志产生的条件和如何控制它，参见Section 7.1.2.3, “Passwords and Logging"。客户端的参见Section 5.5.1.3, “mysql Logging"。

其他设置密码和认证插件的相关信息，参见Section 7.3.5, “Assigning Account Passwords”, and Section 7.3.8, “Pluggable Authentication”.

在MySQL 5.7.6中创建用户

每个账户名的格式，参见Section 7.2.3, “Specifying Account Names”。

例如：

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

如果省略了账户名中的主机名（localhost）部分，会默认为 % 。

上述语句，账户名称('jeffrey'@'localhost')后面可以带上一个可选项 *auth_option * ，它指定了该账户将会如何被认证。这些values允许指定账户的认证插件和资格证明（密码）。每个 auth_option 值只会在用户命名账户之前生效。

在用户范式中，语句可能会包含可选的设置：SSL/TLS, 资源限制, 密码过期, 锁定属性。所有这些可选项在当前语句中都是全局的，可以应用到每个命名账户中。

例如：以下的语句将会创建两个账户，每个都使用默认的认证插件和默认密码。每个账户若要被允许，在连接时必须使用一个合法的X509证书，并且要高达 60次查询/每小时。在最初每个账户都是被锁定的，所以它们只是有效的占位符，在管理员解锁它们之前都无法使用。

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

如果省略可选项，它们将会使用如下的默认值：

Authentication：认证插件会被default_authentication_plugin系统变量定义，并且赋空密码。
SSL/TSL:NONE
Resource limits:Unlimited
Password expiration:PASSWORD EXPIRE DEFAULT
Account locking:ACCOUNT UNLOCK

Authentication Options

一个账户名之后可能会带上可选项 authentication option 来指定账户认证插件、密码，或者两个都有：

auth_plugin 命名了一个认证插件。这个插件名可以是一个引用字符串或者不带引号的标识符。插件名被存储在mysql.user表的plugin列中。
auth_string 或者 hash_string 指定了账户密码。无论是明文或者散列，认证插件都分别规定了一个期望的格式。密码将会被存储在mysql.user表的authentication_string 列中。

CREATE USER 语句允许这些 auth_option 句法：

IDENTIFIED BY 'auth_string'

设置了账户认证插件为默认，hashes了明文密码 'auth_string' ，并且存储在mysql.user表中。（那个hashes不会翻啊，是打乱吗？哈哈哈）
IDENTIFIED BY PASSWORD 'hash_string'

设置了账户认证拆件为默认，设置了哈希密码 'hash_string' ，并且存储在mysql.user表中，这里假定了hash字符串符合了插件的期望格式。

注意

此句法已经过时，将会在下个MySQL发行版中移除。

IDENTIFIED WITH auth_plugin

设置了账户认证插件为 auth_plugin ，并将密码清空，存储在mysql.user表中。
IDENTIFIED WITH auth_plugin BY 'auth_string'

设置了账户认证插件为 auth_plugin ，hashes了明文密码 auth_string ，存储在mysql.user表中。
IDENTIFIED WITH auth_plugin AS 'hash_string'

设置了账户认证插件为 auth_plugin ，设置了哈希密码 'hash_string' ，存储在mysql.user表中，这里假定了hash字符串符合了插件的期望格式。

除非default_authentication_plugin系统变量被设置，否则默认的插件就是mysql_native_password。更多详情，参见Section 7.5.1, “Authentication Plugins”。

例1：指定了明文密码，使用默认插件：

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

例2：指定了认证插件，以及明文密码：

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

SSL/TLS Options

MySQL可以检查X509证书特性，除了基于用户名和密码的通用证书。详情参见Section 7.4, “Using Secure Connections”。

为MySQL账户指定SSL/TSL-related option，要使用REQUIRE语句，它可以用来指定一个或者多个tls_option：

CREATE USER 允许如下 tls_option :

NONE

表示这个账号不带SSL或者X509证书的要求。如果用户名和密码合法，非加密连接就会被允许。不过如果在客户端设置中如果有适合的证书和密钥文件，加密连接也是可以被使用的。
SSL

通知服务端这个账户只允许加密连接。
CREATE USER 'jeffrey'@'localhost' REQUIRE SSL;

在MySQL 5.7.3中，一个客户端只有在指定了 --ssl之后才能获得加密连接，如果一个加密连接无法被建立，那本次的尝试连接就会失败。在MySQL 5.7.3之前的版本中，客户端必须指定 --ssl-ca、--ssl-key、--ssl-cert中的其中一个参数才行。如果既不指定 --ssl-cs也不指定 --ssl-capath，那客户端就不会验证服务器证书。

X509

需要客户端必须有合法的证书，但是确切的证书、发行人、主题都不重要。唯一的要求是它需要能够从CA证书中验证它的签名。使用X509证书总是默认加密，所以SSL选项就不重要了。
CREATE USER 'jeffrey'@'localhost' REQUIRE X509;

客户端必须指定--ssl-key、--ssl-cert选项来进行连接。（它推荐而非必须 --ssl-ca也要被指定，使得公共证书提供者在服务器可以被验证）。发行人和主题也是如此，因为REQUIRE选项隐含了X509的这些要求。

ISSUER 'issuer'

在尝试连接的时候会加以限制，用户必须提供一份由认证中心(CA) issurr 提供的合法X509证书。如果客户端提供了一份合法但是认证中心不一致的证书，服务器会拒绝连接。使用X509证书总是默认加密，所以SSL选项就不是必填项了。

因为ISSUER隐含了X509的要求，客户端必须指定--ssl-key、--ssl-cert选项来进行连接。（它推荐而非必须 --ssl-ca也要被指定，使得公共证书提供者在服务器可以被验证）。
SUBJECT 'subject'

在尝试连接的时候会加以限制，因此客户端必须提供一份包含 subject 主题的合法X509证书。如果客户端提供了一份合法但是不包含此主题的证书，服务器会拒绝连接。使用X509证书总是默认加密，所以SSL选项就不是必填项了。

因为SUBJECT隐含了X509的要求，客户端必须指定--ssl-key、--ssl-cert选项来进行连接。（它推荐而非必须 --ssl-ca也要被指定，使得公共证书提供者在服务器可以被验证）。
CREATE USER 'jeffrey'@'localhost' REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/ O=MySQL demo client certificate/ CN=client/emailAddress=client@example.com';

MySQL会对语句中主题的值跟证书进行简单的字符串比对，所以字母大小写和语句成分的顺序一定要与证书中给出的完全一致。

>**注意** >对于 *emailAddress* ，参见 *REQUIRE ISSUER* 的**注意**内容。

CIPHER 'cipher'

加密连接会请求一个指定的加密方式。这个选项是必须的，用于确保密码和密钥的长度以及强度。如果老算法使用的是短的加密密码，SSL本身可以比较弱。
CREATE USER 'jeffrey'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

SUBJECT 、ISSUER 、CIPHER 可以在 REQUIRE 语句中被组合起来：

SQL 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';

每个选项的顺序无所谓，但是都不可以被指定两次。AND 关键字是可选的。

Resource-Limit Options

一个账户在访问服务器资源时，可能被加以限制，详情参见：Section 7.3.4, “Setting Account Resource Limits”。写语句时，使用 WITH 语句来指定一个或者多个 resource_option 。

CREATE USER 允许下列的 resource_option ：

MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, MAX_CONNECTIONS_PER_HOUR count

这些选项限制了一个账户的每小时查询的数量、每小时更新数量，以及每小时对服务器的连接数量（如果是从查询缓存中出来的数据，则不受 MAX_QUERIES_PER_HOUR 的限制）。如果 count 为0（默认为0），意味着此账户的查询无限制。
MAX_USER_CONNECTIONS count

限制了一个账户对服务器的并发连接数。一个非零的 count 显式指定此限制，如果为0（默认为0），服务器会根据全局系统变量max_user_connections来决定并发量。如果max_user_connections也为0，那么此账户的并发连接量将无限制。

例如：

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

如果限制的是次数，那么最新的一个实例将具有优先权。

Password-Expiration Options

CREATE USER 提供 password_option 选项来管理密码过期，账户密码过期失效或者建立密码过期策略( policy )。 policy 选项不会使得密码过期，它只是决定了服务器何时自动过账户密码（详情参见Section 7.3.6, “Password Expiration Policy”）。

密码的生命周期由最近一次更新的日期时间进行评估。

CREATE USER 允许以下 password_option ：

PASSWORD EXPIRE

使得账户密码过期：
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
PASSWORD EXPIRE DEFAULT

使用全局系统变量max_user_connections来指定密码过期策略。
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
PASSWORD EXPIRE NEVER

禁止密码过期。
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
PASSWORD EXPIRE INTERVAL N DAY

每过N天密码过期。
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;

如果指定了多个过期策略，将使用最后一次指定的策略。

如果一个账户的密码手动过期或者由于超过了自动过期策略决定的密码生命周期，一个客户端session操作将会进入限制模式。在限制模式中，session中执行操作会得到一个异常的结果，知道建立一个新的账户密码，详情参见：Section 7.3.6, “Password Expiration Policy”。