Back
Outdated Version

You are viewing an older version of this section. View current production version.

/Security/

Securing MemSQL min read

MemSQL 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. All communication happens over the MySQL protocol (default port 3306), including replication and management.

A newly installed MemSQL instance has security disabled by default so you can immediately connect to MemSQL: by default, you can log into MemSQL with the root user and an empty password over an unsecured channel. Below are instructions on how to turn on various security features.

Configuring Host-Based Security

The first layer of defense is host-based security. Like MySQL, MemSQL supports the bind-address variable in memsql.cnf, which lets you specify the address on which the server socket binds. For example, if you set it to 127.0.0.1, you will only be able to connect to MemSQL locally.

You can also use a firewall to determine which hosts can access MemSQL. If you’re on Amazon EC2, for example, you can configure security groups to limit the network by specifying allowed IP addresses or security groups.

Configuring Password-Based Security

MemSQL supports granular grant-based security. Here you will find guidelines on

  • Configuring a password policy
  • Adding a root password
  • Adding a user
  • Dropping a user
  • Configuring user passwords

Configuring a Password Policy

Aspects of your cluster’s password policy can be configured using system variables, including password expiration, reuse, and complexity. You can see all system variables related to passwords by executing the following statement:

memsql> SHOW VARIABLES LIKE '%password%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| expire_root_password        | OFF   |
| old_passwords               | OFF   |
| password_expiration_seconds | 0     |
| password_history_count      | 0     |
| strict_passwords            | OFF   |
+-----------------------------+-------+
5 rows in set (0.00 sec)

These variables can be changed by using the SET GLOBAL command, and each is described below.

expire_root_password: Specifies whether the root password can expire. The default value is OFF. When set to OFF, the password_expiration_seconds duration does not apply to the root password. If set to ON, the root password will expire after the password_expiration_seconds duration is reached. This variable can be changed in both Community and Enterprise editions of MemSQL.

old_passwords: Specifies whether previously set passwords can be used again. The default value is OFF. When set to ON, the same password cannot be used until the number of new passwords exceeds the password_history_count value. Then the old password will be removed from the historical password log and can be used again. This variable can be changed in both Community and Enterprise editions of MemSQL.

password_expiration_seconds: The time duration in seconds before a password expires. The default value is 0, which indicates that passwords will never expire. This variable can only be changed in MemSQL Enterprise Edition.

password_history_count: The number of historical passwords per user that are used to validate the uniqueness of a new password. The default value is 0, which indicates that new passwords will not be validated against old passwords. A maximum of 10 historical passwords can be persisted. This variable can only be changed in MemSQL Enterprise Edition.

strict-passwords: Specifies whether to enforce complex passwords that must contain numbers, letters, and must be at least 6 characters in length. The default value is OFF. This variable can only be changed in MemSQL Enterprise Edition.

Adding or Updating the root password

The initial deployment of a MemSQL cluster is installed with the database user root, with a blank password. All communications between nodes in a MemSQL cluster happens over the MySQL protocol, using root and the blank password. As a part of a defense-in-depth security strategy, you may wish to set up password security.

To add a root password, use the MEMSQL-UPDATE-ROOT-PASSWORD command, which leverages the GRANT syntax under the hood. Run the command below to update the root password of all nodes in a MemSQL cluster, replacing <password> with your desired password. Note that this operation is an offline operation that will result in some cluster downtime.

memsql-ops memsql-list -q | xargs -n 1 memsql-ops memsql-update-root-password --no-confirmation -p <password>

Once you have configured the root password on all nodes, if you have already created any databases, run REBALANCE PARTITIONS on each database. On the master aggregator, for each database, run:

REBALANCE PARTITIONS ON database_name;

If you have previously created a Spark cluster with MemSQL Ops, restart the Spark components after setting the root password by running the following commands:

memsql-ops spark-component-stop --all; sleep 3 ; memsql-ops spark-component-start —all

Adding a User

Adding a user is very similar to setting the root password above. Again, the standard GRANT syntax is used. Logged in as root, within the MySQL client commandline:

memsql> GRANT <grant_options> ON *.* TO '<user>'@'<host>' IDENTIFIED BY '<password>'

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. Logged in as root, within the MySQL client command line:

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

Configuring user passwords

Step 1:

Make sure that manual control is disabled, to allow MemSQL Ops to re-attach and configure nodes.

