Skip to main content

GRANT TO ROLE

Access to Dremio objects can be managed by granting privileges to roles. A privilege is the right to perform a specific action on an object.

Syntax
GRANT { objectPrivilege | ALL } ON { <object_type> <object_name> }
TO ROLE <role_name>
Syntax for granting privileges on all folders in an Arctic catalog
GRANT { objectPrivilege } ON ALL FOLDERS IN CATALOG { <object_name> }
TO ROLE <role_name>
Syntax for granting privileges on all tables and views in an Arctic catalog
GRANT { objectPrivilege } ON ALL DATASETS IN CATALOG { <object_name> }
TO ROLE <role_name>
note

The DELETE, INSERT, TRUNCATE, and UPDATE privileges are supported only for Iceberg tables.

The CREATE TABLE and DROP privileges are supported only for sources that support mutability.

objectPrivilege
-- On Organizations
{ CONFIGURE SECURITY | CONFIGURE BILLING | CREATE CATALOG | CREATE CLOUD | CREATE PROJECT | CREATE ROLE | CREATE USER | MANAGE GRANTS | OWNERSHIP } [, ...]
-- On Clouds
{ MANAGE GRANTS | MODIFY | MONITOR | OWNERSHIP } [, ...]
-- On Projects
{ ALTER | ALTER REFLECTION | CREATE SOURCE | CREATE TABLE | DELETE | DROP | EXTERNAL QUERY | INSERT | MANAGE GRANTS | MODIFY | MONITOR | OPERATE | OWNERSHIP | SELECT | UPDATE | USAGE | VIEW JOB HISTORY | VIEW REFLECTION } [, ...]
-- On Engines
{ MANAGE GRANTS | MODIFY | MONITOR | OPERATE | OWNERSHIP | USAGE } [, ...]
-- On Identity and Token Providers
{ MODIFY | MONITOR | OPERATE } [, ...]
-- On non-Arctic Sources
{ ALTER | ALTER REFLECTION | CREATE TABLE | DELETE | DROP | EXTERNAL QUERY | INSERT | MANAGE GRANTS | MODIFY | OWNERSHIP | SELECT | TRUNCATE | UPDATE | VIEW REFLECTION } [, ...]
-- On Folders
{ ALTER | ALTER REFLECTION | CREATE TABLE | DELETE | DROP | INSERT | MANAGE GRANTS | OWNERSHIP | SELECT | SHOW | TRUNCATE | UPDATE | VIEW REFLECTION } [, ...]
-- On Tables in non-Arctic sources
{ ALTER | DELETE | INSERT | MANAGE GRANTS | OWNERSHIP | SELECT | TRUNCATE | UPDATE } [, ...]
-- On User-Defined Functions
{ OWNERSHIP }
-- On Views in non-Arctic sources
{ ALTER | DELETE | INSERT | MANAGE GRANTS | OWNERSHIP | SELECT | TRUNCATE | UPDATE } [, ...]
-- On Scripts
{ VIEW | MODIFY | DELETE | MANAGE GRANTS }
-- On Roles
{ OWNERSHIP }
-- On Users
{ OWNERSHIP }
-- On Arctic catalogs and sources
{ COMMIT | CREATE BRANCH | CREATE TAG | MANAGE GRANTS | OWNERSHIP | USAGE | MODIFY }
-- On Tables in Arctic catalogs
{ ALTER REFLECTION | MANAGE GRANTS | SELECT | VIEW REFLECTION | WRITE }
-- On Views in Arctic catalogs
{ ALTER REFLECTION | MANAGE GRANTS | SELECT | VIEW REFLECTION | WRITE }

Parameters

<objectPrivilege> String

The privilege(s) to be granted to the role. A comma-separated list of privileges can be specified. For more information, read Privileges.


<object_type> String

The name of the type of object on which the specified privilege is being granted.

Enum: ORG, CLOUD, PROJECT, ENGINE, CATALOG, ROLE, USER, SOURCE, IDENTITY PROVIDER, EXTERNAL TOKEN, FOLDER, FUNCTION, TABLE, VIEW


<object_name> String

The name of the object on which the privilege is being granted. Object names need to be qualified with the path if they are nested.

note

For <object_type> ORG or PROJECT, the <object_name> is inferred and should be omitted from the statement.


AT { REF[ERENCE] | BRANCH | TAG | COMMIT } <refValue> String   Optional

For tables and views in an Arctic catalog, reference to the specific branch, tag, or commit in the catalog where you want to run the SQL command. If you do not specify a reference value in the command for a table or view in an Arctic catalog, the command runs on the catalog's default branch.


<role_name> String

The name of the role to which the privilege is being granted.

Examples

Grant CREATE PROJECT and CREATE CLOUD privileges on the organization to a role
GRANT CREATE PROJECT, CREATE CLOUD
ON ORG
TO ROLE "DATA_ENGINEER"
Grant MODIFY privilege on a cloud to a role
GRANT MODIFY, MONITOR
ON CLOUD "Default Cloud"
TO ROLE "DATA_ENGINEER"
Grant OWNERSHIP privilege on a role
GRANT OWNERSHIP ON ROLE data_engineer TO USER user1
Grant a OPERATE privilege on an engine to a role
GRANT OPERATE
ON ENGINE "reflections_engine"
TO ROLE "DATA_ENGINEER"
Grant VIEW JOB HISTORY privilege to a role
GRANT VIEW JOB HISTORY ON SYSTEM TO ROLE "DATA ANALYST"
Grant OWNERSHIP privilege on an Arctic catalog or source to a role
GRANT OWNERSHIP ON CATALOG prodCatalog TO ROLE data_engineer
Grant SHOW privilege on all folders in an Arctic catalog to all users
GRANT SHOW ON ALL FOLDERS IN CATALOG prodCatalog TO ROLE "PUBLIC"
Grant SELECT privilege on all tables and views in an Arctic catalog
GRANT SELECT ON ALL DATASETS IN CATALOG arcticCat1
TO ROLE <role_name>