Categories:

System functions (System Information)

EXPLAIN_PRIVILEGES

返回一个 JSON 字符串，说明执行 SQL 语句需要哪些权限。该函数会分析给定 SQL 语句的授权要求，并以结构化格式返回，其中包含所需的权限、对象类型及对象名称。

See also:

GRANT <privileges> … TO ROLE, SHOW GRANTS

语法

EXPLAIN_PRIVILEGES(
  statement => '<sql_statement>'
  [, missing_only => <boolean> ]
  [, for_role => '<role_name>' ])

实参

statement => 'sql_statement'

包含要分析的 SQL 语句的字符串。系统会分析该语句，以确定执行它需要哪些权限。

missing_only => boolean

控制输出模式的布尔值：

  • false - Returns all privileges required to execute the statement, regardless of whether the current user or specified role has them.
  • true — Returns only the privileges that are missing (not currently held by the current user or specified role). If all required privileges are present, returns {"authorized": true}.

Default: false

for_role => 'role_name'

The name of a role to check privileges for. This argument is used only when missing_only => true. Returns all privileges missing for the role (and its granted roles) to execute the statement.

返回

该函数返回一个 VARCHAR 值，其中包含一个 JSON 对象，以层级结构描述所需的各项权限。JSON 可以包含以下节点类型：

权限节点 – 代表单项权限要求：

{
  "privilege": "<privilege_name>",
  "objectType": "<object_type>",
  "objectName": "<fully_qualified_object_name>"
}
  • privilege — The name of the required privilege (for example, USAGE, SELECT, OWNERSHIP). The special value <ANY> indicates that any privilege on the object is sufficient.
  • objectType — The type of object (for example, DATABASE, TABLE, SCHEMA, ACCOUNT).
  • objectName — The fully qualified name of the object.

AND 节点 – 需要所有包含的权限：

{
  "allOf": [
    /* ... permissions or nodes */
  ]
}

OR 节点 – 至少需要以下其中一项权限：

{
  "oneOf": [
    /* ... permissions or nodes */
  ]
}

决策节点 – 指示授权状态

{
  "authorized": true
}
  • authorized: true — All required privileges are present.
  • authorized: false — Statement cannot be authorized with privilege grants.

访问控制要求

在 SQL 语句中按名称引用该对象时，必须具备相应的权限。通常情况下，只需拥有该对象的任意一项权限即可满足此要求。RESOLVE ALL ON ACCOUNT 权限也符合这一要求。

使用说明

  • The statement argument must be a constant expression. You cannot pass column values or other non-constant expressions.
  • 不支持多语句 SQL。该函数仅接受一个 SQL 语句。
  • Some SQL statements are not supported for privilege analysis (for example, GRANT, REVOKE, USE ROLE, USE SECONDARY ROLES).
  • Some SQL statements have privilege checks that are not supported for privilege analysis. These checks will be omitted from the output.
  • Some indirect privilege checks are not supported for privilege analysis. These checks will be omitted from the output. For example RESOLVE ALL ON ACCOUNT is not included as an option to resolve a database.
  • When an object cannot be resolved the function returns an error indicating that the statement requires access to all objects.
  • The privilege <ANY> means any privilege on the object is sufficient (for example, for USAGE checks where OWNERSHIP would also suffice).

示例

以下示例调用 EXPLAIN_GRANTABLE_PRIVILEGES 函数：

解释 DESC 命令的权限

显示描述架构所需的所有权限：

CALL EXPLAIN_PRIVILEGES(statement => 'DESC SCHEMA mydb.myschema');

输出示例：

{
  "allOf": [
    {
      "privilege": "<ANY>",
      "objectType": "DATABASE",
      "objectName": "MYDB"
    },
    {
      "privilege": "MONITOR",
      "objectType": "SCHEMA",
      "objectName": "MYDB.MYSCHEMA"
    }
  ]
}

This output indicates that you need any privilege on the database MYDB AND the MONITOR privilege on the schema MYDB.MYSCHEMA.

仅检查缺少的权限

检查当前用户缺少哪些权限：

CALL EXPLAIN_PRIVILEGES(
  statement => 'DROP TABLE mydb.myschema.mytable',
  missing_only => true);

如果您拥有所有必需的权限，则返回：

{
  "authorized": true
}

如果缺少权限，则仅返回缺少的权限：

{
  "allOf": [
    {
      "privilege": "OWNERSHIP",
      "objectType": "TABLE",
      "objectName": "MYDB.MYSCHEMA.MYTABLE"
    }
  ]
}

检查特定角色缺少的权限

检查特定角色缺少哪些权限：

CALL EXPLAIN_PRIVILEGES(
  statement => 'SELECT * FROM mydb.myschema.mytable',
  missing_only => true,
  for_role => 'analyst_role');

Determines whether the analyst_role (including privileges from its granted roles) has the necessary privileges to execute the SELECT statement and, if not, returns the missing privileges.