Plan or Package Ownership

All the actions an application plan or a package takes on tables require one or more privileges. The owner of the plan or package must hold every required privilege. Another ID can execute the plan or package, requiring only the EXECUTE privilege. In that way, another ID can exercise all the privileges that are used in validating the plan or package but only within the restrictions imposed by the SQL statements in the original program.

The executing ID can use some of the owner's privileges, within limits. If the privileges are revoked from the owner, the plan or the package is invalidated. It must be rebound, and the new owner must have the required privileges.

The BIND and REBIND subcommands create or change an application plan or a package. On either subcommand, use the OWNER option to name the owner of the resulting plan or package. Keep the following points in mind when naming an owner if the OWNER option is used.

  • Any user can name the primary or any secondary ID.

  • An ID with the BINDAGENT privilege can name the grantor of that privilege.

  • An ID with SYSCTRL or SYSADM authority can name any authorization ID on a BIND command but not on a REBIND command.

The following apply if the OWNER option is omitted.

  • BIND: The primary ID becomes the owner.

  • REBIND: The previous owner retains ownership.

Unqualified Objects

A plan or a package can contain SQL statements that use unqualified table and view names. For static SQL, the default qualifier for those names is the owner of the plan or package. However, you can use the QUALIFIER option of the BIND command to specify a different qualifier.

For plans or packages that contain static SQL, using the BINDAGENT privilege and the OWNER and QUALIFIER options gives you considerable flexibility in performing bind operations. For plans or packages that contain dynamic SQL, the DYNAMICRULES behavior determines how DB2 qualifies unqualified object names.

For unqualified distinct types, user-defined functions, stored procedures, and trigger names in dynamic SQL statements, DB2 finds the schema name to use as the qualifier by searching schema names in the CURRENT PATH special register. For static statements, the PATH bind option determines the path that DB2 searches to resolve unqualified distinct types, user-defined functions, stored procedures, and trigger names.

However, an exception exists for ALTER, CREATE, DROP, COMMENT ON, GRANT, and REVOKE statements. For static SQL, specify the qualifier for these statements in the QUALIFIER bind option. For dynamic SQL, the qualifier for these statements is the authorization ID of the CURRENT SQLID special register.

Plan Execution Authorization

The plan or package owner must have authorization to execute all static SQL statements that are embedded in the plan or package. These authorizations do not need to be in place when the plan or package is bound, nor do the objects that are referred to need to exist at that time.

A bind operation always checks whether a local object exists and whether the owner has the required privileges on it. Any failure results in a message. To choose whether the failure prevents the bind operation from completing, use the VALIDATE option of BIND PLAN and BIND PACKAGE and also the SQLERROR option of BIND PACKAGE. If you let the operation complete, the checks are made again at runtime. The corresponding checks for remote objects are always made at runtime. Authorization to execute dynamic SQL statements is also checked at runtime.

DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson © 2008-2017.
If you may any questions please contact us: