search

Snowflake-Labs / terraform-provider-snowflake

Terraform provider for managing Snowflake accounts
https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest
MIT License
552 stars 420 forks source link

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

Open cdnedlik opened 1 week ago

cdnedlik commented 1 week ago

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?

cdnedlik commented 1 week ago

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.

Screenshot 2024-11-07 at 6 46 38 PM

sfc-gh-asawicki commented 1 week ago

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: