terraform-provider-snowflake: [Bug]: Failed to revoke privileges to add for IMPORTED PRIVILEGES on SNOWFLAKE database

Terraform CLI Version

1.3.8

Terraform Provider Version

0.89

Terraform Configuration

locals {
  SNOWFLAKE_DATABASE_ACCESS_ROLES = [
    "SNOWFLAKE_INSPECTION"
  ]
}

# grant IMPORTED PRIVILEGES on SNOWFLAKE application
resource "snowflake_grant_privileges_to_account_role" "this" {
  for_each          = toset(local.SNOWFLAKE_DATABASE_ACCESS_ROLES)
  account_role_name = each.key
  privileges        = ["IMPORTED PRIVILEGES"]
  on_account_object {
    object_type = "DATABASE"
    object_name = "SNOWFLAKE"
  }
  with_grant_option = false
}

Category

category:resource

Object type(s)

No response

Expected Behavior

Use snowflake_grant_privileges_to_account_role to grant IMPORTED PRIVILEGES on the SNOWFLAKE database to some roles with with_grant_option = false.

Actual Behavior

It gives an error when revoking the privilege.

Error: Failed to revoke privileges to add
│ 
│   with module.custom.snowflake_grant_privileges_to_account_role.this["SNOWFLAKE_INSPECTION"],
│   on ../../../../modules/snowflake/custom/snowflake_database_usage.tf line 17, in resource "snowflake_grant_privileges_to_account_role" "this":
│   17: resource "snowflake_grant_privileges_to_account_role" "this" {
│ 
│ Id: "SNOWFLAKE_INSPECTION"|false|false|IMPORTED PRIVILEGES|OnAccountObject|DATABASE|"SNOWFLAKE"
│ Privileges to add: [IMPORTED PRIVILEGES]
│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 24 unexpected 'IMPORTED'.

The SQL produced indeed is not valid, it produces: REVOKE GRANT OPTION FOR IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" FROM ROLE "SNOWFLAKE_INSPECTION"

instead what is correct is REVOKE IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" FROM ROLE "SNOWFLAKE_INSPECTION"

Steps to Reproduce

run terraform apply on this, replacing the “SNOWFLAKE_INSPECTION” role with one that you have on your Snowflake account:

locals {
  SNOWFLAKE_DATABASE_ACCESS_ROLES = [
    "SNOWFLAKE_INSPECTION"
  ]
}

resource "snowflake_grant_privileges_to_account_role" "this" {
  for_each          = toset(local.SNOWFLAKE_DATABASE_ACCESS_ROLES)
  account_role_name = each.key
  privileges        = ["IMPORTED PRIVILEGES"]
  on_account_object {
    object_type = "DATABASE"
    object_name = "SNOWFLAKE"
  }
  with_grant_option = false
}

How much impact is this issue causing?

Low

Logs

No response

Additional Information

No response

Would you like to implement a fix?

  • Yeah, I’ll take it 😎

About this issue

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

Most upvoted comments

Interesting, I’ll reproduce the issue and get back to you.

Actually this is not yet solved. We are encountering the same issue and the provider version is 0.92.0

╷
│ Error: Failed to revoke privileges to add
│ 
│   with module.snowflake.snowflake_grant_privileges_to_account_role.grant_on_snowflake_database_to_datadog,
│   on modules/snowflake/grant_privileges_to_account_role.tf line 9, in resource "snowflake_grant_privileges_to_account_role" "grant_on_snowflake_database_to_datadog":
│    9: resource "snowflake_grant_privileges_to_account_role" "grant_on_snowflake_database_to_datadog" {
│ 
│ Id: "PROD_DATADOG"|false|false|IMPORTED
│ PRIVILEGES|OnAccountObject|DATABASE|"SNOWFLAKE"
│ Privileges to add: [IMPORTED PRIVILEGES]
│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 24 unexpected 'IMPORTED'.

This is our code definition:

resource "snowflake_grant_privileges_to_account_role" "grant_on_snowflake_database_to_datadog" {
  account_role_name = snowflake_role.datadog.name
  privileges        = ["IMPORTED PRIVILEGES"]
  on_account_object {
    object_type = "DATABASE"
    object_name = "SNOWFLAKE"
  }
  with_grant_option = false
}

The code is applied successfully the first time, but then every time terraform runs again, the issue presents.