REVOKE (package privileges) statement

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

ACCESSCTRL or SECADM authority is required to revoke the CONTROL privilege.

Syntax

           .-,---------------.                                  
           V                 |              (2)                 
>>-REVOKE----+-BIND--------+-+--ON--PACKAGE------package-name--->
             +-CONTROL-----+                                    
             |         (1) |                                    
             '-EXECUTE-----'                                    

         .-,---------------------------------.               
         V                                   |  .-BY ALL-.   
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
           | +-USER--+                     |                 
           | +-GROUP-+                     |                 
           | '-ROLE--'                     |                 
           '-PUBLIC------------------------'                 

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-or to add a new version of- the referenced package.

The BIND privilege 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 name of the package on which privileges are to be revoked. The revoking of a package privilege applies to all versions of the package.

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.

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 privileges from PUBLIC.

BY ALL

Revokes each named privilege from all named users who were explicitly granted those privileges, regardless of who granted them. This is the default behavior.

Rules

• For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
  • For all rows for the specified object in the SYSCAT.PACKAGEAUTH 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

• Revoking a specific privilege does not necessarily revoke the ability to perform the action. A user can proceed with a task if other privileges are held by PUBLIC, a group, or a role, or if the user holds 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