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 <table_name> TO <role_name>;	REVOKE SELECT ON TABLE <table_name> FROM <role_name>;
INSERT	Allows inserting rows into the table. Applies to managed tables only.	GRANT INSERT ON TABLE <table_name> TO <role_name>;	REVOKE INSERT ON TABLE <table_name> FROM <role_name>;
MODIFY	Allows modifying and dropping the table.	GRANT MODIFY ON TABLE <table_name> TO <role_name>;	REVOKE MODIFY ON TABLE <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 <table_name> TO <role_name>;	REVOKE UPDATE ON TABLE <table_name> FROM <role_name>;
TRUNCATE	Allows truncating a table. Applies to managed tables only.	GRANT TRUNCATE ON TABLE <table_name> TO <role_name>;	REVOKE TRUNCATE ON TABLE <table_name> FROM <role_name>;
VACUUM	Allows running the VACUUM operation. Applies to managed tables only.	GRANT VACUUM ON TABLE <table_name> TO <role_name>;	REVOKE VACUUM ON TABLE <table_name> FROM <role_name>;
ALL [PRIVILEGES]	Grants all privileges over the table to a role.	GRANT ALL ON TABLE <table_name> TO <role_name>;	REVOKE ALL ON TABLE <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 modifying table permissions

The following example use GRANT to grant permissions. You can also replace GRANT with REVOKE in any of the examples to remove any granted privileges.

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;

ALL permissions

The following code example grants the role developer_role with all permissions on the table games:

GRANT ALL 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.