[Bug]: Failed to revoke privileges to add for IMPORTED PRIVILEGES on SNOWFLAKE database

[RobbertDM]

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 😎

[RobbertDM]

I'm wondering whether you can give grant_options for IMPORTED PRIVILEGES?

[RobbertDM]

It seems like the concept of grant options is not relevant for IMPORTED PRIVILEGES ?
Just in Snowflake, if I run
GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE "MY_ROLE" WITH GRANT OPTION;
It gives me
Syntax error: unexpected 'WITH'. (line 6)

While
GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE "MY_ROLE" ;
works fine.

On another (non-shared) database, that syntax does actually work and sets the grant_option to true.

[sfc-gh-jcieslak]

Hey @RobbertDM 👋
I believe the configuration should work with object_type = "APPLICATION" because Snowflake for show grants will return it as APPLICATION and not DATABASE. Regarding grant options, it's documented here(scroll to the WITH GRANT OPTION in the optional parameters section) and it says it's not possible to run with IMPORTED PRIVILEGES.

[RobbertDM]

Thanks for the quick answer @sfc-gh-jcieslak

However, then it gives me

│ Error: expected on_account_object.0.object_type to be one of ["USER" "RESOURCE MONITOR" "WAREHOUSE" "COMPUTE POOL" "DATABASE" "INTEGRATION" "FAILOVER GROUP" "REPLICATION GROUP" "EXTERNAL VOLUME"], got APPLICATION
│ 
│   with module.custom.snowflake_grant_privileges_to_account_role.this,
│   on ../../../../modules/snowflake/custom/snowflake_database_usage.tf line 22, in resource "snowflake_grant_privileges_to_account_role" "this":
│   22:     object_type = "APPLICATION"

I am on Snowflake terraform provider 0.89, is this introduced later?

Also, we might want to update the main docs then, they mention object_type = DATABASE:
https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/docs/resources/grant_privileges_to_account_role.md?plain=1#L99

[RobbertDM]

Possibly relevant comment in the code:

terraform-provider-snowflake/pkg/resources/grant_privileges_to_account_role.go

Line 812 in 319ddc3

// To make the logic simpler, we do not allow it and `object_type = "DATABASE"` should be used for all applications.

[RobbertDM]

Ah, I think I found the one causing trouble:

terraform-provider-snowflake/pkg/resources/grant_privileges_to_account_role.go

Lines 529 to 532 in 319ddc3

	if !id.WithGrantOption {
	if err = client.Grants.RevokePrivilegesFromAccountRole(ctx, privilegesToGrant, grantOn, id.RoleName, &sdk.RevokePrivilegesFromAccountRoleOptions{
	GrantOptionFor: sdk.Bool(true),
	}); err != nil {

and specifically, line 531 sets GrantOptionFor to true.

As you mentioned before, IMPORTED PRIVILEGES does not have grant options.

So I think updates will always fail.

[sfc-gh-jcieslak]

Yeah, so my bad. The resource should be working with object_type = "DATABASE", but it shouldn't have grant option enabled. I tested the following config with v0.89.0 and it worked and was created as well as destroyed without any errors:

resource "snowflake_grant_privileges_to_account_role" "test" {
  account_role_name = "TEST_ROLE"
  privileges = [ "IMPORTED PRIVILEGES" ]
  on_account_object {
    object_type = "DATABASE"
    object_name = "SNOWFLAKE"
  }
  with_grant_option = false
}

It ran REVOKE IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" FROM ROLE "TEST_ROLE"

[sfc-gh-jcieslak]

Hey @RobbertDM 👋
Closing due to long inactivity and because similar issues (like this one) were already handled and confirmed the solution works in newer provider versions.

[xhensiladoda]

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.

[sfc-gh-jcieslak]

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

[deanmorin]

I started seeing this issue in 0.92.0 as well, then upgraded to 0.95.0, where it was still happening.

I ended up manually running the revoke/grant statements for imported privileges which seems to have resolved it.

[sfc-gh-jcieslak]

Hey 👋
I'm sorry, but I couldn't reproduce this error in any of the Terraform Provider versions. I used the same configuration:

resource "snowflake_grant_privileges_to_account_role" "test" {
  account_role_name = "TEST_ROLE"
  privileges        = ["IMPORTED PRIVILEGES"]
  on_account_object {
    object_type = "DATABASE"
    object_name = "SNOWFLAKE"
  }
}

Maybe that's more connected to Snowflake than the provider itself. Please run the terraform apply with the TF_LOG=DEBUG environment variable set for more logging and post it here so I can check what SQLs are run (please be careful with sensitive data).

[martingehrke]

Here is the query being run that fails. My terraform is identical to the above.

REVOKE GRANT OPTION FOR IMPORTED PRIVILEGES ON DATABASE "DBNAME" FROM ROLE "DBNAME_READONLY"

[martingehrke]

I started seeing this issue in 0.92.0 as well, then upgraded to 0.95.0, where it was still happening.

I ended up manually running the revoke/grant statements for imported privileges which seems to have resolved it.

what statements worked?

[sfc-gh-jmichalak]

Hi @martingehrke 👋
I assume this happens during terraform destroy. I just tested @sfc-gh-jcieslak's configuration, and it works correctly on my side (apply, plan, destroy). What error do you get? Could you share logs with TF_LOG=DEBUG?