Skip to main content

Access Control

Access control lets you manage and regulate access to objects or data within Dremio by determining who can access specific objects and what actions or operations they can perform on those objects.

Access control mechanisms are fundamental to preventing unauthorized access, enforcing security policies, minimizing risks, protecting sensitive information, maintaining compliance with regulations, and ensuring that users have the appropriate level of access based on their roles and responsibilities within an organization.

Dremio's Access Control Model​

Dremio uses a Role-Based Access Control (RBAC) model, which gives you the ability to restrict access to any object in Dremio. RBAC provides a structured approach to managing and controlling user access by creating roles, assigning privileges to those roles, and then assigning users as members to those roles. Dremio’s RBAC model allows for the implementation of granular-level privileges, which determine the actions that a user/role can take on a given object, such as a table, project, or cloud.

Dremio’s RBAC models consists of the following four concepts:

User: Users are identity-entities in Dremio. They can represent a human or a non-human entity, such as other applications or services.

Role: Roles are entities to which privileges are granted based on job functions, responsibilities, or levels of authority within an organization. Roles can be assigned to users, or other roles to create a hierarchy of roles.

Privilege: Roles are assigned a set of privileges that define what actions or operations users assigned to that role are allowed to perform.

Securable Object: Securable objects are entities that serve as targets for access control rules. In Dremio, securable objects can include sources, tables, views, folders, and more.

Users​

Users can be managed locally via Dremio as well as externally through third-party Identity Providers. A user’s email address is used as the unique identifier in Dremio Cloud and cannot be changed after the user has been created. Users are assigned the PUBLIC role upon creation, unless otherwise specified. There are two different types of users in Dremio: Internal (Local) and External.

Internal Users​

Users that setup a local password for authentication to Dremio are considered internal users. These users’ credentials are managed through Dremio. For more information, see User Management.

External Users​

External users are created and managed by an external application like an identity provider. These users need to be manually invited or provisioned using SCIM. User credentials for external users cannot be changed from within Dremio because they are controlled by the identity manager. Dremio stays in sync with the identity provider when SCIM is configured. If a user is deleted from the identity provider, this will be reflected in Dremio.

Roles​

Roles are a set of privileges that can be assigned to users as needed. Roles can also be assigned to roles to create a child-role hierarchy. Roles are used 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, Analyst and Security_Admin roles can be created to manage privileges for users with different job functions within an organization.

System Roles​

Dremio has two predefined system roles (ADMIN and PUBLIC) that can be used to manage privileges. These are the only roles available in Standard Edition because custom roles aren't included.

ADMIN​

The ADMIN role is designed for administrative users that 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.

note

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

PUBLIC​

The PUBLIC role is assigned by default to all new users added to the organization and cannot be revoked from any user.

This role grants the following privileges to its members:

Membership in the PUBLIC role does not grant USAGE privileges on any projects or SELECT and ALTER privileges on any sources. These privileges must be manually assigned by the project owner or a member of the ADMIN role.

Furthermore, additional privileges can be granted to the PUBLIC role.

Custom Roles​

Custom roles can be created by any user/role that has the CREATE ROLE 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.

For more information on managing Custom roles, please refer to Role Management.

Privileges​

Privileges refer to the defined levels of access or permissions that are assigned to roles or users within Dremio. Privileges determine the operations a user or role can perform on securable objects. Examples of privileges in Dremio include SELECT on a table or view, INSERT on a table, DELETE on a table, CREATE TABLE on a folder, and MANAGE GRANTS on any object.

The assignment of privileges to roles, or users, should be based on the principle of least privilege, where users or roles are given only the minimum privileges required to perform their tasks effectively.

Privileges can be managed using SQL, REST APIs, or the Dremio Console.

For more information, please refer to Privileges.

Granting Privileges​

SQL commands can be run from Dremio’s SQL Runner, or any other external SQL client. Any GRANT command that you enter applies to the scope supplied with the command itself.

