Privileges for Executing a Routine

Home | Previous Page | Next Page Creating and Using SPL Routines > Privileges on Routines >

Privileges for Executing a Routine

The Execute privilege enables users to invoke a routine. The routine might be invoked by the EXECUTE or CALL statements, or by using a function in an expression. The following users have a default Execute privilege, which enables them to invoke a routine:

By default, any user with the DBA privilege can execute any routine in the database.
If the routine is registered with the qualified CREATE DBA FUNCTION or CREATE DBA PROCEDURE statements, only users with the DBA privilege have a default Execution privilege for that routine.
If the database is not ANSI compliant, user public (any user with Connect database privilege) automatically has the Execute privilege to a routine that is not registered with the DBA keyword.
In an ANSI-compliant database, the procedure owner and any user with the DBA privilege can execute the routine without receiving additional privileges.

Granting and Revoking the Execute Privilege

Routines have the following GRANT and REVOKE requirements:

The DBA can grant or revoke the Execute privilege to 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 in an ANSI-compliant database
A database with the NODEFDAC environment variable set to yes
A routine that was created with the DBA keyword

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

Execute Privileges with COMMUTATOR and NEGATOR Functions (IDS)

Important:

If you must explicitly grant the Execute privilege on an SPL function that you create with a COMMUTATOR or NEGATOR modifier, you must also explicitly grant the Execute privilege on the commutator or negator modifier 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 greater FROM PUBLIC
REVOKE EXECUTE ON less FROM PUBLIC
GRANT ROLE accounting TO mary, jim, ted
GRANT EXECUTE ON greater TO accounting
GRANT EXECUTE ON 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 statements in the IBM Informix: Guide to SQL Syntax.