Simplifying Role-Based Access in Firebolt

Governance in Firebolt

In Firebolt, a scope refers to the boundaries within which access permissions are granted, determining the level at which roles and permissions are applied to resources. Firebolt’s object model is hierarchical, where higher-level objects can contain related lower-level objects, and permissions flow from the top down.

There are two main types of scopes in Firebolt: Organization (Global) and Account (Regional).

‍

‍

Organizations (Global)

The Organization's scope is synonymous with the Global scope, meaning that an Organization serves as the top-level container for all resources within Firebolt. An organization is the highest-level entity in Firebolt's hierarchy. The global scope enables centralized control, ensuring consistency in configurations such as authentication and network policies across the entire Firebolt deployment while also providing administrators with an aggregated view of billing information across all accounts. 

Accounts (Regional)

The Account scope operates within the regional context, serving as a container for resources specific to a particular region. Accounts are nested under organizations and act as isolated tenants, meaning that resources granted at this level apply only within the account and are not shared with other accounts. For example, databases, tables, and schemas are managed at the account level and are isolated to that account. The regional scope allows for fine-grained access control, ensuring that permissions can be tailored to specific environments or teams while maintaining isolation between different regions or projects within the organization.

Customers can create any number of accounts in any of the regions Firebolt operates in. 

RBAC (Role-Based Access Control)

The Principle of Least Privilege:

The principle of least privilege dictates that users should only be granted the minimum permissions necessary for them to carry out their tasks. This reduces the risk of accidental or malicious misuse of resources. In practical terms, a user should only be able to access data or perform actions that are directly related to their role or job function.

Role-Based Access Control (RBAC) in Firebolt:

Authorization is the process of determining what actions a user is allowed to perform within a system, based on their identity. It ensures users access only the resources and tasks they are explicitly permitted to, which is a key security measure for preventing unauthorized access or misuse. 

RBAC helps enforce this by assigning users to predefined roles, each containing a specific set of permissions.These roles define what actions a user can perform on what resources within the system. For example a user might be assigned the role of "Data Analyst." This role could include permissions to run queries, view reports, and access certain datasets, but not the ability to delete or modify the underlying data. In this way, roles align with job responsibilities and ensure that users only have access to the resources they need.

The Firebolt RBAC system enables operators to tightly control access to resources like databases, tables, and other objects. The operator can define which actions can be performed on these resources by specific roles.

For instance, a "data_engineer" role in Firebolt might have permission to create and modify tables but might not be allowed to view data in certain tables, while a "data_viewer" might only be allowed to view specific datasets without making any changes. Through RBAC, administrators can ensure that users only have the necessary privileges for their role and job function, adhering to the principle of least privilege.

Practical Examples:

• account_admin: Has full access to all resources in an account
• Read-Only: Can only view data but cannot make any modifications.
• data_scientist_role: Can start & use engines, run queries and view data, but cannot modify schemas or delete tables.

Firebolt’s RBAC system allows you to define these roles in a way that matches your organization's security policies, ensuring that users only have access to the resources they need to perform their jobs. Below, we’ll walk through a few examples demonstrating how to administer RBAC based on an organization's needs. 

Creating Objects & the Concept of Ownership 

As an administrator, you may need to grant a user permission to create resources, such as databases or tables, within your account. In Firebolt, you can grant the CREATE permission to a user. At the account level, you can grant the ability to create Databases, Users, Engines, Schemas, Tables, and Roles. Relevant grant statements for each resource can be found in our documentation. 

GRANT CREATE DATABASE TO developer_role;

When a user creates an object, they automatically become the owner of that object. Ownership gives the creator full control over objects they’ve created, allowing them to perform all operations on the object without needing additional permissions. This ensures a seamless onboarding experience, as newly created objects are immediately available for use. 

‍All objects created in a firebolt account have an owner associated with them. Because the creator has full control over owned objects, CREATE permissions should be granted carefully. Once granted, users will have the ability to create and fully manage their resources. Always ensure that users who receive CREATE permissions understand the responsibility of managing the objects they create.

Transferring Ownership 

In Firebolt, account administrators have the ability to transfer ownership of objects, such as databases, tables, or other resources, from one user to another. This allows administrators to maintain control over the resources in the account and to reassign ownership as needed for operational or organizational reasons.

There are several scenarios where changing object ownership might be necessary:

1. User Role Changes: If a user who created an object leaves the organization or changes roles, the administrator may need to transfer ownership of that object to another user to ensure continued access and management. 
2. Security and Access Control: In situations where an object should no longer be managed by its original creator (for example, due to a security concern or change in responsibilities), administrators can transfer ownership to another user.
3. Resource Management: If resources are being reorganized or consolidated, changing ownership can help ensure that the right users have control over the right resources. 

