Skip to main content

Roles

Roles are a set of privileges that can be assigned to users as needed. Roles can also be assigned to other roles to create a child-role hierarchy, where child roles inherit all privileges from their parent roles. This hierarchical system allows you to organize privileges at scale rather than managing privileges for each individual user (also called members).

You can define roles based on the types of users in your organization. For example, Data_Analyst and Security_Admin roles can be created to manage privileges for users with different job functions within an organization.

See the following role design guidelines:

  • Keep the number of ADMIN role members to 1-2 administrators for security.
  • Begin with 2-3 custom roles based on primary job functions.
  • Create parent roles for common privilege sets, then add specific child roles as needed.
  • Choose clear names that reflect the role's purpose (e.g., Sales_Analyst, Data_Engineer).
  • Use prefixes such as DEPT_, PROJ_, or TEAM_ for consistency.
  • Use the description field to explain each role's intent.

How Role Inheritance Works​

Child roles automatically inherit all privileges from their parent roles, creating a cascading effect that simplifies privilege management.

Example Role Hierarchy 
Data_Viewer (SELECT on public datasets only)
  └── Data_Analyst (inherits Data_Viewer + SELECT on specific datasets)
      └── Data_Engineer (inherits Data_Analyst + CREATE, ALTER privileges)
          └── Data_Admin (inherits Data_Engineer + admin privileges on data sources)

In this example, a Data_Engineer automatically gets all the privileges of Data_Analyst and Data_Viewer, plus their own additional CREATE and ALTER privileges.

System Roles​

Dremio has two predefined system roles: ADMIN and PUBLIC. These roles can be used to manage privileges.

ADMIN​

The ADMIN role is designed for administrative users who require superuser/global access. Users who are assigned this role are granted every privilege across all objects and resources in an organization. The privileges for the ADMIN role are immutable by users.

The first user in an organization is automatically assigned the ADMIN role.

Be cautious when assigning the ADMIN role. Users with ADMIN privileges can modify any data, delete objects, and manage other users' access.

PUBLIC​

The PUBLIC role is assigned by default to all new users added to the organization and cannot be revoked from any user. Think of PUBLIC as the baseline access level that every user in your organization receives.

This role grants the following privileges to its members:

SELECT and ALTER privileges are not granted for any sources and must be assigned by a user with the ADMIN role or through additional custom roles.

Additional privileges can be granted to the PUBLIC role to provide organization-wide baseline access.

Custom Roles​

Custom roles can be created by any user or role that has the CREATE ROLE organization privilege, or by members of the ADMIN role.

You can assign a custom role to users or other roles (to create a child role). The custom role can then be assigned a set of privileges.

View All Roles​

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.

Use SQL​

ADMIN users can also list all roles using the sys.organization.roles system table:

Review all roles and their owners 
SELECT r.role_name, 
       r.role_type, 
       r.owner_type,
       u.user_name as owner_name
FROM sys.organization.roles r
LEFT JOIN sys.organization.users u ON r.owner_id = u.user_id
ORDER BY r.role_name;

Create a Custom Role​

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.
  3. Click Add Role at the top-right corner of the screen.
  4. In the Add Role dialog, for Name, enter the name to associate with the role, such as the position title or employee type that will be associated with the role.
  5. (Optional) For Description, provide any details regarding the purpose of the role or its associated privileges.
  6. Click Add.

Use SQL​

You can also create custom roles using the CREATE ROLE command.

Edit a Custom Role​

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.
  3. On the Roles page, select the role.
  4. On the Edit Role page, make any desired changes, such as adding or removing a child role and adding or removing a member.
  5. Click Save.

Use SQL​

You can also add or remove child roles and members using the GRANT ROLE and REVOKE ROLE SQL commands.

Remove a Custom Role​

Removing a role will immediately revoke all associated privileges from its members. Ensure users have alternative access before deleting roles.

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.
  3. On the Roles page, hover over the row of the role and click Delete that appears next to the role.
  4. Confirm that you want to delete the role.

Once confirmed, the role is deleted and cannot be retrieved.

Use SQL​

You can also remove custom roles using the DROP ROLE command.

Add a Child Role​

Child roles inherit all privileges from their parent roles. This creates a hierarchy where more specific roles build upon broader ones.

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.
  3. On the Roles page, select the parent role, then select the Roles tab.
  4. Click the dropdown multi-select field and either select the desired role or enter a role name to search for it.
  5. Click Add* when you have selected the desired entry or entries. When a child role is added, it will display below the dropdown in a list.
  6. Click Save.

The child role appears in the table along the left side of the screen.

Use SQL​

You can also add child roles to parent roles using the GRANT ROLE SQL command:

Example Association of a Child Role 
-- Make Data_Analyst a child role of Analytics_Team
GRANT ROLE Data_Analyst TO ROLE Analytics_Team;

Remove a Child Role​

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.
  3. On the Roles page, select the parent role, then select the Roles tab.
  4. Hover over the row of the role and click Delete that appears next to the role.
  5. Click Save.

Use SQL​

You can also remove child roles from parent roles using the REVOKE ROLE SQL command.

Add a Member​

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.
  3. On the Roles page, select the role, then select the Members tab.
  4. Click the dropdown multi-select field and either select the desired user (listed by email address) or enter an email address to search for it.
  5. Click Add when you have selected the desired entry or entries. When a member is added, it will display below the dropdown in a list.
  6. Click Save.

Use SQL​

You can also add members to roles using the GRANT ROLE SQL command:

Example creating a role member 
-- Assign Data_Analyst role to a user
GRANT ROLE Data_Analyst TO USER 'jane.doe@company.com';

Remove a Member​

Users cannot remove themselves from the ADMIN role. If you are a member of the ADMIN role and wish to be removed from it, another user who has the necessary privileges must remove you.

Use the Dremio Console​

  1. Click Settings in the side navigation bar and choose Organization settings.
  2. Select Roles in the organization settings sidebar.
  3. On the Roles page, select the role, then select the Members tab.
  4. Hover over the row of the member and click Delete that appears next to the member.
  5. Click Save.

This removes them as a member of this role, and they will no longer possess the privileges associated with that role. However, the user still retains privileges associated with any other roles where they are members.

Use SQL​

You can also remove members from roles using the REVOKE ROLE SQL command.

Limits and Considerations​

  • There is a limit of 10 nested roles in a hierarchy. For more information, see Limits.