[CT-717] Add Grants to BigQuery Materializations

[nathaniel-may]

blocked by: dbt-labs/dbt-core#5263

Once the above work is merged into core, there are some tweaks that need to be made in the BigQuery adapter to fully expose the feature:

• add a call to apply_grants(..., revoke=True) to all custom materializations
• override apply_grants so that it directly makes the API call to revoke grants (BigQuery Docs) instead of getting sql from get_revoke_all_sql.

[jtcohen6]

Following up on latest thinking in dbt-labs/dbt-core#5263 (comment), there may actually be a way to do this that's SQL-only, without any API calls. That may be slightly slower, but it's much more visible, and easier to debug.

Docs:

• https://cloud.google.com/bigquery/docs/table-access-controls
• https://cloud.google.com/bigquery/docs/information-schema-object-privileges

Defaults should work for:

• get_grant_sql
• get_revoke_sql, IFF we take the "more sophisticated" approach of calculating diffs, since BQ only supports revoke [specific_privilege], not revoke all

We'd need a custom version of get_show_grants_sql, which should look like (pseudo code):

select * from {{ relation.project }}.`{{ target.location }}`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
where object_schema = '{{ relation.dataset }}' and object_name = '{{ relation.identifier }}'

Example flow

In dbt:

-- models/my_table.sql

{{ config(grants = {
    'roles/bigquery.dataViewer': ['user:jeremy@example.com'],
    'roles/custom.whatEver': ['user:another@example.com'],
} }}

select ...

So dbt runs:

grant `roles/bigquery.dataViewer` on dbt_jcohen.my_table to "user:jeremy@example.com";
grant `roles/custom.whatEver` on dbt_jcohen.my_table to "user:another@example.com";

Now, we change that to just:

-- models/my_table.sql

{{ config(grants = {
    'roles/bigquery.dataViewer': ['user:jeremy@example.com'],
} }}

select ...

So dbt runs:

select * from `region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
where object_schema = 'dbt_jcohen' and object_name = 'my_table';

(result of that query, in JSON format)

{  "object_catalog": "dbt-test-env",  "object_schema": "dbt_jcohen",  "object_name": "my_table",  "object_type": "TABLE",  "privilege_type": "roles/bigquery.dataViewer",  "grantee": "user:jeremy@example.com"}
{  "object_catalog": "dbt-test-env",  "object_schema": "dbt_jcohen",  "object_name": "my_table",  "object_type": "TABLE",  "privilege_type": "roles/custom.whatEver",  "grantee": "user:another@example.com"}

revoke `roles/custom.whatEver` on dbt_jcohen.my_table from "user:another@example.com";

The cool thing about BigQuery is that we could actually perform the show + revoke + grant statements all in one dynamic "script," using "procedural SQL", if we so chose. That's fairly different from the general implementation we'd need on other databases, though.

[avipaul6]

Will this also work for Authorized Views? @jtcohen6
I can't find any SQL example of revoking just that view from the dataset without having to

1. call the dataset,
2. get the whole list of views and
3. remove just that view
4. update the dataset.

I can see that done via API calls in Python from https://cloud.google.com/bigquery/docs/samples/bigquery-revoke-dataset-access

[jtcohen6]

@avipaul6 This will not work for authorized views, for the reasons you've indicated.

dbt already supports authorized views on BigQuery today, but the syntax is pretty wonky, and differs from the standard we're looking to implement in this feature: https://docs.getdbt.com/reference/resource-configs/bigquery-configs#authorized-views

Our main constraint here by what BigQuery StandardSQL makes available. I'm definitely interested in future work that reconciles / rationalizes dbt's approach to standard grants with the dataset operations required by authorized views.