Syntax

CREATE USER [IF NOT EXISTS] user[@host]
  [IDENTIFIED BY 'password']
  [WITH {DEFAULT RESOURCE POOL = poolname} |
        {FAILED_LOGIN_ATTEMPTS = integer PASSWORD_LOCK_TIME = integer} |
        {DEFAULT RESOURCE POOL = poolname FAILED_LOGIN_ATTEMPTS = integer PASSWORD_LOCK_TIME = integer}]
  [REQUIRE {SSL | X509 | SUBJECT | NONE}]
  [ATTRIBUTE <valid json>]
  [COMMENT <comment string>]

Arguments

user: The name of the user.

• Non-JWT users: Maximum length of 32 characters.

• JWT users: Maximum length of 320 characters.

host: The host that the user can connect from. For example, specifying localhost means the user account can be used only when connecting from the local host. If no host is explicitly specified, the % wildcard will be used, which allows the user to connect from any host.

password: An optional database connection password.

poolname: The default resource pool for the user.

FAILED_LOGON_ATTEMPTS: Together with PASSWORD_LOCK_TIME, specifies the failed login attempt lockout behavior. FAILED_LOGIN_ATTEMPTS is the number of failed attempts allowed after which the account is locked out. A value of 3 would mean that the account would be locked after three failed attempts. Default is 0 which means there is no restriction. When set to a value >=1, PASSWORD_LOCK_TIME must also be specified.

PASSWORD_LOCK_TIME: Together with FAILED_LOGIN_ATTEMPTS, specifies the failed login attempt lockout behavior. PASSWORD_LOCK_TIME is the number of seconds a locked out account must wait before reattempting to log in.

REQUIRE: has the following options:

• SSL option ensures that the user connects via SSL.

• NONE specifies that SSL will not be required for the connection.

• X509 enables mutual authentication of both client (mySQL CLI) and server (memsqld).

