terraform-provider-snowflake: Changing snowflake_grant_privileges_to_role resource involving ownership fails due to dependent grants

Provider 0.68.1

Here’s my snowflake_grant_privileges_to_role resource:

resource "snowflake_grant_privileges_to_role" "admin_schema" {
  for_each          = toset(["PUBLIC", snowflake_schema.raw.name, snowflake_schema.security.name])
  role_name         = local.role_admin
  privileges        = ["OWNERSHIP", "CREATE TABLE", "USAGE"]
  with_grant_option = false

  on_schema {
    # has to be qualified
    schema_name = "${snowflakedb.db.name}.${each.value}"
  }
}

This applied successfully once, but then I needed to modify the schema list in the for_each (specifically, I hardcoded PUBLIC as you see here instead of defining it as a resource). The plan shows all additions even though each grant already exists and is already managed by terraform. For example, here’s one of the three schemata referenced in the resource declaration:

  # module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"] will be created
+ resource "snowflake_grant_privileges_to_role" "admin_schema" {
+       all_privileges    = false
+       id                = (known after apply)
+       on_account        = false
+       privileges        = [
+           "CREATE TABLE",
+           "OWNERSHIP",
+           "USAGE",
        ]
+       role_name         = "ADMIN_PRODUCTION"
+       with_grant_option = false

+       on_schema {
+           schema_name = "PROD.PUBLIC"
        }
    }

Application then fails due to dependent grant errors even though ownership has not actually been changed:

╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable 'PROD.RAW' to role 'ADMIN_PRODUCTION' 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'.
│ 
│   with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"],
│   on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│  165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵
╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'USAGE' on securable 'PROD.SECURITY' to role 'READER_PRODUCTION' 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'.
│ 
│   with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["SECURITY"],
│   on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│  165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵
╷
│ Error: error granting privileges to account role: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable 'PROD.RAW' to role 'ADMIN_PRODUCTION' 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'.
│ 
│   with module.snowflakedb.snowflake_grant_privileges_to_role.admin_schema["SECURITY"],
│   on ../../../modules/snowflakedb/main.tf line 165, in resource "snowflake_grant_privileges_to_role" "admin_schema":
│  165: resource "snowflake_grant_privileges_to_role" "admin_schema" {
╵

(note a different role READER_PRODUCTION is affected in the second error!)

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 2
  • Comments: 15 (3 by maintainers)

Commits related to this issue

Most upvoted comments

Is there a workaround here I’m not seeing? I’m not setting ownership but I am using all and future. During any destroy action, I’m always getting stuck with the error below. I then have to run terraform state rm against the grants, then terraform apply -destroy will work so I can then terraform apply to retest. I’m running this test in a new Snowflake account so there’s no pre-existing resources such as grants causing problems.

@sfc-gh-swinkler you comment seems to imply that using what @dmfay mentioned would fix the ownership issue but @dmfay mentioned in the last paragraph the the teardown problem still occurs.

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

This snowflake_grant_privileges_to_role resource wasn’t intended to work with the special ownership privilege because ownership cannot be revoked in the same way that grant privileges can; ownership can only assigned to a different role. We intend to add a new snowflake_grant_ownership resource to handle ownership permissions. In the meantime, I recommend splitting up the resource into two resources, one which manages ownership and one which manages all other permissions, as @dmfay suggests.

Hey @dmfay @emancu @ceik @jasonicarter @trabianmatt @chrisweis This seems like an issue with the OWNERSHIP privilege. In the newest snowflake_grant_privileges_to_account_role resource (which is now recommended instead of snowflake_grant_privileges_to_role) the OWNERSHIP privilege is not allowed for that reason. Luckily as you can see there’s an open pr (probably 1st out of two) to introduce ownership-granting resource. That resource should resolve this issue, so stay tuned 😃

Hey 👋 Closing, as the issue was about the deprecated resources. 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).

I’m getting the same teardown error and am only using select privilege on a table.

Splitting the schema grants works well for setup, but teardown fails to unroll them during execution:

resource "snowflake_grant_privileges_to_role" "admin_schema_ownership" {
  for_each          = toset(["PUBLIC", snowflake_schema.raw.name, snowflake_schema.security.name, snowflake_schema.mart.name])
  role_name         = local.role_admin
  privileges        = ["OWNERSHIP"]
  with_grant_option = false

  on_schema { schema_name = "${snowflake_database.db.name}.${each.value}" }
  lifecycle { ignore_changes = [ privileges ] }
}

resource "snowflake_grant_privileges_to_role" "admin_schema" {
  for_each          = toset(["PUBLIC", snowflake_schema.raw.name, snowflake_schema.security.name]) # no mart here!
  role_name         = local.role_admin
  privileges        = ["CREATE TABLE", "USAGE"]
  with_grant_option = false

  depends_on = [snowflake_grant_privileges_to_role.admin_schema_ownership]

  on_schema { schema_name = "${snowflake_database.db.name}.${each.value}" }
  lifecycle { ignore_changes = [ privileges ] }
}
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"]: Destroying... [id=ADMIN_STAGING|CREATE TABLE,USAGE|false|false|false|false|true|false|false|false||||false|TEST_DB.PUBLIC|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["PUBLIC"]: Destruction complete after 0s
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["RAW"]: Destroying... [id=ADMIN_STAGING|CREATE TABLE,USAGE|false|false|false|false|true|false|false|false||||false|TEST_DB.RAW|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["RAW"]: Destruction complete after 0s
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["SECURITY"]: Destroying... [id=ADMIN_STAGING|CREATE TABLE,USAGE|false|false|false|false|true|false|false|false||||false|TEST_DB.SECURITY|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema["SECURITY"]: Destruction complete after 0s
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["MART"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.MART|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["PUBLIC"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.PUBLIC|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["RAW"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.RAW|false|]
module.test_environment.snowflake_grant_privileges_to_role.admin_schema_ownership["SECURITY"]: Destroying... [id=ADMIN_STAGING|OWNERSHIP|false|false|false|false|true|false|false|false||||false|TEST_DB.SECURITY|false|]

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

terraform planned things in the correct order – wind down the usage and create-table grants in the public, raw, and security schemata, then destroy the ownership grants the former depend on in those three and the additional mart schema. The first step is successful and drops the three dependent grants. The second, dropping the four ownership grants, fails (including for mart which did not have a dependent grant!).

Other grants are removed correctly: all-tables-in-schema, future-tables-in-schema, database usage. Most of those do have explicit depends_on set, but not to grants of the same type.

The last thing I see in the query log is REVOKE ALL PRIVILEGES ON DATABASE "STAGING_TEST_DB" FROM ROLE "ADMIN_STAGING". Since the “exactly one of” message is from the provider code, I suspect that the REVOKE ALL PRIVILEGES is coming from the revocation of the dependent grants, leaving no valid targets for the revocation of the ownership grant.

I did upgrade to 0.68.2 on seeing #1953, but while that should fix the privileges churn I had to set ignore directives for, the teardown problem still occurs.

@dmfay Thanks for your hint! How did you infer that from the error? 🤔

the exact same thing happened to me earlier this week after I upgraded to 0.68 and missed updating in a couple of places 😄