master-agg$ memsql-ops cluster-manual-control --disable

Step 2:

List all hosts and MemSQL instances. The ID field is used in some of the steps.

  $ memsql-ops memsql-list
   ID         Agent Id    State     Role      Host          Port
   F8DAA45    Aedb2d1     ONLINE    MASTER    10.0.10.187   3306
   C7A7F5D    Af9b08a     ONLINE    LEAF      10.0.15.33    3306
   CCF8070    A4c182e     ONLINE    LEAF      10.0.3.2      3306

Step 3:

Add a new user to all MemSQL nodes in the cluster. The user must have ALL permissions on all tables and databases. It is not necessary to give it the “grant option”, ie, the ability to create new users. Below is a set of one-liner shell commands that issue SQL statements to the nodes in the cluster.

  $ mysql -uroot -h10.0.10.187 -e "grant all on *.* to 'cluster_user' identified by 'trustno1'"
  $ mysql -uroot -h10.0.15.33 -e "grant all on *.* to 'cluster_user' identified by 'trustno1'"
  $ mysql -uroot -h10.0.3.2 -e "grant all on *.* to 'cluster_user' identified by 'trustno1'"

Optionally, you can use network filters on this account. For example, if you want to allow only connections from the 10.0.0.0/16 subnet, you can specify 'cluster_user'@'10.0.%' instead of just 'cluster_user'. Make careful note of the quoting rules in the GRANT syntax.

Step 4:

Configuring leaves. This involves un-monitoring the leaf, removing the leaf from the cluster, and adding it back with the new user. Use the list from step 2 above to get the leaf ID's. Running this sequence one at a time on the leaves ensures that the cluster remains available for the duration of this step. If you are able to tolerate short periods of cluster downtime, you can use remove leaf 'host' FORCE instead, which disables failover. See REMOVE LEAF for more details.

$ memsql-ops memsql-unmonitor CCF8070
$ mysql -uroot -h10.0.10.187 -e "remove leaf '10.0.3.2'"
$ memsql-ops memsql-monitor -h 10.0.3.2 -u cluster_user -p trustno1

$ memsql-ops memsql-unmonitor C7A7F5D
$ mysql -uroot -h10.0.10.187 -e "remove leaf '10.0.15.33'"
$ memsql-ops memsql-monitor -h 10.0.15.33 -u cluster_user -p trustno1

Step 5:

Configuring aggregators. For each aggregator, starting with the master, un-monitor and then monitor with the new permissions.

$ memsql-ops memsql-unmonitor F8DAA45
$ memsql-ops memsql-monitor -h 10.0.10.187 -u cluster_user -p trustno1

Step 6:

Restart the cluster in order to flush old database connections.

$ memsql-ops memsql-restart --all

Now you have a cluster with thecluster_user, which is used solely for intracluster queries and replication.

Step 7:

Optional. Next we recommend adding application-specific users. Note that these users are only required on the aggregators, not the leaves.

memsql> create database app1;
memsql> grant all on app1.* to 'app1_readwrite' identified by '*******';
memsql> grant select on app1.* to 'app1_readonly' identified by 'hunter2';

Step 8:

Optional. Remove other default users that may be present, like the root@localhost user (distinct from the root@% user), the “blank” local users, and dashboard.

  ''@'localhost'
  ''@'127.0.0.1'
  'dashboard'@'%'
  'dashboard'@'localhost'
  'root'@'localhost'

Inspecting Permissions

MemSQL doesn’t expose the users table directly. Instead, you can view grants and permissions by querying information_schema.user_privileges.

This information is also exposed by running SHOW_GRANTS:

memsql> SHOW GRANTS FOR user@domain;

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

Encryption at Rest

In addition to transport-level encryption via SSL, MemSQL is also compatible with at-rest disk-based encryption via LUKS (Linux Unified Key Setup). This section outlines various implementation options. While LUKS is the recommended encryption technology, MemSQL may be compatible with other solutions. Please contact us if you have questions about using MemSQL with other encryption technologies.

How to Install MemSQL Over an Encrypted System

To use MemSQL with at-rest disk-based encryption, the first step is to ensure that your block device is encrypted with LUKS. 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. After encrypting your device, simply install MemSQL to the path where the encrypted volume is mounted.

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 MemSQL normally to encrypted location using MemSQL Ops

How to Use LUKS With Different Versions of Linux:

Need help or have suggestions? Go to SingleStore Forums