[Feature] Support for database roles

Is this your first time submitting a feature request?

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

Describe the feature

This feature request is for dbt-snowflake to include support for database roles.

There is already a request against the dbt-core project https://github.com/dbt-labs/dbt-core/issues/10587 but raising here for visibility. This request also shows there is synergy with other adapter plugins looking for better support around grants.

There is also a really nice looking pattern suggested by @dbeatty10 on feature request #dbt-core/10587 for how something like this might be implemented from a user point of view.

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, ...]

Describe alternatives you've considered

Today, I actually work around this issue by overriding macros get_grant_sql & get_revoke_sql to perform database role grants instead on account-level role grants. Unfortunately, https://github.com/dbt-labs/dbt-snowflake/pull/1188 will close this loop hole and break the technique. This is the real reason behind this request.

Who will this benefit?

I am strongly opinionated here, database roles can be created and managed with a lower level of privilege than account level roles. This will allow Analytics Engineers to better control and setup data access patterns, without having to coordinate with a central admin or platform team. The more dbt can be used to manage the desired end state the better the dbt experience. Who knows where this could go, perhaps in the future dbt could automatically create any missing database roles defined in the grants.

Are you interested in contributing this feature?

yes

Anything else?

No response

dbt-labs / dbt-core