[Bug]: REVOKE and GRANT executed out of order during migration from snowflake_grant_privileges_to_role to snowflake_grant_privileges_to_account_role

[cdnedlik]

Terraform CLI Version

1.9.8

Terraform Provider Version

0.87.2

Company Name

No response

Terraform Configuration

# Before
resource "snowflake_grant_privileges_to_role" "grant_privileges_on_future_tables_to_read_role" {
  provider = snowflake.security_admin

  privileges = ["SELECT", "REFERENCES"]
  role_name  = module.schema_read_role.role_name
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_schema          = local.fully_qualified_schema_name
    }
  }
  with_grant_option = false
}

# After
resource "snowflake_grant_privileges_to_account_role" "grant_privileges_on_future_tables_to_read_role" {
  provider = snowflake.security_admin

  privileges        = ["SELECT", "REFERENCES"]
  account_role_name = module.schema_read_role.role_name
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_schema          = local.fully_qualified_schema_name
    }
  }
  with_grant_option = false
}

Category

category:grants

Object type(s)

resource:grant_privileges_to_account_role

Expected Behavior

When migrating from the use of the deprecated snowflake_grant_privileges_to_role resource to the newer snowflake_grant_privileges_to_account_role across many grants simultaneously, we expected to see Terraform execute all of the REVOKE queries in Snowflake corresponding to the old resources, followed by all of the GRANT queries corresponding to the new resource, which together would result in no net change to the privileges in our Snowflake account.

Actual Behavior

When migrating from the use of the deprecated snowflake_grant_privileges_to_role resource to the newer snowflake_grant_privileges_to_account_role, we saw Terraform execute one set of REVOKE and GRANT queries in the wrong order. Specifically the GRANT for the new resource was executed before the REVOKE for the old resource in one case, so the net outcome was that a previously existing granted privileges no longer existed in our Snowflake account following the resource update.

Steps to Reproduce

1. Define many grants using the resource: "snowflake_grant_privileges_to_role"
2. Terraform apply
3. Replace the existing grants with equivalent grants using the "snowflake_grant_privileges_to_account_role" resource
4. Terraform apply

How much impact is this issue causing?

Low

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎

[cdnedlik]

Looking at query history in our Snowflake account, we can see that for just one of the affected grant resources, the GRANT was executed before the REVOKE, resulting in a net loss of the privileges.

[sfc-gh-asawicki]

Hey @cdnedlik. Thanks for reaching out to us.

The one-step change that you described will result in such non-deterministic ordering. Please follow our migration guidelines: https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/docs/technical-documentation/resource_migration.md#resource-migration.

More references:

• Breaking migration from deprecated to new grant types #2340
• Migration from snowflake_role_grants to snowflake_grant_account_role #2705
• [General Usage]: Migrate snowflake_role to snowflake_account_role #3037