Grant ownership fails on object with dependent grants

[seediang]

Grant ownership fails when there is already a dependent grant on the object.

snowflake_role.role_one: Creating... snowflake_database.database_one: Creating... snowflake_role.role_two: Creating... snowflake_database.database_one: Creation complete after 2s [id=database_one] snowflake_schema.schema_one: Creating... snowflake_role.role_one: Creation complete after 2s [id=role_one] snowflake_role_grants.role_one: Creating... snowflake_role.role_two: Creation complete after 2s [id=role_two] snowflake_role_grants.role_two: Creating... snowflake_role_grants.role_one: Creation complete after 0s [id=role_one] snowflake_schema.schema_one: Creation complete after 0s [id=database_one|schema_one] snowflake_schema_grant.create_table: Creating... snowflake_role_grants.role_two: Creation complete after 1s [id=role_two] snowflake_schema_grant.create_table: Creation complete after 1s [id=database_one|schema_one||CREATE TABLE] snowflake_schema_grant.ownership: Creating...

Error: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable '"database_one"."schema_one"' to role '"role_one"' 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'.

on main.tf line 42, in resource "snowflake_schema_grant" "ownership": 42: resource "snowflake_schema_grant" "ownership" {

The following is an example used to create the error above

provider "snowflake" {
  account = "SNOWFLAKEACCOUNT"
  // running as ACCOUNTADMIN TO AVOID dealing with security
  // not a good practise
  role = "ACCOUNTADMIN"
}

resource "snowflake_database" "database_one" {
    name = "database_one"
}

resource "snowflake_schema" "schema_one" {
    name        = "schema_one"
    database    = snowflake_database.database_one.name
}

// CREATE ROLE_ONE and grant to SYSADMIN
resource "snowflake_role" "role_one" {
    name    = "role_one"
}
resource "snowflake_role_grants" "role_one" {
    role_name   = snowflake_role.role_one.name
    roles       = ["SYSADMIN"]
}

// CREATE ROLE_TWO and grant to SYSADMIN
resource "snowflake_role" "role_two" {
    name    = "role_two"
}
resource "snowflake_role_grants" "role_two" {
    role_name   = snowflake_role.role_two.name
    roles       = ["SYSADMIN"]
}

resource "snowflake_schema_grant" "create_table" {
    schema_name   = snowflake_schema.schema_one.name
    database_name = snowflake_database.database_one.name
    privilege     = "CREATE TABLE"
    roles         = [snowflake_role.role_one.name]    
}

resource "snowflake_schema_grant" "ownership" {
    schema_name   = snowflake_schema.schema_one.name
    database_name = snowflake_database.database_one.name
    privilege     = "OWNERSHIP"
    roles         = [snowflake_role.role_two.name]   
    // use the depends_on to force the order 
    depends_on    = [snowflake_schema_grant.create_table]
}

I believe the fix is to use the COPY CURRENT GRANTS clause see https://docs.snowflake.com/en/sql-reference/sql/grant-ownership.html

[zpencerq]

I've worked around it by making all grants into a map (via for_each) where the key includes the owner of the object so a change of owner will destroy/recreate each grant.

[ejaoua]

is there any follow-up on this ? I am using terraform 0.25.21 but still whenever I change the view, the grants are lost from snowflake side. I need then to do a second terraform apply to get the correct grants.

[ericmjalbert]

I think this ticket can be closed since it's been merged into main https://github.com/Snowflake-Labs/terraform-provider-snowflake/pull/153 and it's been released since https://github.com/Snowflake-Labs/terraform-provider-snowflake/releases/tag/v0.11.0.

I've just tested this out and I'm correctly seeing the COPY CURRENT GRANTS on the SQL that terraform is running.

@ejaoua can you provide a bit more information on the issue you're seeing? (Log of the SQL that terraform runs, MVP of the terraform you're running, output of terraform providers)?

[lachniej]

@Relativity74205 does the new 'on_all' & 'on_future' parameters for object grants not support #153? I am getting the same error as @saperry above with the on_all and on_future parameters set to true when trying to grant privileges objects in a cloned database, and the corresponding snowflake activity sql text does not include the COPY CURRENT GRANTS where the privilege == 'OWNERSHIP'.

[DustinMoriarty]

I am seeing this problem as well. I guess it could be considered a "feature" since it is mapping new API parameters. However, it seems like a pretty important feature once one starts to get very far into using terraform to manage roles. However, it seems that all the *_grant resources need a copy_current_grants option for the case of ownership or there should be a separate resource for granting ownership similar to snowflake_role_ownership_grant except for all other grant resources.

[sfc-gh-asawicki]

We are closing this issue as part of a cleanup described in announcement. If you believe that the issue is still valid in v0.89.0, please open a new ticket.