GRANT/REVOKE

NOTE:

This functionality is for Dremio v16.0+ Enterprise Edition only.

Access control may be managed via the SQL Editor. This allows for the most flexible application of role-based access control. The sys.privileges system table enables administrators to determine not only who receives what privilege, but also under the confines of what scope, on an object-by-object basis.

Syntax

The successful execution of SQL commands is dependent on the correct structuring of each command. RBAC is accomplished using a specific syntax, which is shown below.

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

Here is an example of an 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 | SELECT | VIEW JOB HISTORY | VIEW REFLECTION

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:

| { 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 user name being granted the privilege:

| USER user_name
| ROLE group_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 same structure as when granting privileges, but with a couple of differences. 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 would not prevent user1 from performing SELECT actions on any dataset contained in the source.

SYSTEM Syntax

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