Data privileges #

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

Learn more about the basics of adding and removing privileges on the Galaxy privileges basics page.

The sections of this page cover the different data privileges that can be managed in Starburst Galaxy. This includes privileges that can be applied to catalogs, schemas, tables, views, and columns, as well as managing access to object storage locations and the rights to execute functions and SQL routines.

Management of account-level and cluster-level privileges is discussed in Account and cluster privileges.

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 privileges #

Tables have names, and are contained in a schema, which in turn is contained in a catalog. Tables may also have owners. Ownership of a table is automatically assigned to the creating role when the table is created using Galaxy, but if the table was created outside of Galaxy it does not have an explicit owner. If there is no explicitly specified role owning the table, then the role owning the table is the one that owns the schema. Similarly, if there is no explicitly specified role which owns the schema, then the role owning the catalog owns the table.

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

View privileges #

Privileges for views and materialized views follow the same semantics as tables, with minor differences. The following details apply for views:

• Views are stored in the metastore configured in the catalog.
• 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 are executed with the permissions of the view owner as that owner is specified in Galaxy.

• Because of this, 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 using the UI or with a SQL 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.

• In order to query a view, the executing role must have privileges on both the view and any tables the view was created from. For example, consider a SELECT statement on the above prod_data.monthly_sales.june_sales view where sysadmin is the view owner:
  • If the view is defined as SECURITY INVOKER, the querying role must have SELECT access on the view in addition to any tables the view was created from.
  • If the view is defined as SECURITY DEFINER, sysadmin is the role executing the query. Because of this, the role querying the view must have SELECT access on the view, but sysadmin must also have SELECT WITH GRANT OPTION on all tables the view was created from. If not, this could represent a privilege escalation. Unless sysadmin has GRANT OPTION on the underlying tables, it could grant table access to others it should not have the authority to by creating a view.
  • SELECT WITH GRANT OPTION is not required on SECURITY DEFINER views when the role running the query has the view owner’s role in its active role set.

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.

Data products privileges #

The View data product privilege lets you grant a role access to view the description details of an individual data product, but not to query the underlying schema.

This privilege is useful for cases where a role lacks the Select from table privilege for the schema that forms the basis of a data product. In this case, the role cannot see the data product or query it, or query the schema directly. This privilege lets you partially override this limitation to allow roles to see that the data product exists, but not query it without being added to a different role that has the Select from table privilege for the underlying schema.

Note that adding this privilege to a role that has or inherits the Select from table privilege does not remove the ability to query the data product. Only when a data product’s schema is not selectable is the data product invisible. To render a data product invisible to a role, you might need to remove the Select from table privilege from the public role, whose privileges most other roles inherit.

A single privilege in the data products category is available to grant to any role:

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.*.*.