As an account administrator, you can view who owns a particular object by querying the relevant information schema view: 

‍

‍

For more info, please reference our docs page on Ownership.

For example, I can view all engines in my account, as well as their status, the URL for the engine, and the username of the engine’s owner. 

SELECT engine_name, engine_owner, status, url FROM information_schema.engines

I’ll transfer ownership of engine ‘my_engine’ to user testuser: 

ALTER engine my_engine OWNER to testuser;

To validate, I’ll query the engines information schema view: 

SELECT engine_name, engine_owner, status, url FROM information_schema.engines

The ownership of the engine ‘my_engine’ has been transferred to a different user. If the previous owner wants to operate or use the engine, they will now need to be explicitly granted permission.

Roles & Inheritance

In Firebolt, Role-Based Access Control (RBAC) governs permissions through roles, similar to the model in PostgreSQL. A role can be granted permissions, and those permissions can be inherited by other roles, creating a hierarchy of permissions across users and database objects. 

‍

Granting Permissions

Roles can be granted to Users or Roles. When a role (let's call it Role A) is granted to another role (let's call it Role B), Role B inherits the permissions of Role A. 

GRANT role "role_a" TO "role_b";

‍

In this scenario, Role B automatically inherits all permissions granted to Role A. This creates a hierarchical structure where child roles inherit permissions from their parent roles.

For example, if Role C is granted to Role B, Role B will inherit the permissions from both Role A and Role C, forming a chain of inherited permissions. Importantly, this inheritance is unidirectional; Role A does not inherit permissions from Role B. It is always the child role that inherits permissions from the parent role.

When a user is assigned multiple roles, the user's effective permissions are the union of all the permissions granted to those roles. As an example, Role A gives permission to USE and OPERATE an Engine, while ROLE C gives permission to USE any database in account-1, access the public schema in db_1, and select data from any table in db_1. If a user is granted Role A & Role C, the user will have all privileges granted by Role A & Role C. 

‍

Revoking Permissions 

If permissions are revoked from a role, they are removed from that role. If there is a duplicate grant in a parent role, that role will still retain its permissions unless that grant is revoked from that specific role. This is because roles retain their own permissions independently. This behavior is consistent in both PostgreSQL and in Firebolt.

REVOKE SELECT ON TABLE customer_pii FROM role_c;

Additionally, a role cannot deny permissions inherited from another role. It can only inherit them. If an administrator attempts to revoke a permission that does not exist in a role, the operation will succeed with a no-op.

Use cases

Granting Read-Only Access to All Tables and Views Across Multiple Databases

As an administrator, you may have a service that needs to query data across multiple databases within your Firebolt account, but you want to ensure that the service can only read from the tables and views, without being able to modify any data. Let’s assume that you have a production account containing five databases, and you need to grant a service role read-only access to all tables and views across these databases. Here’s how to set this up:

1. Create a Service Account (Global Resource)
   Associate it with a user in your production account (service accounts can have many users associated with them, enabling service accounts to interact with resources accounts)
2. Create an Engine
   Engines enable you to interact with your data. Your service will need usage privileges on the engine. If the service in question will also start/stop the engine, it will need operate privileges. 
3. Create a Role 
4. Create grant statements

-- Enable the ability to use an engine to query databases 
GRANT USAGE ON ENGINE "prodEngine" TO read_role;

-- Enable the ability for your service to start/stop the engine:
GRANT OPERATE ON ENGINE "prodEngine" To read_role;

-- Allows access to the database and enables attaching engines to it 
GRANT USAGE ON DATABASE db_1 TO read_role;
GRANT USAGE ON DATABASE db_2 TO read_role;
GRANT USAGE ON DATABASE db_3 TO read_role;
GRANT USAGE ON DATABASE db_4 TO read_role;
GRANT USAGE ON DATABASE db_5 TO read_role;

 
-- Allow the ability to USE objects in the public schema for each database
GRANT USAGE ON SCHEMA public IN DATABASE db_1 TO read_role; 
GRANT USAGE ON SCHEMA public IN DATABASE db_2 TO read_role;
GRANT USAGE ON SCHEMA public IN DATABASE db_3 TO read_role;
GRANT USAGE ON SCHEMA public IN DATABASE db_4 TO read_role;
GRANT USAGE ON SCHEMA public IN DATABASE db_5 TO read_role;



