CREATE USER

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 | NONE}]
  [ATTRIBUTE <valid json>]
  [COMMENT <comment string>]

CREATE USER user IDENTIFIED WITH authentication_gss AS 'kerberos-spn';

CREATE USER user IDENTIFIED WITH authentication_pam AS 'auth-string';

Arguments

• user: Username of the SingleStore database 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 is used, which allows the user to connect from any host.

• password: An optional database connection password for the user.

• poolname: The default resource pool for the user.

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; you must 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 specified only once:

  • DEFAULT RESOURCE POOL = <poolname>

  • FAILED_LOGIN_ATTEMPTS = integer PASSWORD_LOCK_TIME = <integer>

  For example:

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

  • WITH FAILED_LOGIN_ATTEMPTS = 10 PASSWORD_LOCK_TIME = 20

  • 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. Refer to Cluster Level Locks for more information.

• Users configured with REQUIRE X509 can log in is as follows:

  Shell

  Copy

  singlestore -u <user> \
      --ssl-ca=/path/to/ca.pem \
      --tls-version=TLSv1.2 \
      --ssl-cert=/path/to/client-cert.pem \
      --ssl-key=/path/to/client-key.pem

  The --ssl-cert, --ssl-key and --ssl-ca options must be specified for a user configured with REQUIRE X509, otherwise the login attempt results in an error. The client’s certificate is verified by the server during SSL authentication

• Refer to the Permissions Matrix for the required permissions.

• Refer to Configuring a Password Policy for information on how to configure a robust password policy.

Examples

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

  SQL

  Copy

  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 in addition to controlling the login attempts:

  SQL

  Copy

  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:

  SQL

  Copy

  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:

  SQL

  Copy

  CREATE USER 'user1'@'svchost-xxxx' ATTRIBUTE '{"phone": "1234567890"}';
  
  CREATE USER 'user1'@'svchost-xxxx' COMMENT 'Some information about user1';

• The following example demonstrates how to retrieve the ATTRIBUTE and COMMENT values from the information_schema.USERS view:

  SQL

  Copy

  CREATE USER singlestore@svchost-xxxx ATTRIBUTE '{"a": 1, "b": 2}' COMMENT 'Some information about the SingleStore user.';

  SQL

  Copy

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

  +-------------+---------------+----------------------------------------------+
  | USER        | ATTRIBUTE     | COMMENT                                      |
  +-------------+---------------+----------------------------------------------+
  | singlestore | {"a":1,"b":2} | Some information about the SingleStore user. |
  +-------------+---------------+----------------------------------------------+

  SQL

  Copy

  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 the SingleStore user. |    3 |
  +-------------+---------------+----------------------------------------------+------+

• The following examples demonstrate assigning REQUIRE X509 to a user:

  SQL

  Copy

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