Access Management Enterprise
Dremio employs access management, 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 table or view 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.
If a user has privileges for a single table, they may create views 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 as before with the original dataset. For more information, read View Delegation.
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, as well as the datasets' wikis. 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.
Because 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 table 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 views they create using that table. However, they may not access any views created from a table 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 tables and views contained in that folder (see the example below).
Ownership
Object ownership is a security feature used to control access to an object as well as oversee who has that control. In Dremio, each object MUST have an owner, and may have only one owner. Ownership is automatically granted to the user who initially creates the object. For example, if User1 creates an S3 data source, Dremio automatically assigns ownership of the source to User1.
The implication of ownership is that only an object's owner retains all privileges for that object. As a result, the owner can grant or revoke user and role access to that object and its child objects, modify the object's settings, and drop or delete the object as desired. Read View Delegation and Methods of Privilege Assignment for examples and more information.
The following behaviors and limitations apply to ownership:
- Each object may only have one 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, the object's access control settings may not work.
- Object owners may be identified by querying sys."tables" or sys.views for views.
- If an object has no owner, the owner_id will display as
$unowned
.
- If an object has no owner, the owner_id will display as
For datasets with no owner, the system user (administrative account) can use the GRANT SQL command to grant ownership to a user or role that has access to the table (or the underlying table, for a view).
View Delegation
View delegation means that tables with restricted access may be shared with other Dremio users through the creation of views. When a user with SELECT access to a table creates a view, that user automatically becomes owner of the new object.
Upon creating the view, the same rules of ownership apply to the view. 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.
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.
View delegation is different from privilege assignment. View delegation is implicit delegation of the SELECT privilege on underlying objects. For example, suppose user1
has the SELECT privilege on Table1
and creates View1
based on Table1
. If user1
grants the SELECT privilege on View1
to user2
, then the SELECT privilege that user1
has on Table1
is implicitly granted to user2
. user2
can see the data in Table1
even though they lack explicit SELECT privileges on Table1
. Privilege assignment is an explicit delegation: the owner of an object or a user who belongs to the ADMIN role grants privileges on the object to other users.
Example: View Delegation
user1
has SELECT
access to Table1
and creates View1
, which is based upon Table1
. Whereas in Table1
, user1
was restricted to only querying, with View1
, they now have ALTER
and MANAGE GRANTS
privileges. user1
may now grant user2
with the SELECT
privilege for View1
because of view delegation. The privileges for both the table and view are outlined below:
View1 | Table1 | |
---|---|---|
user1 | ALTER, MANAGE GRANTS | SELECT |
user2 | SELECT | No access |
user2
previously lacked access to the data in Table1
but may now see it using SELECT queries on View1
. The following table describes the actions that each user may perform:
User | Action | Outcome |
---|---|---|
user1 | View the results of View1 queries | yes |
user1 | Modify the original query of View1 | yes |
user2 | View the results of View1 queries | yes |
user2 | Modify the original query of View1 | no |
Because of view delegation, user2
can view the data in Table1
through the results of queries on View1
. However, user2
has no access to Table1
, so they cannot see the original dataset from the Dremio interface and cannot modify the original View1
query.
Example: View Delegation with Revoked Access to the Original Table
To continue the previous example, suppose user1
originally had SELECT
access to Table1
, which they used to create View1
. If an administrator revokes their SELECT
access to Table1
, the following table lists the privileges that user1
and user2
now have on Table1
and View1
:
View1 | Table1 | |
---|---|---|
user1 | ALTER, MANAGE GRANTS | No access |
user2 | SELECT | No access |
The revoked privilege affects the actions that user1
can take with regard to View1
. The following table describes what actions each user may now perform:
User | Action | Outcome |
---|---|---|
user1 | View the results of View1 queries | no |
user1 | Modify the original query of View1 | yes (and no) |
user2 | View the results of View1 queries | no |
user2 | Modify the original query of View1 | no |
user1
and user2
may no longer view the results of the View1
query. Both users lack privileges for the underlying table Table1
, so neither can see data from the table or view from the Dremio interface. Also, user1
can no longer modify the original View1
query unless they remove the reference to Table1
from it. If user1
attempts to modify the query but does not remove the reference, they will not be able to save the modified query.
Example: View Delegation with Original Query Changed and Revoked Access
In this example, both user1
and user2
have ALTER
and MANAGE GRANTS
privileges for Table1
and View1
created from it, as shown in the following table:
View1 | Table1 | |
---|---|---|
user1 | ALTER, MANAGE GRANTS | SELECT |
user2 | ALTER, MANAGE GRANTS | SELECT |
Suppose an administrator then revokes access to both Table1
and View1
from user2
. If user2
modified and saved View1
before their privileges were revoked, subsequent queries would be executed as user2
. After the privileges of user2
are revoked, subsequent queries performed by user1
would display an error message about lacking sufficient privileges. This is because View1
was last altered by user2
, making user2
the owner of View1
.
user2
can no longer see Table1
or query either View1
or Table1
from the Dremio interface. user1
can see only the last definition of View1
that user2
saved. user1
must modify the last definition of View1
to retain ownership and resume viewing query results.
Methods of Assigning Privileges
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 Privileges (GRANT/REVOKE) SQL commands.
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 (see the example scenario outlined below).
You should use this method if you want to restrict a user's access to any other existing or future datasets.
If you're granting the user access to a table, then remember that they'll be able to create views based on that dataset, 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:
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
.
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 to 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:
All datasets exampleGRANT 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.
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
privilege on Folder3
, which means they now have access to all existing and future datasets contained in that folder and its subfolders.