# SingleStore User Management

## Configure SingleStore User Accounts

> **⚠️ Warning**: By default, user accounts are configured independently on each SingleStore aggregator node. Whenever you add a new aggregator node, you will also need to configure user accounts on it. Users do not normally connect to leaves, so it is generally not necessary to configure user accounts on leaf nodes. Only the `root` user is required on leaf nodes.

Also, you can automatically synchronize non-root user accounts across your cluster to avoid manually managing these accounts on each aggregator node. See [Synchronizing Permissions Across Your Cluster](https://docs.singlestore.com/db/v9.1/security/administration/synchronizing-permissions-across-your-cluster.md) for more details on how to enable this functionality.

## Secure the Initial SingleStore User Accounts

## Configure the `root` Password

When SingleStore is installed, the `root` user is created on each SingleStore node. You are required to set a password when running [sdb-admin create-node](https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/sdb-admin-commands/create-node.md), but this can be set to a blank password for testing purposes. As a best practice, you should set a secure password for the `root` user.

Once your cluster is deployed, you can also change the password for the `root` user with the [sdb-admin change-root-password](https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/sdb-admin-commands/change-root-password.md) command. This command configures the `root` password for a single SingleStore node. To configure the password on all nodes in a SingleStore cluster, run:

```shell
sdb-admin change-root-password --all --yes --password <secure_password>

```

Changing the `root` password is an online operation for both aggregators and leaves and you do not have to perform any additional operations; however, you must ensure your nodes are running before calling the `change-root-password` command.

> **⚠️ Warning**: Setting the `root` password via a command-line argument is often not secure and SingleStore recommends safer, alternative methods to configure passwords.

While it is convenient to set the `root` password using a command-line argument, it is often recommended against this practice for security reasons. The command-line argument accepts passwords entered as plain text, which makes them vulnerable to being discovered in the list of processes running on the system during application runtime. The plain text passwords could also be saved and accessed in the history file if your command-line interpreter maintains a history. For example, in Bash, the command history including password inputs is logged at \~/.bash\_history. In addition, the `root` password supplied as a command-line argument is displayed on-screen and is visible to anyone who is reading the user’s screen.

Some of the more secure, alternative methods to set SingleStore `root` password are as follows.

* Set the `root` password using the `MEMSQL_PASSWORD` environment variable. This option is best suited for automated applications.
* As of SingleStore Toolbox 1.6.4, Toolbox commands can solicit the `root` password interactively from users. The passwords entered in an interactive prompt are not displayed on-screen and are effectively secured from anyone reading the user’s screen. The interactive prompt is invoked if neither the `--password` flag nor the `MEMSQL_PASSWORD` environment variable has been used to set the `root` password.

## Deleting Unnecessary Default Users

In MemSQL 6.0 and later, the only default user created on each SingleStore node during installation is the `'root'@'%'` user, which should be configured as described in the previous section.

In MemSQL 5.8 and earlier, several default users are created on each SingleStore node during installation. SingleStore recommends deleting all of these default users except for the `'root'@'%'` user.

To delete these users, use the `DROP USER` command. On each SingleStore node (including both aggregators and leaves), log in as the `root` user or another user with sufficient permissions, and run:

```sql
DROP USER ''@'localhost';
DROP USER ''@'127.0.0.1';
DROP USER 'root'@'localhost';
DROP USER 'dashboard'@'%';
DROP USER 'dashboard'@'localhost';

```

These additional default users are created in MemSQL installations of 5.8 and earlier, but not 6.0 and later. They are not changed during upgrade, so a SingleStore cluster installed on 5.8 or earlier and upgraded to 6.0 or later will still have these user accounts, unless you have deleted them.

Note that since user accounts are configured independently on each node, whenever you add a new SingleStore node, you should also delete any unnecessary default users on it.

> **⚠️ Warning**: The users ''@'localhost' and ''@'127.0.0.1' are “anonymous” user accounts, which allow any user to log in from the localhost (with a limited set of permissions). The blank user string matches any username - for example, attempting to log in as user alice from localhost will match the ''@'localhost' user, unless a user account 'alice'@'localhost' exists, in which case that takes precedence. Note that even if a user account 'alice'@'%' exists, the anonymous user account takes precedence over that. More specific hostnames take precedence first, and a specific username takes precedence over a blank “anonymous” username for the same hostname specificity.For example, a login as user ‘alice’ from localhost matches 'alice'@'localhost', ''@'localhost', and 'alice'@'%' in that order of precedence. This can cause unexpected behavior: if you have the anonymous users and an 'alice'@'%' user, but not an 'alice'@'localhost' user, when you attempt to log in as the user ‘alice’ from localhost, you will be logged in as ''@'localhost', whereas when you attempt to log in as the user ‘alice’ from any other host, you will be logged in as the 'alice'@'%' user. SingleStore recommends deleting the anonymous user accounts.For this reason, the `'root'@'localhost'` account is necessary only when the anonymous user accounts for localhost are present. Logging in as the `root` user from localhost matches `'root'@'localhost'`, `''@'localhost'`, and `'root'@'%'` in that order of precedence. Therefore, when the anonymous user accounts are not present, the `'root'@'localhost'` account can be removed, leaving only the `'root'@'%'` account. SingleStore recommends deleting the `'root'@'localhost'` account in addition to the anonymous user accounts to avoid the possibility of misconfiguring the `'root'@'localhost'` and `'root'@'%'` accounts differently (for example, misconfiguring them with different passwords).The `'dashboard'@'%'` and `'dashboard'@'localhost'` accounts were used by MemSQL Ops versions prior to 4.0. As they are no longer used, SingleStore recommends deleting them.

## Add a User

To add a user, use the [GRANT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/grant.md) command.&#x20;

If you have `sync_permissions` enabled on your cluster, log into the master aggregator as the `root` user or another user with sufficient permissions, and run:

```sql
GRANT <grant_options> TO '<user>'@'<host>' IDENTIFIED BY '<password>'

```

For example:

```sql
GRANT SELECT, INSERT ON db.* TO 'username'@'%' IDENTIFIED BY 'password'

```

See the [GRANT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/grant.md) documentation for more details.

> **📝 Note**: If you do not have `sync_permissions` enabled, then you must perform the above operations on each aggregator in your cluster.

You can also use [Kerberos](https://docs.singlestore.com/db/v9.1/security/authentication/kerberos-authentication.md), [SAML](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication.md), or [PAM](https://docs.singlestore.com/db/v9.1/security/authentication/pam-authentication.md) instead of password-based authentication.

## Change a Password for a User

To change a password for a user, you have the following options:

* Use `GRANT` as shown in the previous section. You must have `SUPER` privileges to change another user’s password.
* Use `SET PASSWORD` to create a password hash and assign it to the user. Users can also change their own password with this command.
  ```sql
  SET PASSWORD FOR 'username'@'host' = PASSWORD('password');

  ```

## Remove a User

To remove a user, use the [DROP USER](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/drop-user.md) command.

Similar to other user management operations, if you have `sync_permissions` enabled, log into the master aggregator as the `root` user or another user with sufficient permissions, and run:

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

```

If `sync_permissions` is not enabled, then you must perform this operation on each aggregator in the cluster.

## Inspect 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/show-grants.md):

```sql
SHOW GRANTS FOR user@domain;

```

## Set a Failed Login Attempt Lockout Policy

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 *both* `FAILED_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:

```sql
CREATE USER user1 WITH FAILED_LOGIN_ATTEMPTS = 4 PASSWORD_LOCK_TIME = 14400;

```

Enabling the feature for a role:

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

```sql
ALTER USER user ACCOUNT UNLOCK;

```

If [sync permissions](https://docs.singlestore.com/db/v9.1/security/administration/synchronizing-permissions-across-your-cluster.md) is not enabled, `ACCOUNT UNLOCK` should be issued on the aggregator where the user is to be unlocked.

If `sync_permissions` is enabled, `ACCOUNT UNLOCK` should be issued on the Master Aggregator since all user modifications will have to come from the Master. This will unlock the account across the cluster.

## In this section

* [Synchronizing LDAP Users and Groups](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/singlestore-user-management/ldap-user-sync.md)
* [Role-Based Access Control (RBAC) at Database Level](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/singlestore-user-management/role-based-access-control-rbac-at-database-level.md)

***

Modified at: January 20, 2023

Source: [/db/v9.1/user-and-cluster-administration/singlestore-user-management/](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/singlestore-user-management/)

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