Privileges and entities #

A privilege granted to a role conveys the right to perform specific operations.

Privileges #

The grantor of a privilege is not recorded as part of grant, it is only stored in the audit log.

Entity privileges grant rights to a single entity or a collection of entities of the same kind, for example, the right to SELECT rows from a specific table, the entity in this case.

Account privileges are not associated with a specific entity. Account privileges include rights to create new top-level entities, such as clusters, catalogs and users, and rights to manage security for all entities.

Grant a privilege #

One role can be used to grant a privilege for a specific entity to any other role under the following circumstances:

• Role includes the grant WITH ADMIN OPTION
• Role is the owner of the entity.
• Role has the Manage security role.

Account privileges #

Use the following privileges to control allowed actions at the Account level. Adding privileges at the Account level determines the access rights to your Starburst Galaxy account as a whole:

Cluster privileges #

You can use the following privileges to control allowed actions on the cluster entities:

Catalog privileges #

Catalogs are identified by catalog name, and catalog names must be unique within your Starburst Galaxy account. The only privilege on a catalog is Create schema, which allows a role with that privilege to create new schemas in the catalog.

A catalog is visible to a role in any of the following situations:

• The role has the Create schema privilege on the catalog.
• The role has some privilege on a schema or table contained in the catalog.
• The role is the catalog owner.
• The role has the Manage security privilege.

When a catalog is created, the creating user is prompted to specify roles that can read from and/or write to the catalog. These roles are granted wildcard permissions on schemas and tables in the catalog.

Schema privileges #

Schemas have names and are contained in a catalog. By default, the role that owns the catalog containing the schema also owns the schema, but you can change the schema owner in the UI and in Trino.

In SQL, you can set the owner of a schema monthly_sales in catalog prod_data to role sysadmin this way:

ALTER SCHEMA prod_data.monthly_sales
SET AUTHORIZATION ROLE sysadmin

You can see the current owner of a schema from the SHOW CREATE statement:

SHOW CREATE SCHEMA prod_data.monthly_sales

A schema can contain one or more tables and/or views.

Table and view privileges #

Tables and views have names, and are contained in a schema, which in turn is contained in a catalog. By default, the role that owns the schema containing the table owns the table. If there is no explicitly specified role owning the schema, then the role owning the table is the one that owns the catalog.

Tables have the following privileges that can be granted either with SQL commands or in the Starburst Galaxy UI:

The following details apply for views.

• Views are stored in the metastore.
• The catalog owner can drop and alter views.
• Views created with SECURITY INVOKER outside of Galaxy are executed as expected in Galaxy. The view is run with the set of permissions granted to the currently executing role.

• Views created with SECURITY DEFINER outside of Galaxy do not execute until you define an owning role for the view. You must explicitly set an owning role with a statement like the following:

  ALTER VIEW view_name SET AUTHORIZATION ROLE role_name;
  

  For example, for a view june_sales in schema monthly_sales in catalog prod_data, set the owner to role sysadmin as follows:

  ALTER VIEW prod_data.monthly_sales.june_sales
    SET AUTHORIZATION ROLE sysadmin;
  

  SECURITY DEFINER views do not assume an owning role because that could result in a privilege escalation. The SECURITY DEFINER syntax restricts the permissions with which a view can be run. If that view is defined in a system outside of Galaxy, then Galaxy cannot make any assumptions about the roles that can be used to execute the view. The outside system and Galaxy have separate security domains. This is why Starburst Galaxy requires the user to explicitly set the owning role for views.

Column privileges #

The Column privilege grants SELECT or UPDATE privileges to a column within a table. This privilege is commonly used to deny access to a column, specifically a column containing sensitive financial or personal identifying information.

If a query is run on a table that has columns on which SELECT is denied, those columns are omitted from the result set.

To grant a SELECT or UPDATE privilege, a role in the active role set must have a table-level grant of the same privilege with grant option.

Location privileges #

The location privilege applies only to object storage catalogs. Use this privilege to restrict creating or altering objects in unexpected object storage locations outside the configured default location of the current catalog.

Your cloud provider credentials, such as an IAM role or AWS key for S3 catalogs, may have broad access rights to multiple locations. If you provide these credentials when creating a catalog for a narrow location, users connecting to that catalog may have the inadvertently granted right to make changes outside that narrow location.

