Manage role-based access control

Role-based access control provides the ability to control permissions and determine who can access and perform operations on specific objects in Firebolt. Permissions are assigned to roles which are, in turn, assigned to users or other roles. A user can be assigned multiple roles.

A user interacting with Firebolt must have the appropriate permissions to use an object. Permissions from all roles assigned to a user are considered in each interaction in Firebolt.

To view all roles, click Govern to open the govern space, then choose Roles from the menu, or query the information_schema.applicable_roles view.

System-defined roles

Roles are assigned to users to allow them to complete tasks on relevant objects to fulfill their business needs. Each account is provisioned with a set of system-defined roles.

Role Name	Description
public	Equivalent to `USAGE` on every database as well as `USAGE` and `CREATE` on every `public` schema. This role is automatically granted to every new user, but can be revoked if desired.
system_admin	Enables managing databases, engines, schemas, tables, and views. This includes setting database and engine properties as well as access to the observability functionality on all engines.
account_admin	Enables all the permissions and the ability to manage the account.

System-defined roles can neither be modified nor dropped. Users with the account_admin role can grant roles to other users.

Custom roles

A user with the account_admin role can create custom roles. You can create a custom role using SQL, or via the user interface (UI).

Privileges can be granted to a custom role either by the account_admin or by the owner of a resource. For example, a user who created a table can grant SELECT on that table to an arbitrary custom role.

Ownership

When a user creates an object, they become its owner. The owner of an object can perform any operation on the object, even if the privileges normally required to perform that operation aren’t granted to any of the user’s roles. See Ownership for more information.

An owner of an object can also grant privileges over that object to any role. A role owner can grant that role to users and other roles without the requirement of being an admin.

Permissions

A set of permissions can be granted to a custom role over the following supported objects: account, database, engine, schema, table, or view. Available permissions for each object type are listed as follows:

To view permissions for schemas, tables and views in the current database, query the information_schema.object_privileges.
To view account, role, user, engine and database permissions, make sure that current database is not selected, and query the information_schema.object_privileges. To unselect current database in the the UI, choose the None option in the current database selector.

When the current database is selected, information_schema.object_privileges shows permissions only for objects in the current database. It doesn’t show permissions for accounts, roles, users, engines, databases, and objects in other databases.

Select the current database with USE DATABASE or with the database selector in the UI.

Account

Permissions can be granted on accounts to allow creating, modifying and using databases and engines.

Permission	Description
CREATE DATABASE	Enables creating new databases in the account.
USAGE ANY DATABASE	Enables using all current and future databases in the account.
MODIFY ANY DATABASE	Enables editing all current and future databases in the account.
CREATE ENGINE	Enables creating new engines in the account.
USAGE ANY ENGINE	Enables using all current and future engines in the account.
OPERATE ANY ENGINE	Enables starting and stopping all current and future engines in the account.
MODIFY ANY ENGINE	Enables editing all current and future engines in the account.
CREATE ROLE	Enables creating new roles in the account.
MODIFY ANY ROLE	Enables editing all current and future roles in the account.
CREATE USER	Enables creating new users in the account.
MODIFY ANY USER	Enables editing all current and future users in the account.

Role

Permissions can be granted on roles to allow modification of roles per account.

Permission	Description
MODIFY	Enables dropping the role.

User

Permissions can be granted on users to allow modification of users per account.

Permission	Description
MODIFY	Enables dropping or altering any properties of the user.

Engine

Permissions can be granted on engines to allow usage, operation and modification of engines per account.

Permission	Description
USAGE	Enables using the engine to execute queries.
OPERATE	Enables stopping and starting the engine.
MODIFY	Enables dropping or altering any properties of the engine.

Database

Permissions can be granted on databases to allow usage and modification of databases per account.

Permission	Description
USAGE	Enables using the database and attaching engines to it.
MODIFY	Enables altering the properties of a database and dropping it.
USAGE ANY SCHEMA	Enables using all current and future schemas in the database.
VACUUM ANY	Enables VACUUM on all existing and future tables in the database.

Schema

Permissions can be granted on schemas to allow usage and modification of schemas and their tables and views.

Permission	Description
USAGE	Enables using the schema.
MODIFY	Enables modifying the schema.
CREATE	Enables creating objects in the schema.
DELETE ANY	Enables delete on all existing and future tables in the schema.
INSERT ANY	Enables insert on all existing and future tables in the schema.
UPDATE ANY	Enables update on all existing and future tables in the schema.
TRUNCATE ANY	Enables truncate on all existing and futureall tables in the schema.
VACUUM ANY	Enables VACUUM on all existing and future tables in the schema.
MODIFY ANY	Enables modify and drop on all existing and future objects in the schema.
SELECT ANY	Enables select on all existing and future objects in the schema.

Performing actions on a schema and objects contained therein also requires the USAGE permission on its parent database.

Table

Permissions can be granted on tables to allow operations on them.

