Execute privileges with COMMUTATOR and NEGATOR functions

Important: If you explicitly grant the Execute privilege on an SPL function that is the commutator or negator function of a UDR, you must also grant that privilege on the commutator or the negator function before the grantee can use either. You cannot specify COMMUTATOR or NEGATOR modifiers with SPL procedures.

The following example demonstrates both limiting privileges for a function and its negator to one group of users. Suppose you create the following pair of negator functions:

CREATE FUNCTION greater(y PERCENT, z PERCENT)
RETURNS BOOLEAN
NEGATOR= less(y PERCENT, z PERCENT);
. . .
CREATE FUNCTION less(y PERCENT, z PERCENT)
RETURNS BOOLEAN
NEGATOR= greater(y PERCENT, z PERCENT);

By default, any user can execute both the function and negator. The following statements allow only accounting to execute these functions:

REVOKE EXECUTE ON FUNCTION greater FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION less FROM PUBLIC;
GRANT accounting TO mary, jim, ted;
GRANT EXECUTE ON FUNCTION greater TO accounting;
GRANT EXECUTE ON FUNCTION less TO accounting;

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 who were granted the Execute privilege by that user.

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