---

SingleStore DB supports features for user authentication, password policies, fine-grained access controls, and certificate-based network encryption between clients and the database cluster, as well as between individual nodes in the cluster.

By default, you can log into SingleStore DB with the root user, but must specify a password to connect to the database, or explicitly define an empty password during deployment to connect without specifying a password. This page describes configuration changes and best practices required to secure SingleStore DB.

Configuring SingleStore DB user accounts

Securing the initial SingleStore DB user accounts

Configuring the root password

When SingleStore DB is installed, the root user is created on each SingleStore node. You are required to set a password when running sdb-admin create-node, 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 command. This command configures the root password for a single SingleStore node. To configure the password on all nodes in a cluster, run:

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.

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 DB 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 DB 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. We recommend 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:

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

Adding a User

To add a user, use the GRANT command. 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:

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

For example:

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

See the GRANT documentation for more details.

You can also use Kerberos, SAML, or PAM instead of password-based authentication.

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

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

Removing a User

To remove a user, use the DROP USER 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:

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

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

Inspecting 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;

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

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

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.

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

Unlocking a Locked Account

To unlock a locked account:

Use the ALTER USER command and specify ACCOUNT UNLOCK.

ALTER USER user ACCOUNT UNLOCK;

If sync permissions 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.

Configuring Host-Based Security

You can use a firewall to restrict which hosts can access SingleStore DB. For example, if you’re running a cluster on Amazon EC2, you can configure security groups to restrict network access by specifying allowed IP addresses or security groups.

You can also set the bind-address variable to restrict the range of IP addresses which are allowed to connect to SingleStore DB. For example, if you set it to 127.0.0.1, you will only be able to connect to SingleStore DB locally. See bind_address in the list of engine variables.

Configuring SSL

See SSL Secure Connections.

Setting SECURE_FILE_PRIV

The secure_file_priv global variable controls where users with the FILE READ and FILE WRITE privilege can read or save files. It should be set on all nodes to a directory that is not used by SingleStore DB or other software. If not set, a user with the FILE READ or FILE WRITE privilege can tamper with the system by reading or creating files in sensitive locations. For information on how to set engine variables, see the engine variables overview.

Encryption at Rest

SingleStore DB is compatible with at-rest disk-based encryption via LUKS (Linux Unified Key Setup). SingleStore DB is also compatible with other encryption solutions that may have different security characteristics. Please see our partner integrations documentation or contact us if you have questions about using SingleStore DB with other encryption technologies.

To use SingleStore DB with LUKS, configure your block device to be encrypted with LUKS, and then simply install SingleStore DB on the encrypted volume.

For more information about how to implement LUKS with different versions of Linux, see the links in the section below. Note that ‘ecryptfs’ should never be used - only volume or block level encryption.

Example Setup Process

• Prepare block device
• Encrypt block device with LUKS
• Create filesystem (i.e., mkfs.ext4 /dev/mapper/myencryptedvolume)
• Mount filesystem (i.e., mount /dev/mapper/myencryptedvolume /data)
• Install SingleStore DB normally to encrypted location using the SingleStore management tools or MemSQL Ops

How to Use LUKS With Different Versions of Linux

• Red Hat Enterprise Linux: For an overview on using LUKS with Red Hat, go here.

  • Red Hat Enterprise Linux version 5 (Linux kernel 2.6.18-8)
  • Red Hat Enterprise Linux version 6 (Linux kernel 2.6.32-71)
  • Red Hat Enterprise Linux version 7 (Linux kernel 3.10.0-123)

• SUSE Linux Enterprise Server:

  • SUSE version 10 (Linux kernel 2.6.16.21-0.8)
  • SUSE version 11 (Linux kernel 2.6.27.19-5.1)
  • SUSE version 12 (Linux kernel 3.12.28-4.6)

• Ubuntu: For information about implementing LUKS on Ubuntu, go here.

• openSUSE: For information about implementing LUKS on openSUSE, go here.