Routine-Level Privileges

When you create a user-defined routine (UDR), you become owner of the UDR and you automatically receive the Execute privilege on that UDR.

The Execute privilege allows you to invoke the UDR with an EXECUTE FUNCTION or EXECUTE PROCEDURE statement, whichever is appropriate, or with a CALL statement in an SPL routine. The Execute privilege also allows you to use a user-defined function in an expression, as in this example:

SELECT * FROM table WHERE in_stock(partnum) < 20;

For users, roles, or members of the PUBLIC group who need the Execute privilege on a given UDR, the GRANT statement supports the following syntax:

Notes:

¹ See Routine Parameter List
² See Specific Name


Element	Description	Restrictions	Syntax
routine	A user-defined routine	Must exist	Identifier
SPL_routine	An SPL routine	Must be unique in the database	Identifier

The following statement grants Execute privilege on the delete_order routine to user finn:

GRANT EXECUTE ON ROUTINE delete_order TO finn;

Whether you must grant the Execute privilege explicitly depends on the following conditions:

If you have DBA-level privileges, you can use the DBA keyword of CREATE FUNCTION or CREATE PROCEDURE to restrict the default Execute privilege to users with the DBA privilege. You must explicitly grant the Execute privilege on that UDR to users who do not have the DBA privilege.
If you have the Resource database-level privilege but not the DBA privilege, you cannot use the DBA keyword when you create a UDR:
- When you create a UDR in a database that is not ANSI compliant, PUBLIC can execute that UDR. You do not need to issue a GRANT statement for other users to receive the Execute privilege.
- Setting the NODEFDAC environment variable to yes prevents PUBLIC from executing the UDR until you explicitly grant the Execute privilege.
In an ANSI-compliant database, the creator of a UDR must explicitly grant the Execute privilege on the UDR for other users to be able to execute it.

In HCL OneDB™, if two or more UDRs have the same name, use a keyword from this list to specify which of those UDRs a user list can execute.

Keyword: UDR that the User Can Execute
SPECIFIC: The UDR identified by specific name
FUNCTION: Any function with the specified routine name (and parameter types that match routine parameter list, if specified)
PROCEDURE: Any procedure with the specified routine name (and parameter types that match routine parameter list, if specified)
ROUTINE: Functions or procedures with the specified routine name (and parameter types that match routine parameter list, if specified)

If both a user-defined function and a user-defined procedure of HCL OneDB have the same name and the same list of parameter data types, you can grant the Execute privilege to both with the keyword ROUTINE.

To limit the Execute privilege to one routine among several that have the same identifier, use the FUNCTION, PROCEDURE, or SPECIFIC keyword.

To limit the Execute privilege to a UDR that accepts certain data types as arguments, include the routine parameter list or use the SPECIFIC keyword to introduce the specific name of a UDR.

If an external function has a negator function, you must grant the Execute privilege on both the external function and on its negator function before other users can execute the external function.

A user must hold the Usage privilege on the language in which the user-defined routine is written to register a UDR with the CREATE FUNCTION, CREATE FUNCTION FROM, CREATE PROCEDURE, CREATE PROCEDURE FROM, or CREATE ROUTINE FROM statement. For more information on the requirements to register a UDR, see Privileges necessary for using CREATE FUNCTION.