Terraform Plan Continually Proposes ALL PRIVILEGES Privilege Grant Despite Successful Apply

[jacobcbeaudin]

Snowflake Provider Version

0.71.0

Terraform Version

1.4.6

Describe the bug

I am encountering an issue with the Snowflake Terraform provider where the terraform plan continually proposes to grant the ALL PRIVILEGES privilege to a role, even after the plan has been successfully applied.

Steps to Reproduce:

Run terraform plan. The generated execution plan proposes to grant the ALL PRIVILEGES privilege to the role.
Run terraform apply. The plan is applied successfully, indicating that the ALL PRIVILEGES privilege has been granted to the role.
Run terraform plan again. The plan still proposes to grant the ALL PRIVILEGES privilege between same future plural schema objects / account objects to the role, as if the previous apply did not take effect.

Expected behavior

After running terraform apply, the subsequent terraform plan should not propose the same changes again.

Code samples and commands
Terraform doce to dynamically grant privileges to roles

resource "snowflake_grant_privileges_to_role" "account_object" {
  provider   = snowflake.security_admin
  for_each   = { for account_object in local.role_account_object_privileges : "${account_object.role_name}-ON-${account_object.account_object_type}-${account_object.account_object_name}" => account_object }
  privileges = each.value.privileges
  role_name  = each.value.role_name

  on_account_object {
    object_type = each.value.account_object_type
    object_name = each.value.account_object_name
  }
}

resource "snowflake_grant_privileges_to_role" "schema" {
  provider = snowflake.security_admin
  for_each = { for schema in local.role_schema_privileges : "${schema.role_name}-ON-${schema.grant_type}-${schema.database_name}.${schema.schema_name}" => schema }

  privileges = each.value.privileges
  role_name  = each.value.role_name

  dynamic "on_schema" {
    for_each = each.value.grant_type == "all" ? [1] : []
    content {
      all_schemas_in_database = each.value.database_name
    }
  }

  dynamic "on_schema" {
    for_each = each.value.grant_type == "future" ? [1] : []
    content {
      future_schemas_in_database = each.value.database_name
    }
  }

  dynamic "on_schema" {
    for_each = each.value.grant_type == "specific" ? [1] : []
    content {
      schema_name = "\"${each.value.database_name}\".\"${each.value.schema_name}\""
    }
  }
}

resource "snowflake_grant_privileges_to_role" "schema_object" {
  provider   = snowflake.security_admin
  for_each   = { for schema_object in local.role_schema_object_privileges : "${schema_object.role_name}-ON-${replace(schema_object.object_type, " ", "_")}-${schema_object.database_name}.${schema_object.schema_name}.${schema_object.object_name}" => schema_object }
  privileges = each.value.privileges
  role_name  = each.value.role_name

  on_schema_object {
    object_type = each.value.object_type
    object_name = "\"${each.value.database_name}\".\"${each.value.schema_name}\".\"${each.value.object_name}\""
  }
}

resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
  provider   = snowflake.security_admin
  for_each   = { for schema_object in local.role_schema_object_plural_privileges : "${schema_object.role_name}-ON-${schema_object.grant_type}-${replace(schema_object.object_type_plural, " ", "_")}-IN-${schema_object.database_name}.${schema_object.schema_name}" => schema_object }
  privileges = each.value.privileges
  role_name  = each.value.role_name

  dynamic "on_schema_object" {
    for_each = each.value.grant_type == "all" ? [1] : []
    content {
      all {
        object_type_plural = each.value.object_type_plural
        in_schema          = "\"${each.value.database_name}\".\"${each.value.schema_name}\""
      }
    }
  }

  dynamic "on_schema_object" {
    for_each = each.value.grant_type == "future" ? [1] : []
    content {
      future {
        object_type_plural = each.value.object_type_plural
        in_schema          = "\"${each.value.database_name}\".\"${each.value.schema_name}\""
      }
    }
  }
}

