GET STATEMENT PERMISSIONS

GET STATEMENT PERMISSIONS analyzes an SQL statement and returns a list of permissions required to execute it.

Use this function to understand the permissions required, before granting them to a specific role.

Learn more about the permission system in the access control guide.

See also GRANT, CREATE ROLE.

Permissions

No special permissions required.

Syntax

get_statement_permissions_statement ::=
    SELECT GET_STATEMENT_PERMISSIONS(query_stmt)
    ;

Parameters

Parameter

Description

query_stmt

The statement to analyze

Returns

This utility returns details of the required permissions to run the statement.

If the statement requires no special permissions, the utility returns an empty result set.

Result columns

permission_type

The permission type required

object_type

The object on which the permission is required

object_name

The object name

The following table describes the supported permissions:

Permission

Object

Description

LOGIN

Cluster

Login permissions (with a password) allows a role to be a user and login to a database

PASSWORD

Cluster

Sets the password for a user role

CREATE FUNCTION

Database

Allows a user to create a Python UDF

SUPERUSER

Cluster, Database, Schema

The most privileged role, with full control over a cluster, database, or schema

CONNECT

Database

Allows a user to connect and use a database

CREATE

Database, Schema

For a role to create and manage objects, it needs the CREATE and USAGE permissions at the respective level

USAGE

Schema

For a role to see tables in a schema, it needs the USAGE permissions

SELECT

Table

Allows a user to run SELECT queries on table contents

INSERT

Table

Allows a user to run COPY FROM and INSERT statements to load data into a table

UPDATE

Table

Allows a user to modify the value of certain columns in existing rows without creating a table

DELETE

Table

Allows a user to run DELETE, TRUNCATE statements to delete data from a table

DDL

Database, Schema, Table, Function

Allows a user to alter tables, rename columns and tables, etc.

EXECUTE

Function

Allows a user to execute UDFs

ALL

Cluster, Database, Schema, Table, Function

All of the above permissions at the respective level

Examples

Getting permission details for a simple statement

t=> SELECT GET_STATEMENT_PERMISSIONS('SELECT * from nba');
permission_type | object_type | object_name
----------------+-------------+-------------------
SELECT          | table       | master.public.nba
USAGE           | schema      | master.public

Getting permission details for a DDL statement

Tip

Use dollar quoting ($$) to avoid escaping a statement

t=> SELECT GET_STATEMENT_PERMISSIONS($$ALTER TABLE nba RENAME COLUMN "Weight" TO "Mass"$$);
permission_type | object_type | object_name
----------------+-------------+-------------------
DDL             | table       | master.public.nba
USAGE           | schema      | master.public