REVOKE (routine privileges) statement

This form of the REVOKE statement revokes privileges on a routine (function, method, or procedure) that is not defined in a module.

Invocation

This statement can be embedded in an application program or issued through the use of 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 held by the authorization ID of the statement must include ACCESSCTRL or SECADM authority or ACCESSCTRL authority on the schema containing the routine.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE EXECUTE ONfunction-designatorFUNCTIONschema.*method-designatorMETHOD * FORtype-nameschema.*procedure-designatorPROCEDUREschema.*FROM ,USERGROUPROLEauthorization-namePUBLIC BY ALLRESTRICT
function-designator
Read syntax diagramSkip visual syntax diagramFUNCTIONfunction-name(,data-type)SPECIFIC FUNCTIONspecific-name
method-designator
Read syntax diagramSkip visual syntax diagramMETHODmethod-name(,data-type)FORtype-nameSPECIFIC METHODspecific-name
procedure-designator
Read syntax diagramSkip visual syntax diagramPROCEDUREprocedure-name(,data-type)SPECIFIC PROCEDUREspecific-name

Description

EXECUTE
Revokes the privilege to run the identified user-defined function, method, or procedure.
function-designator
Uniquely identifies the function from which the privilege is revoked. For more information, see Function, method, and procedure designators.
FUNCTION schema.*
Identifies the explicit grant for all the existing and future functions in the schema. Revoking the schema.* privilege does not revoke any privileges that were granted on a specific function. In dynamic SQL statements, if a schema is not specified, the schema in the CURRENT SCHEMA special register will be used. In static SQL statements, if a schema is not specified, the schema in the QUALIFIER precompile/bind option will be used.
method-designator
Uniquely identifies the method from which the privilege is revoked. For more information, see Function, method, and procedure designators.
METHOD *
Identifies the explicit grant for all the existing and future methods for the type type-name. Revoking the * privilege does not revoke any privileges that were granted on a specific method.
FOR type-name
Names the type in which the specified method is found. The name must identify a type already described in the catalog (SQLSTATE 42704). In dynamic SQL statements, the value of the CURRENT SCHEMA special register is used as a qualifier for an unqualified type name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified type names. An asterisk (*) can be used in place of type-name to identify the explicit grant on all existing and future methods for all existing and future types in the schema. Revoking the privilege using an asterisk for method and type-name does not revoke any privileges that were granted on a specific method or on all methods for a specific type.
procedure-designator
Uniquely identifies the procedure from which the privilege is revoked. For more information, see Function, method, and procedure designators.
PROCEDURE schema.*
Identifies the explicit grant for all the existing and future procedures in the schema. Revoking the schema.* privilege does not revoke any privileges that were granted on a specific procedure. In dynamic SQL statements, if a schema is not specified, the schema in the CURRENT SCHEMA special register will be used. In static SQL statements, if a schema is not specified, the schema in the QUALIFIER precompile/bind option will be used.
FROM
Specifies from whom the EXECUTE privilege is revoked.
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.
authorization-name
Lists the authorization IDs of one or more users, groups, or roles.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

PUBLIC
Revokes the EXECUTE privilege from PUBLIC.
BY ALL
Revokes the EXECUTE privilege from all named users who were explicitly granted the privilege, regardless of who granted it. This is the default behavior.
RESTRICT
Specifies that the EXECUTE privilege cannot be revoked if both of the following conditions are true (SQLSTATE 42893):
  • The specified routine is used in a view, trigger, constraint, index extension, SQL function, SQL method, aggregate interface function, transform group, or is referenced as the SOURCE of a sourced function.
  • The loss of the EXECUTE privilege would cause the owner of the view, trigger, constraint, index extension, SQL function, SQL method, aggregate interface function, transform group, or sourced function to no longer be able to execute the specified routine.

Rules

  • It is not possible to revoke the EXECUTE privilege on a function or method defined with schema 'SYSIBM' or 'SYSFUN' (SQLSTATE 42832).
  • For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
    • For all rows for the specified object in the SYSCAT.ROUTINEAUTH catalog view where the grantee is authorization-name:
      • If all rows have a GRANTEETYPE of 'U', USER is assumed.
      • If all rows have a GRANTEETYPE of 'G', GROUP is assumed.
      • If all rows have a GRANTEETYPE of 'R', ROLE is assumed.
      • If all rows do not have the same value for GRANTEETYPE, an error is returned (SQLSTATE 56092).

Notes

  • If a package depends on a routine (function, method, or procedure), and the EXECUTE privilege on that routine is revoked from PUBLIC, a user, or a role, the package becomes inoperative if the routine is a function or a method, and the package becomes invalid if the routine is a procedure, unless the package owner still holds the EXECUTE privilege on the routine. The package owner can still hold the EXECUTE privilege if:
    • The package owner was explicitly granted the EXECUTE privilege
    • The package owner is a member of a role that holds the EXECUTE privilege
    • The EXECUTE privilege was granted to PUBLIC
    • The EXECUTEIN privilege was granted to PUBLIC
    Because group privileges are not considered for static packages, the package becomes inoperative (in the case of a function or a method) or invalid (in the case of a procedure) even if a group to which the package owner belongs holds the EXECUTE privilege.

Examples

  • Example 1: Revoke the EXECUTE privilege on function CALC_SALARY from user JONES. Assume that there is only one function in the schema with function name CALC_SALARY. 
       REVOKE EXECUTE ON FUNCTION CALC_SALARY FROM JONES RESTRICT
  • Example 2: Revoke the EXECUTE privilege on procedure VACATION_ACCR from all users at the current server. 
       REVOKE EXECUTE ON PROCEDURE VACATION_ACCR FROM PUBLIC RESTRICT
  • Example 3: Revoke the EXECUTE privilege on function NEW_DEPT_HIRES from HR (Human Resources). The function has two input parameters of type INTEGER and CHAR(10), respectively. Assume that the schema has more than one function named NEW_DEPT_HIRES. 
       REVOKE EXECUTE ON FUNCTION NEW_DEPT_HIRES (INTEGER, CHAR(10))
     FROM HR RESTRICT
  • Example 4: Revoke the EXECUTE privilege on method SET_SALARY for type EMPLOYEE from user Jones. 
       REVOKE EXECUTE ON METHOD SET_SALARY FOR EMPLOYEE FROM JONES RESTRICT