Sign inTry Free

CREATE USER

On this page

The CREATE USER command creates a new user account.

Syntax

CREATE USER  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}]

Note

Instead of authentication by password, the Kerberos, PAM, and SAML authentication types can also be used with the IDENTIFIED WITH clause:

IDENTIFIED WITH {authentication_gss | authentication_pam | authentication_saml}

For example:

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.

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.

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.

ATTRIBUTE and COMMENT

Optional arguments used to provide additional information about a user. The values can be seen in the USERS 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 to specify privileges for the new user.

  • 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.

  • 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:

    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:

    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

Last modified:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In