Dynamic SQL Authorization After Plan-to-Package Migration
Your company is migrating a CICS/COBOL transaction from a plan to a package. The transaction uses both static SQL and dynamic SQL built from user-entered account filters. In test, it ran under a plan owned by DB2ADM with DYNAMICRULES(BIND); users had EXECUTE on the plan but no table privileges. In production, the package is bound with DYNAMICRULES(RUN) and owned by APPOWNR. After cutover, 250 call-center users get SQLCODE -551 on the dynamic statement, while static statements succeed. The security team will not grant SELECT on ACCT to PUBLIC or to the 250 individual IDs. They will allow a role if it is auditable and can be activated only by the application. The application server connects with individual RACF IDs and can issue SET CURRENT SQLID. The package has 80 packages and 15 plans; a rebind is allowed in a maintenance window, but application code changes are frozen.
1. Why do the static statements succeed while the dynamic statement fails with -551?
Read the worked answer
Static SQL authorization is checked at bind time against the package owner's privileges. If APPOWNR has the required authority on the referenced tables, users only need EXECUTE on the package. Dynamic SQL under DYNAMICRULES(RUN) is authorized at execution time using the current SQLID, which is the user's ID unless the application changes it. EXECUTE on the package or plan does not confer SELECT on ACCT for dynamic SQL. That is why the static statements work and the dynamic statement fails. Verify the current SQLID, the package's DYNAMICRULES setting, and the contents of SYSIBM.SYSTABAUTH for the user and any role before changing anything.
2. What least-privilege solution would you recommend, given that application code changes are frozen?
Read the worked answer
The narrowest option is to rebind the affected packages with DYNAMICRULES(BIND) under a dedicated runtime owner that has only the privileges the transaction needs, such as SELECT on ACCT and EXECUTE on any dependent objects. That keeps the 250 users from needing table privileges and avoids granting SELECT to PUBLIC. Confirm that APPOWNR or the new owner does not have excessive authority and that the dynamic SQL cannot reach objects outside the transaction's scope. Grant EXECUTE on the packages and plans to the call-center users or their RACF group. If the security team insists on per-user auditability and role activation, use a trusted context with a role, but test the role activation path in a test LPAR because changing DYNAMICRULES and ownership affects every dynamic statement in those packages.
3. What audit and verification steps would you perform before and after the change?
Read the worked answer
Before the rebind, capture the current package owner, DYNAMICRULES setting, and authorization for a sample user. After the change, verify with a test user that static and dynamic statements both succeed, and confirm that the user still has no direct SELECT on ACCT. Check that PUBLIC does not have EXECUTE on the packages or plans. Enable or review Db2 audit traces for the relevant authorization events if the security team requires evidence. Keep the runtime owner ID tightly controlled, and document that the package owner, not the end user, is the authorization path for static SQL and for dynamic SQL under DYNAMICRULES(BIND).