EXPLAIN_PRIVILEGES¶

Syntax¶

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

Arguments¶

statement => 'sql_statement'

A string containing the SQL statement to analyze. The statement is analyzed to determine which privileges are required to execute it.

missing_only => boolean

Boolean value that controls the output mode:

• 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.

Returns¶

The function returns a VARCHAR value containing a JSON object that describes the required privileges in a hierarchical structure. The JSON can contain the following node types:

Permission Node — Represents a single privilege requirement:

{
  "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 Node — All contained privileges are required:

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

OR Node — At least one of the contained privileges is required:

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

Decision Node — Indicates authorization status

{
  "authorized": true
}

• authorized: true — All required privileges are present.

• authorized: false — Statement cannot be authorized with privilege grants.

Access control requirements¶

You must have privileges to refer to the object by name in the SQL statement. Most commonly, this requirement is satisfied by having at least one privilege on the object. The RESOLVE ALL ON ACCOUNT privilege also meets this requirement.

Usage notes¶

• The statement argument must be a constant expression. You cannot pass column values or other non-constant expressions.

• Multi-statement SQL is not supported. The function accepts only a single SQL statement.

• 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).

Examples¶

The following examples call the EXPLAIN_GRANTABLE_PRIVILEGES function:

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

{
  "allOf": [
    {
      "privilege": "<ANY>",
      "objectType": "DATABASE",
      "objectName": "MYDB"
    },
    {
      "privilege": "MONITOR",
      "objectType": "SCHEMA",
      "objectName": "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');