-- Allow the ability to read from any tables or views in the public schema
GRANT SELECT ANY ON SCHEMA public IN db_1 TO read_role;
GRANT SELECT ANY ON SCHEMA public IN db_2 TO read_role;
GRANT SELECT ANY ON SCHEMA public IN db_3 TO read_role;
GRANT SELECT ANY ON SCHEMA public IN db_4 TO read_role;
GRANT SELECT ANY ON SCHEMA public IN db_5 TO read_role;

Note: To grant usage privileges on all current and future databases within an account, use the following Grant Statement:

GRANT USAGE ANY DATABASE ON ACCOUNT <account_name> TO <role_name>;

‍

Granting Write-Only Permissions to Manage Data in a Database

As an administrator, you have a service account that is used by an ETL (Extract, Transform, Load) service running on your cloud infrastructure. The service performs data processing tasks that require the need to insert, update, truncate, and delete records in your Firebolt data warehouse as part of an automated pipeline.

The service needs to interact with the database, but it should never have any permissions that allow it to read or manage your firebolt objects. To ensure the principle of least privilege, the service account is granted only the permissions necessary to perform its data-modifying tasks. 

-- Enable the ability to use an engine to query databases 
GRANT USAGE ON ENGINE "prodEngine" TO write_role;

-- Enable the ability for your service to start/stop the engine:
GRANT OPERATE ON ENGINE "prodEngine" To write_role;

-- Allows access to the database and enables attaching engines to it. This is required for any operations on child resources(i.e inserts into tables) to work
GRANT USAGE ON DATABASE db_1 TO write_role;

-- Allow the ability to USE objects in the public schema in database db_1
GRANT USAGE ON SCHEMA public IN DATABASE db_1 TO write_role; 

-- Allow the ability to insert into any tables
GRANT INSERT ANY ON SCHEMA public IN DATABASE db_1 TO write_role;

-- Allow the ability to update data in any tables
GRANT UPDATE ANY ON SCHEMA public IN DATABASE db_1 TO write_role;

-- Allow the ability to truncate data in any table, but not allow the deletion of any table
GRANT TRUNCATE ANY ON SCHEMA public IN DATABASE db_1 TO write_role;

-- Allow the ability to delete rows and partitions from all current and future tables 
GRANT DELETE ANY ON SCHEMA public IN DATABASE db_1 TO write_role;

‍

Automating the Granting/Revoking of Roles using a Service Account

Using Firebolt’s API in tandem with a Service Account, customers can automate the process of Granting & Revoking privileges across their data-warehouse implementation. 

Imagine the following scenario: A customer has 100 databases with multiple tables in each. They want to create a viewer role that enables a set of users to be able to select from any of the tables in any database. In the example below, we create a shell script to automate the creation of these grants.

#!/bin/bash

if [ -z "$FIREBOLT_API_URL" ] || [ -z "$FIREBOLT_TOKEN" ]; then
  echo "ERROR: FIREBOLT_API_URL or FIREBOLT_TOKEN is not set."
  exit 1
fi

catalog_response=$(curl --location "$FIREBOLT_API_URL" \
    --header "Authorization: Bearer $FIREBOLT_TOKEN" \
    --data "SELECT catalog_name FROM information_schema.catalogs;")

# Extract database names from information schema.catalog 
databases=$(echo "$catalog_response" | jq -r '.data[].catalog_name')

# Check if databases are retrieved
if [ -z "$databases" ]; then
  echo "No databases found in the catalog."
  exit 1
fi

role="role123"

# loop through each db
for db in $databases; do
  echo "Granting USAGE on schema or database: $db"

  # grant usage on schema for each db
  curl --location "$FIREBOLT_API_URL" \
    --header "Authorization: Bearer $FIREBOLT_TOKEN" \
    --data "GRANT USAGE ON SCHEMA public IN DATABASE $db TO $role;"

  # Grant SELECT on all tables for each db
  echo "Granting SELECT on all tables in public schema for database: $db"
  curl --location "$FIREBOLT_API_URL" \
    --header "Authorization: Bearer $FIREBOLT_TOKEN" \
    --data "GRANT SELECT ANY ON SCHEMA public IN DATABASE $db TO $role;"

done

‍

A Robust & Flexible RBAC System

Firebolt’s RBAC system provides administrators with granular control over their data-warehouse deployment, ensuring that users only have the permissions they need to perform their function. 

By assigning roles based on job responsibilities, organizations can minimize risk, uphold data confidentiality, and enforce the principle of least privilege. For detailed guidance on managing RBAC in Firebolt, explore our comprehensive documentation.