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 are required to run GET_STATEMENT_PERMISSIONS.

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