Manage Database Users
On this page
Add a Database User
Note
While a SingleStore Helios user can log into the Cloud Portal and run SQL commands via the SQL Editor, the same SingleStore Helios username cannot be used to log into a database from a third-party SQL client or development tool.
To log into a SingleStore database from a third-party SQL client or development tool, you must first add a database user.
As a workspace's admin user is not always a log-in option for all organization members, SingleStore recommends adding a separate database user for each organization member.
Caution
If you have invited another SingleStore Helios user to join your organization, please refrain from adding a database user with the same SingleStore Helios username until after the invitation has been accepted.
Alternatively, you may create a database user with a different username than the SingleStore Helios username, though this may make user management more challenging as additional organization members are added.
A user can be added by using the CREATE USER command.GRANT command cannot be used to add a new user since auto user creation by using the GRANT command is deprecated and the NO_ variable is enabled by default.
Change a Database User Password
The database admin password is configured when the workspace is first created.admin by default.
To change this password, navigate to the Deployments in the left navigation, and then select Access > User Access.
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.
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_.
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.
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_ and PASSWORD_ for the user or role.FAILED_ is the number of failed attempts before the account is locked, for example: 4.PASSWORD_ is the number of seconds a locked out account must wait before reattempting to log in.
Note
You must set both FAILED_ and PASSWORD_ 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_ value is applied.
If a user and a role the user is tied to have conflicting FAILED_ settings, the lower value is applied.
Update Lockout Settings
If the PASSWORD_ value is updated for a role or user, the new setting applies to currently locked accounts.PASSWORD_ is then set to 4 hours, the new limit is enforced and the account will be unlocked 4 hours after it was locked.
If the FAILED_ setting for a locked out user is updated to be higher than the current setting, the user is unlocked.FAILED_ 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: November 26, 2024