Example SQL command for granting a privilege on a securable object
GRANT <privilege> ON <securable_object> TO USER <user_name>
GRANT <privilege> ON <securable_object> TO ROLE <role_name>

To grant privileges to a user/role on an object (sources, folders, tables, views) using the Dremio Console:

  1. For sources, click the gear button at the top right of the screen. For folders and tables and views, click the ellipsis (…) button at the right side of the screen and then select Settings.
  2. Select the Privileges tab. This is where you can manually grant privileges on an object to users and roles.
note

For some object types, the settings dialog automatically opens to display the privilege settings, and you do not need to select the Privileges tab.

  1. Enter the user’s username or the role name under Add User/Role.
  2. Click Add to Privileges. If the entry matches a user or role in Dremio, then a record will appear for them in the privileges table.
  3. Select the desired privileges for that user or role.
  4. When finished, click Save.

To grant privileges to a user or role for an organization using the Dremio UI:

  1. Locate the organization screen.
  2. Click the gear button next to the organization name at the top of the screen.
  3. In the organization settings sidebar, select the Privileges tab. This is where you can manually assign organization-level privileges to users and roles.
  4. Enter the user’s email address or the role name under Add User/Role.
  5. Click Add to Privileges. If the entry matches a user or role in Dremio, then a record will appear for them in the privileges table.
  6. Select the desired privileges for that user or role.
  7. When finished, click Save to preserve your changes.
note

In the Privileges tab, the pre-populated row for All Users represents the PUBLIC role. Use the “All Users” row to grant specific privileges to the PUBLIC role. The “flag” next to a user/role represents the owner of the object.

Revoking Privileges​

Similarly to GRANT, any REVOKE command that you enter applies to the scope supplied with the command itself. The SQL syntax for revoking privileges from a user/role is:

Example SQL command for revoking a privilege on a securable object
REVOKE <privilege> ON <securable_object> FROM USER <user_name>
REVOKE <privilege> ON <securable_object> FROM ROLE <role_name>

To revoke a user’s or role’s privileges for an object (sources, folders, and tables and views) using the Dremio UI:

  1. Locate the desired object.
  2. For sources, click the gear button at the top right of the screen. For folders and tables and views, click the ellipsis (…) button at the right side of the screen and then select Settings.
  3. Select the Privileges tab to view the currently assigned privileges.
note

For some object types, the settings dialog automatically opens to display the privilege settings, and you do not need to select the Privileges tab.

  1. Scroll down to the desired user or role record. If the user or role is not listed, then they do not have specific privileges on the object aside from any privileges listed in the All Users row, which represents the PUBLIC role.
  2. Clear the checkboxes in the columns for the privileges you wish to revoke.
  3. When finished, click Save to preserve your changes.
note

If a user has a specific privilege on an object through their memberships in multiple roles and the privilege is revoked for one of the roles, the user retains the privilege until it is revoked on the same object for all roles to which the user belongs.

To revoke a user’s or role’s privileges for an organization using the Dremio UI:

  1. Locate the organization screen.
  2. Click the gear button next to the organization name at the top of the screen.
  3. In the organization settings sidebar, select the Privileges tab. This is where Dremio displays organization-level privileges for users and roles.
  4. Scroll down to the desired user or role record. If the user or role is not listed, then they do not have organization-level privileges.
  5. Clear the checkbox in the columns for the privileges that you wish to revoke.
  6. When finished, click Save to preserve your changes.

Securable Objects​

Securable objects are entities that serve as targets for access control rules. Privileges are granted, or revoked, on securable objects. These privileges define the actions that users or roles can perform on the securable object, such as read, write, modify, or more.

The concept of securable objects allows for granular access control, enabling administrators to define precise permissions and restrictions for different users or roles within the RBAC framework.

Each securable object, such as a project, cloud, or dataset, exists within a hierarchy of objects (or containers). The uppermost object is the Organization. Sonar sources and Arctic catalogs contain additional securable objects.

Inheritance, Scope, and Ownership​

Inheritance​

