snowflake_database_grant force replacement on role list change

[TerjeRusska]

Provider Version

v0.37.1

Terraform Version

v1.2.8

Describe the bug

snowflake_database_grant resource triggers force replacement when roles list is changed. Also the resource ID consists of all previous roles (???) Is there a reason snowflake_database_grant reacts differently compared to snowflake_schema_grant?

resource snowflake_database_grant db_usage {
  provider = snowflake.securityadmin

  database_name = snowflake_database.db.name

  privilege = "USAGE"
  roles     = [
    snowflake_role.role1.name,
    snowflake_role.role2.name,
    snowflake_role.role3.name,
  ]

  shares = []

  with_grant_option = false
}

  # snowflake_database_grant.db_usage must be replaced
-/+ resource "snowflake_database_grant" "db_usage" {
      ~ id                     = "DB|||USAGE|ROLE1,ROLE2|false" -> (known after apply)
      ~ roles                  = [ # forces replacement
          + "ROLE3",
            # (2 unchanged elements hidden)
        ]
      - shares                 = [] -> null
        # (4 unchanged attributes hidden)
    }

Expected behavior

# snowflake_database_grant.db_usage will be updated in-place
  ~ resource "snowflake_database_grant" "db_usage" {
        id                     = "DB|||USAGE|false"
      ~ roles                  = [
          + "ROLE3",
            # (2 unchanged elements hidden)
        ]
        # (5 unchanged attributes hidden)
    }

Code samples and commands

Please add code examples and commands that were run to cause the problem.

Additional context

Add any other context about the problem here.

[frsann]

This is a very awkward bug indeed! As the grants are recreated, for a short time roles are missing their permission to the database, causing all kinds of issues. Even worse, if other changes in the same plan fail for some reason, the grants are not re-applied, leaving the roles without their grants for an extended period of time.

The only workaround I can think of if defining all database grants individually to roles, and setting the enable_multiple_grants = true (haven't tested yet).

[sfc-gh-swinkler]

So we can definitely fix the force new behavior, as I agree that is quite awkward. The reason for setting the role as the ID is because this isn't actually one object, but multiple object being managed at once (each grant is an object). Therefore, the ID is selected as a set of all of the names of the roles, which is what you would need to Read them or perform import. That being said, its not a very good reason as we could also use a randomly generated ID, and then just do the Read normally. I'm afraid if we do that, it could be considered a breaking change. I will consider the best way to implement this.

[frsann]

Thanks for getting back to this @sfc-gh-swinkler! Out of curiosity and my own learning, why is the behavior different for other grants? For example, schema_grants are translated into multiple grant-queries as well, right, and they don't have this same behavior? Or is it a matter of unintended consequence of an older logic to constructing the ID of the database_grant?

[mmehta07]

Privileges have been revoked automatically when using Terraform Snowflake Provider.

A user trying to grant "Usage" privileges to Role on Database using Terraform Scripts but for some reason Terraform revokes the privileges.

Snowflake_database_grant resource:

resource "snowflake_database_grant" "EDP_NPRD_DNA_INT_DB_SECURITY_CTRL_grant_db_1" { database_name = "<Database name>" privilege = "USAGE" roles = ["<Role name>"] #depends_on = [snowflake_role.<Role name>] provider = snowflake.sysadmin with_grant_option = true enable_multiple_grants = true }

Why the permissions are getting revoked?

[tursid]

Privileges are being revoked using database_grant resource. We also faced an issue when having this resource terraformed. Even when plan wasn't touching this resource we faced that the role that wasn't terraformed in it got revoked permissions.

Why is this resource revoking unspecified roles permissions?

[sfc-gh-jcieslak]

Hey 👋 Closing the issue as it's referring to the deprecated resource and we're not supporting them. Please, try to use the latest provider version with the non-deprecated resources (e.g. snowflake_grant_privileges_to_account_role). If the issue persists on the non-deprecated resource (or the feature is present in Snowflake, but not in the provider), please create a new GitHub issue. The migration guide may help during migration to the latest resources. Here's a list of the latest, non-deprecated, grant resources:

Resources snowflake_grant_privileges_to_database_role snowflake_grant_privileges_to_account_role snowflake_grant_account_role snowflake_grant_database_role snowflake_grant_application_role (coming soon) snowflake_grant_privileges_to_share snowflake_grant_ownership (coming soon)

Data sources snowflake_grants