Role SQL Commands

NOTE:

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

Role management may be performed via the SQL manager. This allows you to perform basic functions on a role-by-role basis, such as creating and removing roles as well as creating and removing users. To grant or revoke privileges to a role, use the GRANT command.

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

CREATE ROLE

The following SQL command may be used to create new role in Dremio.

Syntax

CREATE ROLE <role_name>

Here is an example of the SQL command:

CREATE ROLE role1

Required Parameters

<role_name>

Indicates the name being associated with the new role.

GRANT ROLE TO USER

The following SQL command may be used to assign an existing role to a Dremio user.

Syntax

GRANT ROLE <role_name> TO USER <username>

Here is an example of the SQL command:

GRANT ROLE role1 TO USER user1

Required Parameters

<role_name>

Indicates the name of the role being assigned to a user.

<username>

Indicates the username associated with the user account being granted the role.

REVOKE ROLE FROM USER

The following SQL command may be used to remove an existing role from a specified user.

Syntax

REVOKE ROLE <role_name> FROM USER <username>

Here is an example of the SQL command:

REVOKE ROLE role1 FROM USER user1

Required Parameters

<role_name>

Indicates the name of the role to be revoked.

<username>

Indicates the username associated with the user account from which the role will be revoked.

GRANT ROLE TO ROLE

The following SQL command may be used to assign an existing role to another role. This is also known as a nested role.

Syntax

GRANT ROLE <sub_role> TO ROLE <parent_role>

Here is an example of the SQL command:

GRANT ROLE subrole TO ROLE role1

Required Parameters

<sub_role>

Indicates the name of the role that will be nested.

<parent_role>

Indicates the name of the role that will be used as the parent to the nested role.

REVOKE ROLE FROM ROLE

The following SQL command may be used to remove a nested role from the specified parent role.

Syntax

REVOKE ROLE <sub_role> FROM ROLE <parent_role>

Here is an example of the SQL command:

REVOKE ROLE subrole FROM ROLE role1

Required Parameters

<sub_role>

Indicates the name of the role that will be removed from the parent role.

<parent_role>

Indicates the name of the parent that the nested role is currently assigned to.

DROP ROLE

The following SQL command may be used to delete roles from Dremio.

Syntax

DROP ROLE <role_name>

Here is an example of the SQL command:

DROP ROLE role1

Required Parameters

<role_name>

Indicates the role being deleted.

System Table

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

To view users, enter the following command:

SELECT * FROM sys.roles