| title | Roles & Privileges |
|---|---|
| description | Understand the role-based access control system, built-in roles, and how to define custom access policies. |
| category | platform |
| status | publish |
| slug | roles |
import VideoPlayer from '@commons-components/Video/VideoPlayer.astro'; import createRole from '@docs-website-assets/introduction/video/roles-privileges/roles_create_custom_role.mp4'; import manageRole from '@docs-website-assets/introduction/video/roles-privileges/roles_manage_roles.mp4';
import Callout from "@commons-components/Information/Callout.astro";
In SQLite Cloud, a Role is a named collection of permissions (privileges) that allows specific actions on resources like databases, tables. Users can have multiple roles, which determine their access to the system.
Roles are the bridge between Users and Resources:
- Users authenticate into the system.
- Roles define what those users are allowed to do.
- Resources (Databases, Tables) are the objects being accessed.
You can manage role definitions via the SQLite Cloud Dashboard under the Roles section.
SQLite Cloud comes with a set of pre-defined roles designed to cover the most common use cases. These roles are available immediately and cannot be modified, but they can be scoped to specific databases or tables when assigned to a user.
- ADMIN: This role possesses the highest level of privileges, with unrestricted access to all assigned permissions.
- READ: Grants read-only access to a specified database or table.
- READWRITE: Offers both read and write functionality for a specified database or table.
- DBADMIN: Allows for administrative tasks like indexing and statistics gathering but doesn't manage users or roles.
These roles implicitly apply to the entire cluster (*) and do not require specific scoping during assignment.
- READANYDATABASE: Provides read-only access to any database and table.
- READWRITEANYDATABASE: Grants read and write capabilities across any database and table.
- DBADMINANYDATABASE: Provides administrative functions for any database.
- USERADMIN: Enables the creation and modification of roles and users.
- CLUSTERADMIN: Empowers users to manage and monitor the cluster.
- CLUSTERMONITOR: Offers read-only access to cluster monitoring commands.
- HOSTADMIN: Allows monitoring and management of individual nodes.
Below is the technical definition of all built-in roles and their mapped privileges:
>> LIST ROLES
-----------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------|--------------|-----------|
rolename | builtin | privileges | databasename | tablename |
-----------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------|--------------|-----------|
ADMIN | 1 | READ,INSERT,UPDATE,DELETE,READWRITE,PRAGMA,CREATE_TABLE,CREATE_INDEX,CREATE_VIEW, | | |
| | CREATE_TRIGGER,DROP_TABLE,DROP_INDEX,DROP_VIEW,DROP_TRIGGER,ALTER_TABLE,ANALYZE, | | |
| | ATTACH,DETACH,DBADMIN,BACKUP,RESTORE,DOWNLOAD,PLUGIN,SETTINGS,USERADMIN, | | |
| | CLUSTERADMIN,CLUSTERMONITOR,CREATE_DATABASE,DROP_DATABASE,HOSTADMIN,SWITCH_USER,WEBLITE,ADMIN | NULL | NULL |
READ | 1 | READ | NULL | NULL |
READANYDATABASE | 1 | READ | * | * |
READWRITE | 1 | READ,INSERT,UPDATE,DELETE,READWRITE | NULL | NULL |
READWRITEANYDATABASE | 1 | READ,INSERT,UPDATE,DELETE,READWRITE | * | * |
DBADMIN | 1 | READ,INSERT,UPDATE,DELETE,READWRITE,PRAGMA,CREATE_TABLE,CREATE_INDEX,CREATE_VIEW, | | |
| | CREATE_TRIGGER,DROP_TABLE,DROP_INDEX,DROP_VIEW,DROP_TRIGGER,ALTER_TABLE,ANALYZE,ATTACH,DETACH,DBADMIN | NULL | NULL |
DBADMINANYDATABASE | 1 | READ,INSERT,UPDATE,DELETE,READWRITE,PRAGMA,CREATE_TABLE,CREATE_INDEX,CREATE_VIEW, | | |
| | CREATE_TRIGGER,DROP_TABLE,DROP_INDEX,DROP_VIEW,DROP_TRIGGER,ALTER_TABLE,ANALYZE,ATTACH,DETACH,DBADMIN | * | * |
USERADMIN | 1 | USERADMIN | * | * |
CLUSTERADMIN | 1 | CLUSTERADMIN | * | * |
CLUSTERMONITOR | 1 | CLUSTERMONITOR | * | * |
HOSTADMIN | 1 | BACKUP,RESTORE,DOWNLOAD,CREATE_DATABASE,DROP_DATABASE,HOSTADMIN | * | * |
-----------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------|--------------|-----------|If the built-in roles do not fit your specific security model, you can create User-Defined Roles. This allows you to mix and match specific privileges.
- Navigate to the Roles section in the left sidebar.
- Click the Create Role button.
- Name: Enter a unique name for the role (e.g.,
AuditLogger,HRManager). - Privileges: Select the specific atomic privileges this role should possess (see list below).
- Click Create.
From the Roles list, you can:
- Inspect: Click on a role to see exactly which privileges it contains.
- Edit: Add or remove privileges from a custom role (Built-in roles cannot be edited).
- Delete: Remove a custom role.
In a role-based access control system, a Privilege represents a specific action or permission that a user or role is allowed to perform within the system. It defines what a user can or cannot do, such as reading, writing, or managing certain resources like tables, databases, or settings. Essentially, a privilege is a right or ability granted to a user or role, specifying their level of access and control over the system's resources.
A privilege can be granted, revoked and assigned to a given role. A role can contains any combination of privileges.
Below is the complete list of available privileges:
| NONE | READ | INSERT |
| UPDATE | DELETE | READWRITE |
| PRAGMA | CREATE_TABLE | CREATE_INDEX |
| CREATE_VIEW | CREATE_TRIGGER | DROP_TABLE |
| DROP_INDEX | DROP_VIEW | DROP_TRIGGER |
| ALTER_TABLE | ANALYZE | ATTACH |
| DETACH | DBADMIN | BACKUP |
| RESTORE | DOWNLOAD | PLUGIN |
| SETTINGS | USERADMIN | CLUSTERADMIN |
| CLUSTERMONITOR | CREATE_DATABASE | DROP_DATABASE |
| HOSTADMIN | SWITCH_USER | WEBLITE |
| ADMIN |