March 25, 2014

Plan Program Authorization

A DB2 plan is used to set runtime parameters and security when executing applications on a local DB2 system. A plan specifies run-time options as well as one or more collections of program “packages” that the plan can execute.

The list of packages that a plan can use is defined in the package list parameter at the time the plan is bound. To avoid having to rebind the plan every time it needs to be authorized to execute a package, a collection name is used with a wild card package name to provide dynamic access to any package in the collection. For example you may allow employees to view payroll information but restrict them from updating it. They would use the READPAY PLAN that uses the collection (READONLY_PAYINFO with a program package called PAY001. The syntax for the BIND is:

            BIND PLAN (READPAY)



To bind this plan so program PAY002 can be added to the READONLY_PAYINFO collection without the need to rebind it with the new package name, use this syntax:


            BIND PLAN (READPAY)



Now, as packages are added to the collection READONLY_PAYINFO, they are available to the plan READPAY.

Of course, if a package is inadvertently added to a collection, any authorized user could then execute the plan, which could lead to unauthorized uses of the application package. To protect against these types of exposures, a new bind and rebind plan option was introduced with DB2 11. This option serves essentially as a double check that's designed to ensure, as IBM puts it, "a valid program is authorized to execute the plan."

As is often the case when new features are introduced, plan program authorization isn't tightly integrated with DB2; therefore, some manual steps must be implemented. First, you must create the DB2 table, SYSIBM.DSNPROGAUTH. The DB2 sample library SDSNSAMP(DSNTIJSG) contains both DDL to create table SYSIBM.DSNPROGAUTH and sample DML to INSERT a row into the table.

The row inserted into SYSIBM.DSNPROGAUTH contains information to authorize a PLAN to be used for a given program package. To activate plan program authorization, you must BIND or REBIND a PLAN using the new parameter, PROGAUTH. Once enabled (the system default is DISABLE), DB2 will examine table SYSIBM.DSNPROGAUTH to find a row with combination of PLAN NAME and PACKAGE NAME.

Several new reason codes are available to help determine why program authorization may fail. Here's an example:

00F3003C The program authorization is enabled by specifying PROGAUTH option when the plan was bound. The MDC value for the program does not match the program authorized to use the plan. The request to allocate a plan to the program name is denied.

If the program name exists in SYSIBM.DSNPROGAUTH table, then correct the ROGMDCVAL and PROGMDCPAD values for the program. Otherwise, the program name must be added to the SYSIBM.DSNPROGAUTH table.

To find a list of plans that have enabled program authorization:



                      CAST(QUALIFIER AS CHAR(10)) AS QUALIFIER,

                      RELBOUND, PROGAUTH, PLENTRIES



      WITH UR


Do you ever need to lock down individual packages so they can only be used by specific plans? Personally, I've never required this level of security, and I'd worry that it could be a maintenance nightmare. If DB2 customers are really demanding this feature, I'd expect IBM to soon add the capability to insert into the DSNPROGAUTH table through a grant statement or some form of external security package. What are your thoughts on this?