GRANT (module privileges) statement

This form of the GRANT statement grants privileges on a module.

Invocation

This statement can be embedded in an application program or issued by using dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges that are held by the authorization ID of the statement must include at least one of the following authorities:
  • The WITH GRANT OPTION for EXECUTE on the module.
  • ACCESSCTRL authority on the schema that contains the module.
  • ACCESSCTRL or SECADM authority.
Note: In Db2 11.5.7 and later, the needed authorities are different if the module is SYSIBMADM.UTL_DIR. In this case, the authorities that are held by the authorization ID of the statement must include at least one of the following options:
  • The WITH GRANT OPTION for EXECUTE on the module.
  • SYSADM authority.
If the module on which the privilege is granted is SYSIBMADM.UTL_DIR, and the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set to UTL_DIR_DBAUTH, then the privileges that are held by the authorization ID of the statement are different. In this case, the needed privileges must include at least one of the following options:
  • The WITH GRANT OPTION for EXECUTE on the module
  • ACCESSCTRL authority on the schema that contains the module.
  • SYSADM, ACCESSCTRL, or SECADM authority

Syntax

Read syntax diagramSkip visual syntax diagramGRANTEXECUTEONMODULE module-nameTO ,USERGROUPROLEauthorization-namePUBLIC WITH GRANT OPTION

Description

EXECUTE
Grants the privilege to reference published module objects and run the following operations:
  • Run any published routines defined in the module.
  • Read from and write to any published global variables defined in the module.
  • Reference any published user-defined types defined in the module.
  • Reference any published conditions defined in the module.
ON MODULE module-name
Identifies the module on which the privilege is granted. The module-name must identify a module that exists at the current server (SQLSTATE 42704).
TO
Indicates to whom the privilege is granted.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group name.
ROLE
Specifies that the authorization-name identifies a role name. The role name must exist at the current server (SQLSTATE 42704).
authorization-name
Lists one or more authorization IDs.
PUBLIC
Grants the privilege to a set of users (authorization IDs). For more information, see Authorization, privileges and object ownership.
WITH GRANT OPTION
Allows the specified authorization-names to grant the EXECUTE privilege to other users. If WITH GRANT OPTION is omitted, the specified authorization-names cannot grant the EXECUTE privilege to others unless they receive that authority from some other source.

Notes

Privileges that are granted to a group
  • A privilege that is granted to a group is not used for authorization checking on any of the following items:
    • Static DML statements in a package.
    • A base table that is actively processing a CREATE VIEW statement.
    • A base table that is actively processing a CREATE TABLE statement for a materialized query table.
    • Create SQL routine.
    • Create trigger.

Example

Grant the EXECUTE privilege on module MYMODA to user JONES:
   GRANT EXECUTE
     ON MODULE MYMODA
       TO JONES