PingKai Logo下载
搜索

CREATE USER

该语句用于创建一个新用户，并为其指定密码。在 MySQL 权限系统中，用户是由用户名和其连接来源主机的组合。因此，可以创建一个只能从 IP 地址 192.168.1.1 连接的用户 'newuser2'@'192.168.1.1'。同样，也可以让两个用户拥有相同的用户名部分，但由于他们从不同主机登录，因此拥有不同的权限。

语法

CreateUserStmt
CREATE USER IfNotExists UserSpecList RequireClauseOpt ConnectionOptions PasswordOption LockOption AttributeOption ResourceGroupNameOption
IfNotExists
IF NOT EXISTS
UserSpecList
UserSpec ,
UserSpec
Username AuthOption
AuthOption
IDENTIFIED BY AuthString PASSWORD HashString WITH StringName BY AuthString AS HashString
StringName
stringLit Identifier
ConnectionOptions
WITH MAX_USER_CONNECTIONS N
PasswordOption
PASSWORD EXPIRE DEFAULT NEVER INTERVAL N DAY PASSWORD HISTORY DEFAULT N PASSWORD REUSE INTERVAL DEFAULT N DAY PASSWORD REQUIRE CURRENT DEFAULT FAILED_LOGIN_ATTEMPTS N PASSWORD_LOCK_TIME N UNBOUNDED
LockOption
ACCOUNT LOCK ACCOUNT UNLOCK
AttributeOption
COMMENT CommentString ATTRIBUTE AttributeString
ResourceGroupNameOption
RESOURCE GROUP Identifier
RequireClauseOpt
REQUIRE NONE SSL X509 RequireListElement AND
RequireListElement
ISSUER Issuer SUBJECT Subject CIPHER Cipher SAN SAN TOKEN_ISSUER TokenIssuer

示例

创建一个密码为 newuserpassword 的用户。

mysql> CREATE USER 'newuser' IDENTIFIED BY 'newuserpassword';
Query OK, 1 row affected (0.04 sec)

创建一个只能登录到 192.168.1.1 的用户。

mysql> CREATE USER 'newuser2'@'192.168.1.1' IDENTIFIED BY 'newuserpassword';
Query OK, 1 row affected (0.02 sec)

创建一个强制使用 TLS 连接登录的用户。

CREATE USER 'newuser3'@'%' IDENTIFIED BY 'newuserpassword' REQUIRE SSL;
Query OK, 1 row affected (0.02 sec)

创建一个登录时必须使用 X.509 证书的用户。

CREATE USER 'newuser4'@'%' IDENTIFIED BY 'newuserpassword' REQUIRE ISSUER '/C=US/ST=California/L=San Francisco/O=PingCAP';
Query OK, 1 row affected (0.02 sec)

创建一个在创建时即被锁定的用户。

CREATE USER 'newuser5'@'%' ACCOUNT LOCK;
Query OK, 1 row affected (0.02 sec)

创建一个带有注释的用户。

CREATE USER 'newuser6'@'%' COMMENT 'This user is created only for test';
SELECT * FROM information_schema.user_attributes;
+-----------+------+---------------------------------------------------+
| USER      | HOST | ATTRIBUTE                                         |
+-----------+------+---------------------------------------------------+
| newuser6  | %    | {"comment": "This user is created only for test"} |
+-----------+------+---------------------------------------------------+
1 rows in set (0.00 sec)

创建一个带有 email 属性的用户。

CREATE USER 'newuser7'@'%' ATTRIBUTE '{"email": "user@pingcap.com"}';
SELECT * FROM information_schema.user_attributes;
+-----------+------+---------------------------------------------------+
| USER      | HOST | ATTRIBUTE                                         |
+-----------+------+---------------------------------------------------+
| newuser7  | %    | {"email": "user@pingcap.com"} |
+-----------+------+---------------------------------------------------+
1 rows in set (0.00 sec)

创建一个不允许重用最近 5 个密码的用户：

CREATE USER 'newuser8'@'%' PASSWORD HISTORY 5;
Query OK, 1 row affected (0.02 sec)

创建一个密码被手动过期的用户：

CREATE USER 'newuser9'@'%' PASSWORD EXPIRE;
Query OK, 1 row affected (0.02 sec)

创建一个最大连接数限制为 3 的用户：

CREATE USER 'newuser10'@'%' WITH MAX_USER_CONNECTIONS 3;
SELECT User, Host, max_user_connections FROM mysql.user WHERE User='newuser10';
+-----------+------+----------------------+
| user      | host | max_user_connections |
+-----------+------+----------------------+
| newuser10 | %    |                    3 |
+-----------+------+----------------------+
1 row in set (0.01 sec)

创建一个使用资源组 rg1 的用户：

CREATE USER 'newuser11'@'%' RESOURCE GROUP rg1;
SELECT USER, HOST, USER_ATTRIBUTES FROM MYSQL.USER WHERE USER='newuser11';
+----------+------+---------------------------+
| USER     | HOST | USER_ATTRIBUTES           |
+----------+------+---------------------------+
| newuser11| %    | {"resource_group": "rg1"} |
+----------+------+---------------------------+
1 rows in set (0.00 sec)

MySQL 兼容性

以下 CREATE USER 选项目前尚未被 TiDB 支持，会被解析但会被忽略：

  • PASSWORD REQUIRE CURRENT DEFAULT
  • WITH MAX_QUERIES_PER_HOUR
  • WITH MAX_UPDATES_PER_HOUR

以下 CREATE USER 选项同样不被 TiDB 支持，且解析器不会接受：

  • DEFAULT ROLE
  • PASSWORD REQUIRE CURRENT OPTIONAL

另请参阅