Sign inTry Free

Role-Based Access Control (RBAC) at Database Level

On this page

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

It is assumed that SingleStore is not currently configured in strict mode. If strict mode is already enabled, the cluster must be wiped and reinstalled before you can proceed.

Role-based Operations

Users can access a database and execute their functions and responsibilities through the creation of users, roles and groups, and granting of correct permissions (privileges).

Here is a sample of standard roles.

Role

Description

Compliance Officer

Management for roles and schema authorizations.

Security Officer

Full authority to view, modify, and 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 SingleStore 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.

Relation Between Users, Roles and Groups

  1. A role can have multiple privileges.

  2. A group can have multiple roles.

  3. A group can have multiple users.

  4. A user can have multiple roles.

  5. A user can be assigned to multiple groups.

Users inherit the permissions, and roles of the groups they are assigned to.

Role Creation

SingleStore recommends that 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 SingleStore. 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 USAGE on *.* to ROLE 'compliance_role' WITH GRANT OPTION;


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

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

Warning

Strict Mode has been deprecated and is no longer supported.

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

Set Strict Mode with Management Tools

Set strict_mode on all nodes in the cluster. This is the preferred method to ensure the value is set correctly across all nodes in the cluster.

sdb-admin update-config --all --key strict_mode --value true

Set Strict Mode without Management Tools

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

Last modified: May 29, 2024

Was this article helpful?

On this page

Was this article helpful?