# CREATE USER

The `CREATE USER` command creates a new user account.

## Syntax

```sql
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>]
```

> **📝 Note**: Instead of authentication by password, the [Kerberos](https://docs.singlestore.com/db/v9.1/security/authentication/kerberos-authentication/configuring-singlestore-for-kerberos-authentication.md), [PAM](https://docs.singlestore.com/db/v9.1/security/authentication/pam-authentication.md), and [SAML](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication.md) authentication types can also be used with the `IDENTIFIED WITH` clause:`IDENTIFIED WITH {authentication_gss | authentication_pam | authentication_saml}`For example:```sql
> 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.

## FAILED\_LOGIN\_ATTEMPTS and PASSWORD\_LOCK\_TIME

**FAILED\_LOGIN\_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. For example, a value of `3` means that the account will be locked after three failed attempts. Default is `0`, which means there is no restriction. When set to a value greater than or equal to `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.

> **📝 Note**: You must set both `FAILED_LOGIN_ATTEMPTS` and `PASSWORD_LOCK_TIME` to enable the lockout feature.

To modify the `FAILED_LOGIN_ATTEMPTS` and `PASSWORD_LOCK_TIME` for a user, or to unlock a locked user, use [ALTER USER](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/alter-user.md).

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, refer to [Securing SingleStore](https://docs.singlestore.com/db/v9.1/security.md).

## REQUIRE

`REQUIRE` supports the following options:

* `REQUIRE SSL`: Ensures that the user connects via SSL.
* `REQUIRE NONE`: Specifies that SSL is not required for the connection.
* `REQUIRE X509`: Enables mutual authentication between the client and SingleStore. The user can connect only if the client presents a valid TLS client certificate that is not expired and chains to the CA bundle configured on the server. Connections that do not provide a valid certificate are rejected. Example:
  ```sql
  CREATE USER 's2user'@'%' REQUIRE X509;
  ```
* `REQUIRE SUBJECT '<subject-dn-string>'`: In addition to extending `REQUIRE X509`, `REQUIRE SUBJECT` checks the subject in the client certificate provided during mTLS connection and enforces that the `Subject DN` in the client certificate must exactly match the configured value. This verifies both trust (via certificate chaining to the CA) and identity (via the `Subject DN` string). For example:
  ```sql
  CREATE USER 's2user'@'%' REQUIRE SUBJECT '/CN=s2user/O=example/C=US';
  ```
  When running `CREATE USER` or `ALTER USER`, specify the `SUBJECT` in [OpenSSL “oneline” format](https://docs.openssl.org/master/man1/openssl-namedisplay-options/). Separate fields, such as `CN`, `O`, `C`, `ST`, and `L`, with a `/`. To generate the `Subject DN` string in OpenSSL "oneline" format, run the following command:
  ```shell
  openssl x509 -in client-cert.pem -noout -subject -nameopt compat
  ```
  If the `openssl` output escapes characters inside a DN value, escape each backslash (`\`) in the output because the value is entered as a SQL string literal. For example, if the `Subject` contains `\+`, replace it with `\\+` in the `CREATE USER` or `ALTER USER` statement. For example:
  ```sql
  CREATE USER 's2user'@'%' REQUIRE SUBJECT '/O=example/CN=Js3g\\+AF\\+/C=US';
  ```
  A `Subject` mismatch can return the same generic certificate verification error as CA-chain issues. To troubleshoot, first verify that the client certificate chains to the CA bundle configured on the server (`REQUIRE X509`), and then verify that the `Subject DN` in the certificate exactly matches the configured `REQUIRE SUBJECT` value.

## ATTRIBUTE and COMMENT

Optional arguments used to provide additional information about a user. The values can be seen in the [USERS](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/resource-and-user-management/users.md) information schema view. 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/grant.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operations-that-take-either-a-database-or-a-cluster-level-lock/cluster-level-locks.md) for more information.
* Users configured with `REQUIRE X509` can log in is as follows:
  ```shell
  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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.
* Refer to [Configuring a Password Policy](https://docs.singlestore.com/db/v9.1/security/authentication/configuring-a-password-policy.md) 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
  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
  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
  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
  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
  CREATE USER singlestore@svchost-xxxx ATTRIBUTE '{"a": 1, "b": 2}' COMMENT 'Some information about the SingleStore user.';

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

  ```
  ```output

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

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

  ```
  ```output

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

***

Modified at: June 15, 2026

Source: [/db/v9.1/reference/sql-reference/security-management-commands/create-user/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/create-user/)

(An index of the documentation is available at /llms.txt)