MemSQL Documentation

MemSQL is a high-performance, in-memory database that combines the horizontal scalability of distributed systems with the familiarity of SQL.

Get Started    

Role-Based Access Control (RBAC) Deployment Guide

This section outlines how to securely deploy MemSQL in a reduced-privileges, role-separated environment.

Role-Based Access Control (RBAC) Configuration

Goal

Deploy a role-based security control model for MemSQL operations.

Assumptions

MemSQL is not currently configured in strict mode. If strict mode is already enabled, the cluster must be wiped and reinstalled before one can proceed.

Implementation

Role-based operations

Role
Description

Compliance Officer

Management for roles and schema authorizations.

Security Officer

Full authority to view, modify, create users and groups. Manages user passwords

Database Administrator

This role cannot execute backups, nor can it read any of the data within the database. Responsible for creating and removing databases. Ability to restore backups.

Cluster Administrator

Minimal set of privileges required to run a MemSQL cluster.

Backup Operator

Authorization to perform cluster backups.

Application Schema Owner

Dedicated, per-application role, authorized to execute create, alter, and delete DDL statements. Cannot view application data.

Application Service Account

Dedicated, per-application role, authorized to execute select, update, insert, delete DML.

Role Creation

MemSQL recommends the following roles be used as a starting point for all use of the RBAC functionality. It is strongly suggested that these commands be kept in a separate, version controlled, file and loaded into MemSQL. These scripts should be executed on all nodes where users will connect, typically all aggregators and on all leaves if the application's design requires it to bypass the aggregators.

Compliance Officer

CREATE ROLE 'compliance_role';
GRANT GRANT OPTION on *.* to ROLE 'compliance_role';

CREATE GROUP 'compliance';
GRANT ROLE 'compliance_role' to 'compliance';

Security Officer

CREATE ROLE 'security_role';
GRANT CREATE USER on *.* to ROLE 'security_role';

CREATE GROUP 'security';
GRANT ROLE 'security_role' to 'security';

Database Administrator

CREATE ROLE 'dba_role';
GRANT CREATE DATABASE, DROP DATABASE on *.* to ROLE 'dba_role';
GRANT RELOAD on *.* to ROLE 'dba_role';
GRANT SUPER on *.* to ROLE 'dba_role';
GRANT SHOW METADATA on *.* to ROLE 'dba_role';

CREATE GROUP 'dba';
GRANT ROLE 'dba_role' to 'dba';

Cluster Administrator

CREATE ROLE 'cluster_role';
GRANT CLUSTER on *.* to ROLE 'cluster_role';
GRANT SHOW METADATA on *.* to ROLE 'cluster_role';

CREATE GROUP 'cluster';
GRANT ROLE 'cluster_role' to 'cluster';

Backup Operator

CREATE ROLE 'backup_operator_role'; 
GRANT BACKUP, RELOAD on *.* to ROLE 'backup_operator_role';

CREATE GROUP 'backup_admins';
GRANT ROLE 'backup_operator_role' to 'backup_admins';

Application Schema Owner
Create one for each distinct application:

CREATE ROLE 'app_<NAME>_schema_role';
GRANT CREATE, ALTER, DROP on <NAME>.* to ROLE 'app_<NAME>_schema_role';
GRANT CREATE VIEW, ALTER VIEW, DROP VIEW on <NAME>.* to ROLE 'app_<NAME>_schema_role';
GRANT SHOW VIEW on <NAME>.* to ROLE 'app_<NAME>_schema_role';
GRANT CREATE TEMPORARY TABLES on <NAME>.* to ROLE 'app_<NAME>_schema_role';

CREATE GROUP 'app_<NAME>_schema';
GRANT ROLE 'app_<NAME>_schema_role' to 'app_<NAME>_schema';

Application Service Account
Create one for each distinct application:

