OWNERSHIP can only be transferred error on destruction of snowflake_grant_privileges_to_role resource

[liamjamesfoley]

Provider Version
0.71

Terraform Version
1.5.2

Describe the bug
https://docs.snowflake.com/en/sql-reference/sql/grant-ownership
Destruction of snowflake_grant_privileges_to_role produces that a query that will always fail.

Expected behavior
This is happening in a managed access schema module I'm working on, so TBH, I don't care as long as the resource destruction happens without using terraform state rm b/c I'm also destroying the schema, but ownership should probably go to ACCOUNTADMIN?

Since snowflake_schema_grant is being deprecated the new resource should have something similar to revert_ownership_to_role_name.
https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/pkg/resources/schema_grant.go#L106-L114

Code samples and commands

Resource:

resource "snowflake_grant_privileges_to_role" "owner_schema_grant" {
  # `GRANT OWNERSHIP ON SCHEMA ${schema_identifier} TO ROLE ${owner_role};`,
  role_name  = snowflake_role.owner_role.name
  privileges = ["OWNERSHIP"]
  on_schema {
    schema_name = "${var.db_name}.${snowflake_schema.schema.name}"
  }
  with_grant_option = true
}

[imre-kerr-sb1]

We also have issues with ownership grants. For now we are using the following workaround:

data "snowflake_current_role" "current" {}

resource "snowsql_exec" "owner_schema_grant" {
  name = "owner_schema_grant"

  create {
    statements           = "GRANT OWNERSHIP ON SCHEMA ${schema_identifier} TO ROLE ${snowflake_role.owner_role.name};"
    number_of_statements = 1
  }

  read {
    statements           = "SHOW GRANTS ON SCHEMA ${schema_identifier};"
    number_of_statements = 1
  }

  delete {
    statements           = "GRANT OWNERSHIP ON SCHEMA ${schema_identifier} TO ROLE ${data.snowflake_current_role.current.name};"
    number_of_statements = 1
  }
}

[entechlog]

Having the same error on schema and warehouse ownership changes. When we try to change the owner using snowflake_grant_privileges_to_role resource the plan runs fine but apply fails with below error.

Error: error revoking privileges from account role: exactly one of AllPrivileges, GlobalPrivileges, AccountObjectPrivileges, SchemaPrivileges, or SchemaObjectPrivileges must be set

Workaround
Step 1: Change the owner manually using sql, This is just setting a default role
GRANT OWNERSHIP ON WAREHOUSE "ALL_MAGIC_QUERY_WH_XS" TO ROLE "ENTECHLOG_MAGIC_ROLE" REVOKE CURRENT GRANTS;

Step 2: Remove the problem state from terrafrom state
terraform state rm module.dbt_wh_xs.snowflake_grant_privileges_to_role.ownership_warehouse_grant["sysadmin_role"]

Step 3: Rerun terrraform plan and apply to make the ownership change

[sfc-gh-jcieslak]

Hey @liamjamesfoley
I think we can close this one. In the newer, non-deprecated snowflake_grant_privileges_to_account_role resource the OWNERSHIP privilege is not allowed (because ownership privilege is more complex and should be managed by different resources). Luckily I started to work on this resource and in one or two next minor versions should be usable. Here's a pr to follow (#2604) it also lists other issues connected to OWNERSHIP privilege (like this one), so you can follow the progress in them. This one was specific to snowflake_grant_privileges_to_role which is now deprecated and in the newer resource OWNERSHIP is not allowed.

[sfc-gh-jcieslak]

Hey 👋
Closing, as the issue was about the deprecated/not allowed functionality. Recently, we released a new grant resource which is capable of granting ownership. Please, give it a try. If there will be any issues with it, create another GitHub issue. Also, please check our technical documentation section where you can find a migration guide that can help you with upgrading to the latest grant resources and our newly added design decision doc (regarding new grant resources).