[❄️ Snowflake Official] Grant Resource Refactoring

[sfc-gh-swinkler]

Introduction

Hello, I am Scott Winkler! I am a Snowflake employee and tech lead in charge of maintaining the provider. I would like to use this discussion forum as an avenue to gather public opinion and share internal roadmap planning. We would like to do a better job of engaging with the community. If you find this kind of topic to be useful, then please leave a 👍 and comment below.

Problem Statement

There are currently 25 grant resources, which can be confusing to use because its not always clear what APIs are being invoked under the hood. I propose refactoring grant resources to better match what the grant API supports https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html.

There are basically five different kinds of grants:

1. role grants
2. database role grants
3. share grants
4. ownership grant for roles
5. ownership grant for database roles

The question is: how many resources should be created to manage these different grants? 5? 10? Or something else?

5 Resources

Because there are five basic types of grants, the simplest approach would be to create five different types of resources:

1. snowflake_role_grant
2. snowflake_database_role_grant
3. snowflake_share_grant
4. snowflake_role_ownership_grant
5. snowflake_database_role_ownership_grant

These five resources would replace all existing 25 grant resources that we have. An example implementation of the snowflake_role_grant is shown below

resource "snowflake_role_grant" "rg" {
  //required
  object_name = "" //Specifies the identifier for the object on which the privileges are granted. (required for account objects and schema objects)
  object_type = "" // Specifies the type of object (required for schema objects):
  object_type_plural = "" 
  role_name = ""
  database_role_name = "" // needs to be fully qualified

  //optional
  on_future = true
  with_grant_option = true
  schema_name = ""
  database_name = ""

  //privileges 
  global_privileges = []
  account_object_privileges = []
  schema_privileges = []
  schema_object_privileges = []
}

Alternatively, we could have code blocks to group each type of privilege together:

resource "snowflake_role_grant" "rg1" {
  global_privileges {
    all_privileges = false
    privileges = [
      // ...
    ]
  }

  account_object_privileges {
    object_name = ""
    object_type = ""
    all_privileges = false
    privileges = [
      // ...
    ]
  }

  schema_privileges {
    schema_name = ""
    database_name = ""
    all_privileges = false

  }

  schema_object_privileges {
    // ...
  }
}

These implementations both closely matches the API. However, one thing I am worried about is that the snowflake_role_grant and snowflake_database_role_grant would be difficult to use, because they have so many attributes. In December I faced a similar problem when I was creating some new resources to manage Snowflake parameters. It would have been possible to create a single overarching snowflake_parameter resource to handle everything, but i thought that it would make more sense from a usability point of view to have them as three separate resources which you can see here:

• snowflake_account_parameter
• snowflake_session_parameter
• snowflake_object_parameter

I believe this was a good decision, but I also didn't solicit feedback from the community, something I hope to remedy this time around. That brings me to the second proposal that I have.

10 Resources

Since the role grants and database role grants are fairly complex, it might make sense to split them into separate resources based on the type of privileges you are trying to assign. For example we could have the following 10 resources.

1. snowflake_role_grant_global_privileges
2. snowflake_role_grant_account_object_privileges
3. snowflake_role_grant_schema_privileges
4. snowflake_role_grant_schema_object_privileges
5. snowflake_database_role_grant_privileges
6. snowflake_database_role_grant_schema_privilges
7. snowflake_database_role_grant_schema_object_privileges
8. snowflake_share_grant_object_privileges
9. snowflake_role_ownership_grant
10. snowflake_database_role_ownership_grant

Note that role_grant has been split into 4 different resources, and database role grant has been split into 3. An example of the snowflake_role_grant_global_privileges resource is shown below.

resource "snowflake_role_grant_global_privilege" {
  all_privileges = true
  global_privileges = []
  role_name = ""
  with_grant_option = true
}

As you can see, its quite a bit simpler, and more focused on global privileges in particular.Similarly for the snowflake_role_grant_schema_object_priviligesas shown below. This one is more complicated, but at least the complexity is contained and doesn't spill over into other types of role grants.

resource "snowflake_role_grant_schema_object_privilege" {
  object_type = ""
  object_identifier {
    name = ""
    schema = ""
    database = ""
  }

  // in place of object_type and object_identifier
  object_type_plural = ""
  on_future = false

  database_name = ""
  schema_name = ""
  
  all_privileges = true
  schema_object_privileges = []
  role_name = ""
  with_grant_option = true
}

The question I have for the community is what do you thing the best course of action is? Do you see the existing grant resources as being a problem, or not really? If you were to refactor grant resources, how would you do it?

Also I am not sure on the names of what these resources should be yet. I figured it should be done in a way that groups them together when you are searching, but open to suggestions.

[danu165]

I think the 10 resource approach makes sense as it alleviates the complexity of trying to do it all within only 5. The only thing I would ask is that the current approach still work in a deprecated fashion. For example, the AWS Terraform provider deprecated a bunch of parameters for their s3_bucket resource. Might be trickier to accomplish the same with resources rather than parameters for a resource, but would be ideal if possible.

[frsann]

This is not a comment on the number of resources, but I see a lot of promise in this suggested approach. My spontaneous thought is that turning the current "many-roles-to-one-privilege"-mapping to "one-role-to-many-privileges" is already a welcome change, and will reduce a lot of complexity (and risks of collateral damage! I'm referring to issue #1200) that are associated with managing grants. 👍

[bennylu2]

Would five resources but adding the ability to specify the type (account, schema, etc.) of grant make sense?

[dnuzz]

One thing I would ask is that there is a resource to grant N roles TO Another role. Right now I believe that role granting works by specifying a role and then specifying what roles have access to it, whereas a common pattern is to grant a number of roles to a single "roll-up role". I appreciate that we are talking about moving to a system where multiple privileges (or ALL ) can be granted together, as right now the paradigm for a single privilege per grant on a database to N number of roles makes for some immensely confusing Terraform.

[Relativity74205]

@dnuzz
Therefore we have written some Terraform code to "transpose" a role mapping between functional roles and access roles to a datastructure, which fits the snowflake_role_grants resource. I would also welcome, if new grant resources would tackle this issue.

[ssousa33-OS]

Idea:

resource "snowflake_database" "db" {
  for_each                                    = var.databases
  name                                         = upper(each.key)
  data_retention_time_in_days = each.value.data_retention_time_in_days
  comment                                  = each.value.comment
  roles_with_usage_privilege    = each.value.rbac.usage
  roles_with_monitor_privilege = each.value.rbac.monitor
  ## ...
}

In a large org, the current <object_type>_grants is essentially unusable. Attempting to follow generally accepted practices for RBAC creates thousands of resources, resulting in extremely long and convoluted plans. If securable objects could handle grants to self, my org's tfstate would be reduced from 5757 resource addresses to 202!

[sfc-gh-jcieslak]

Closing as the grant redesign was concluded. More info here.