Permission	Description
DELETE	Enables deleting rows and dropping partitions from the table. Applicable only on managed tables.
INSERT	Enables inserting rows into the table. Applicable only on managed tables.
UPDATE	Enables updating rows in the table. Applicable only on managed tables.
TRUNCATE	Enables truncating the table. Applicable only on managed tables.
VACUUM	Enables VACUUM on the table. Applicable only to managed tables.
MODIFY	Enables modifying and dropping the table.
SELECT	Enables selecting rows from the table.

Performing actions on a table also requires:

the USAGE permission on the parent schema.
the USAGE permission on the parent database.

Aggregating Index

In order to create an aggregating index, a user must have:

the MODIFY permission on the table.
the CREATE permission on the parent schema.
the USAGE permission on the parent schema.
the USAGE permission on the parent database.

Dropping an aggregating index requires:

the MODIFY permission on the table.
the USAGE permission on the parent schema.
the USAGE permission on the parent database.

View

Permissions can be granted on views to allow usage and modification of views.

Permission	Description
SELECT	Enables using the view.
MODIFY	Enables modifying the view.

Performing operations on a view also requires:

the USAGE permission on the parent schema.
the USAGE permission on the parent database.

Owner rights

When selecting over a view, the user executing a query needs to have a SELECT privilege over the view. However, SELECT privileges over tables and views referenced in that view are enforced for the view owner rather than the user executing the query. For example:

CREATE USER user1 WITH ROLE=role1;
CREATE USER user2 WITH ROLE=role2;

CREATE TABLE base_table (a int); -- executed by user1
CREATE VIEW view_over_base_table AS SELECT * FROM base_table; -- executed by user1

GRANT SELECT ON VIEW view_over_base_table TO role2;
REVOKE SELECT ON TABLE base_table FROM role2;

SELECT * FROM base_table; -- executed by user2, fails with an authorization error
SELECT * FROM view_over_base_table; -- executed by user2, successfully

REVOKE USAGE ON SCHEMA public FROM role1;
-- role1 no longer has no access to the table due to missing schema usage privileges
SELECT * FROM view_over_base_table; -- executed by user2 and fails because the view owner's role1 cannot access table t

Create role

SQL

To create a custom role using SQL, use the CREATE ROLE statement. For example:

CREATE ROLE user_role;

UI

To create a custom role via the UI:

Govern > Roles

Click Govern to open the govern space, then choose Roles from the menu.
From the Roles management page, choose New role.
Enter a role name.
Choose the object type you want to grant permissions on for the role from the left-hand list; databases or engines.
Choose the permissions you want to grant for each object type. You can use the toggles at the top to grant permissions over all databases or engines, or you can define permissions more granularly on existing databases or engines using the table views, where you can also search by database or engine name.

Create role

Delete role

To delete a custom role using SQL, use the DROP ROLE statement. For example:

DROP ROLE user_role;

UI

To delete a custom role via the UI:

Click Govern to open the govern space, then choose Roles from the menu.
Search for the relevant role using the top search filters or by scrolling through the list. Hover over the right-most column to make the role menu appear, then choose Delete role.
Choose Confirm.

Delete role

Grant permissions to a role

SQL

To grant a permission to a role using SQL, use the GRANT statement. For example:

GRANT USAGE ON DATABASE my_db TO user_role;

UI

To grant a permission to a role via the UI:

Click Govern to open the govern space, then choose Roles from the menu:

Govern > Roles

Search for the relevant role using the top search filters, or by scrolling through the list of logins. Hover over the right-most column to make the role menu appear, then choose Edit role.
Choose the permissions tab for the object type you want to manage permissions for, then select the desired permissions. To grant permissions over all objects of that type, choose the topmost line.
Click Update.

Edit role

Grant role

SQL

To grant a role to a user or another role using SQL, use the GRANT ROLE statement. For example:

GRANT ROLE user_role TO ROLE user2_role;

UI

To grant a role to a user via the UI:

Click Govern to open the govern space, then choose Users from the menu:

Govern > Users

Search for the relevant user using the top search filters, or by scrolling through the list of logins. Hover over the right-most column to make the user menu appear, then choose Edit user details.
Check all the roles you want assigned to the user.
Click Update.

Edit user

Revoke permissions

SQL

To revoke a permission from a role using SQL, use the REVOKE statement. For example:

REVOKE USAGE ON DATABASE my_db FROM user_role;

UI

To revoke a permission from a role via the UI, follow the same steps above that you would to grant permissions.

Revoke role

SQL

To revoke a role from a user or another role using SQL, use the REVOKE ROLE statement. For example:

REVOKE ROLE user_role FROM USER alex;

UI

To revoke a role from a user or another role via the UI, follow the same steps above that you would to grant a role.

Check assigned privileges using SQL

To show all effective privileges of the user running the query:

SELECT
  AR.grantee,
  AR.role_name,
  OP.privilege_type,
  OP.object_type,
  OP.object_name
FROM information_schema.transitive_applicable_roles AS AR
JOIN information_schema.object_privileges AS OP
ON (AR.role_name = OP.grantee)
WHERE
  AR.grantee = session_user();

Returns:

grantee	role_name	privilege_type	object_type	object_name
test_user	account_admin	USAGE	engine	engine1
test_user	account_admin	USAGE	database	db1