I run terraform plan and it generates the following execution plan:

  # module.snowflake.snowflake_grant_privileges_to_role.account_object["USER_ROLE-ON-DATABASE-DB"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "account_object" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|true|false|false|false|false|DATABASE|DB||false||false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema["USER_ROLE-ON-future-DB.*"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|true|false|false|true||||false||true|DB"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema["USER_ROLE-ON-specific-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|true|false|false|false||||false|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }


  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-EVENT_TABLES-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||EVENT TABLES|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-EXTERNAL_TABLES-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||EXTERNAL TABLES|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-FILE_FORMATS-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||FILE FORMATS|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-FUNCTIONS-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||FUNCTIONS|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-MATERIALIZED_VIEWS-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||MATERIALIZED VIEWS|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-PROCEDURES-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||PROCEDURES|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-SEQUENCES-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||SEQUENCES|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-STAGES-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||STAGES|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-STREAMS-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||STREAMS|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-TASKS-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||TASKS|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-TABLES-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||TABLES|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }
  # module.snowflake.snowflake_grant_privileges_to_role.schema_object_plural["USER_ROLE-ON-future-VIEWS-IN-DB.SCHEMA"] will be updated in-place
  ~ resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
        id                = "USER_ROLE|ALL PRIVILEGES|false|false|false|false|false|true|false|true|||VIEWS|true|\"DB\".\"SCHEMA\"|false|"
      ~ privileges        = [
          + "ALL PRIVILEGES",
        ]
        # (4 unchanged attributes hidden)

        # (1 unchanged block hidden)
    }

Further documented examples of this behavior:

• FUTURE GRANTS are updated everytime #1573
• Terraform Plan Continually Proposes USAGE Privilege Grant Despite Successful Apply on Future File Formats #1981
• Imported privileges on SNOWFLAKE database not registered in state #1998

[sfc-gh-swinkler]

wondering why you are not using the "all_privileges" boolean flag? https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/grant_privileges_to_role#all_privileges

Although we should add validation to prevent this from being passed directly anyways.

[jacobcbeaudin]

@sfc-gh-swinkler I modified script to use the all_privileges boolean flag and am still getting the same issue. Modified code can be found below

# grant privileges to specific databases
resource "snowflake_grant_privileges_to_role" "account_object" {
  provider = snowflake.security_admin
  for_each = {
    for account_object in var.account_object_privileges :
    "${account_object.account_object_type}-${account_object.account_object_name}" => account_object
  }

  role_name = var.role_name

  # privileges and all_privileges are mutually exclusive
  privileges     = each.value.privileges != ["ALL PRIVILEGES"] ? each.value.privileges : null
  all_privileges = each.value.privileges == ["ALL PRIVILEGES"] ? true : null

  on_account_object {
    object_type = each.value.account_object_type
    object_name = each.value.account_object_name
  }
}

resource "snowflake_grant_privileges_to_role" "schema" {
  provider = snowflake.security_admin
  for_each = {
    for schema in var.schema_privileges :
    "${schema.grant_type}-${schema.database_name}.${schema.schema_name != null ? schema.schema_name : "*"}" => schema
  }

  role_name = var.role_name

  # privileges and all_privileges are mutually exclusive
  privileges     = each.value.privileges != ["ALL PRIVILEGES"] ? each.value.privileges : null
  all_privileges = each.value.privileges == ["ALL PRIVILEGES"] ? true : null

  dynamic "on_schema" {
    for_each = each.value.grant_type == "all" ? [1] : []
    content {
      all_schemas_in_database = each.value.database_name
    }
  }

  dynamic "on_schema" {
    for_each = each.value.grant_type == "future" ? [1] : []
    content {
      future_schemas_in_database = each.value.database_name
    }
  }

  dynamic "on_schema" {
    for_each = each.value.grant_type == "specific" ? [1] : []
    content {
      schema_name = "\"${each.value.database_name}\".\"${each.value.schema_name}\""
    }
  }
}

resource "snowflake_grant_privileges_to_role" "schema_object" {
  provider = snowflake.security_admin
  for_each = {
    for schema_object in var.schema_object_privileges :
    "${replace(schema_object.object_type, " ", "_")}-${schema_object.database_name}.${schema_object.schema_name}.${schema_object.object_name}" => schema_object
  }

  role_name = var.role_name

  # privileges and all_privileges are mutually exclusive
  privileges     = each.value.privileges != ["ALL PRIVILEGES"] ? each.value.privileges : null
  all_privileges = each.value.privileges == ["ALL PRIVILEGES"] ? true : null

  on_schema_object {
    object_type = each.value.object_type
    object_name = "\"${each.value.database_name}\".\"${each.value.schema_name}\".\"${each.value.object_name}\""
  }
}

