[Bug]: Destroying schema ownership grant fails unless the owning role is assigned to the provider's role

[QuintenBruynseraede]

Terraform CLI Version

1.3.10

Terraform Provider Version

0.92.0

Terraform Configuration

provider "snowflake" {
  account       = <redacted>
  user          = <redacted>
  role          = "ACCOUNT_ADMIN"
  authenticator = "JWT"
  private_key   = <redacted>
}

resource "snowflake_database" "db" {
  name    = "TEST_DB"
}

resource "snowflake_role" "db_owner" {
  name    = "DB_OWNER"
}

resource "snowflake_grant_ownership" "grant_schema_ownership" {
  account_role_name   = snowflake_role.db_owner.name
  outbound_privileges = "COPY"
  on {
    all {
      object_type_plural = "SCHEMAS"
      in_database        = snowflake_database.db.name
    }
  }
}

resource "snowflake_schema" "schema" {
  database            = snowflake_database.db.name
  name                = "TEST_SCHEMA"
  is_managed          = true
}

resource "snowflake_grant_ownership" "table_ownership" {
  account_role_name   = <some other role>
  outbound_privileges = "COPY"
  on {
    all {
      object_type_plural = "TABLES"
      in_schema          = "TEST_DB.TEST_SCHEMA"
    }
  }
}

Category

category:resource

Object type(s)

No response

Expected Behavior

We should be able to create and destroy these resources without errors.

Actual Behavior

When destroying the table ownership resource, the provider will reassign ownership of the schema's objects (tables, streams, ...) to the ACCOUNT_ADMIN role (which is the role used to init the provider). Because in this setup, the original owner (DB_OWNER) is not granted to the ACCOUNT_ADMIN role, Snowflake rejects this.

╷
│ Error: An error occurred when transferring ownership back to the original role
│
│ Id: ToAccountRole|"DB_OWNER"|COPY|OnAll|TABLES|InSchema|"TEST_DB"."TEST_SCHEMA"
│ Error: 003514 (23001): SQL execution error: Ownership restriction violation in a managed access schema. Grantee need to be a subordinate role of the schema owner.
╵

Steps to Reproduce

1. Apply the configuration
2. Destroy the configuration

How much impact is this issue causing?

Low

Logs

No response

Additional Information

In previous versions of the provider, we granted ownership on schema objects (e.g. tables) using snowflake_schema_grant, which had an attribute revert_ownership_to_role_name. That attribute allowed you to specify a role to which ownership would be granted on delete. The new snowflake_grant_ownership resource no longer supports this, but instead always sets ownership to the role passed at provider initialization.

For this use case, the addition of a similar attribute would be a good solution.

Would you like to implement a fix?

• Yeah, I'll take it 😎