database role support in resource 'snowflake_grant_privileges_to_role'

[yinxu0619]

Is your feature request related to a problem? Please describe.

• Thank you for maintaining the provider. it has been a great help to us!
  We noticed the recent use of the new resource 'snowflake_grant_privileges_to_role.' We tried to use a database role in Snowflake as the granted role, but it seems that the error message suggests that the 'database role' keyword is missing in the SQL.

Describe the solution you'd like

A clear and concise description of what you want to happen.

• Is it possible to have a parameter to confirm whether it is a database role? just like

resource "snowflake_grant_privileges_to_role" "g" {
 privileges = ["MODIFY", "CREATE TABLE"]
 role_name  = snowflake_role.r.name
 database_role = True  <-- 
 on_schema {
   all_schemas_in_database = snowflake_database.d.name
 }
}

or

resource "snowflake_grant_privileges_to_role" "g" {
  privileges = ["MODIFY", "CREATE TABLE"]
  database_role_name  = snowflake_role.r.name  <--
  on_schema {
    all_schemas_in_database = snowflake_database.d.name
  }
}

A clear and concise description of any alternative solutions or features you've considered.

• Currently, we are still using custom roles, but this goes against the original purpose of Snowflake's database roles.

Add any other context or screenshots about the feature request here.
a quick test demo to reproduce

terraform {
  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = " ~> 0.75.0"
    }
  }
}

resource "snowflake_database" "db" {
  name = "db"
}

resource "snowflake_schema" "schema" {
  database = snowflake_database.db.name
  name = "schema"
}

resource "snowflake_database_role" "db-schema_ro" {
  database = snowflake_database.db.name
  name = "test_role"
  comment = "test ro role"
}

resource "snowflake_grant_privileges_to_role" "schema_to_ro_role" {
  role_name = "${snowflake_database_role.db-schema_ro.name}"
  on_schema {
    schema_name = "\"${snowflake_database.db.name}\".\"${snowflake_schema.schema.name}\""
  }
  depends_on = [ snowflake_database_role.db-schema_ro ]
  
}

resource "snowflake_grant_privileges_to_role" "tables_to_ro_role" {
    privileges = ["SELECT", "REFERENCES"]
    role_name = "${snowflake_database_role.db-schema_ro.name}"
    on_schema_object {
      future {
        object_type_plural = "TABLES"
        in_schema = "\"${snowflake_database.db.name}\".\"${snowflake_schema.schema.name}\""
      }
    }
    depends_on = [ snowflake_database_role.db-schema_ro ]
}

when you apply the above tf, you will get an error in Snowflake like

GRANT SELECT, REFERENCES ON FUTURE TABLES IN SCHEMA "db"."schema" TO ROLE "test_role"

[yinxu0619]

Hi again,

Actually grant database role to database role / custom role is also unavailable for the moment : )

thanks
xyin

[mengjunOS]

We encounter the same issue as well. Our workaround is to use the snowflake_grant_privileges_to_role in multiple resources to grant all privileges on db, schemas, views and tables. It will be great to see the database_role support ASAP.

[sfc-gh-asawicki]

Hey @yinxu0619. Thanks for creating the issue.

Granting to database roles has yet to be supported. We are currently reworking grant resources, and supporting grants for database roles is one of our highest priorities. Stay tuned! :)

[astanfel]

@sfc-gh-asawicki Any updates on this? db role support would be a huge help to add into the provider

[sfc-gh-asawicki]

Hey @astanfel. I don't want to spoil it but we have the designs ready, and we will probably implement them next week. 🤞

cc: @sfc-gh-jcieslak

[sfc-gh-jcieslak]

Hey @yinxu0619
I guess we can close this one as snowflake_grant_privileges_to_database_role and snowflake_grant_database_role has been available for some time.

[sfc-gh-jcieslak]

Hey 👋
Closing the issue as it was resolved by introducing the snowflake_grant_privileges_to_database_role and snowflake_grant_database_role resources.