resource "snowflake_grant_privileges_to_role" "schema_object_plural" {
  provider = snowflake.security_admin
  for_each = {
    for schema_object in var.schema_object_plural_privileges :
    "${schema_object.grant_type}-${replace(schema_object.object_type_plural, " ", "_")}-IN-${schema_object.database_name}.${schema_object.schema_name}" => schema_object
  }

  role_name = var.role_name

  # privileges and all_privileges are mutually exclusive
  privileges     = each.value.privileges != ["ALL PRIVILEGES"] ? each.value.privileges : null
  all_privileges = each.value.privileges == ["ALL PRIVILEGES"] ? true : null

  dynamic "on_schema_object" {
    for_each = each.value.grant_type == "all" ? [1] : []
    content {
      all {
        object_type_plural = each.value.object_type_plural
        in_schema          = "\"${each.value.database_name}\".\"${each.value.schema_name}\""
      }
    }
  }

  dynamic "on_schema_object" {
    for_each = each.value.grant_type == "future" ? [1] : []
    content {
      future {
        object_type_plural = each.value.object_type_plural
        in_schema          = "\"${each.value.database_name}\".\"${each.value.schema_name}\""
      }
    }
  }
}

[GomathiMa]

I am facing similar issue. OWNERSHIP Grants are being modified for every terraform plan and apply ~ resource "snowflake_grant_privileges_to_role" "db_test_grt_to_infra" {
id = "INFRA_ROLE|OWNERSHIP|false|false|false|true|false|false|false|false|DATABASE|DB_TEST||false||false|"
~ privileges = [
+ "OWNERSHIP",
]
# (4 unchanged attributes hidden)

    # (1 unchanged block hidden)
}

[fernandoataoldotcom]

thanks for creating this - seeing the same for "IMPORTED PRIVILEGES"

[jacobcbeaudin]

thanks for creating this - seeing the same for "IMPORTED PRIVILEGES"

I am also seeing this for "IMPORTED PRIVILEGES"

[sfc-gh-jcieslak]

Hey @jacobcbeaudin @fernandoataoldotcom could you check if you still have those issues with the latest version of the provider using snowflake_grant_privileges_to_account_role?

@GomathiMa I'm working on a resource only dedicated to granting ownership. Granting ownership is/will be only allowed through this resource.

[fernandoataoldotcom]

@sfc-gh-jcieslak thanks for getting back to me

I'm attempting to migrate to the new resource, but the "IMPORTED PRIVELEGES" is behaving differently than the deprecated object.

I'm getting this error when I attempt to use the same privilege string - for context, I was attempting to delete the old resources and replace them with the new ones.
Revoking individual privileges on imported database is not allowed. Use 'REVOKE IMPORTED PRIVILEGES' instead.

edit: some additional context on the new resource usage

I can successfully deploy the privileges using the sample in the docs (https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/grant_privileges_to_account_role).
Howver, when I attempt to delete the privileges, I get this error:
Error: [grants_validations.go:44] exactly one of AccountRoleGrantPrivileges fields [AllPrivileges GlobalPrivileges AccountObjectPrivileges SchemaPrivileges SchemaObjectPrivileges] must be set

resource "snowflake_grant_privileges_to_account_role" "example" {
account_role_name = snowflake_role.db_role.name
privileges = ["IMPORTED PRIVILEGES"]
on_account_object {
object_type = "DATABASE" # All applications should be using DATABASE object_type
object_name = "SNOWFLAKE"
}
}

[sfc-gh-jcieslak]

Hey @fernandoataoldotcom
I'm not quite sure what you're trying to say. Are you using the latest (0.87.2) version? I ran the following configuration and it worked just fine (or I'm missing something?):

resource "snowflake_role" "test" {
  name = "some_acc_role"
}

resource "snowflake_grant_privileges_to_account_role" "example" {
  account_role_name = snowflake_role.test.name
  privileges        = ["IMPORTED PRIVILEGES"]
  on_account_object {
    object_type = "DATABASE"
    object_name = "SNOWFLAKE"
  }
}

On delete REVOKE IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" FROM ROLE "some_acc_role" was run. If you're having migration issues, then maybe this guide could help you with that.

[fernandoataoldotcom]

@sfc-gh-jcieslak - great, thanks. I'll take a look at the docs

[adrian5991]

Adding another data point: we're seeing the same issue with IMPORTED PRIVILEGES and MONITOR EXECUTION on v0.86.0.

[sfc-gh-jcieslak]

Hey @adrian5991
Please, try to use the latest version with the snowflake_grant_privileges_to_account_role resource (If it won't work proceed with the instruction below).

Closing this issue (as it is about the deprecated grant resource). Please create another issue if you have any further issues with IMPORTED PRIVILEGES, MONITOR EXECUTION, or any other issue. Also, before creating a new issue, please check if the issue persists in the latest version of the provider using non-deprecated resources.