GRANT (package privileges) statement

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 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 at least one of the following authorities:
  • CONTROL privilege on the referenced package
  • The WITH GRANT OPTION for each identified privilege on package-name
  • ACCESSCTRL on the schema containing the package
  • ACCESSCTRL or SECADM authority
One of the following authorities is required to grant the CONTROL privilege:
  • ACCESSCTRL
  • SECADM
  • ACCESSCTRL authority on the schema containing the package

Syntax

Read syntax diagramSkip visual syntax diagramGRANT,BINDCONTROLEXECUTE1ONPACKAGE2 package-name TO ,USERGROUPROLEauthorization-namePUBLIC WITH GRANT OPTION
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 allows a user to re-issue the BIND command against that package, or to issue the REBIND command. It also allows a user to create a new version of an existing package.

In addition to the BIND privilege, a user must hold the necessary privileges on each table referenced by static DML statements contained in a 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.

CONTROL grants the ability to grant the previously mentioned privileges (except for CONTROL) to others.

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. The granting of a package privilege applies to all versions of the package (that is, to all packages that share the same package name and package schema).
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.
ROLE
Specifies that the authorization-name identifies a role name. The role name must exist at the current server (SQLSTATE 42704).
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
Grants the privileges to a set of users (authorization IDs). For more information, see Authorization, privileges and object ownership.
WITH GRANT OPTION
Allows the specified authorization-name to GRANT the privileges to others.

If the specified privileges include CONTROL, the WITH GRANT OPTION applies to all of the applicable privileges except for CONTROL (SQLSTATE 01516).

Rules

  • For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
    • If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined as ROLE in the database, and as either GROUP or USER according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
    • If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
    • If the authorization-name is defined in the database as ROLE only, ROLE is assumed.

Notes

  • Package privileges apply to all versions of a package (that is, all packages that share the same package ID and package schema). It is not possible to restrict access to only one version. Because CONTROL privilege is implicitly granted to the binder of a package, if two different users bind two versions of a package, then both users will implicitly be granted access to each other's package.
  • Privileges granted to a group: A privilege that is granted to a group is not used for authorization checking on:
    • Static DML statements in a package
    • A base table while processing a CREATE VIEW statement
    • A base table while processing a CREATE TABLE statement for a materialized query table
    • Create SQL routine
    • Create trigger

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