---

MemSQL supports features for user authentication, 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.

Encryption is a large topic, covered in SSL Network Encryption .

MemSQL ships with security disabled by default, but it supports enough of the MySQL security protocol to enable simple user and host logins with passwords, and SSL encryption. 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

• adding a root password
• adding a user
• dropping a user
• configuring user passwords

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>

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

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.

  $ 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'"

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;