REVOKE statement (function or procedure privileges)

This form of the REVOKE statement revokes privileges on user-defined functions, cast functions that were generated for distinct types, and stored procedures.

Syntax for REVOKE (function or procedure privileges)

Read syntax diagramSkip visual syntax diagramREVOKEEXECUTEONfunction-designatorPROCEDURE,procedure-name*FROM ,authorization-nameROLErole-namePUBLIC BY,authorization-nameROLErole-nameALLINCLUDING DEPENDENT PRIVILEGESNOT INCLUDING DEPENDENT PRIVILEGESRESTRICT
function-designator:
Read syntax diagramSkip visual syntax diagramFUNCTION,function-name(,parameter-type)*SPECIFIC FUNCTION,specific-name
parameter-type:
Read syntax diagramSkip visual syntax diagramdata-type AS LOCATOR1
data-type:
Read syntax diagramSkip visual syntax diagrambuilt-in-type 2distinct-type-namearray-type-name3
Notes:
  • 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
  • 2 For the syntax diagram for this fragment, see Syntax for built-in-type.
  • 3 array-type-name is supported only for compiled SQL scalar functions.

Description for REVOKE (function or procedure privileges)

EXECUTE
Revokes the privilege to run the identified user-defined function, cast function that was generated for a distinct type, or stored procedure.
function-designator

Identifies the function from which the privilege is revoked. The function must exist at the current server, and it must be a function that was defined with the CREATE FUNCTION statement or a cast function that was generated by a CREATE TYPE statement. The function-designator identifies the SQL function by its name, a function signature that uniquely identifies the function, or its specific name. For more information, see Function designators.

If the function was defined with a table parameter (the LIKE TABLE was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to identify the function. Instead, identify the function with its function name, if unique, or with its specific name.

An * can be specified for a qualified or unqualified function-name. An * (or schema-name.*) indicates that the privilege is revoked for all the functions in the schema. You (or the indicated grantors) must have granted the privilege on FUNCTION * to all identified users (including PUBLIC if specified). Privileges granted on specific procedures are not affected.

PROCEDURE procedure-name
Identifies a stored procedure that is defined at the current server.

An * can be specified for a qualified or unqualified procedure-name. An * (or schema-name.*) indicates that the privilege is revoked for all the procedures in the schema. You (or the indicated grantors) must have granted the privilege on PROCEDURE * to all identified users (including PUBLIC if specified). Privileges granted on specific procedures are not affected.

FROM
Refer to REVOKE statement for a description of the FROM clause.
BY
Refer to REVOKE statement for a description of the BY clause.
INCLUDING DEPENDENT PRIVILEGES or NOT INCLUDING DEPENDENT PRIVILEGES
Specifies whether revoking a privilege or an authority from an authorization ID or a role also results in revoking the grants that were made by that user. The default value is based on the authority that is being revoked and the REVOKE_DEP_PRIVILEGES system parameter:
  • When ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
  • When the REVOKE_DEP_PRIVILEGES system parameter is set to NO, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and an error is returned if the statement includes INCLUDING DEPENDENT PRIVILEGES.
  • Otherwise, INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
INCLUDING DEPENDENT PRIVILEGES
Specifies that revoking a privilege or an authority from an authorization ID or a role also results in revoking dependent privileges. This means that any grants that were made by the user will continue to be revoked, until all grants in the chain have been revoked.

INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set to NO, which enforces the behavior to not include the dependent privileges.

NOT INCLUDING DEPENDENT PRIVILEGES
Specifies that revoking a privilege or an authority from an authorization ID or a role does not cause the grants that were made by the user to be revoked. However, for the revoked privileges, all implications of the privilege being revoked are applied. For example, if the revoked privileges were required to bind a package successfully, that package would continue to be invalidated as a result of the package owner losing these privileges. An object might be dropped if a privilege is revoked that was used to create the object.

NOT INCLUDING DEPENDENT PRIVILEGES must be specified when ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked.

NOT INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set toYES, which enforces the behavior to include dependent privileges in the revoke.

RESTRICT
Prevents the EXECUTE privilege from being revoked on a user-defined function or stored procedure if the revokee owns any of the following objects and does not have the EXECUTE privilege from another source:
  • A function that is sourced on the function
  • A view that uses the function
  • A trigger package that uses the function or stored procedure
  • A table that uses the function in a check constraint or user-defined default clause
  • A materialized query table whose fullselect uses the function
  • An extended index that uses the function

Notes for REVOKE (function or procedure privileges)

For considerations that apply to all REVOKE statement forms, see Notes for REVOKE.

Examples for REVOKE (function or procedure privileges)

Example 1: Revoke the EXECUTE privilege on function CALC_SALARY for user JONES. Assume that there is only one function in the schema with function CALC_SALARY. 
   REVOKE EXECUTE ON FUNCTION CALC_SALARY FROM JONES;
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;
Example 3: Revoke the privilege of the administrative assistant to grant EXECUTE privileges on function DEPT_TOTAL to other users. The administrative assistant will still have the EXECUTE privilege on function DEPT_TOTALS. 
   REVOKE EXECUTE ON FUNCTION DEPT_TOTALS
         FROM ADMIN_A;
Example 4: Revoke the EXECUTE privilege on function NEW_DEPT_HIRES for HR (Human Resources). The function has two input parameters with data types of INTEGER and CHAR(10), respectively. Assume that the schema has more than one function that is named NEW_DEPT_HIRES. 
   REVOKE EXECUTE ON FUNCTION NEW_DEPT_HIRES (INTEGER, CHAR(10))
         FROM HR;

You can also code the CHAR(10) data type as CHAR().

Example 5: Revoke the EXECUTE privilege on function FIND_EMPDEPT from role ROLE1: 
   REVOKE EXECUTE ON FUNCTION FIND_EMPDEPT
         FROM ROLE ROLE1;