Access Control Structure

NOTE:

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

Dremio employs access control, which allows for the assignment of access policies, or privileges, to users or groups.

Based on the inheritance model, when a user is granted privileges to an object with child objects, the user’s privilege also applies to the child objects. This is also known as scope, meaning the specific objects to which a privilege applies.

Access to objects is granted via privileges assigned to users or groups.

Object Hierarchy

Each object resides within a container in a hierarchy of containers. The upper-most container exists as the system user, or administrator account. All other objects are contained within sources or spaces, organized into folders. The hierarchy of these objects is illustrated below.

Inheritance

The objects to which privileges are granted depend on the inheritance model. In other words, granting access to a parent object, such as a folder, also gives that user access to any existing and future datasets contained in that folder. For example, giving a user privileges to ALL DATASETS will only grant the user access to existing datasets, not the folders that contain the datasets. In comparison, granting privileges at the source level will extend that user’s access to the source’s existing and future folders/schema and datasets. The object to which a user’s privileges are applied is also known as the scope, and follow a parent-child relationship.

By the rules of inheritance, user or group access may be granted as high or low in the object hierarchy as you wish for access to reach.

Permissions granted to an individual physical dataset (PDS) or virtual dataset (VDS) mean that a user’s access only extends to that dataset, not to the parent folder or other datasets created in the same folder. So if a user only needs access to a single dataset, administrators need only grant privileges to that object.

Consider the image above, which shows an example of object structure in Dremio. If a user is granted privileges to a single dataset, such as TableA1, then that is the one object they have access to. However, if a user is granted privileges at the folder level, such as Folder1, then that user’s access extends to any existing and future child objects created, including FolderA, TableA1, TableB1, and so on.

NOTE

If a user has privileges for a single PDS, they may create VDS’s based on that dataset, but with the user now having ALTER and MANAGE GRANTS privileges for any VDS. However, the user still retains the same privileges as before with the original dataset. For more information, see Ownership Chaining below.

Scope

Scope is a concept used to describe what objects a user or group has access to. Privileges are assigned by object, which ultimately determines what a grantee may perform set functions upon. For example, you may set a user’s scope to FolderA, which will give the user access to all existing and future datasets contained in the folder. But they will not have access to any other folders or the source. The object a user is granted access to is dependent on the inheritance model, which means based on the object type, it may contain child objects. For example, if a user is granted privilege to a folder, the user’s access also extends to all existing and future datasets contained in that folder.

For example, user1 is granted the SELECT privilege to the folder FolderC. This object contains multiple datasets, which the user may now access. However, there exists a parent folder and another subfolder with its own datasets.

Becaus of the established scope, user1 may not access FolderD because they were only granted access to FolderC’s 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 select a single PDS as the scope of a user’s privilege, then that user may only perform that action to the existing dataset, as well as any future VDS’s they create using that PDS. However, they may not access any VDS’s created from a PDS by another user (see the example below). However, if the scope is instead set at the folder level, then the user may perform the granted privilege to all PDS’s and VDS’s contained in that folder (see the example below).

Ownership Chaining

Physical datasets with restricted access may be shared with other Dremio users through the creation of a virtual dataset. When a user creates a VDS from a PDS they have access to, they become the owner of that new dataset. The user may then grant privileges to other users for that VDS (see the example below). Ownership of the new VDS is then dependent on who the last user was that modified and saved the original query for the VDS (see Example 3 below).

NOTE

The shared VDS still selects from the underlying dataset using the VDS owner’s permissions at the time of the VDS’s last modifiation, even if the end user querying the VDS lacks access to the underlying PDS. This applies to each physical dataset (PDS) on the data graph and chain of datasets.

Example 1: Ownership chaining on a VDS

user1 has SELECT access to pds1 and creates vds1, which is based upon the previous physical dataset. Whereas in the first dataset she was restricted to only querying, with vds1 she now has ALTER and MANAGE GRANTS privileges. user1 may now grant user2 with the SELECT privilege for vds1 because of ownership chaining. The privileges for both the physical and virtual dataset are outlined below:

vds1 pds1
user1 ALTER, MANAGE GRANTS SELECT
user2 SELECT No access

user2 previously lacked access to the data found in pds1, but may now perform queries on vds1. The following table describes what actions each user may perform:

User Action Outcome
user1 View the results of vds1 queries yes
user1 Modify the original query of vds1 yes
user2 View the results of vds1 queries yes; through ownership chaining
user2 Modify the original query of vds1 no

Becaise of ownership chaining, user2 can view the results of the vds1 query. However, because user2 has no access to pds1, they cannot see the original dataset from the Dremio interface and also cannot modify the original vds1 query.

Example 2: Ownership chaining on a VDS with revoked access to the original PDS

user1 originally had SELECT access to pds1, which they used to create vds1. However, an administrator just revoked their access to pds1.

vds1 pds1
user1 ALTER, MANAGE GRANTS No access
user2 SELECT No access

This now impacts the actions of user1 with regard to vds1. The following table describes what actions each user may now perform:

User Action Outcome
user1 View the results of vds1 queries no
user1 Modify the original query of vds1 yes
user2 View the results of vds1 queries no
user2 Modify the original query of vds1 no

Because of ownership chaining, both user1 and user2 may still view the results of the vds1 query. However, now that both users lack privileges for the underlying pds1 dataset, neither can see it from the Dremio interface or modify the original vds1 query.

Example 3: Ownership chaining on a VDS with original query changed and revoked access

Both user1 and user2 had ALTER and MANAGE GRANTS privileges for the pds1 dataset and the vds1 dataset created from it.

vds1 pds1
user1 ALTER, MANAGE GRANTS SELECT
user2 ALTER, MANAGE GRANTS SELECT

An administrator then revokes access to both pds1 and vds1 from user2.

If user2 modified and saved vds1 before their privileges were revoked, subsequent queries would be executed as user2. Once the privileges of user2 become revoked, subsequent queries performed by user1 would display an error message about lacking sufficient privileges. This is because vds1 was last altered by user2, making user2 the owner of the dataset.

The following table describes what actions each user may now perform:

User Action Outcome
user1 View the results of vds1 modified queries no
user1 Modify the original query of vds1 yes
user2 View the results of vds1 queries no
user2 Modify the original query of vds1 no

user2 may no longer view or query either vds1 or pds1 from the Dremio interface. user1 would need to modify the original query to retain ownership of the dataset and resume viewing query results.

Methods of Privilege Assignment

When assigning privileges to users, you may follow one of three methods: granting to a single dataset, granting to ALL DATASETS, and granting to a scope. Examples of these methods may be found under each section.

Each of these examples includes an SQL command. For more information regarding syntax, review the Access Control SQL Commands.

1. Granting to a Single Dataset

When you have a user that needs access to only one physical dataset and no other objects, then you would simply assign them privileges for that dataset (see the example scenario outlined below).

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 PDS, then remember that they’ll be able to create VDS’s based on that dataset, which that user can then grant access to other users.

Example: Single dataset

You have a user that you only 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:

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 physical dataset, not any other datasets contained in the same folder. However, user1 may still create VDS’s 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:

GRANT SELECT ON ALL DATASETS IN SYSTEM 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:

GRANT SELECT ON FOLDER Folder3 TO USER user1

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

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