[Bug]: snowflake_grant_privileges_to_database_role + on_schema_object + object_type_plural + SECRETS gives 'Error: 001003 (42000): SQL compilation error'

[jarach]

Terraform CLI Version

1.9.5

Terraform Provider Version

0.94.1

Terraform Configuration

resource "snowflake_database_role" "test" {
  database = "DEV_HME_RAW_DB"
  name     = "DBR_TEST_SC"
}

resource "snowflake_grant_privileges_to_database_role" "test" {
  database_role_name = "${snowflake_database_role.test.database}.${snowflake_database_role.test.name}"
  on_schema_object {
    all {
      object_type_plural = "SECRETS"
      in_schema = "DEV_HME_RAW_DB.SECRETS_SC"
    }
  }
  privileges         = ["READ"]
}

Category

category:grants

Object type(s)

resource:grant_privileges_to_database_role

Expected Behavior

Grant privileges to ALL SECRETS in DATABASE.SCHEMA

Actual Behavior

Error: An error occurred when granting privileges to database role

with module.main.snowflake_grant_privileges_to_database_role.test,
on ....\modules\main\test.tf line 7, in resource "snowflake_grant_privileges_to_database_role" "test":
7: resource "snowflake_grant_privileges_to_database_role" "test" {

Id: "DEV_HME_RAW_DB"."DBR_TEST_SC"|false|false|READ|OnSchemaObject|OnAll|SECRETS|InSchema|"DEV_HME_RAW_DB"."SECRETS_SC"
Database role name: {DEV_HME_RAW_DB DBR_TEST_SC}
Error: 001003 (42000): SQL compilation error:
syntax error line 1 at position 26 unexpected 'IN'.

Steps to Reproduce

Apply provided terraform piece of code.

How much impact is this issue causing?

Low

Logs

No response

Additional Information

'snowflake_grant_privileges_to_database_role' is working for other schema object like: TABLES, VIEWS, STAGES, etc.

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jmichalak]

Hi @jarach 👋

It seems like it's an error on the Snowflake side. According to the docs, a query like GRANT READ ON ALL SECRETS IN SCHEMA "TEST"."PUBLIC" TO DATABASE ROLE "TEST"."TEST3" should work, but it does not.

Please create a general Snowflake ticket for that. It would be great if you CC @sfc-gh-jmichalak @sfc-gh-asawicki @sfc-gh-jcieslak . We would like to know the result of it.

When this is fixed on the Snowflake side, it should work in the provider too, so I think no action is required on the provider side.

[jarach]

I opened a ticket #00851941. I was not able to add you on CC (to Watchers). I'll let you know the results.

[jarach]

Official answer from Snowflake support:

Unfortunately, these commands are currently not supported. A feature request for supporting bulk grants on SECRETS objects has been submitted to our engineering team. Currently, it's under review. I don't have any ETA when it will be available. I have copied this email to your Account Manager for visibility. Please follow up with your account team for future updates on this feature request. Please let us know if you have any additional questions or if you are agreeable to resolving the case.

I asked to either correct documentation or implement the "feature" as soon as possible.

[sfc-gh-jmichalak]

Thanks. We can keep the issue open until it gets implemented on the Snowflake side.