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.
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.
MemSQL supports granular grant-based security. Here you will find guidelines on
- Configuring a password policy
- Adding a
- Adding a
- Dropping a
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
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.
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
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>'
To remove a user, use the
DROP USER command. Logged in as
root, within the MySQL client command line:
memsql> DROP USER '<user>'@'<host>'
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
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
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
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
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
Restart the cluster in order to flush old database connections.
$ memsql-ops memsql-restart --all
Now you have a cluster with the
cluster_user, which is used solely for intracluster queries and replication.
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';
Optional. Remove other default users that may be present, like the
[email protected] user (distinct from the
root@% user), the "blank" local users, and dashboard.
''@'localhost' ''@'127.0.0.1' 'dashboard'@'%' 'dashboard'@'localhost' 'root'@'localhost'
MemSQL doesn't expose the users table directly. Instead, you can view grants and permissions by querying
This information is also exposed by running
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.
Currently, MemSQL does not have built-in features for the security of data "at rest", for example, encrypted backups and logs. It is, however, entirely possible to encrypt backup files with an external program after they are written, or to use disk volumes with transparent encryption.