Micro Focus Enterprise Developer 3.0 for Visual Studio 2013 > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > REVOKE (Function or Procedure Privileges)

REVOKE (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

REVOKE EXECUTE ON
       {FUNCTION {function-name [(parameter-type[,...])] | * } |
        SPECIFIC FUNCTION specific-name[,...] |
        PROCEDURE {procedure-name[,...] | *}
    FROM {authorization-name[,...] | PUBLIC} [BY {authorization-name[,...] | ALL}] 
    RESTRICT

Description

EXECUTE

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

FUNCTION or SPECIFIC FUNCTION

Identifies the function on which the privilege is granted. 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 DISTINCT TYPE statement.

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.

FUNCTION function-name

Identifies the function by its name. You can identify a function by its name only if there is exactly one function with function name in the schema. If you do not explicitly qualify the function name with a schema name, the function name is implicitly qualified with a schema name according to the following rules:

If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER option when the plan or package was created or last rebound. If QUALIFIER was not used, the schema name is the owner of the plan or package.
If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

An * can be specified for a qualified on 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 functions are not affected.

FUNCTION function-name (parameter-type,...)

Identifies the function by its function signature, which uniquely identifies the function.

function-name

Specifies the name of the function. If you do not explicitly qualify the function name with a schema name, the function name is implicitly qualified with a schema name as described in the preceding description for FUNCTION function-name.

(parameter-type,...)

Identifies the number of input parameters of the function and their data types.

The data type of each parameter must match the data type that was specified in the CREATE FUNCTION statement for the parameter in the corresponding position. The number of data types and the logical concatenation of the data types is used to uniquely identify the function.

For data types that have a length, precision, or scale attribute, you can specify a value or use a set of empty parentheses:

Empty parentheses indicate that DB2 ignores the attribute when determining whether the data types match.
FLOAT cannot be specified with empty parentheses because its parameter value indicates different data types (REAL or DOUBLE).
If you use a specific value for a length, precision, or scale attribute, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.
The specific value for FLOAT(n) does not have exactly match the defined value of the source function because 1<=n<= 21 indicates REAL and 22<=n<=53 indicates DOUBLE. Matching is based on whether the data type is REAL or DOUBLE.
If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default length of the data type is implied. For example:
The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

For data types with a subtype or encoding scheme attribute, specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that DB2 ignores the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.

SPECIFIC FUNCTION specific-name

Identifies the function by its specific name.

PROCEDURE procedure-name

Identifies a stored procedure that is defined at the current server. If you do not explicitly qualify the procedure name with a schema name, the procedure name is implicitly qualified with a schema name according to the following rules:

If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER option when the plan or package was created or last rebound. If QUALIFIER was not used, the schema name is the owner of the plan or package.
If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

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.

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:

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

Example:

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 RESTRICT;

Revoke the EXECUTE privilege on procedure VACATION_ACCR from all users at the current server.

REVOKE EXECUTE ON PROCEDURE VACATION_ACCR FROM PUBLIC RESTRICT;

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 RESTRICT;

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 RESTRICT;

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