GRANT/REVOKE Enterprise

The GRANT and REVOKE commands enable administrators to determine not only who receives what privilege, but also the scope or objects these privileges apply to.

Administrators may reference the sys.privileges and sys.membership system tables to see all current privilege assignments for both users and roles.

Syntax

GRANT and REVOKE syntax

GRANT { <objectPrivilege>[, ...] | ALL } ON <object> TO <grantee>
REVOKE { <objectPrivilege>[, ...] | ALL } ON <object> FROM <grantee>

objectPrivilege

-- On SYSTEM
{ ALL | ALTER | ALTER REFLECTION | CONFIGURE SECURITY | CREATE ROLE | CREATE SOURCE | CREATE TABLE | CREATE USER | DELETE | DROP | EXECUTE | EXPORT DIAGNOSTICS | EXTERNAL QUERY | INSERT | MANAGE GRANTS | MODIFY | READ METADATA | SELECT | TRUNCATE | UPDATE | UPLOAD FILE | VIEW JOB HISTORY | VIEW REFLECTION }

-- On Sources
{ ALTER | ALTER REFLECTION | CREATE TABLE | DELETE | DROP | EXTERNAL QUERY | INSERT | MANAGE GRANTS | MODIFY | OWNERSHIP | READ METADATA | SELECT | TRUNCATE | UPDATE | VIEW REFLECTION }

-- On Spaces
{ ALTER | ALTER REFLECTION | DELETE | INSERT | MANAGE GRANTS | MODIFY | OWNERSHIP | READ METADATA | SELECT | TRUNCATE | UPDATE | VIEW REFLECTION }

-- On Folders
{ ALTER | ALTER REFLECTION | CREATE TABLE | DELETE | DROP | MANAGE GRANTS | OWNERSHIP | READ METADATA | SELECT | TRUNCATE | UPDATE | VIEW REFLECTION }

-- On Scripts
{ VIEW | MODIFY | DELETE | MANAGE GRANTS }

-- On Tables
{ ALTER | MANAGE GRANTS | READ METADATA | SELECT | TRUNCATE | UPDATE }

-- On User-Defined Functions
{ ALTER | EXECUTE | MANAGE GRANTS | OWNERSHIP }

-- On Views
{ ALTER | DELETE | INSERT | MANAGE GRANTS | OWNERSHIP | READ METADATA | SELECT | TRUNCATE | UPDATE }

-- On Users
{ ALTER | OWNERSHIP }

-- On Roles
{ ALTER | OWNERSHIP }

object

-- For a specific object
<object_type> <object_name>

-- For all datasets within an object
ALL DATASETS IN { SPACE | SOURCE | FOLDER | SCHEMA } <object_name>

-- Within the scope of a project or system
{ <project_name> | SYSTEM }

note

• Anywhere in this documentation that a value is enclosed in < and >, should be treated as an indicator of where a custom value should be provided. The value you enter should not be enclosed in < or >.

• When granting or revoking multiple privileges for a user or group, separate each privilege by a single comma.

Parameters

<objectPrivilege> String

The privilege(s) to be granted to or revoked from the user. 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 or revoked, such as ROLE, USER, TABLE, VIEW, FOLDER or SCHEMA, SOURCE, and SPACE.

---

<object_name> String

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

---

ALL DATASETS IN <object_name> String

Used to grant privileges for all datasets in a space, source, folder, or schema. The OWNERSHIP privilege is not valid for ALL DATASETS commands. Instead, you must grant ownership individually for each table, view, or folder.

---

<grantee> String

The user type or username to whom the privilege is being granted or revoked, such as USER <username>, ROLE <role_name>, or PROJECT <project_name>. Do not use symbols like - in a user or role name, as the SQL editor cannot parse these values.

---

{ <project_name> | SYSTEM } String

Whereas all other objects require that you specify the object type followed by the object’s identifier, SYSTEM and projects do not. Depending on the scope, all you need is the name of the project or simply SYSTEM. For more information, read Privileges.

note

Revoking a privilege will only remove privileges from the object specified, so pay close attention to the scope of existing privileges. If you revoke privileges for a user at a low-level object, but they hold the privilege with a higher-level object, then they still retain the privilege for anything contained within the parent object. For example, user1 has the SELECT privilege at the source level and an administrator revokes that SELECT privilege on a folder. This does not prevent user1 from performing SELECT actions on any dataset contained in the source. Therefore, make sure to revoke the privilege at the object level that the user has privileges for.

Granting the ROLE ADMIN Privilege

You may grant an internal or external groups administrative-level access to a cluster using the GRANT ROLE ADMIN command. This effectively treats all users with that role as full system admins in Dremio.

This access control requires different structure than what is provided in the Syntax section. Instead, use this format, replacing <groupName> with an existing Dremio role name:

Syntax for GRANT ROLE ADMIN privilege

GRANT ROLE ADMIN TO ROLE <groupName>

Viewing Privileges

Administrators may view a list of all existing privileges, the users they’re assigned to, and the objects associated from the sys.privileges system table. This is accessed from the SQL Editor and is viewable only by administrators in Dremio.

To view privileges, enter the following command:

View privileges

SELECT * FROM sys.privileges

Examples

Grant a SELECT privilege on an individual view

GRANT SELECT ON VIEW view1 TO USER user1

Grant a SELECT privilege on all datasets in a folder

GRANT SELECT ON ALL DATASETS IN FOLDER Regions TO ROLE analyst

Grant a SELECT privilege in the SYSTEM scope

GRANT SELECT ON SYSTEM TO USER user1

Grant an OWNERSHIP privilege on a view

GRANT OWNERSHIP ON VIEW "Midwest".Regions TO ROLE analyst

Grant an OWNERSHIP privilege on a role

GRANT OWNERSHIP ON ROLE data_engineer TO USER user1

Grant VIEW JOB HISTORY privilege to a user

GRANT VIEW JOB HISTORY ON SYSTEM TO USER "user@dremio.com"

Grant the CONFIGURE SECURITY privilege to a user

GRANT CONFIGURE SECURITY ON SYSTEM TO USER user1

Granting multiple privileges on a dataset

GRANT SELECT,ALTER ON VIEW view1 TO USER user1

Revoke a SELECT privilege on a dataset

REVOKE SELECT ON DATASET Dataset1 FROM USER user1