On this page

    Privileges

    Based on the inheritance model, when a user or role is granted privileges to an object with child objects, those privileges also apply to the child objects, regardless of an object’s ownership. This is also known as scope, meaning the limits to which a privilege applies.

    By default, all users are granted the PUBLIC role. However, privileges must be manually applied to each object.

    Object Hierarchy

    Each object, such as a project, cloud, or dataset, exists within a hierarchy of “containers.” The uppermost container exists as the system administrator. All other objects are contained within sources or spaces and then organized into folders. The hierarchy of these objects is illustrated and described further on the Architecture help page.

    All Supported Privileges

    note:

    Users may only see and use child objects if they’ve been given access to its parent objects. For example, a user cannot use SELECT or ALTER privileges on a dataset until they’ve been granted the USAGE privilege for the project housing those datasets.

    If a user lacks the necessary privileges to edit or use an object, they will see a permissions error message indicating such.

    Dataset Privileges

    PrivilegeTarget ObjectsDescription
    ALLProject, Space, Source, Folder/Schema, Dataset/ViewGrant the user all possible privileges for an object type, except MANAGE GRANTS and OWNERSHIP.
    ALTERProject, Space, Source, Folder/Schema, Dataset/ViewEdits PDS or view definitions or settings of all datasets in scope. For PDS's, this includes managing metadata, such as Metadata Refresh and Forget.
    ALTER REFLECTIONProject, Source, Space, Folder/SchemaCreate, edit, and view reflections on all datasets in scope. Includes granting access to all interfaces, such as the Dataset Reflection pages, Administrator Reflection pages, and any REST API endpoints.
    DELETEProject, Source, Space, Folder/Schema, Dataset/ViewExecute the associated DML operation on all datasets in scope. This is only supported with Apache Iceberg datasets.
    DROPProject, Source, Folder/SchemaDrop tables on any dataset in the scope.
    EXECUTEDataset/ViewExecute the associated user-defined function (UDF) for the purposes of querying tables/view with row access or column masking filters applied. Only the owner of a UDF may view or edit the associated function. Users or roles must have this privilege in order to apply filtering and masking policies.
    INSERTProject, Source, Space, Folder/Schema, Dataset/ViewExecute the associated DML operation on all datasets in scope. This is only supported with Apache Iceberg datasets.
    MANAGE GRANTSOrganization, Project, Engine, Cloud, Source, Space, Folder/Schema, Dataset/ViewModifies the privileges of all objects in the set scope. Also changes the owner of all objects within the scope.
    OWNERSHIPOrganization, Cloud, Project, Engine, Space, Source, Folder/Schema, Dataset/View, Users, RolesAllows all actions on the object and objects within the object. Actions include modifying object settings, granting/revoking user and role access, and deleting the object.
    ROLLBACKProject, Source, Space, Folder/Schema, Dataset/ViewExecute the associated DML operation on all datasets in scope. This is only supported with Apache Iceberg datasets.
    SELECTProject, Source, Space, Folder/Schema, Dataset/ViewGives the ability to execute SELECT queries in the scope.
    TRUNCATEProject, Source, Space, Folder/Schema, Dataset/ViewExecute the associated DML operation on all datasets in scope. This is only supported with Apache Iceberg datasets.
    UPDATEProject, Source, Space, Folder/Schema, Dataset/ViewExecute the associated DML operation on all datasets in scope. This is only supported with Apache Iceberg datasets.
    VIEW REFLECTIONProject, Source, Space, Folder/SchemaView Reflections on all datasets in the scope. Includes access to all Dremio interfaces, such as the Dataset Reflection pages, Administrator Reflection pages, and any REST API endpoints.
    VIEW SCHEMAProject, Source, Space, Folder/SchemaView the schema definitions, wikis, graphs of all datasets in the scope. However, this does not allow a user to read the data contained in any affected datasets.

    Organization, Project, Cloud, Engine, Source, & Space Privileges

    PrivilegeTarget ObjectsDescription
    CREATE BILLING ACCOUNTOrganizationCreates a new billing account, which is used to handle usage invoices if you are using Dremio Enterprise edition. The account creator is the default owner.
    CREATE CLOUDOrganizationCreate a new cloud. Users may not add clouds without this privilege. The cloud creator is the default owner for the cloud.
    CREATE PROJECTOrganizationCreate a new project. The project creator is the default owner of the project.
    CREATE ROLEOrganizationCreate a role. The user responsible for its creation automatically becomes its owner.
    CREATE TABLEProject, Source, FolderCreate a table using CREATE TABLE AS SELECT (CTAS) for all datasets in the scope.
    CREATE USEROrganizationCreate a user. The user responsible for its creation automatically becomes its owner.
    EXTERNAL QUERYProject, SourceRun the external_query table function on the source.
    MODIFYProject, Cloud, Engine, Source, SpaceEdit and delete an object. The following conditions apply:
    • If Space or Source, edit the object's settings.
    • If Project, edit workload management settings and change support key settings.
    MONITORProject, Cloud, EngineAllow the user to read all current object settings.
    OPERATEProject, EngineGive the ability to start and stop an engine, as well as blacklist any node in the scope. If this is set at the Project level, it applies to all Engines.
    USAGEProject, EngineGive the ability to use the object. Without this privilege, users cannot access datasets contained in a project or run queries using the available engines.
    • Projects: This privilege is required for a user to access the project and its datasets.
    • Engines: This privilege is required for a user to run queries against the engine. By default, engines grant all users the USAGE privilege, but this can be revoked or otherwise changed.
    VIEW_JOB_HISTORYProjectGive the ability to view the job history of all users from the Jobs page.

    Understanding 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 PDS, they may create a virtual dataset (i.e., “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 PDS, meaning the extended access granted to the view will not extend to the PDS it is based upon. For more information, see VDS 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 perform 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.

    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).

    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 PDS as the scope of a user’s privilege, then that user may only perform the associated action to that dataset. However, if the scope of a privilege is instead set at the folder level, then the user may perform the granted privilege to all PDSs and views contained in that folder.

    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.

    The implication of ownership is that only an object’s owner retains all privileges for that object. As a result, the owner is able to grant or revoke user/role access to that object and any child objects associated, modify the object’s settings, and also 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 ownership may be assigned or modified via GRANT SQL 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

    VDS Delegation

    Physical datasets (PDS) with restricted access may be used with greater access through the creation of a view. When a user with SELECT access to a PDS 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 PDS.

    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 GRANT SQL 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 VDS lacks privileges to modify the underlying PDS. This applies to each physical dataset (PDS) 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 physical dataset 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 PDS, 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 physical dataset, 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:

    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:

    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.

    Granting Privileges

    By default, all users have all privileges granted to them for any objects without applicable permissions. Once a specific user has been granted access to an object, access is then restricted to only users granted access. All other users no longer have access.

    The manual granting of privileges is accomplished using the SQL Editor, REST APIs, or the Privileges screen. The SQL Editor is accessible from any dataset and any GRANT SQL commands entered here will apply to the scope supplied with the command itself.

    To assign privileges to a user for an object using the Dremio interface:

    1. Navigate to the desired object (folder, dataset, source, etc.).
    2. Under the Action column on the right-hand side of the screen, click the gear button for spaces and data sources or the ellipses () button for folders and datasets.
    3. Click Edit Details or Settings, depending on the object type.
    4. Select the Privileges tab. This is where all privileges may be manually assigned to users for an object.
    5. Enter the user’s name or email address under Add User/Role.
    6. Click Add to Privileges. If the entry matches a user in Dremio, then a record will appear for them in the privileges table.
    7. Select the desired privileges for that user.
    8. When finished, click Save to preserve your changes.

    Revoking Privileges

    By default, all users have all privileges granted to them for any objects without applicable permissions. Once a specific user has been granted access to an object, access is then restricted to only users granted access. All other users no longer have access.

    The manual revoking of privileges is accomplished using the SQL Editor, REST APIs, or the Privileges screen. The SQL Editor is accessible from any dataset and any REVOKE SQL commands entered here will apply to the scope supplied with the command itself.

    To revoke privileges from a user for an object using the Dremio interface:

    1. Navigate to the desired object (folder, dataset, source, etc.).
    2. Under the Action column on the right-hand side of the screen, click the gear button for spaces and data sources or the ellipses () button for folders and datasets.
    3. Click Edit Details or Settings, depending on the object type.
    4. Select the Privileges tab. This is where all currently-assigned privileges display.
    5. Scroll down to the desired user record. If the user you’re searching for does not display here, then they do not have privileges for the object, unless all privileges are granted to all users via the PUBLIC role.
    6. Deselect the checkbox under the privilege columns you wish to revoke access.
    7. When finished, click Save to preserve your changes.

    note:

    If a user has been granted a specific privilege for an object by more than one group and that privilege is revoked for one group, the user will retain that privilege until it is revoked by all groups associated with the same objects.