[Bug]: grants on future tables in database not working

[aleenprd]

Terraform CLI Version

1.8.1

Terraform Provider Version

0.97.0

Terraform Configuration

# future in database
resource "snowflake_grant_privileges_to_account_role" "example" {
  privileges        = ["SELECT", "INSERT"]
  account_role_name = snowflake_account_role.db_role.name
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_database        = snowflake_database.db.name
    }
  }
}

Category

category:resource

Object type(s)

resource:grant_privileges_to_account_role

Expected Behavior

I expect to be able to access future tables

Actual Behavior

Role can't access new objects created in database

Steps to Reproduce

# future in database
resource "snowflake_grant_privileges_to_account_role" "example" {
  privileges        = ["SELECT", "INSERT"]
  account_role_name = snowflake_account_role.db_role.name
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_database        = snowflake_database.db.name
    }
  }
}

followed by terraform apply

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

Would you like to implement a fix?

• [ ] Yeah, I'll take it 😎

[aleenprd]

I have found this article helpful but I am having an issue with the code:

locals {
  environment           = var.environment
  database_name         = local.environment == "prod" ? var.domain_name : "${upper(local.environment)}_${var.domain_name}"
  schema_objects        = ["TABLE", "VIEW", "MATERIALIZED VIEW", "DYNAMIC TABLE"] 
  schema_objects_plural = toset([for obj in local.schema_objects : format("%sS", obj)])
  # These are the object types that we want to grant permissions on for user roles
  all_schemas = [for schema_obj in local.database_name.schemas : schema_obj.name]
  all_schema_object_pairs = flatten([
    for schema in local.all_schemas : [
      for object_type in local.schema_objects_plural : {
        schema      = schema
        object_type = object_type
      }
    ]
  ])
}

resource "snowflake_grant_privileges_to_account_role" "ro_developers_all_future_objects" {
  for_each = { for idx, pair in local.all_schema_object_pairs : "${pair.schema}-${pair.object_type}" => pair }
  account_role_name = var.developer_role
  privileges        = ["SELECT"]
  on_schema_object {
    future {
      object_type_plural = each.value.object_type
      in_schema          = "\"${local.database_name}\".\"${each.value.schema}\""
    }
  }

}

│ Error: Unsupported attribute
│
│ on dev.tf line 7, in locals:
│ 7: all_schemas = [for schema_obj in local.database_name.schemas : schema_obj.name]
│ ├────────────────
│ │ local.database_name is a string
│
│ Can't access attributes on a primitive-typed value (string).

[sfc-gh-jcieslak]

Here's an example of what I believe you are trying to achieve. Please let me know if that helps:

resource "snowflake_account_role" "test" {
  name = "test_tf_role"
}

resource "snowflake_grant_account_role" "test" {
  role_name = snowflake_account_role.test.name
  user_name = "<your_username>"
}

resource "snowflake_database" "test" {
  name = "test_tf_db"
}

resource "snowflake_schema" "test" {
  database = snowflake_database.test.name
  name = "test_tf_sch"
}

resource "snowflake_grant_privileges_to_account_role" "database_usage" {
  privileges        = ["USAGE"]
  account_role_name = snowflake_account_role.test.name
  on_account_object {
    object_type = "DATABASE"
    object_name = snowflake_database.test.name
  }
}

resource "snowflake_grant_privileges_to_account_role" "schema_usage" {
  privileges        = ["USAGE"]
  account_role_name = snowflake_account_role.test.name
  on_schema {
    schema_name = snowflake_schema.test.fully_qualified_name
  }
}

resource "snowflake_grant_privileges_to_account_role" "test" {
  privileges        = ["SELECT", "INSERT"]
  account_role_name = snowflake_account_role.test.name
  on_schema_object {
    future {
      object_type_plural = "TABLES"
      in_database        = snowflake_database.test.name
    }
  }
}

resource "snowflake_table" "test" {
  database = snowflake_database.test.name
  schema = snowflake_schema.test.name
  name = "test_tf_table"

  column {
    type = "NUMBER(38,0)"
    name = "num"
  }
}

You have to replace <your_username>, and then you can see that after apply you can run the following commands in SQL:

use role accountadmin;
grant usage on warehouse snowflake to role "test_tf_role"; -- should be granted by higher privileged role like ACCOUNTADMIN, needed for insert
use role "test_tf_role";
use warehouse snowflake;
insert into "test_tf_db"."test_tf_sch"."test_tf_table" values (1), (2), (3);
select * from "test_tf_db"."test_tf_sch"."test_tf_table";

which proves that the future grants were granted.

[aleenprd]

I found out that future grants on objects in a schema given to mkre specialist roles will actually dominate over grants applied on schema level to more general roles. A quirck of Snowflake. The solution was to use for each to apply the individual grants to the general roles

[sfc-gh-jcieslak]

Ok, then it seems like we are good to close the thread, right?