The objects to which user/role's privileges apply depend on the inheritance model and ownership. Granting access to a parent object, such as your organization's Dremio project, also gives that user/role the same privilege access to any objects in the project that exist currently or are created in the future, whether it is a dataset or source. Without privileges based at the project level, even basic users cannot view, query, or alter the datasets it houses.

For example, giving a user privileges to ALL DATASETS will only grant the user access to all existing datasets in that project, not the folders that contain the datasets--which means they cannot access the dataset without first having the same privilege applied to the parent folder, and then the source, and then the project.

The following rules apply:

  • The object to which a user's privileges are applied affects what is known as the scope, and, like inheritance, follows a parent-child relationship with regard to inheritance.
  • Even if a user only requires access to a single dataset, they must have access to the parent object(s) that contain it.
note

If a user has sufficient privileges for a single table, they may create a view based on that dataset, but with the user now having ALTER and MANAGE GRANTS privileges for any view. However, the user still retains the same privileges they held before with the original table, meaning the extended access granted to the view will not extend to the table it is based upon. For more information, see View Delegation below.

Scope​

Scope is a concept used to describe what objects a user or group has access to. Privileges are assigned at the object level, which ultimately determines the actions a user or role may execute for any given object.

The following rules apply:

  • As described by the inheritance model any objects contained within that folder will likewise apply the privileges a user has for a parent object, such as FolderA.
  • The user's access also extends to all existing and future datasets contained in that folder.
  • Privileges on a source, space, or dataset also apply to the wikis for the source, space, or dataset.

For example, user1 is granted the SELECT privilege for FolderD. This object contains multiple datasets, which the user may now query (but not edit, as ALTER was not granted). The user may also view (but not edit) the wikis for the datasets in the folder.

Because of the established scope, user1 may not access FolderC because they were only granted access to FolderD and its child objects.

Current vs. Future Objects​

Based on the selected scope, you may restrict a user's access to future and existing datasets. For example, if you set privileges for a single table as the scope of a user's privilege, then that user's actions will be restricted to that single table. However, if the scope of a privilege is instead set at the folder level, then the user's actions can be applied to any tables and views contained in that folder.

Ownership​

Default Ownership of Objects​

By default, when a user creates an object in a Dremio project, they become the owner of that object through the OWNERSHIP privilege. Owners of objects inside a project cannot query the objects unless they have the USAGE privilege on the project. Once they are granted the USAGE privilege on the project, object owners have all other implicit privileges on the object.

Hierarchical Ownership​

OWNERSHIP in Dremio is scoped/hierarchical. For example, the owner of an organization can change the owner of the projects within the organization (or do anything else on the child objects of the organization: project, engines, sources, clouds, users, roles, identity providers, folders, tables, views, and UDFs).

Privilege Delegation​

Object ownership is a security feature used by database administrators to ensure access to an object is controlled as well as oversee who has that control. With Dremio, each object MUST have an owner, which is automatically granted to the user that initially created the object. For example, if a system administrator creates an S3 data source, Dremio automatically assigns them ownership.

Object owners must be granted the USAGE privilege on the project that contains the object they own. Once they are granted USAGE privileges on the project, the object owner also retains all other privileges for that object. The owner can then grant or revoke user/role access to that object and any child objects associated, modify the object's settings, and drop or delete the object as desired.

The following behaviors or limitations apply to ownership:

  • Each object may only have one (1) owner
  • An object's creator is automatically granted ownership
  • Object owners must have the USAGE privilege on the project that contains the object to query or grant and revoke other privileges on the object.
  • Object ownership may be assigned or modified with GRANT TO USER or GRANT TO ROLE commands
  • If an owner is deleted or removed, access to the object may not work
  • Object owners may be identified by querying sys.project."tables" or sys.project.views for views
    • If an object has no owner, the owner_id will display as $unowned
Transferring Organization Ownership​

Organization owners can transfer their ownership to another user or role in the Dremio console or with the GRANT TO USER and GRANT TO ROLE SQL commands.

caution

