[Feature] Support Snowflake Database Roles in grants

[Aviss]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Snowflake supports the concept of a database role, a role that can only be assigned permissions on a database, but no account level permissions like managing users.

However, this comes with a slightly altered SQL-Query where GRANTS and REVOKES are concerned.

# Database role grant
GRANT SELECT ON DB.SCHEMA.TABLE to database role ROLE_NAME;
# Regular role grant
GRANT SELECT ON DB.SCHEMA.TABLE to [role] ROLE_NAME;
# Database role revoke
REVOKE SELECT ON DB.SCHEMA.TABLE from database role ROLE_NAME;
# Regular role revoke
REVOKE SELECT ON DB.SCHEMA.TABLE from [role] ROLE_NAME;

From the logs I gather that DBT does not specify the object type in the Query and thus implicitly refers to roles. In fact, when a databse role grant has been (manually) added to a table this leads to an error:

show grants on [Model]
09:25:06.053642 [debug] [Thread-1 (]: SQL status: SUCCESS 3 in 0.0 seconds
[...]
revoke SELECT on [Model] from [DATABASE_ROLE_NAME];
09:25:06.164517 [debug] [Thread-1 (]: Snowflake adapter: Snowflake query id: 01b67eb5-0302-f4cc-0002-501e0063758a
09:25:06.166090 [debug] [Thread-1 (]: Snowflake adapter: Snowflake error: 002003 (02000): SQL compilation error:
Role '[DATABASE_ROLE_NAME]' does not exist or not authorized.

For revokes the neccessary information can be gathered from the granted_to column in the show grants response:

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by	granted_by_role_type
[...]	SELECT	TABLE	[TABLE_ID]	ROLE	[ROLE_NAME]	false	ACCOUNTADMIN	ROLE
[...]	SELECT	TABLE	[TABLE_ID]	DATABASE_ROLE	[DATABASE_ROLE_NAME]	false	ACCOUNTADMIN	ROLE

However I can not think of a concise way to configure this distinction in the models.

One option would be to duplicate the grants keyword:

{{
    config(
        materialized='incremental',
        grants = {
            'select': '[ROLE_NAME]'
        },
        database_role_grants = {
            'select': '[DATABASE_ROLE_NAME]'
        }
    )
}}

Alternatively a special notation could be used:

{{
    config(
        materialized='incremental',
        grants = {
            'select': 'DATABSE_ROLE.[DATABASE_ROLE_NAME]'
        },
    )
}}

As none of these options are particularly elegant, I would understand a reluctance to implement this feature. I'm opening this issue mostly for documentation purposes since I was unable to find anything on this topic online.

In that sense I would suggest adding this limitation to the DBT grants documentation page since it already contains a database specific section

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[dbeatty10]

Thanks for opening this @Aviss!

Ah, I can see how the syntax for granting to database roles differs and how that would necessitate new options within the dbt interface.

This looks similar to the following issue in dbt-redshift: dbt-labs/dbt-redshift#415. It has the associated PR dbt-labs/dbt-redshift#626 which proposes the following interface:

If this pattern were adopted within dbt-core, then the interface for dbt-snowflake might look like:

models:
    - name: MODEL_NAME_1
      config:
        grants:
            # New syntax option
            select:
                role: [ROLE_NAME_1, ROLE_NAME_2, ...]
                database_role: [DATABASE_ROLE_NAME_1, DATABASE_ROLE_NAME_2, ...]
            insert:
                role: [ROLE_NAME_1, ROLE_NAME_2, ...]
                database_role: [DATABASE_ROLE_NAME_1, DATABASE_ROLE_NAME_2, ...]
    - name: MODEL_NAME_2
      config:
        grants:
            # Also preserve existing syntax for full backwards compatibility
            select: [ROLE_NAME_1, ROLE_NAME_2, ...]

This isn't something we're likely to prioritize anytime soon, but leaving this issue open for folks to upvote.

[dbeatty10]

In that sense I would suggest adding this limitation to the DBT grants documentation page since it already contains a database specific section

Good idea @Aviss ! 💡

Opened this PR to update the documentation: dbt-labs/docs.getdbt.com#6120