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 syntaxGRANT { <objectPrivilege>[, ...] | ALL } ON <object> TO <grantee>
REVOKE { <objectPrivilege>[, ...] | ALL } ON <object> FROM <grantee>
-- On SYSTEM
{ ALL | ALTER | ALTER REFLECTION | CONFIGURE SECURITY | CREATE ROLE | CREATE SOURCE | CREATE TABLE | CREATE USER | DELETE | DROP | EXECUTE | EXTERNAL QUERY | INSERT | MANAGE GRANTS | MODIFY | MONITOR | OPERATE | READ METADATA | SELECT | TRUNCATE | UPDATE | UPLOAD FILE | USAGE | 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 | VIEW REFLECTION }
-- On User-Defined Functions
{ ALTER | EXECUTE | MANAGE GRANTS | OWNERSHIP }
-- On Views
{ ALTER | DELETE | EXECUTE | INSERT | MANAGE GRANTS | OWNERSHIP | READ METADATA | SELECT | TRUNCATE | UPDATE | VIEW REFLECTION }
-- On Users
{ ALTER | MANAGE GRANTS | OWNERSHIP }
-- On Roles
{ OWNERSHIP }
-- 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 }
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.
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:
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 privilegesSELECT * FROM sys.privileges
Examples
Grant a SELECT privilege on an individual viewGRANT SELECT ON VIEW view1 TO USER user1
GRANT SELECT ON ALL DATASETS IN FOLDER Regions TO ROLE analyst
GRANT SELECT ON SYSTEM TO USER user1
GRANT OWNERSHIP ON VIEW "Midwest".Regions TO ROLE analyst
GRANT OWNERSHIP ON ROLE data_engineer TO USER user1
GRANT VIEW JOB HISTORY ON SYSTEM TO USER "user@dremio.com"
GRANT CONFIGURE SECURITY ON SYSTEM TO USER user1
GRANT SELECT,ALTER ON VIEW view1 TO USER user1
REVOKE SELECT ON DATASET Dataset1 FROM USER user1