[Bug]: Can't Destroy snowflake_grant_ownership resource when more grants exist

[AaronCoquet-Easypark]

Terraform CLI Version

1.9.8

Terraform Provider Version

0.97.0

Company Name

Easypark

Terraform Configuration

/* This is from a module */
resource "snowflake_database" "this" {
  provider = snowflake.sysadmin

  name    = local.db_name_u
  comment = local.m_comment

  is_transient = local.m_timetravel_range == 0 ? true : false

  data_retention_time_in_days     = local.m_timetravel_range
  max_data_extension_time_in_days = local.m_extend_fail_safe_to

}

# Set ownership of database
resource "snowflake_grant_ownership" "this" {
  provider = snowflake.securityadmin

  account_role_name = var.set_ownership_to

  on {
    object_type = "DATABASE"
    object_name = snowflake_database.this.name
  }

  outbound_privileges = local.m_existing_grants_behavior
}

resource "snowflake_grant_privileges_to_account_role" "sysadmin_admin" {
  provider = snowflake.securityadmin
  count    = var.set_ownership_to != "SYSADMIN" ? 1 : 0

  privileges = [
    "MODIFY",
    "MONITOR",
    "USAGE"
  ]

  with_grant_option = false

  on_account_object {
    object_type = "DATABASE"
    object_name = snowflake_database.this.name
  }

  account_role_name = "SYSADMIN"
}

Category

category:grants

Object type(s)

resource:grant_ownership, resource:grant_privileges_to_account_role

Expected Behavior

When doing anything which will result in snowflake_grant_ownership to be destroyed (even if recreated), the resource would be destroyed and ownership would revert to the original (or previous, or designated, etc.) owner.

Actual Behavior

The destroy fails with error:

Error: An error occurred when transferring ownership back to the original role
  Id: ToAccountRole|"{CurrentOwner}"||OnObject|DATABASE|"{Database}"
  Error: 003036 (23001): SQL execution error: Dependent grant of privilege 'MODIFY' on securable '{Sandbox}' to role 'SYSADMIN' exists.  It must be revoked first.  More than one dependent grant may exist: use 'SHOW GRANTS' command to view them.  To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.

Steps to Reproduce

1. Create the resources using the configuration above, assigning ownership (var.set_ownership_to) to any role except SYSADMIN or SECURITYADMIN
2. Attempt to destroy the resources

How much impact is this issue causing?

Medium

Logs

https://gist.github.com/AaronCoquet-Easypark/f75f47973e4b39cb68b7b28dad47e217

Additional Information

This seems to be similar to #3143, but is distinct from it. Once resolved, I fear that this issue will reveal that #3143 is also present.

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jcieslak]

Hey @AaronCoquet-Easypark
Thanks for reporting this. It seems like there is an implicit dependency on roles that are preventing Terraform from deleting the ownership resource. I believe that specifying the dependency between the resources in the correct manner should resolve the issue (The order should be specified so create will happen in that order: database -> grant ownership -> grant privileges; then delete will happen in reverse, meaning the privileges to the sysadmin should not break the grant ownership resource). Here's an example:

resource "snowflake_database" "test" {
  name = "grant_ownership_test_database"
}

resource "snowflake_account_role" "test" {
  name = "grant_ownership_test_account_role"
}

resource "snowflake_grant_ownership" "test" {
  account_role_name = snowflake_account_role.test.name
  on {
    object_type = "DATABASE"
    object_name = snowflake_database.test.name
  }
}

resource "snowflake_grant_privileges_to_account_role" "test" {
  depends_on = [ snowflake_grant_ownership.test ]
  account_role_name = "SYSADMIN"
  privileges = [ "USAGE", "MODIFY", "MONITOR" ]
  on_account_object {
    object_type = "DATABASE"
    object_name = snowflake_database.test.name
  }
}

Let me know if that's the case.

[sfc-gh-jcieslak]

Hey @AaronCoquet-Easypark
Could you confirm that my previous message is true and no work is needed on our side? Thanks!