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

Attention

This behavior change is in the 2026_01 bundle.

For the current status of the bundle, refer to Bundle history.

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

the value of the name column is:

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

For the following procedure:

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

the value in the name column is:

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

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

the value of the name column is:

MY_DB.MY_SCHEMA.AREA_OF_CIRCLE(FLOAT)

For the following procedure:

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

the value in the name column is:

MY_DB.MY_SCHEMA.OUTPUT_MESSAGE(VARCHAR)

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