Sign inTry Free

Manage Database Users

On this page

Add a Database User

Database users are created automatically for all SingleStore Helios users who are granted access to a workspace group. These users can access workspaces in the Portal or the SQL Editor and can also be used for third-party SQL clients or development tools.

Refer Authenticate via Browser-based SSO using JWTs for more information about authentication from third-party clients.

Database users can also be added by using the CREATE USER command. The GRANT command cannot be used to add a new user since auto user creation by using the GRANT command is deprecated and the NO_AUTO_CREATE_USER variable is enabled by default.

If you have invited another SingleStore Helios user to join your organization, refrain from adding a database user with the same SingleStore Helios email address (i. e. the username) until after the invitation has been accepted.

As the admin user is not always a workspace log-in option for all organization members, SingleStore recommends adding a separate database user for each organization member.

Change a Database User Password

The database admin password is configured when the workspace is first created. This is also referred to as the “Master Username” in the Cloud Portal, which is admin by default.

To change this password, navigate to the Deployments in the left navigation, and then select Access > User Access. Select the Reset button next to the Password field and follow the provided instructions.

Note: You must have SUPER privileges to change another user’s password using the GRANT command.

Use the SET PASSWORD command to change a database user's password. A database user can also use this command to change their own password.

SET PASSWORD FOR 'username'@'host' = PASSWORD('password');

Remove a Database User

To remove a database user, use the DROP USER command.

DROP USER '<user>'@'<host>'

Inspect Database User Permissions

You can view grants and permissions by querying information_schema.user_privileges.

You can also view grants for a user by running SHOW GRANTS:

SHOW GRANTS FOR user@domain;

Set a Login Attempt Lockout Policy for a Database User

You can specify the number of times a user can enter an incorrect password before they are locked out of the system. When a user reaches this limit, their account is locked for the specified number of seconds.

This feature can be enabled per user or per role, in which case every user belonging to that role will be subject to failed login attempt lockout.

Enable the Lockout Policy

To enable the lockout policy:

Set bothFAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME for the user or role. FAILED_LOGIN_ATTEMPTS is the number of failed attempts before the account is locked, for example: 4. 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 feature.

Enable the lockout feature at 4 failed attempts, with a lockout time of 4 hours (14400 seconds) when creating a user:

CREATE USER user1 WITH FAILED_LOGIN_ATTEMPTS = 4 PASSWORD_LOCK_TIME = 14400;

Enabling the feature for a role:

CREATE ROLE general WITH FAILED_LOGIN_ATTEMPTS = 4 PASSWORD_LOCK_TIME = 14400;

If a user is associated with more than one role with different password lock times, the larger PASSWORD_LOCK_TIME value is applied.

If a user and a role the user is tied to have conflicting FAILED_LOGIN_ATTEMPTS settings, the lower value is applied.

Update Lockout Settings

If the PASSWORD_LOCK_TIME value is updated for a role or user, the new setting applies to currently locked accounts. For example, if a locked out user’s lockout time setting is 1 day, and PASSWORD_LOCK_TIME is then set to 4 hours, the new limit is enforced and the account will be unlocked 4 hours after it was locked. If a user’s lockout time setting is 4 hours, and the setting is increased to 1 day, the user will remain locked out for 1 day.

If the FAILED_LOGIN_ATTEMPTS setting for a locked out user is updated to be higher than the current setting, the user is unlocked. If the new setting is lower than the current number of failed login attempts, and also higher than the user’s current number of failed login attempts, the new setting is ignored until the user successfully logs in. The user is still subject to the original FAILED_LOGIN_ATTEMPTS setting.

Unlock a Locked Account

To unlock a locked account:

Use the ALTER USER command and specify ACCOUNT UNLOCK.

ALTER USER user ACCOUNT UNLOCK;

Last modified: February 3, 2025

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