GRANT/REVOKE

Access control may be managed via the SQL Editor using the GRANT or REVOKE SQL commands. This allows for the most flexible application of role-based access control. The GRANT command 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

The successful execution of SQL commands is dependent on properly structuring each command. Access is given or taken away by running one of the following:

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

Here is an example of a full SQL command:

GRANT SELECT ON VDS vds1 TO USER user1

This is the basic order of an SQL command to grant privileges. Each command must consist of the privilege being granted or revoked, the object or scope associated, and the user or group.

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

Required Parameters

<privilege>

Indicates the privilege to be assigned or revoked:

| ALTER | ALTER REFLECTION | CREATE | EXTERNAL QUERY | MANAGE GRANTS | MODIFY | ROLE ADMIN | SELECT | VIEW JOB HISTORY | VIEW REFLECTION | OWNERSHIP |

Note:

Role Admin: The ROLE ADMIN privilege requires different syntax, as described under Granting the ROLE ADMIN Privilege.

For more information regarding these privileges and the objects they may be assigned to, see the Privileges help page.

<object>

<object_type> <object_name>
| ALL DATASETS IN [ SPACE | SOURCE | FOLDER | SCHEMA ] <object_name>
| { project_name | SYSTEM }

<object_type>

Indicates the type of object:

| ROLE
| USER
| { PDS | TABLE }
| { VDS | VIEW }
| { FOLDER | SCHEMA }
| SOURCE
| SPACE

<object_name>

Indicates the name of the object specified as the scope:

| Any fully-qualified Source, Space, folder, PDS, VDS, etc.

<grantee>

Indicates the user type and username being granted the privilege. Do not use symbols like - in a user or role name, as the SQL Editor cannot parse these values.

| USER user_name
| ROLE role_name
| PROJECT project_name

Multiple Privileges

When granting or revoking multiple privileges for a user or group, separate each privilege listed by a single comma. See the example below:

GRANT SELECT,ALTER ON VDS vds1 TO USER user1

Revoking Privileges

The syntax for revoking a privilege follows the syntax provided previously. See the example below:

REVOKE SELECT ON DATASET Dataset1 FROM USER user1

Note:

Please note that revoking a privilege will only remove privileges from the object specified, so pay close attention to the scope of existing privileges. If the user possesses the privilege at a specific object level, make sure to revoke the privilege at that object level as well. If you revoke a privilege for a child object and the user has the same privilege granted at a higher level, then the user still retains the privilege for that object.

Revoking at the Wrong Scope

If you remove 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. An administrator performs the following SQL command:

REVOKE SELECT ON FOLDER FolderA FROM USER user1

This does not prevent user1 from performing SELECT actions on any dataset contained in the source.

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:

GRANT ROLE ADMIN TO ROLE <groupName>

Granting the SYSTEM Scope

Of the objects listed above, SYSTEM requires slightly different syntax. Whereas all other objects require that you specify the object type followed by the object’s identifier, SYSTEM does not.

This is shown in the example below:

GRANT SELECT ON SYSTEM TO USER user1

View 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:

SELECT * FROM sys.privileges