search

dbt-labs / dbt-snowflake

dbt-snowflake contains all of the code enabling dbt to work with Snowflake
https://getdbt.com
Apache License 2.0
280 stars 172 forks source link

[ADAP-944] [Feature] Support secondary roles #803

Open seub opened 12 months ago

seub commented 12 months ago

Is this your first time submitting a feature request?

Describe the feature

This feature request is basically a re-opening of dbt-labs/dbt-core#449, but I thought I'd rephrase it slightly differently. It is also related to dbt-core dbt-labs/dbt-adapters#212.

It would be nice if dbt supported secondary roles for the Snowflake adapter. I'm imagining something like, in profiles.yml:

<profile_name>:
  outputs:
    <target_name>:
      type: snowflake
      ...
      role: <primary role>
      secondary_roles: <value>

  ...

where <value> is something that can directly be pasted into the Snowflake query

use secondary roles <value>;

such as none, all, role_name, or a comma-separated list of role names.

Describe alternatives you've considered

As a workaround, I've tried to use a pre-hook:

# dbt_project.yml
...
models:
  +pre-hook:
    - use secondary role <role_name>;
    ...

This almost works, but not quite for (at least) the following reasons:

Who will this benefit?

Here is a use case that I think many people will be interested in.

dbt is great for easily switching environments with--target, and providing powerful tools like --defer and state selection. This enables many projects to implement a CI/CD for their org's dbt projects, which include building the dbt project (probably with --defer --select state:modified+) in an isolated "CI environment", say a dedicated database DBT_CI_TESTS.

In order for dbt to operate in DBT_CI_TESTS, a natural solution is to grant whichever role dbt is using RW privileges in that database. However this is not ideal because:

  1. It's not super scalable: as more and more dbt projects use more and more roles, they will all need to be manually granted access.
  2. It doesn't fully leverage the isolation of environments: ideally, you'd want dbt to only have write access to the database DBT_CI_TESTS during the CI tests, so that you have a strong guarantee that your prod environment won't be messed up.

Secondary roles elegantly solve both these issues! Just put the usual role as the secondary role, and put as the primary role a dedicated role DBT_CI_TESTS_RW that only has RW access in DBT_CI_TESTS.

Are you interested in contributing this feature?

Yes

Anything else?

No response

rumbin commented 11 months ago

Amazing to see that the feature I am looking for just now has been suggested merely 3 days ago...

My use case is to be able to use dynamic data masking using the is_role_in_session() function. This way our domain-specific analysts can all use the same primary role for wirking with dbt but the accessibility of PII data would be controlled by secondary roles that we'd grant to them based on their data domains.

luke-siebert commented 6 months ago

I would also like to see this feature added, as we have several teams using DBT and are implementing a Snowflake security model that will require the use of secondary roles.

jharris-coh commented 6 months ago

We'd love to see this feature as well. It's not ideal having a robust security model that takes advantage of secondary roles and pivoting to a less scalable workaround for adding new users to work in dbt in our transformation layer.

benjaminifaught commented 4 months ago

My organization would also like this feature.

jtcohen6 commented 4 months ago

IMO the way this should work is very similar to how dbt already supports snowflake_warehouse: by configuring a secondary role on the model/test/etc, and dbt would call use secondary role before materializing that model.

I see I said this a while back:

This requires us to fix two things in dbt-core: