Role 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. 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>';