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.
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. |
Database Administrator |
This role cannot execute backups, nor can it read any of the data within the database. |
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. |
Application Service Account |
Dedicated, per-application role, authorized to execute select, update, insert, delete DML. |
Relation Between Users, Roles and Groups
![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt16233ac43b7200d0/66db60c110aa0e3672e14500/diagram_RBAC-in-SingleStore-Bw3dkv.jpg?width=850&disable=upscale&auto=webp)
-
A role can have multiple privileges.
-
A group can have multiple roles.
-
A group can have multiple users.
-
A user can have multiple roles.
-
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.
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.
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.
GRANT USAGE ON *.* TO 'app_<NAME>_user' IDENTIFIED BY 'AVery$ecurePassword123';GRANT GROUP 'app_<NAME>_schema' TO 'app_<NAME>_user';
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