snowflake_grant_privileges_to_role

[Bryan-Meier]

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

We are often giving a role access to future and existing objects like tables, views, functions, etc. We have to define 2 different resources for these scenarios. One for the future and another for the current objects. If you are in a situation where you are iterating across every schema in every database and applying future and current grants, this spawns a ton of resources, which leads to slower plans, drift detection, etc.

Describe the solution you'd like

It would be great if we could include multiple operations in the 1 resource for scenarios as described above. I could see the usage being something like this:

resource "snowflake_grant_privileges_to_role" "grant_schema_read_usage" {
  privileges = ["USAGE"]

  role_name  = "ROLE_NAME"

  on_schema {
    future_schemas_in_database = var.database_name
    schema_name = "\"${var.database_name}\".\"${var.schema_name}\""
  }
}

or

resource "snowflake_grant_privileges_to_role" "grant_schema_read_tables" {
  privileges = ["SELECT"]
  role_name  = "ROLE_NAME"
  
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_schema          = "\"${var.database_name}\".\"${var.schema_name}\""
    }

    all {
      object_type_plural = "TABLES"
      in_schema          = "\"${var.database_name}\".\"${var.schema_name}\""
    }
  }
}

Describe alternatives you've considered

The alternative is to keep doing this as separate resources.

[Josh-a-e]

Similarly, we often find ourselves with a need to declare one of these for each user. Having an array of role_names would be exceptionally useful and cut down on very large amounts of code

resource "snowflake_grant_privileges_to_role" "grant_schema_read_usage" {
  privileges = ["USAGE"]

  # role_name  = "ROLE_NAME"
  role_names  = ["ROLE_NAME_A", "ROLE_NAME_B"]

  on_schema {
    future_schemas_in_database = var.database_name
    schema_name = "\"${var.database_name}\".\"${var.schema_name}\""
  }
}

[sfc-gh-jcieslak]

Hey @Bryan-Meier. Thanks for reaching out to us.

We are in the process of redesigning the existing resources and adding the missing functionalities as part of https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#supporting-all-snowflake-ga-features). I'll add it to our list of things we would like to address/discuss.

[lukeyz1]

@Josh-a-e This would definitely be very useful as we also would like to grant multiple roles to the same object. @sfc-gh-jcieslak Could this be a feature in the roadmap or is there limitations to this?

[sfc-gh-jcieslak]

Hey @lukeyz1 It may, we'll talk about it. There are limitations to those proposals, but I didn't analyze them well enough to say if they'll end up in the provider or not. There are certainly some questions or cases I can think of we would struggle with (grant resources are already complex with one role and one action), but because it's a common issue with grant resources (large number of them == slower apply times) we have them on our todo list of things we would like to analyze/improve.

[Bryan-Meier]

@sfc-gh-jcieslak Do you know if Snowflake has considered simplifying the privilege and role schema/approach? The approach that Snowflake adopted from the beginning is verbose to the extreme.

I have been around a lot of databases systems in my career and I gotta say (IMO) Microsoft's privilege hierarchical approach to privileges in products like SQL Server is hands down the easiest to manage and requires way less manual maintenance. I would guess that I could apply all the privileges I would need in more than half the SQL statements on a privilege system like SQL Server. It would also make this TF project much more simplified. 😄

[sfc-gh-jcieslak]

Hey 👋 , I'm not aware of Snowflake's design decisions on their grants model, but I agree they're tricky to work with sometimes 😓. Thanks for the feedback though, I'll forward it and maybe get back to you if there is anything worth sharing.