Skip to main content
Version: 0.0.1

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

{{< codeheader "CREATE ROLE syntax" >}}

CREATE ROLE <role_name>

Here is an example of the SQL command:

{{< codeheader "CREATE ROLE example" >}}

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​

{{< codeheader "GRANT ROLE TO USER syntax" >}}

GRANT ROLE <role_name> TO USER <username>

Here is an example of the SQL command:

{{< codeheader "GRANT ROLE TO USER example" >}}

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​

{{< codeheader "REVOKE ROLE FROM USER syntax" >}}

REVOKE ROLE <role_name> FROM USER <username>

Here is an example of the SQL command:

{{< codeheader "REVOKE ROLE FROM USER example" >}}

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​

{{< codeheader "GRANT ROLE TO ROLE syntax" >}}

GRANT ROLE <sub_role> TO ROLE <parent_role>

Here is an example of the SQL command:

{{< codeheader "GRANT ROLE TO ROLE example" >}}

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​

{{< codeheader "REVOKE ROLE FROM ROLE syntax" >}}

REVOKE ROLE <sub_role> FROM ROLE <parent_role>

Here is an example of the SQL command:

{{< codeheader "REVOKE ROLE FROM ROLE example" >}}

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​

{{< codeheader "DROP ROLE syntax" >}}

DROP ROLE <role_name>

Here is an example of the SQL command:

{{< codeheader "DROP ROLE example" >}}

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:

{{< codeheader "View users" >}}

SELECT * FROM sys.roles