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.

Syntax¶

get_statement_permissions_statement ::=
    SELECT GET_STATEMENT_PERMISSIONS(query_stmt)

Parameter¶

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.

`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 allows a role to be a user and login to a database
`CREATE FUNCTION`	Database	Allows a user to create UDFs
`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:

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

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

Permissions¶

No special permissions are required to run GET_STATEMENT_PERMISSIONS.

For more information, see GRANT, CREATE ROLE