Grant and revoke 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 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 this, 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.