Roles & Privileges

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.

Built-in Roles

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.

General Access Roles

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

Any Database 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.

Pub/Sub Roles

• SUB: Grants the subscribe privilege to a specified database, table, or channel.
• PUB: Offers the publish privilege to a specified database, table, or channel.
• PUBSUB: Combines subscribe and publish privileges for a specified database, table, or channel.
• PUBSUBADMIN: Allows the creation and removal of channel privileges for a specified database or channel.
• SUBANYCHANNEL: Provides the subscribe privilege for any channel or table.
• PUBANYCHANNEL: Grants the publish privilege for any channel or table.
• PUBSUBANYCHANNEL: Combines subscribe and publish privileges for any channel or table.
• PUBSUBADMINANYCHANNEL: Permits the creation and removal of channel privileges for any channel.

Cluster Management Roles

• 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,SUB,PUB,PUBSUB,BACKUP,RESTORE,DOWNLOAD,PLUGIN,SETTINGS,USERADMIN,												  |              |           |
                       |         | CLUSTERADMIN,CLUSTERMONITOR,CREATE_DATABASE,DROP_DATABASE,HOSTADMIN,SWITCH_USER,PUBSUBCREATE,PUBSUBADMIN,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                                                                        | *            | *         |
 SUB                   | 1       | SUB                                                                                                                                    | NULL         | NULL      |
 SUBANYCHANNEL         | 1       | SUB                                                                                                                                    | *            | *         |
 PUB                   | 1       | PUB                                                                                                                                    | NULL         | NULL      |
 PUBANYCHANNEL         | 1       | PUB                                                                                                                                    | *            | *         |
 PUBSUB                | 1       | SUB,PUB,PUBSUB                                                                                                                         | NULL         | NULL      |
 PUBSUBANYCHANNEL      | 1       | SUB,PUB,PUBSUB                                                                                                                         | *            | *         |
 PUBSUBADMIN           | 1       | SUB,PUB,PUBSUB,PUBSUBCREATE,PUBSUBADMIN                                                                                                | NULL         | NULL      |
 PUBSUBADMINANYCHANNEL | 1       | SUB,PUB,PUBSUB,PUBSUBCREATE,PUBSUBADMIN                                                                                                | *            | *         |
-----------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------|--------------|-----------|

Custom Roles

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.

Creating a Custom Role

1. Navigate to the Roles section in the left sidebar.
2. Click the Create Role button.
3. Name: Enter a unique name for the role (e.g., AuditLogger, HRManager).
4. Privileges: Select the specific atomic privileges this role should possess (see list below).
5. Click Create.

Managing Roles

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.

Privileges Reference

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: