# Role-Based Access Control (RBAC) at Database Level This guide outlines how to secure SingleStore Helios [database users](https://docs.singlestore.com/cloud/user-and-workspace-administration/manage-organizations.md) 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](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/create-user.md), [roles](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/create-role.md) and [groups](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/create-group.md), and [granting](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/grant.md) 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. | | WorkspaceAdministrator | Minimal set of privileges required to run aSingleStoreworkspace. | | Backup Operator | Authorization to performworkspacebackups. | | 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 ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt16233ac43b7200d0/6a33d0993634d3d87e1bebc1/diagram_RBAC-in-SingleStore-eyDzhT.jpg) 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 against the workspace endpoint. **Compliance Officer** ```sql 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** ```sql CREATE ROLE 'security_role'; GRANT CREATE USER on *.* to ROLE 'security_role'; CREATE GROUP 'security'; GRANT ROLE 'security_role' to 'security'; ``` **Database Administrator** ```sql CREATE ROLE 'dba_role'; GRANT CREATE DATABASE, DROP DATABASE on *.* to ROLE 'dba_role'; GRANT RELOAD on *.* to ROLE 'dba_role'; GRANT SHOW METADATA on *.* to ROLE 'dba_role'; CREATE GROUP 'dba'; GRANT ROLE 'dba_role' to 'dba'; ``` **Workspace Administrator** ```sql CREATE ROLE 'workspace_role'; GRANT CLUSTER on *.* to ROLE 'workspace_role'; GRANT SHOW METADATA on *.* to ROLE 'workspace_role'; CREATE GROUP 'workspace'; GRANT ROLE 'workspace_role' to 'workspace'; ``` **Backup Operator** ```sql 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: ```sql CREATE ROLE 'app__schema_role'; GRANT CREATE, ALTER, DROP on .* to ROLE 'app__schema_role'; GRANT CREATE VIEW, ALTER VIEW, DROP VIEW on .* to ROLE 'app__schema_role'; GRANT SHOW VIEW on .* to ROLE 'app__schema_role'; GRANT CREATE TEMPORARY TABLES on .* to ROLE 'app__schema_role'; CREATE GROUP 'app__schema'; GRANT ROLE 'app__schema_role' to 'app__schema'; ``` **Application Service Account** Create one for each distinct application: ```sql CREATE ROLE 'app__role'; GRANT SELECT, INSERT, UPDATE, DELETE on .* to ROLE 'app__role'; GRANT SHOW VIEW on .* to ROLE 'app__role'; GRANT LOCK TABLES on .* to ROLE 'app__role'; GRANT CREATE TEMPORARY TABLES on .* to ROLE 'app__role'; CREATE GROUP 'app_'; GRANT ROLE 'app__role' to 'app_'; ``` ## 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** ```sql GRANT USAGE ON *.* TO 'compliance_user' IDENTIFIED BY 'AVery$ecurePassword123'; GRANT GROUP 'compliance' TO 'compliance_user'; ``` **Security Officer** ```sql GRANT USAGE ON *.* TO 'security_user' IDENTIFIED BY 'AVery$ecurePassword123'; GRANT GROUP 'security' TO 'security_user'; ``` **Database Administrator** ```sql GRANT USAGE ON *.* TO 'dba_user' IDENTIFIED BY 'AVery$ecurePassword123'; GRANT GROUP 'dba' TO 'dba_user'; ``` **Workspace Administrator** ```sql GRANT USAGE ON *.* TO 'workspace_user' IDENTIFIED BY 'AVery$ecurePassword123'; GRANT GROUP 'workspace' TO 'workspace_user'; ``` **Backup Operator** ```sql 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. ```sql GRANT USAGE ON *.* TO 'app__schema_user' IDENTIFIED BY 'AVery$ecurePassword123'; GRANT GROUP 'app__schema' TO 'app__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. ```sql GRANT USAGE ON *.* TO 'app__user' IDENTIFIED BY 'AVery$ecurePassword123'; GRANT GROUP 'app__schema' TO 'app__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. ```sql 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'; ``` *** Modified at: June 4, 2025 Source: [/cloud/security/administration/role-based-access-control-rbac-at-database-level/](https://docs.singlestore.com/cloud/security/administration/role-based-access-control-rbac-at-database-level/) (An index of the documentation is available at /llms.txt)