• SUBJECT checks the subject in the client certificate provided during mTLS connection. The subject specified here should exactly match the subject in the client certificate and should be specified in OpenSSL oneline format (“/” between every field. Specifying REQUIRE SUBJECT automatically means that the user is configured with REQUIRE X509 and has to provide a client certificate when connecting. For example:

CREATE USER 'bob' REQUIRE SUBJECT '/CN=bob/O=example/C=US';

To modify the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME settings for a user, or to unlock a locked user, use ALTER USER.

If an account is locked either manually using the ALTER USER … ACCOUNT LOCK command or automatically as a result of exceeding the values specified for FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME arguments then restarting the cluster removes the lock and returns the account’s status to ONLINE. The account lock needs to be explicitly re-applied after the cluster is back online and/or automated as part of the customer’s operational procedures. Users can confirm the lock state before and after a restart by viewing the ACCOUNT_STATUS column in the information_schema.USERS view.

For more information about failed login attempt lockout behavior, see Securing SingleStore.

ATTRIBUTE and COMMENT: Optional arguments used to provide additional information about a user. The values can be seen in the USERS table. When both arguments are specified in the same statement, ATTRIBUTE must come first. For ATTRIBUTE, the value must be a valid JSON object.

Remarks

• The CREATE USER command must be run by a user with administrative privileges on the database. Also, the new user does not have any privileges granted by default. Use GRANT to specify privileges for the new user.

• Other CREATE USER command options aren’t applied in case the user exists and IF NOT EXISTS is specified, one has to use the GRANT command to assign privileges or the ALTER USER command to change user properties.

• The WITH keyword must be used with the following options and should be specified only once:

  • [DEFAULT RESOURCE POOL = <poolname>]

  • [FAILED_LOGIN_ATTEMPTS = integer PASSWORD_LOCK_TIME = <integer>]

  Copy

  WITH DEFAULT RESOURCE POOL = pool1 FAILED_LOGIN_ATTEMPTS = 10 PASSWORD_LOCK_TIME = 20

  or

  Copy

  WITH FAILED_LOGIN_ATTEMPTS = 10 PASSWORD_LOCK_TIME = 20

  or

  Copy

  WITH DEFAULT RESOURCE POOL = pool1

• This command causes implicit commits. Refer to COMMIT for more information.

• The CREATE USER command only applies to the current node unless the sync_permissions engine variable is set to ON.

• The CREATE USER command holds a global lock on the cluster until the processing of the command is completed. See Cluster Level Locks for more info.

• For users with REQUIRE X509, login is done as follows:

  Copy

  memsql -u user \
  	--ssl-ca=/etc/mysql/certs/ca.pem \
  	--tls-version=TLSv1.2 \
  	--ssl-cert=/etc/mysql/certs/client-cert.pem \
      --ssl-key=/etc/mysql/certs/client-key.pem

  All three namely, --ssl-cert, --ssl-key and --ssl-ca must be specified with REQUIRE X509 else the login results in an error. The client’s certificate provided as above will be verified by the server during SSL authentication

• Refer to the Permission Matrix for the required permissions.

Examples

The following example demonstrates how to add a new user and grant it the SELECT privilege on all tables in the mydb database:

CREATE USER joe;
GRANT SELECT ON mydb.* TO 'joe'@'%';

The following example demonstrates how to create a resource pool and assign it to a new user along with controlling the login attempts"

CREATE RESOURCE POOL test_pool WITH QUERY_TIMEOUT = 20;

CREATE USER user1 IDENTIFIED BY 'User123' WITH DEFAULT RESOURCE POOL = test_pool FAILED_LOGIN_ATTEMPTS = 3 PASSWORD_LOCK_TIME = 14400;

The following example demonstrates the use of the default_require_ssl variable and the REQUIRE option:

CREATE USER `alice`@`%`;              -- uses default_require_ssl var (off by default)

CREATE USER `alice`@`%` REQUIRE NONE; -- does not require SSL

CREATE USER `alice`@`%` REQUIRE SSL;  -- requires SSL

The following examples demonstrate the use of ATTRIBUTE and COMMENT:

CREATE USER 'user1'@'localhost' ATTRIBUTE '{"phone": "1234567890"}';

CREATE USER 'user1'@'localhost' COMMENT 'Some information about user1';

Retrieving ATTRIBUTE and COMMENT values from the INFORMATION_SCHEMA.USERS view:

CREATE USER singlestore@localhost ATTRIBUTE '{"a": 1, "b": 2}' COMMENT 'some information about user singlestore';

Query OK, 0 rows affected (0.026 sec)

SELECT USER, ATTRIBUTE, COMMENT from INFORMATION_SCHEMA.USERS WHERE user='singlestore';

+-------------+---------------+-----------------------------------------+
| USER        | ATTRIBUTE     | COMMENT                                 |
+-------------+---------------+-----------------------------------------+
| singlestore | {"a":1,"b":2} | some information about user singlestore |
+-------------+---------------+-----------------------------------------+
1 row in set (0.127 sec)

SELECT USER, ATTRIBUTE, COMMENT, ATTRIBUTE::a + ATTRIBUTE::b AS c FROM INFORMATION_SCHEMA.USERS WHERE user='singlestore';

+-------------+---------------+-----------------------------------------+------+
| USER        | ATTRIBUTE     | COMMENT                                 | c    |
+-------------+---------------+-----------------------------------------+------+
| singlestore | {"a":1,"b":2} | some information about user singlestore |    3 |
+-------------+---------------+-----------------------------------------+------+
1 row in set (0.114 sec)

The following examples demonstrate assigning REQUIRE X509 to a user:

CREATE USER 'user'@'localhost' REQUIRE X509;

-- (or)

CREATE USER 'user'@'localhost';
ALTER USER 'user'@localhost' REQUIRE X509;

-- (or)

CREATE USER 'user'@'localhost';
GRANT USAGE on *.* to 'user'@'localhost' REQUIRE X509;

-- (or)

CREATE USER 'user'@'localhost';
CREATE ROLE 'role';
GRANT USAGE on *.* to ROLE 'role' REQUIRE X509;
CREATE GROUP 'group';
GRANT ROLE 'role' to 'group';GRANT GROUP 'group' to 'user'@'localhost';