[Docs]: snowflake_grant_privileges_to_role does not generate a valid string for stored procedures (object_name)

[dipling82]

Documentation Link

https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/grant_privileges_to_role

Description

Terraform CLI and Provider Versions

Terraform CLI: 1.5.6
Snowflake-Labs/snowflake Version: 0.37.1 -> 0.83.1

Use cases or problem statement

As part of our Terraform code, we group privileges using roles. In this particular case we want to manage the access to stored procedures also by roles.
Until now we have achieved this goal by using the resource "snowflake_procedure_grant". After migrating the provider though, we got a series of warnings during the TF Plan phase like the one below:

│ Warning: Deprecated Resource
│ 
│   with module.sf_test_environment.snowflake_procedure_grant.procedure_usage_grant_clone_xyz,
│   on ../modules/snowflake_environment/procedures.tf line 95, in resource "snowflake_procedure_grant" "procedure_usage_grant_clone_xyz":
│   95: resource "snowflake_procedure_grant" "procedure_usage_grant_clone_xyz" {
│ 
│ This resource is deprecated and will be removed in a future major version
│ release. Please use snowflake_grant_privileges_to_role instead.
│ 
│ (and 22 more similar warnings elsewhe

Now when we replace the code, i.e. the resource type snowflake_procedure_grant by the recommended one being snowflake_grant_privileges_to_role, the TF Plan process does not complain anymore.
But the subsequent TF Apply fails with the following error:

│ Error: error granting privileges to account role: 090208 (42601): Argument types of function 'CLONE_XYZ' must be specified.
│ 
│   with module.sf_test_environment.snowflake_grant_privileges_to_role.procedure_usage_grant_clone_xyz,
│   on ../modules/snowflake_environment/procedures.tf line 95, in resource "snowflake_grant_privileges_to_role" "procedure_usage_grant_clone_xyz":
│  95: resource "snowflake_grant_privileges_to_role" "procedure_usage_grant_clone_xyz" {

The affected TF code looks like this:

resource "snowflake_grant_privileges_to_role" "procedure_usage_grant_clone_xyz" {
  privileges = ["USAGE"]
  role_name = snowflake_role.functional_role["clone_xyz"].name
  on_schema_object {
    object_type = "PROCEDURE"
    object_name = "\"${module.databases["ABC"].name}\".\"${module.databases["ABC"].schemas["METADATA"].name}\".\"${snowflake_procedure.compute_metadata.name}\""
  }
  with_grant_option = false
}

Summary

We are not able to generate a valid (fully qualified) procedure name like DB_NAME.SCHEMA_NAME.PROCEDURE_NAME() using the resource snowflake_grant_privileges_to_role because of the parenthesis in object_name.

How much impact is this issue causing?

We are not able to fully migrate to a recent provider version.

References

No response

Would you like to implement a fix?

None

[sfc-gh-jcieslak]

Hey @dipling82 👋
Unfortunately, procedure identifiers are something we don't support right now (they contain arguments as part of their identifiers). Identifiers are something we would like to look into and they're on our roadmap. I cannot give you the exact date when this will be fixed, but in the meantime, we'll try to provide a workaround for this. I'll notify you here once the workaround is available in the latest version. In the meantime, you can check our unsafe execute resource if you would like to specify raw SQLs for now and then migrate to the introduced resource.

[dassowmd]

I ran into this, but a good workaround and possibly just 'The way' to do this is to include the args. The biggest thing is to watch the double quotes.

The docs have quotes around their fully qualified resource name, which was my issue

What worked was removing the escaped double quotes around the proc signature:

##################################
### schema object privileges
##################################

# list of privileges
resource "snowflake_grant_privileges_to_role" "g9" {
  privileges = ["USAGE"]
  role_name  = snowflake_role.r.name
  on_schema_object {
    object_type = "PROCEDURE"
    object_name = "\"my_db\".\"my_schema\".my_proc(VARCHAR, VARCHAR)" # note this is a fully qualified name!
  }
}

[sfc-gh-jcieslak]

Oh, yeah. You're right. This may work. Still, the internal identifier representation could be better, but I'm glad it works for you. Closing the issue as it was somewhat resolved + we're aware of the issue and have identifier improvements on our roadmap.