SQL Reference

GRANT (Package Privileges)

This form of the GRANT statement grants privileges on 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 grant the CONTROL privilege, SYSADM or DBADM authority is required.

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

Syntax

            .-,-------------------.
            V                     |
>>-GRANT--------+-BIND---------+--+----------------------------->
                +-CONTROL------+
                |         (1)  |
                '-EXECUTE------'
 
                 (2)
>----ON--PACKAGE-------package-name----------------------------->
 
         .-,------------------------------------.
         V                                      |
>----TO----+-+-------+---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

Grants the privilege to bind a package. The BIND privilege is really a rebind privilege, because the package must have already been bound (by someone with BINDADD authority) to have existed at all.

In addition to the BIND privilege, the user must hold the necessary privileges on each table referenced by static DML statements contained in the program. This is necessary because authorization on static DML statements is checked at bind time.

CONTROL

Grants the privilege to rebind, drop, or execute the package, and extend package privileges to other users. The CONTROL privilege for packages is automatically granted to creators of packages. A package owner is the package binder, or the ID specified with the OWNER option at bind/precompile time.

BIND and EXECUTE are automatically granted to an authorization-name that is granted CONTROL privilege.

EXECUTE

Grants the privilege to execute the package.

ON PACKAGE package-name

Specifies the name of the package on which privileges are to be granted.

TO

Specifies to whom the privileges are granted.

USER

Specifies that the authorization-name identifies a user.

GROUP

Specifies that the authorization-name identifies a group name.

authorization-name,...

Lists the authorization IDs of one or more users or groups.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

PUBLIC

Grants the privileges to all users.

Rules

• If neither USER nor GROUP is specified, then
  • If the authorization-name is defined in the operating system only as GROUP, then GROUP is assumed.
  • If the authorization-name is defined in the operating system only as USER or if it is undefined, USER is assumed.
  • If the authorization-name is defined in the operating system as both, or DCE authentication is used, an error (SQLSTATE 56092) is raised.

Examples

Example 1:  Grant the EXECUTE privilege on PACKAGE CORPDATA.PKGA to PUBLIC.

  GRANT EXECUTE
    ON PACKAGE CORPDATA.PKGA
    TO PUBLIC

Example 2:  GRANT EXECUTE privilege on package CORPDATA.PKGA to a user named EMPLOYEE. There is neither a group nor a user called EMPLOYEE.

  GRANT EXECUTE ON PACKAGE
     CORPDATA.PKGA TO EMPLOYEE

or

  GRANT EXECUTE ON PACKAGE
     CORPDATA.PKGA TO USER EMPLOYEE