Let’s say you create a catalog to access the S3 location s3://bucket-name/folder-name/, but you create the catalog with an AWS key that has write rights throughout all of s3://bucket-name/*. This allows anyone connecting to this catalog to create, for example, s3://bucket-name/different-folder.

If you set the location privilege for a role to s3://bucket-name/folder-name/*, you restrict creation or alteration of schemas or tables to the folder-name location only for that role.

The location privilege is only verified for CREATE or ALTER operations of a table or schema, because these are the only operations capable of creating objects outside the default location of the catalog. If that operation is within the configured location of the catalog, the catalog privileges and schema privileges are also considered.

The location privilege is not checked for SELECT, INSERT, UPDATE, or DELETE operations on object storage tables, where table privileges manage access instead.

A single location privilege is available to grant to any role:

The location privilege can only be set with the Starburst Galaxy UI, and not with a SQL command. The Create SQL privilege does not correspond to any SQL statement.

When you select the Location button, Galaxy prompts you to enter an object storage URI. Use a format similar to the following examples, and adapted to your object storage URI pattern and naming:

s3://*
s3://bucket-name/abc/xyz/*
gs://*
gs://bucket-name/abc/xyz/*
abfs://file-system@storage-account.dfs.core.windows.net/*
abfs://file-system@storage-account.dfs.core.windows.net/abc/xyz/*

