如何用Terraform snowflake_grant_privileges_to_role给指定表列表授权给Share

需要将指定表列表的权限授予指定Share。原先使用的snowflake_table_grant资源已弃用，现在要改用官方提供的snowflake_grant_privileges_to_role相关资源，但官方文档里没有找到针对指定表列表（而非全表）授予Share权限的完整示例。

弃用的旧实现（OLD_WAY） #

resource "snowflake_table_grant" "grant" {
    for_each      = local.select_flat

    database_name = each.value.database_name
    schema_name   = each.value.database_name
    table_name    = each.value.table_name

    privilege         = "SELECT"
    roles             = [snowflake_role.role.name]
    shares            = [snowflake_share.share.name]

    on_future         = true
    with_grant_option = false
}

locals {
  select_flat = merge(flatten([
    for database_name, schemas in var.select: [
      for schema_name, tables in schemas: {
          for table_name in tables: 
            "${database_name}-${schema_name}-${table_name}" => {
                 "database_name" = database_name
                 "schema_name" = schema_name
                 "table_name" = table_name
             }                 
      }
    ] 
  ])...)
}

官方提供的新语法示例（NEW_WAY） #

resource "snowflake_grant_privileges_to_role" "g12" {
  privileges = ["SELECT"]
  role_name  = snowflake_role.r.name
  on_schema_object {
    all {
      object_type_plural = "TABLES"
      in_schema          = "\"my_db\".\"my_schema\""
    }
  }
}

解决方案 #

要给指定表列表授予Share权限，可结合for_each遍历目标表列表，为每个表单独配置授权资源，直接关联目标Share即可。具体实现如下：

1. 保留扁平化表列表逻辑（复用旧代码的local配置） #

locals {
  select_flat = merge(flatten([
    for database_name, schemas in var.select: [
      for schema_name, tables in schemas: {
          for table_name in tables: 
            "${database_name}-${schema_name}-${table_name}" => {
                 "database_name" = database_name
                 "schema_name" = schema_name
                 "table_name" = table_name
             }                 
      }
    ] 
  ])...)
}

2. 用新资源实现指定表的Share授权 #

resource "snowflake_grant_privileges_to_share" "table_select_grant" {
  for_each = local.select_flat

  privileges = ["SELECT"]
  share_name = snowflake_share.share.name

  on_schema_object {
    object_type = "TABLE"
    fully_qualified_name = "\"${each.value.database_name}\".\"${each.value.schema_name}\".\"${each.value.table_name}\""
  }
}

说明：

• 使用snowflake_grant_privileges_to_share直接给Share授权，无需通过中间角色，更贴合需求场景
• 借助for_each遍历扁平化后的表列表，为每个表创建独立的授权实例
• 通过fully_qualified_name指定单个表的完整标识符，实现精准授权

如果需要同时给角色和Share授权，可以分别配置两个资源：

# 给角色授权指定表
resource "snowflake_grant_privileges_to_role" "table_select_to_role" {
  for_each = local.select_flat

  privileges = ["SELECT"]
  role_name  = snowflake_role.role.name

  on_schema_object {
    object_type = "TABLE"
    fully_qualified_name = "\"${each.value.database_name}\".\"${each.value.schema_name}\".\"${each.value.table_name}\""
  }
}

# 给Share授权指定表
resource "snowflake_grant_privileges_to_share" "table_select_to_share" {
  for_each = local.select_flat

  privileges = ["SELECT"]
  share_name = snowflake_share.share.name

  on_schema_object {
    object_type = "TABLE"
    fully_qualified_name = "\"${each.value.database_name}\".\"${each.value.schema_name}\".\"${each.value.table_name}\""
  }
}

内容的提问来源于stack exchange，提问作者Kar