Privileges #

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

The grantor of a privilege is not recorded as part of grant, it is only stored in the audit log. Issuing a grant with a specified grantor is not supported, and results in an error.

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 #

You can use the following privileges to control allowed actions on the account entity, so your specific Starburst Galaxy account:

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.

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

ALTER TABLE prod_data.monthly_sales.june_sales
SET AUTHORIZATION ROLE sysadmin

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.
• Views without explicit owners are disabled.
• Views configured with SECURITY DEFINER are disabled. The run-as identity for views with SECURITY DEFINER is the explicitly defined owner of the view.
• The catalog owner is not set as the run-as identity for views, and can therefore not execute them.
• The catalog owner can drop and alter views.

This behavior prevents a privilege escalation. You can not declare a view in an external system with limited privileges, and then execution the view in Starburst Galaxy with the catalog owner’s privileges.

Location privileges #

The location entity applies only to object storage catalogs. This permission is only checked for CREATE or ALTER operations of a table or schema when a non-default location property is provided in the SQL command. Use this privilege to allow or restrict creating or altering objects in unexpected object storage locations outside the default location, such as subdirectories or sibling directories of the default.

The default location for object storage is defined in the metastore. It can be explicitly defined for an entire catalog, such as with the Default directory name field in Galaxy’s catalog configuration for Glue and built-in metastores; or it can be implied. The implied default location for new schemas is a directory named the same as the schema and placed under the catalog’s location. For new tables, the default location is under the schema’s location.

The location privilege is not checked for SELECT, INSERT, UPDATE, or DELETE operations on object storage tables, where the table privileges manage access for that operation. To create a new or alter an existing schema or table in its default location, only the Create schema and Create table privilege is considered.

Object storage catalogs have a single location privilege:

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 the formats shown in the following examples.

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

The Create SQL checkbox and Add privileges buttons 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.

Depending on your object storage configuration, it can happen that you grant the Create SQL privilege to a particular URI for a particular role, but then this grant prevents granting the same privilege to a different role.

In this case, consider creating a placeholder role that has only the single location privilege granted for a URI, and no other settings. Then assign the placeholder role as a child role for other roles. The following example shows two placeholder roles with S3 URI locations. They are themselves child roles of the engineering and qa_ci roles, which inherit the settings of the placeholder roles.

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 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 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 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.monthly_sales" TO 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 wildcard. 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 by with a deny privilege grant to the role. For example, “parent_role” might inherit privilege Create user from “child_role”. That privilege can be reversed by granting a deny privilege for Create user to “parent_role”.

You can create deny privileges in the UI using the privileges dialog. Examples for using the SQL statement DENY related to the examples form the wildcard privileges section are the following:

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.