The Create schema and table in location checkbox and Save privileges button do not activate until you enter a URI with a valid format ending with /*. However, Galaxy does not check the specified URI’s location for validity.

Function privileges #

Function privileges allow you to control access to table functions in specific catalogs and for specific roles.

Query pass-through table function #

The query or raw_query query pass-through table functions are available in the following catalogs:

• Elasticsearch, raw_query
• MongoDB, query
• MySQL, query
• PostgreSQL, query
• SQL Server, query

By default, access to these functions is disabled.

Query pass-through allows you to use the native query language of the underlying data source to compose a query string, and pass it directly to the data source as argument for a table function. This query needs to return a table as result set of at least one column with one value:

SELECT * FROM TABLE(
    example.system.query(query =>
       'query_string'
    )
)

The invocation references the catalog name example and accesses the query or raw_query in the system schema. The query and raw_query table functions are always located in the system schema. The supplied query_string in the native query language must be valid in the data source. It is typically the SQL dialect of the database, but can also be another query language such as the Elasticsearch Query DL in the case of an Elasticsearch data source. The native query returns a table, which in turn can be used in the containing SQL query like any other table reference. In the preceding example, all columns and rows of the table are returned as provided by the table function, due to the SELECT * FROM.

Google Sheets table functions #

The sheet table function is available in the Google Sheets catalog.

By default, the function privilege catalog-name.system.sheet is automatically created and the Execute sheet table function selection is enabled when you create the catalog.

The sheet table function allows you to query a sheet directly without specifying it as a named table in the metadata sheet:

sheet(id, range) => table

For usage examples, see How to use the Google Sheets catalog on the Google Sheets catalog page.

Wildcard privileges #

Wildcard permissions are useful for catalogs managed outside of Starburst Galaxy, as they define privileges on tables and schemas that do not exist yet.

Many catalogs have large numbers of schemas and tables, and it is not practical to grant privileges on each one individually. Instead, wildcard privilege grants can be used to grant privileges on every table in a specific schema or catalog. Wildcard privileges are supported only for schema and table entities.

Table wildcard privilege grants #

Tables are identified by the name of a catalog, the name of a schema contained in the catalog, and the name of a table within the schema. Table june_sales in schema monthly_sales in catalog prod_data is represented as prod_sales.monthly_sales.june_sales.

Tables support privileges to SELECT, INSERT, DELETE and UPDATE the table. A table wildcard represents either all tables in a catalog regardless of the schema name, or all tables in a specific schema inside a catalog. A grant of SELECT on any table contained in any schema in the catalog prod_data is denoted as prod_data.*.*. Similarly, a grant of SELECT on any table in schema monthly_sales in catalog prod_data is denoted by prod_data.monthly_sales.*.

You can grant wildcard table privileges using the UI Add privileges dialog. Alternatively, you can grant wildcard table privileges using SQL. A grant of SELECT on all tables in all schemas in catalog prod_data to role read_only_users looks like:

GRANT SELECT ON "prod_data"."*"."*" TO ROLE read_only_users

The schema and table wildcards must be in double quotes because * has special meaning in SQL.

Similarly, a grant of SELECT on all tables in schema monthly_sales in catalog prod_data to role read_only_users looks like:

GRANT SELECT ON "prod_data.monthly_sales"."*" to ROLE read_only_users

You can create exceptions to wildcard table grants using deny table grants. Suppose a role has received a wildcard grant of SELECT on prod_data.*.*, allowing SELECT on any table in any schema in catalog prod_data. If the role is denied the SELECT for wildcard prod_data.monthly_sales.*, all tables in schema monthly_sales will be inaccessible to that role no matter what other grants the role has received.

Schema wildcard privilege grants #

Schemas support just one privilege – Create table. A schema wildcard consists of a catalog name and a schema name of *. If the name of the catalog is prod_data, a grant of CREATE_TABLE to schema prod_data.* grants the privilege CREATE_TABLE on all schemas in catalog prod_data to a role. This is done in the UI in the Add privileges dialog.

You can grant Create table to a role in Trino:

GRANT CREATE ON SCHEMA "prod_data.*" TO ROLE prod_maintainer_role

You can create exceptions to a wildcard schema privilege grant by creating deny privilege grants in both the UI and in SQL. Given a grant of CREATE_TABLE to all schemas in catalog prod_data, denoted by prod_data.*, granting a deny privilege for schema prod_data.monthly_sales to the role creates an exception that denies the schema privilege CREATE_TABLE on schema monthly_sales to the role.

Read only access with wildcard privileges #

Catalog creation includes an optional step to assign read only access for a catalog. You can achieve the same behavior by creating a wildcard privilege for all schemas and tables in a catalog mycatalog.*.* that grants the SELECT privilege.

Full read and write access with wildcard privileges #

Catalog creation includes an optional step to assign full read and write access for a catalog. You can achieve the same behavior with the following setup:

• Grant the Create schema privilege on the catalog.
• Grant a wildcard privilege of CREATE_TABLE to any schema in the catalog mycatalog.*.
• Grant a wildcard privilege of SELECT to any table in any schema in the catalog mycatalog.*.*.
• Grant a wildcard privilege of INSERT to any table in any schema in the catalog mycatalog.*.*.
• Grant a wildcard privilege of UPDATE to any table in any schema in the catalog mycatalog.*.*.
• Grant a wildcard privilege of DELETE to any table in any schema in the catalog mycatalog.*.*.

Deny a privilege #

The effect of a privilege grant on an entity to a role can be reversed by granting a deny privilege to that role. This is most useful for table privileges, but any inherited privilege on any entity can be denied. Learn more details in wildcard privileges.

A deny privilege grant can create an exception to a wildcard privilege grant, preventing the access that the privilege grant previously allowed.

Deny privilege grants always override grants that allow access, including any inherited from roles and wildcards. Deny privilege grants should therefore be rarely used, and are typically used only to override a wildcard grant.

Deny privileges are most useful for schemas and tables, to create exceptions to wildcard privilege grants. However, any privilege grant to a role or inherited from a child role on any entity can be reversed with a deny privilege grant to the role. For example, “upper_role” might inherit privilege Create user from “lower_role”. That privilege can be reversed by granting a deny privilege for Create user to “upper_role”.

You can create deny privileges in the UI using the privileges dialogs. The following examples of using the SQL statement DENY correspond to the examples from the wildcard privileges section:

You can deny table privileges to a role with SQL:

DENY SELECT ON "prod_data.monthly_sales"."*" TO read_only_users

You can deny a CREATE_TABLE privilege on the schema monthly_sales this way:

DENY CREATE ON SCHEMA prod_data.monthly_sales

SQL does not provide a mechanism to grant or deny privileges on clusters, users, or catalogs.

Entities #

The Starburst Galaxy access control system manages privileges to access all entities – roles, users, clusters, catalogs, schemas, tables, object storage locations, and functions.

Entities and SQL privileges #

The following table lists the available types of entities and their associated privileges, that can be managed with SQL statements.

Entities with No SQL privileges in the Privileges column can only be managed with the Starburst Galaxy UI, and not with SQL commands.