SQL Reference

REVOKE (Package Privileges)

This form of the REVOKE statement revokes CONTROL, BIND, and EXECUTE privileges against a package.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• CONTROL privilege on the referenced package
• SYSADM or DBADM authority.

To revoke the CONTROL privilege, SYSADM or DBADM authority are required.

Refer to REVOKE (Database Authorities), REVOKE (Index Privileges), REVOKE (Schema Privileges), REVOKE (Server Privileges) and REVOKE (Table, View, or Nickname Privileges) for other GRANT statements.

Syntax

             .-,-----------------.
             V                   |
>>-REVOKE------+-BIND---------+--+------------------------------>
               +-CONTROL------+
               |         (1)  |
               '-EXECUTE------'
 
                 (2)
>----ON--PACKAGE-------package-name----------------------------->
 
           .-,------------------------------------.
           V                                      |
>----FROM----+-+-------+---authorization-name--+--+------------><
             | +-USER--+                       |
             | '-GROUP-'                       |
             '-PUBLIC--------------------------'
 

Notes:

1. RUN can be used as a synonym for EXECUTE.

2. PROGRAM can be used as a synonym for PACKAGE.

Description

BIND

Revokes the privilege to execute BIND or REBIND on the referenced package.

The BIND privileges cannot be revoked from an authorization-name that holds CONTROL privilege on the package without also revoking the CONTROL privilege.

CONTROL

Revokes the privilege to drop the package and to extend package privileges to other users.

Revoking CONTROL does not revoke the other package privileges.

EXECUTE

Revokes the privilege to execute the package.

The EXECUTE privilege cannot be revoked from an authorization-name that holds CONTROL privilege on the package without also revoking the CONTROL privilege.

ON PACKAGE package-name

Specifies the package on which privileges are revoked.

FROM

Indicates from whom the privileges are revoked.

USER

Specifies that the authorization-name identifies a user.

GROUP

Specifies that the authorization-name identifies a group name.

authorization-name,...

Lists one or more authorization IDs.

The authorization ID of the REVOKE statement itself cannot be used (SQLSTATE 42502). It is not possible to revoke the privileges from an authorization-name that is the same as the authorization ID of the REVOKE statement.

PUBLIC

Revokes the privileges from PUBLIC.

Rules

• If neither USER nor GROUP is specified, then:
  • If all rows for the grantee in the SYSCAT.PACKAGEAUTH catalog view have a GRANTEETYPE of U, then USER will be assumed.
  • If all rows have a GRANTEETYPE of G, then GROUP will be assumed.
  • If some rows have U and some rows have G, then an error (SQLSTATE 56092) is raised.
  • If DCE authentication is used, then an error is raised (SQLSTATE 56092).

Notes

• Revoking a specific privilege does not necessarily revoke the ability to perform the action. A user may proceed with their task if other privileges are held by PUBLIC or a group, or if they have privileges such as ALTERIN on the schema of a package.

Examples

Example 1:  Revoke the EXECUTE privilege on package CORPDATA.PKGA from PUBLIC.

   REVOKE EXECUTE
     ON PACKAGE CORPDATA.PKGA
     FROM PUBLIC

Example 2:  Revoke CONTROL authority on the RRSP_PKG package for the user FRANK and for PUBLIC.

   REVOKE CONTROL
     ON PACKAGE RRSP_PKG
     FROM USER FRANK, PUBLIC