# 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 property 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](../../docs/build/hooks-operations.md). 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](../../docs/build/hooks-operations.md).

## 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](../artifacts/manifest-json.md).

### 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

models/schema.yml

```yml
models:
  - name: specific_model
    config:
      grants:
        select: ['reporter', 'bi']
```

The `grants` config can also be defined:

* under the `models` config in the project file (`dbt_project.yml`)
* in a `config()` Jinja macro within a model's SQL file

See [configs and properties](../configs-and-properties.md) for details.

### Seeds

seeds/schema.yml

```yml
seeds:
  - name: seed_name
    config:
      grants:
        select: ['reporter', 'bi']
```

The `grants` config can also be defined under the `seeds` config in the project file (`dbt_project.yml`). See [configs and properties](../configs-and-properties.md) for details.

### Snapshots

snapshots/schema.yml

```yml
snapshots:
  - name: snapshot_name
    config:  
      grants:
        select: ['reporter', 'bi']
```

The `grants` config can be defined:

* Under the `snapshots` config in the property file (`snapshots/schema.yml`)
* Under the `snapshots` config in the project file (`dbt_project.yml`)
* In a snapshot's SQL file `config()` Jinja macro

See [configs and properties](../configs-and-properties.md) 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

```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

```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

```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](./plus-prefix.md).
* `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

```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

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

### Revoke from one user

dbt\_project.yml

```yml
models:
  +grants:
    select: ['user_b']
```

### Revoke from all users

dbt\_project.yml

```yml
models:
  +grants:
    select: []
```

### Stop dbt from managing grants

dbt\_project.yml

```yml
models:

  # this section intentionally left blank
```

## 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

```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:

```sql
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

```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:

```sql
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.

### BigQuery

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:

* [Understanding GCP roles](https://cloud.google.com/iam/docs/understanding-roles)
* [How to format grantees](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-control-language#user_list)

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"](./bigquery-configs.md#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"](./grants.md)

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:

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

Granting permission in a model schema using BigQuery:

models/schema.yml

```yml
models:
  - name: specific_model
    config:
      grants:
        roles/bigquery.dataViewer: ['user:someone@yourcompany.com']
```

### Databricks

* OSS Apache Spark / Delta Lake do not support `grants`.

* Databricks automatically enables `grants` on SQL endpoints. For interactive clusters, admins should enable grant functionality using these two setup steps in the Databricks documentation:

  * [Enable table access control for your workspace](https://docs.databricks.com/administration-guide/access-control/table-acl.html)
  * [Enable table access control for a cluster](https://docs.databricks.com/security/access-control/table-acls/table-acl.html)

* In order to grant `READ_METADATA` or `USAGE`, use [post-hooks](./pre-hook-post-hook.md)

### Redshift

* Redshift supports granting to users, [groups](https://docs.aws.amazon.com/redshift/latest/dg/r_Groups.html), and [roles](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-managing.html). Use the `group:` or `role:` prefix in grantee names to grant to groups or roles. Unprefixed names are treated as users.

```yaml
models:
  +grants:
    select: ["user1", "user:user2", "group:analysts", "role:reporter"]
```

### Snowflake

* dbt accounts for the [`copy_grants` configuration](./snowflake-configs.md#copying-grants) when calculating which grants need to be added or removed.
* Granting to / revoking from is only fully supported for Snowflake roles (not [database roles](https://docs.snowflake.com/user-guide/security-access-control-overview#types-of-roles)).

## Was this page helpful?

YesNo

[Privacy policy](https://www.getdbt.com/cloud/privacy-policy)[Create a GitHub issue](https://github.com/dbt-labs/docs.getdbt.com/issues)

This site is protected by reCAPTCHA and the Google [Privacy Policy](https://policies.google.com/privacy) and [Terms of Service](https://policies.google.com/terms) apply.