Table permissions

In Firebolt, a table is a structured data object within a database, composed of rows and columns. Tables are the foundational units for organizing, querying, and managing data in your Firebolt data warehouse. Table-level permissions allow roles to perform actions such as selecting, modifying, or managing data within specific tables.

To perform actions on a table, roles must also have USAGE permissions on both the parent schema and the parent database of the table.

Table-level privileges

Privilege	Description	GRANT Syntax	REVOKE Syntax
SELECT	Allows selecting rows from the table.	GRANT SELECT ON <table_name> TO <role_name>;	REVOKE SELECT ON <table_name> FROM <role_name>;
INSERT	Allows inserting rows into the table. Applies to managed tables only.	GRANT INSERT ON <table_name> TO <role_name>;	REVOKE INSERT ON <table_name> FROM <role_name>;
MODIFY	Allows modifying and dropping the table.	GRANT MODIFY ON <table_name> TO <role_name>;	REVOKE MODIFY ON <table_name> FROM <role_name>;
DELETE	Allows deleting rows and dropping partitions from the table. Applies to managed tables only.	GRANT DELETE ON TABLE “<table_name>” TO <role_name>;	REVOKE DELETE ON TABLE “<table_name>” FROM <role_name>;
UPDATE	Allows updating rows in the table. Applies to managed tables only.	GRANT UPDATE ON <table_name> TO <role_name>;	REVOKE UPDATE ON <table_name> FROM <role_name>;
TRUNCATE	Allows truncating a table. Applies to managed tables only.	GRANT TRUNCATE ON <table_name> TO <role_name>;	REVOKE TRUNCATE ON <table_name> FROM <role_name>;
VACUUM	Allows running the VACUUM operation. Applies to managed tables only.	GRANT VACUUM ON <table_name> TO <role_name>;	REVOKE VACUUM ON <table_name> FROM <role_name>;

To grant permissions across all tables in a schema, use schema-level privileges. For example, privileges like SELECT ANY, INSERT ANY, or DELETE ANY at the schema level will apply to all current and future tables within that schema.

Aggregating Indexes

An aggregating index in Firebolt accelerates queries involving aggregate functions on large tables. This reduces compute usage and improves query performance.

To create or drop an aggregating index, a role must have the following permissions:

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

To drop an aggregating index, the role requires:

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

Examples of granting table permissions

SELECT permission

The following code example grants the role developer_role permission to read data from the games table:

GRANT SELECT ON TABLE "games" TO developer_role;

INSERT permission

The following code example gives the role developer_role permissions to insert rows into the games table:

GRANT INSERT ON TABLE “games” TO developer_role;

MODIFY permission

The following code example grants the role developer_role permission to alter or drop the games table:

GRANT MODIFY ON TABLE “games” TO developer_role;

DELETE permission

The following code example gives the role developer_role permission to delete rows or partitions from the games table:

GRANT DELETE ON TABLE “games” TO developer_role;

UPDATE permission

The following code example grants the role developer_role permission to update rows in the games table:

GRANT UPDATE ON TABLE “games” TO developer_role;

TRUNCATE permission

The following code example gives the role developer_role permission to truncate the games table, removing all rows:

GRANT TRUNCATE ON TABLE “games” TO developer_role;

VACUUM permission

The following code example grants the role developer_role permission to run the VACUUM operation on the games table:

GRANT VACUUM ON TABLE “games” TO developer_role;

Considerations

• Use the REVOKE statement to remove any granted privileges. Replace GRANT with REVOKE in the examples above.
• Table-level permissions apply only to the specified table. For broader control, consider granting schema-level privileges.