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.
Syntax
>>-REVOKE EXECUTE ON--+-| function-designator |----------+------>
+-FUNCTION--+---------+--*---------+
| '-schema.-' |
+-| method-designator |------------+
+-METHOD * FOR--+-type-name------+-+
| '-+---------+--*-' |
| '-schema.-' |
+-| procedure-designator |---------+
'-PROCEDURE--+---------+--*--------'
'-schema.-'
.-,---------------------------------.
V |
>--FROM----+-+-------+--authorization-name-+-+------------------>
| +-USER--+ |
| +-GROUP-+ |
| '-ROLE--' |
'-PUBLIC------------------------'
.-BY ALL-.
>--+--------+--RESTRICT----------------------------------------><
function-designator
|--+-FUNCTION--function-name--+-------------------------+-+-----|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC FUNCTION--specific-name---------------------'
method-designator
|--+-METHOD--method-name--+-------------------------+--FOR--type-name-+--|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC METHOD--specific-name-----------------------------------'
procedure-designator
|--+-PROCEDURE--procedure-name--+-------------------------+-+---|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC PROCEDURE--specific-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, 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,
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
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