CREATE ROLE 'app_<NAME>_role';
GRANT SELECT, INSERT, UPDATE, DELETE on <NAME>.* to ROLE 'app_<NAME>_role';
GRANT SHOW VIEW on <NAME>.* to ROLE 'app_<NAME>_role';
GRANT LOCK TABLES on <NAME>.* to ROLE 'app_<NAME>_role';
GRANT CREATE TEMPORARY TABLES on <NAME>.* to ROLE 'app_<NAME>_role';

CREATE GROUP 'app_<NAME>';
GRANT ROLE 'app_<NAME>_role' to 'app_<NAME>’;

Account Creation

MemSQL recommends the following roles be used as a starting point for all use of the RBAC functionality. It is strongly suggested that these commands be kept in a separate, version controlled, file and loaded into MemSQL. Depending on requirements, these scripts should be executed on all nodes where users will connect, typically all aggregators and optionally leaves.

Compliance Officer

GRANT USAGE ON *.* TO 'compliance_user' IDENTIFIED BY 'AVery$ecurePassword123';
GRANT GROUP 'compliance' TO 'compliance_user';

Security Officer

GRANT USAGE ON *.* TO 'security_user' IDENTIFIED BY 'AVery$ecurePassword123';
GRANT GROUP 'security' TO 'security_user';

Database Administrator

GRANT USAGE ON *.* TO 'dba_user' IDENTIFIED BY 'AVery$ecurePassword123';
GRANT GROUP 'dba' TO 'dba_user';

Cluster Administrator

GRANT USAGE ON *.* TO 'cluster_user' IDENTIFIED BY 'AVery$ecurePassword123';
GRANT GROUP 'cluster' TO 'cluster_user';

Backup Operator

GRANT USAGE ON *.* TO 'backup_user' IDENTIFIED BY 'AVery$ecurePassword123';
GRANT GROUP 'backup_admins' TO 'backup_user';

Application Schema Owner
Create one for each distinct application.

GRANT USAGE ON *.* TO 'app_<NAME>_schema_user' IDENTIFIED BY 'AVery$ecurePassword123';
GRANT GROUP 'app_<NAME>_schema' TO 'app_<NAME>_schema_user';

Application Service Accounts
Create one for each distinct application. For additional release validation, one may consider creating a number of accounts for the same application and rotating them during code releases. They should all have unique names and unique passwords.

GRANT USAGE ON *.* TO 'app_<NAME>_user' IDENTIFIED BY 'AVery$ecurePassword123';
GRANT GROUP 'app_<NAME>_schema' TO 'app_<NAME>_user';

Enable Strict Mode

Licensing Note

As of the time of this publication, strict mode is made available and licensed only as part of the MemSQL Advanced Security Option. Before using or implementing this functionality, please consult with your enterprise's licensing administrator to confirm that your enterprise has purchased the necessary Advanced Security Option license from MemSQL

Once all roles and grants have been configured in the cluster, strict mode should be enabled. After this has been done, the superuser 'root' role can no longer be used to configure the cluster.

Caution

Once strict mode is enabled, it cannot be disabled!

Disable Account

MemSQL ships with a number of anonymous accounts which should be removed prior to deploying in strict mode.

DROP USER ''@'localhost';
DROP USER ''@'127.0.0.1';
DROP USER 'root'@'%';
DROP USER 'root'@'localhost';
MemSQL Ops
memsql-ops memsql-update-config --all --key strict_mode --value true
MemSQL Ops Unavailable

Edit memsql.cnf and add the following to the [server] section on every node that had roles created on it and restart.

---snip---
[server]

strict_mode = true
---snip---

Appendix: Role-Based Access Control Command Reference

The following commands are available to query the status of the different users, groups, and roles in MemSQL Enterprise running in RBAC mode.

SHOW USERS;
SHOW USERS FOR ROLE 'role';
SHOW USERS FOR GROUP 'group';
SHOW GROUPS;
SHOW GROUPS FOR ROLE 'role';
SHOW GROUPS FOR USER 'user'@'%';
SHOW ROLES;
SHOW ROLES FOR USER 'user'@'%';
SHOW ROLES FOR GROUP 'group';

Role-Based Access Control (RBAC) Deployment Guide