CREATE USER

This statement creates a new user, specified with a password. In the MySQL privilege system, a user is the combination of a username and the host from which they are connecting from. Thus, it is possible to create a user 'newuser2'@'192.168.1.1' who is only able to connect from the IP address 192.168.1.1. It is also possible to have two users have the same user-portion, and different permissions as they login from different hosts.

Synopsis

CreateUserStmt
IfNotExists
UserSpecList
UserSpec
AuthOption
StringName
PasswordOption
LockOption
AttributeOption

CreateUserStmt ::=
    'CREATE' 'USER' IfNotExists UserSpecList RequireClauseOpt ConnectionOptions PasswordOption LockOption AttributeOption

IfNotExists ::=
    ('IF' 'NOT' 'EXISTS')?

UserSpecList ::=
    UserSpec ( ',' UserSpec )*

UserSpec ::=
    Username AuthOption

AuthOption ::=
    ( 'IDENTIFIED' ( 'BY' ( AuthString | 'PASSWORD' HashString ) | 'WITH' StringName ( 'BY' AuthString | 'AS' HashString )? ) )?

StringName ::=
    stringLit
|   Identifier

PasswordOption ::= ( 'PASSWORD' 'EXPIRE' ( 'DEFAULT' | 'NEVER' | 'INTERVAL' N 'DAY' )? | 'PASSWORD' 'HISTORY' ( 'DEFAULT' | N ) | 'PASSWORD' 'REUSE' 'INTERVAL' ( 'DEFAULT' | N 'DAY' ) | 'FAILED_LOGIN_ATTEMPTS' N | 'PASSWORD_LOCK_TIME' ( N | 'UNBOUNDED' ) )*

LockOption ::= ( 'ACCOUNT' 'LOCK' | 'ACCOUNT' 'UNLOCK' )?

AttributeOption ::= ( 'COMMENT' CommentString | 'ATTRIBUTE' AttributeString )?

Examples

Create a user with the newuserpassword password.

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

Create a user who can only log in to 192.168.1.1.

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

Create a user who is enforced to log in using TLS connection.

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

Create a user who is required to use X.509 certificate at login.

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 a user who is locked upon creation.

CREATE USER 'newuser5'@'%' ACCOUNT LOCK;

Query OK, 1 row affected (0.02 sec)

Create a user with a comment.

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)

Create a user with an email attribute.

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)

Create a user who is not allowed to reuse the last 5 passwords:

CREATE USER 'newuser8'@'%' PASSWORD HISTORY 5;

Query OK, 1 row affected (0.02 sec)

Create a user whose password is manually expired:

CREATE USER 'newuser9'@'%' PASSWORD EXPIRE;

Query OK, 1 row affected (0.02 sec)

MySQL compatibility

The following CREATE USER options are not yet supported by TiDB, and will be parsed but ignored:

TiDB does not support WITH MAX_QUERIES_PER_HOUR, WITH MAX_UPDATES_PER_HOUR, and WITH MAX_USER_CONNECTIONS options.
TiDB does not support the DEFAULT ROLE option.
TiDB does not support PASSWORD EXPIRE, PASSWORD HISTORY or other options related to password.

CREATE USER

Synopsis

Examples

MySQL compatibility

See also