Grant and revoke the execute privilege

To control the Execute privilege on a UDR, use the EXECUTE ON clause of the GRANT and REVOKE statements.

The database server stores privileges for UDRs in the sysprocauth system catalog table.

UDRs have the following GRANT and REVOKE requirements for the Execute privilege:
  • The DBA can grant the Execute privilege to or revoke it from any routine in the database.
  • The creator of a routine can grant or revoke the Execute privilege on that particular routine. The creator forfeits the ability to grant or revoke by including the AS grantor clause with the GRANT EXECUTE ON statement.
  • Another user can grant the Execute privilege if the owner applied the WITH GRANT keywords in the GRANT EXECUTE ON statement.
A DBA or the routine owner must explicitly grant the Execute privilege to non-DBA users for the following conditions:
  • A routine that was registered with the DBA keyword
  • A routine in an ANSI-compliant database
  • A routine in a database that is not ANSI-compliant, but with the NODEFDAC environment variable set to yes.

An owner can restrict the Execute privilege on a routine even though the database server grants that privilege to public by default. To do so, issue the REVOKE EXECUTE ON...PUBLIC statement. The DBA and owner can still execute the routine and can grant the Execute privilege to specific users, if applicable.

A user might receive the Execute privilege accompanied by the WITH GRANT option authority to grant the Execute privilege to other users. If a user loses the Execute privilege on a routine, the Execute privilege is also revoked from all users to whom that user granted the Execute privilege.

The following example shows an equal() function defined for a UDT and the GRANT statement to enable user mary to execute this variation of the equal() function:
CREATE FUNCTION equal (arg1 udtype1, arg2 udtype1)
RETURNING BOOLEAN
EXTERNAL NAME "/usr/lib/udtype1/lib/libbtype1.so(udtype1_equal)"
LANGUAGE C
END FUNCTION;

GRANT EXECUTE ON FUNCTION equal(udtype1, udtype1) TO mary;

User mary does not have permission to execute any other UDR named equal().

In an ANSI-compliant database, the AS grantor clause is required, rather than optional, if the DBA who issues the GRANT EXECUTE statement is not the owner of the specified routine. Only users who hold the DBA privilege can specify the AS grantor clause. The specified grantor must be the owner of the specified routine, as listed in the owner column of the sysprocedures system catalog table. This cannot be the name of a role or the PUBLIC keyword.

Similarly, the AS revoker clause is required in the REVOKE EXECUTE statement, rather than optional, if the DBA who issues the REVOKE EXECUTE statement is not the owner of the specified routine. The revoker must be registered in the sysprocedures catalog table as the owner of the routine.

For more information, see GRANT statement and REVOKE statement in the HCL OneDB™ Guide to SQL: Syntax.