Package and plan execution privileges in DB2

Plans, packages, and why EXECUTE exists

A package is the bound form of SQL from one DBRM (one program or one version of a program) in a collection. A plan is what a TSO CALL, batch IKJEFT01, CICS RCT entry, or similar attachment actually runs. The plan’s PKLIST names the packages it may use.

If every teller needed SELECT, UPDATE, and INSERT on BANK.ACCOUNT, you would be granting table access to hundreds of IDs — and they could use SPUFI, not only the teller program. Instead you bind the teller program so the owner has the table privileges, then:

1
2
GRANT EXECUTE ON PLAN TELLPLAN TO TELLGRP;
GRANT EXECUTE ON PACKAGE TELLCOLL.* TO TELLGRP;

TELLGRP can run the application. They cannot open SPUFI and SELECT the whole account table unless you also granted table privileges. That separation is the whole point of plan and package EXECUTE.

Package privileges

Db2 for z/OS documents these explicit package privileges:

• BIND — BIND, REBIND, and FREE PACKAGE, and the DROP PACKAGE statement. Depending on the installation option BIND NEW PACKAGE, BIND can also create a new version of an existing package.
• COPY — the COPY option of BIND PACKAGE (copy a package into a collection). This privilege is z/OS-specific; Db2 LUW uses CONTROL instead.
• EXECUTE — execute the package, and depending on how the name appears in PKLIST, include it in BIND PLAN PKLIST.
• ALL — all package privileges (the ones the grantor is allowed to grant on that package).

1
2
3
4
5
6
7
8
9
GRANT BIND, COPY, EXECUTE
  ON PACKAGE TELLCOLL.TELLPGM
  TO ROLE TELLER_ADMIN WITH GRANT OPTION;
 
GRANT EXECUTE ON PACKAGE TELLCOLL.TELLPGM TO TELLGRP;
 
GRANT ALL ON PACKAGE TELLCOLL.TELLPGM TO ROLE TELLER_ADMIN;
 
REVOKE EXECUTE ON PACKAGE TELLCOLL.TELLPGM FROM TELLGRP;

You can grant to an authorization name, to ROLE role-name, or to PUBLIC. WITH GRANT OPTION lets the grantee pass the same privilege on. Only an explicit GRANT can be REVOKEd. If two grantors gave EXECUTE, both must revoke before the ID loses it.

Collection wildcards

Naming collection.* grants the privilege on every package in that collection, including packages bound later. That is the usual production pattern. Granting only collection.package-id is tighter but means every new program needs another GRANT.

1
2
GRANT EXECUTE ON PACKAGE PAYROLL.* TO PAYBATCH;
GRANT BIND ON PACKAGE PAYROLL.* TO ROLE PAY_BINDER;

Who may grant package privileges

You must hold the privilege WITH GRANT OPTION, or hold an authority that includes it (SYSADM, PACKADM on that collection, and similar). The binder / OWNER of a package receives package privileges as an owner. BINDAGENT lets one ID bind on behalf of another owner; the owner still needs the SQL privileges inside the package.

Plan privileges

Plans have a shorter list:

• BIND — BIND, REBIND, and FREE PLAN for that plan name. Creating a new plan name requires the system privilege BINDADD (or SYSADM / SYSCTRL / system DBADM as documented for BIND ADD).
• EXECUTE — run programs that use the plan.

1
2
3
4
5
6
7
GRANT BIND, EXECUTE ON PLAN DSN8CP12 TO PUBLIC;
 
GRANT EXECUTE ON PLAN DSN8CP12 TO ADAMSON, BROWN WITH GRANT OPTION;
 
GRANT BIND ON PLAN PAYPLAN TO ROLE PAY_BINDER;
 
REVOKE BIND ON PLAN PAYPLAN FROM ROLE PAY_BINDER;

BIND REPLACE on an existing plan can drop existing EXECUTE grants unless you use the BIND option that retains them. IBM warns that this default is easy to miss after a production rebind: yesterday’s tellers get SQLCODE -551 until you GRANT EXECUTE again. For packages, EXECUTE privileges are retained on replace.

PKLIST: the plan must be allowed to use the package