Ownership transfers take effect immediately, and organizations have only one owner. Only the organization owner can make ownership changes.

To use the Dremio console to transfer ownership to another user or role:

  1. On the Organization home page, click This is the icon that represents the organization settings. in the left navigation menu.

  2. Select the Privileges tab in the organization settings sidebar.

  3. At the top of the Privileges page, click Transfer Ownership. A search field appears under Owner.

  4. In the search field under Owner, find the user or role to which you want to transfer ownership and click the user or role name to select it.

  5. Click Transfer.

  6. In the Transfer ownership to this user/role? confirmation dialog, click Transfer.

View Delegation​

Tables with restricted access may be used with greater access through the creation of a view. When a user with SELECT access to a table creates a view, then that user automatically becomes owner of the new object. They now have the ability to query and alter settings or data as desired. However, changes made to the view will not affect the original table.

Upon creating the view, the same rules of ownership as described above apply. The owner or delegation identity does not change when a view is edited or queried, but must be manually changed via the GRANT TO USER or GRANT TO ROLE commands. To identify the owner of a view, query the sys.views table.

note

The shared view still selects from the underlying dataset using the view owner's permissions at the time of the view's last modification, even if the end user querying the view lacks privileges to modify the underlying table. This applies to each table on the data graph and chain of datasets.

Scenarios for Assigning Privileges​

When assigning privileges to users via SQL commands, you may utilize these methods: granting to a single dataset, granting to ALL DATASETS, and granting to a scope. Examples may be found under each section below.

note

These examples assume users already have the correct privileges assigned at the project level to access child folders and datasets.

Each of these examples includes an SQL command.

1. Granting to a Single Dataset​

When you have a user that needs access to only one table and no other objects, then you would simply assign them privileges for that dataset.

You should use this method if you want to restrict a user's access from any other existing or future datasets.

note

If you're granting the user access to a table, then remember that they'll be able to create views based on that table, which that user can then grant access to other users.

Example: Single dataset​

You have a user that you only want to give access to an individual table. You would need to navigate to the Privileges screen from that dataset's settings and grant the user the SELECT privilege, or perform the following command from the SQL Editor:

Example command for single dataset
GRANT SELECT ON TABLE TableA1 TO USER user1

The image below illustrates the objects user1 now has access to.

This restricts user1 so that they may only access the TableA1 table, not any other datasets contained in the same folder. However, user1 may still create views based on TableA1.

2. Granting to ALL DATASETS​

When you have a user that needs access to all existing datasets, then you would use the SQL syntax ON ALL DATASETS (see the example scenario outlined below). This gives the user access to all existing datasets. The user would not, however, automatically receive access to any future datasets created by other users.

You should use this method of privilege assignment if you want to restrict a user's access from parent objects, but still wish for them to have access to all existing datasets.

Example: ALL DATASETS​

You have a specific user that needs access to all datasets in a specific folder, but they do not require privileges for the folders containing these tables. You would then execute the following command from the SQL Editor:

Example command for all datasets
GRANT SELECT ON ALL DATASETS IN PROJECT project1 TO USER user1

The image below illustrates the objects user1 now has access to.

This command restricts the scope of user1 to all datasets presently found in source1, such as TableC1 and TableD1. Should additional datasets be created in the future, user1 will not have access to them.

3. Granting to a Scope​

When you want to grant a user access to a parent object, such as a folder, this will also grant the user access to any datasets contained (see the example scenario outlined below).

You should use this method of privilege management if you wanted to grant a user access to all existing and future datasets contained under a parent object.

Example: Scope​

This method grants a user access to all existing and future datasets contained under a specified object. To accomplish this, you need to navigate to the Privileges screen from that folder's settings and grant the user the SELECT privilege, or execute the following command from the SQL Editor:

Example command for grant to a scope
GRANT SELECT ON FOLDER Folder3 TO USER user1

The image below illustrates the objects user1 now has access to.

This grants user1 the SELECT privilege on Folder3, which means they now have access to all existing and future datasets contained in that folder and its subfolders.