Categories:

System functions (System Information)

EXPLAIN_GRANTABLE_PRIVILEGES¶

返回一个 JSON 字符串，表示 Snowflake 中每种对象类型的所有可授予权限。此函数提供关于不同对象类型上可授予哪些权限的全面信息，包括每种权限对应的可用授权类型。

See also:

GRANT <privileges> … TO ROLE , GRANT CALLER ,

语法

EXPLAIN_GRANTABLE_PRIVILEGES(
  [ grantee => '<grantee_type>' ]
  [, object_type => '<object_type_name>' ]
  [, grant_type => '<grant_type_name>' ])

实参

所有实参都是可选的，并使用命名参数语法：

grantee => 'grantee_type'

按授权对象类型筛选结果。有效值：模型监视器名称字符串。

• ROLE
• APPLICATION

Default: ROLE

被授权者类型决定了哪些权限可用。例如，应用程序无法单独拥有对象的所有权。

object_type => 'object_type_name'

Filter results to a single object type. Accepts the singular form of the object type name (for example, 'DATABASE', 'TABLE', 'SCHEMA'). The text is case-insensitive.

grant_type => 'grant_type_name'

将筛选结果限定为支持特定授权类型的权限。有效值：模型监视器名称字符串。

• 'INDIVIDUAL' — Grants on individual objects. See GRANT <privileges> … TO ROLE.
• 'ALL' — Bulk grants on all current objects (for example, GRANT ... ON ALL TABLES IN SCHEMA). See GRANT <privileges> … TO ROLE.
• 'FUTURE' — Bulk grants on future objects (for example, GRANT ... ON FUTURE TABLES IN SCHEMA). See Future grants on database or schema objects.
• 'INHERITED' — Bulk grants on both current and future objects in a container (combines ALL and FUTURE). See GRANT <privileges> … TO ROLE.
• 'CALLER' — Caller grants on individual objects. See GRANT CALLER.
• 'INHERITED_CALLER' — Bulk caller grants on all current and future objects in a container (for example, GRANT INHERITED CALLER ... ON ALL TABLES IN SCHEMA). See GRANT CALLER.

文本不区分大小写。

返回

该函数返回一个包含 JSON 数组的 VARCHAR。数组中的每个元素都是一个 JSON 对象，代表一种对象类型，并具有以下结构：

{
  "parent": "<parent_object_type>",
  "singular": "<singular_name>",
  "plural": "<plural_name>",
  "privileges": {
    "<privilege_name>": ["<grant_type>", /* ... additional grant types */],
    /* ... additional privileges */
  }
}

JSON 字段：

• parent — The parent object type in the object hierarchy (for example, SCHEMA is the parent of TABLE). The string is empty for top-level objects like ACCOUNT.
• singular — The singular form of the object type name (for example, DATABASE). Used for individual grants.
• plural — The plural form of the object type name (for example, DATABASES). Used for bulk grants.
• privileges — A map where each key is a privilege name and each value is an array of grant type names indicating how that privilege can be granted.

使用说明

• 所有实参都必须是常量表达式。不能传递列值或其他非常量表达式。
• 如果未提供实参，则该函数将返回所有对象类型中角色的所有可授予权限。

示例

以下示例调用 EXPLAIN_GRANTABLE_PRIVILEGES 函数：

• Get all grantable privileges for roles
• Get privileges for a specific object type
• Filter by grantee type

获取角色的所有可授予权限

返回角色的所有对象类型及其可授予的权限：

CALL EXPLAIN_GRANTABLE_PRIVILEGES();

获取特定对象类型的权限

Return only the privileges for the 'DATABASE' object type:

CALL EXPLAIN_GRANTABLE_PRIVILEGES(object_type => 'DATABASE');

输出示例：

[
  {
    "parent": "ACCOUNT",
    "singular": "DATABASE",
    "plural": "DATABASES",
    "privileges": {
      "APPLYBUDGET": ["ALL", "FUTURE", "INDIVIDUAL", "INHERITED"],
      "CREATE SCHEMA": ["INDIVIDUAL"],
      "IMPORTED PRIVILEGES": ["INDIVIDUAL"],
      "MODIFY": ["ALL", "FUTURE", "INDIVIDUAL", "INHERITED"],
      "MONITOR": ["ALL", "FUTURE", "INDIVIDUAL", "INHERITED"],
      "OWNERSHIP": ["INDIVIDUAL"],
      "REFERENCE_USAGE": ["ALL", "FUTURE", "INDIVIDUAL", "INHERITED"],
      "USAGE": ["ALL", "FUTURE", "INDIVIDUAL", "INHERITED"]
    }
  }
]

按授权对象类型筛选

可用于应用程序的返回权限：

CALL EXPLAIN_GRANTABLE_PRIVILEGES(grantee => 'APPLICATION');

Applications can’t have individual ownership, so OWNERSHIP only shows grant types such as 'ALL', 'FUTURE', and 'INHERITED'.