BIND PLAN … PKLIST(TELLCOLL.*) does not magically give every future runner EXECUTE on every package. Two checks show up in real life:

• At plan bind, the plan owner needs the right to include those packages (EXECUTE on the package, or ownership, or an authority that includes it), unless your shop’s PKLIST rules and bind options say otherwise for the way the name is specified.
• At run, the user needs EXECUTE on the plan. For some attachments and for stored procedures, the user (or the role in a trusted context) also needs EXECUTE on the package that actually runs.

Stored procedures are the textbook second GRANT. After CREATE PROCEDURE you typically:

1
2
3
GRANT EXECUTE ON PROCEDURE BANK.GET_BAL TO TELLGRP;
GRANT EXECUTE ON PACKAGE BANKAPP.GET_BAL TO TELLGRP;
GRANT EXECUTE ON PACKAGE BANKAPP.GET_BAL TO ROLE ADMINISTRATOR;

DYNAMICRULES on the calling plan or package decides which ID or role must hold EXECUTE for a CALL. Native SQL procedures and external procedures both have a package that must be executable.

Collection privilege and PACKADM

The collection itself has CREATE IN: permission to BIND PACKAGE into that collection (to name it). Without CREATE IN, BIND PACKAGE(TELLCOLL) fails even if you have BINDADD at the system level.

1
2
3
GRANT CREATE IN ON COLLECTION TELLCOLL TO ROLE TELLER_ADMIN;
 
GRANT PACKADM ON COLLECTION GOLFS TO PKA01 WITH GRANT OPTION;

PACKADM ON COLLECTION gives PKA01 package privileges on all packages in GOLFS plus CREATE IN on GOLFS. WITH GRANT OPTION lets PKA01 grant those to others. Shops often give PACKADM to a role that exists only in a bind trusted context, not to every developer’s TSO ID.

BINDADD, OWNER, and RETAIN

• BINDADD — system privilege to add a new plan or package. Grant it to binders, not to end users.
• OWNER on BIND — the owner must have the SQL privileges for static statements. The binder needs to be the owner, hold BINDAGENT from the owner, or hold a qualifying administrative authority.
• RETAIN (plan BIND) — keep existing EXECUTE grants when replacing a plan. Packages keep EXECUTE on replace without an extra switch.

If RACF access control is active, the same privileges appear as resources in classes MDSNPK (packages) and MDSNPL (plans), for example DB2P.TELLCOLL.TELLPGM.EXECUTE. Native GRANT and RACF PERMIT must not silently disagree.

Dynamic SQL inside a “static” program

EXECUTE on the plan does not automatically authorize PREPARE of a dynamic SELECT the program builds at run time. Dynamic SQL uses DYNAMICRULES (RUN, BIND, DEFINE, INVOKE, and the *_SYS variants). Under RUN, the runner’s authorization ID (or role) needs the table privileges. Under BIND, the owner’s privileges are reused, which is powerful and must be designed on purpose. Do not assume “they only have EXECUTE” if the program issues dynamic SQL.

Explain It Like I'm Five

The package is a lunchbox the cook packed (BIND) using the cook’s kitchen keys (table privileges). The plan is the cafeteria tray that holds those lunchboxes. Kids in the lunch line do not get kitchen keys. They get a ticket that says they may carry that tray (EXECUTE ON PLAN) and sometimes a ticket for a particular lunchbox (EXECUTE ON PACKAGE). COPY is permission to photocopy a lunchbox into another fridge (another collection). BIND on an existing tray is permission to repack it, which can accidentally throw away old tickets unless you ask to RETAIN them.

Exercises

1. Write GRANT statements so group TELLGRP can run plan TELLPLAN and any package in collection TELLCOLL, but cannot BIND.
2. Explain why GRANT SELECT ON BANK.ACCOUNT TO TELLGRP is usually the wrong way to let tellers use a COBOL update program.
3. After BIND PLAN TELLPLAN ACTION(REPLACE) without RETAIN, tellers get -551. What was lost, and how do you prevent it next time?
4. Grant PACKADM on collection DEVCOL to role DEV_PACKADM. What three capabilities did you just give?
5. List the z/OS package privileges and circle which one does not exist on Db2 LUW under the same name.