Routine-Level Privileges

If you revoke the Execute privilege on a UDR from a user, that user can no longer execute that UDR in any way. For details of how a user can execute a UDR, see Routine-Level Privileges.

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

In an ANSI-compliant database, the owner name must qualify the routine name, unless the user who issues the REVOKE statement is the owner of the routine.

The following example cancels the Execute privilege of user mark on the delete_order routine that is owned by luke:

REVOKE EXECUTE ON ROUTINE luke.delete_order FROM mark;

In HCL OneDB™, any negator function for which you grant the Execute privilege requires a separate, explicit, REVOKE statement.

When you create a UDR under any of the following circumstances, PUBLIC is not granted Execute privilege by default. Therefore you must explicitly grant the Execute privilege before you can revoke it:

You create the UDR in an ANSI-compliant database.
You have DBA privilege and specify DBA after the CREATE keyword to restrict the Execute privilege to users with the DBA database-level privilege.
The NODEFDAC environment variable is set to yes to prevent the PUBLIC group from receiving any access privileges by default that are not explicitly granted.

Setting NODEFDAC to yes also prevents PUBLIC from receiving table access privileges by default when a new table is created in a database that was not created as mode ANSI. The NODEFDAC setting, however, cannot prevent the PUBLIC group from being granted the same privileges by a user who holds the necessary access privileges on the new UDR or on the new table.

If you create a UDR with none of the above conditions in effect, however, PUBLIC can execute your UDR without the GRANT EXECUTE statement. To limit who can execute your UDR, revoke Execute privilege FROM PUBLIC, and grant it to users (see User List) or roles (see Role Name).

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 no longer execute.

Keyword: UDR for Which Execution by the User is Prevented
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)