SHOW GRANTS: Changes to output for grants on functions and procedures (Preview)¶

In the output of the SHOW GRANTS command, the value in the name column is changing for functions and procedures:

Before the change:

The name column includes the names and types of the arguments and the return type.

For example, for the following function:

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  ...

Copy

the value of the name column is:

MY_DB.MY_SCHEMA."AREA_OF_CIRCLE(RADIUS FLOAT):FLOAT"

Copy

For the following procedure:

CREATE PROCEDURE output_message(message VARCHAR)
  RETURNS VARCHAR
  ...

Copy

the value in the name column is:

MY_DB.MY_SCHEMA."OUTPUT_MESSAGE(MESSAGE VARCHAR):VARCHAR"

Copy

After the change:

The name column just includes the types of the arguments.

For example, for the following function:

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  ...

Copy

the value of the name column is:

MY_DB.MY_SCHEMA.AREA_OF_CIRCLE(FLOAT)

Copy

For the following procedure:

CREATE PROCEDURE output_message(message VARCHAR)
  RETURNS VARCHAR
  ...

Copy

the value in the name column is:

MY_DB.MY_SCHEMA.OUTPUT_MESSAGE(VARCHAR)

Copy

This change makes it easier to use the value in the name column in GRANT and REVOKE statements that you want to execute.

For example, suppose that you want to revoke the privileges granted on functions and procedures to the my_custom_role role. You can run the SHOW GRANTS command:

SHOW GRANTS TO ROLE my_custom_role
  ->> SELECT "privilege", "granted_on", "name"
        FROM $1
        WHERE "granted_on" IN ('FUNCTION', 'PROCEDURE');

+-----------+------------+-----------------------------------------+
| privilege | granted_on | name                                    |
|-----------+------------+-----------------------------------------|
| USAGE     | FUNCTION   | MY_DB.MY_SCHEMA.AREA_OF_CIRCLE(FLOAT)   |
| USAGE     | PROCEDURE  | MY_DB.MY_SCHEMA.OUTPUT_MESSAGE(VARCHAR) |
+-----------+------------+-----------------------------------------+

Then, you can copy and paste the returned values into REVOKE statements to revoke those privileges:

REVOKE USAGE ON FUNCTION MY_DB.MY_SCHEMA.AREA_OF_CIRCLE(FLOAT) FROM ROLE my_custom_role;

REVOKE USAGE ON PROCEDURE MY_DB.MY_SCHEMA.OUTPUT_MESSAGE(VARCHAR) FROM ROLE my_custom_role;

Ref: 2190