Skip to main content

grants

You can manage access to the datasets you're producing with dbt by using grants. To implement these permissions, define grants as resource configs on each model, seed, or snapshot. Define the default grants that apply to the entire project in your dbt_project.yml, and define model-specific grants within each model's SQL or YAML file.

The grant resource configs enable you to apply permissions at build time to a specific set of recipients and model, seed, or snapshot. When your model, seed, or snapshot finishes building, dbt ensures that the grants on its view or table match exactly the grants you have configured.

dbt aims to use the most efficient approach when updating grants, which varies based on the adapter you're using, and whether dbt is replacing or updating an object that already exists. You can always check the debug logs for the full set of grant and revoke statements that dbt runs.

You should define grants as resource configs whenever possible, but you might occasionally need to write grants statements manually and run them using hooks. For example, hooks may be appropriate if you want to:

  • Apply grants on other database objects besides views and tables.
  • Create more granular row- and column-level access, use masking policies, or apply future grants.
  • Take advantage of more advanced permission capabilities offered by your data platform, for which dbt does not offer out-of-the-box support using resource configuration.
  • Apply grants in a more complex or custom manner, beyond what the built-in grants capability can provide.

For more information on hooks, see Hooks & operations.

Definition​

You can use the grants field to set permissions or grants for a resource. When you run a model, seed data, or snapshot a dataset, dbt will run grant and/or revoke statements to ensure that the permissions on the database object match the grants you have configured on the resource.

Like all configurations, grants will be included in dbt project metadata, including the manifest artifact.

Common syntax​

Grants have two key components:

  • Privilege: A right to perform a specific action or set of actions on an object in the database, such as selecting data from a table.
  • Grantees: One or more recipients of granted privileges. Some platforms also call these "principals." For example, a grantee could be a user, a group of users, a role held by one or more users (Snowflake), or a service account (BigQuery/GCP).

Configuring grants​

You can configure grants in dbt_project.yml to apply grants to many resources at once—all models in your project, a package, or a subfolder—and you can also configure grants one-by-one for specific resources, in YAML config: blocks or right within their .sql files.

models/schema.yml
models:
  - name: specific_model
    config:
      grants:
        select: ['reporter', 'bi']

The grants config can also be defined:

  • under the models config block in dbt_project.yml
  • in a config() Jinja macro within a model's SQL file

See configs and properties for details.

Grant config inheritance​

When you set grants for the same model in multiple places, such as in dbt_project.yml and in a more-specific .sql or .yml file, dbt's default behavior replaces the less-specific set of grantees with the more-specific set of grantees. This "merge and clobber" behavior updates each privilege when dbt parses your project.

For example:

dbt_project.yml
models:
  +grants:  # In this case the + is not optional, you must include it for your project to parse.
    select: ['user_a', 'user_b']
models/specific_model.sql
{{ config(grants = {'select': ['user_c']}) }}

As a result of this configuration, specific_model will be configured to grant the select privilege to user_c only. After you run specific_model, that is the only granted privilege you would see in the database, and the only grant statement you would find in dbt's logs.

Let's say we wanted to add user_c to the existing list of grantees receiving the select privilege on specific_model, rather than replacing that list entirely. To accomplish that, we can use the + ("addition") symbol, prefixing the name of the privilege:

models/specific_model.sql
{{ config(grants = {'+select': ['user_c']}) }}

Now, the model will grant select to user_a, user_b, AND user_c!

Notes:

  • This will only take effect for privileges which include the + prefix. Each privilege controls that behavior separately. If we were granting other privileges, in addition to select, and those privilege names lacked the + prefix, they would continue to "clobber" rather than "add" new grantees.
  • This use of +, controlling clobber vs. add merge behavior, is distinct from the use of + in dbt_project.yml (shown in the example above) for defining configs with dictionary values. For more information, see the plus prefix.
  • grants is the first config to support a + prefix for controlling config merge behavior. Currently, it's the only one. If it proves useful, we may extend this capability to new and existing configs in the future.

Conditional grants​

Like any other config, you can use Jinja to vary the grants in different contexts. For example, you might grant different permissions in prod than dev:

dbt_project.yml
models:
  +grants:
    select: "{{ ['user_a', 'user_b'] if target.name == 'prod' else ['user_c'] }}"

Revoking grants​

dbt only modifies grants on a node (including revocation) when a grants configuration is attached to that node. For example, imagine you had originally specified the following grants in dbt_project.yml:

dbt_project.yml
models:
  +grants:
    select: ['user_a', 'user_b']

If you delete the entire +grants section, dbt assumes you no longer want it to manage grants and doesn't change anything. To have dbt revoke all existing grants from a node, provide an empty list of grantees.

dbt_project.yml
models:
  +grants:
    select: ['user_b']

General examples​

You can grant each permission to a single grantee, or a set of multiple grantees. In this example, we're granting select on this model to just bi_user, so that it can be queried in our Business Intelligence (BI) tool.

models/table_model.sql
{{ config(materialized = 'table', grants = {
    'select': 'bi_user'
}) }}

When dbt runs this model for the first time, it will create the table, and then run code like:

grant select on schema_name.table_model to bi_user;

In this case, we're creating an incremental model, and granting the select privilege to two recipients: bi_user and reporter.

models/incremental_model.sql
{{ config(materialized = 'incremental', grants = {
    'select': ['bi_user', 'reporter']
}) }}

When dbt runs this model for the first time, it will create the table, and then run code like:

grant select on schema_name.incremental_model to bi_user, reporter;

In subsequent runs, dbt will use database-specific SQL to show the grants already on incremental_model, and then determine if any revoke or grant statements are needed.

Database-specific requirements and notes​

While we try to standardize the terms we use to describe different features, you will always find nuances in different databases. This section outlines some of those database-specific requirements and notes.

In our examples above and below, you will find us referring to a privilege named select, and a grantee named another_user. Many databases use these or similar terms. Be aware that your database may require different syntax for privileges and grantees; you must configure grants in dbt with the appropriate names for both.

On BigQuery, "privileges" are called "roles," and they take the form roles/service.roleName. For instance, instead of granting select on a model, you would grant roles/bigquery.dataViewer.

Grantees can be users, groups, service accounts, domains—and each needs to be clearly demarcated as such with a prefix. For instance, to grant access on a model to someone@yourcompany.com, you need to specify them as user:someone@yourcompany.com.

We encourage you to read Google's documentation for more context:

Note

The grants config and the grant_access_to config are distinct.

  • grant_access_to: Enables you to set up authorized views. When configured, dbt provides an authorized view access to show partial information from other datasets, without providing end users with full access to those underlying datasets. For more information, see "BigQuery configurations: Authorized views"
  • grants: Provides specific permissions to users, groups, or service accounts for managing access to datasets you're producing with dbt. For more information, see "Resource configs: grants"

You can use the two features together: "authorize" a view model with the grants_access_to configuration, and then add grants to that view model to share its query results (and only its query results) with other users, groups, or service accounts.

BigQuery examples​

Granting permission using SQL and BigQuery:

{{ config(grants = {'roles/bigquery.dataViewer': ['user:someone@yourcompany.com']}) }}

Granting permission in a model schema using BigQuery:

models/schema.yml
models:
  - name: specific_model
    config:
      grants:
        roles/bigquery.dataViewer: ['user:someone@yourcompany.com']
0
Edit this page