Caching authorization IDs for packages

Db2 authorization can cache roles or primary authorization IDs for handling packages. Db2 checks and caches a role if it is in effect and authorized. If a role is not in effect or authorized, Db2 checks and caches the primary authorization ID.

About this task

Start of changeCaching roles or authorization IDs for packages can provide benefits for handling the following objects at run time:End of change

  • Stored procedures
  • Remotely bound packages
  • Local packages in a package list in which the plan owner does not have execute authority on the package at bind time, but does at run time
  • Local packages that are not explicitly listed in a package list, but are implicitly listed by collection-id.*, *.*, or *.package-id
Important: Statistics trace field QTPACNOT records the number of authorization checks for package execution authority that could not use the package authorization cache. If a package executes successfully because the authorization ID of the process has installation SYSADM or installation SYSOPR authority, or a package for a system-defined routine executes successfully because the authorization ID of the process has installation SYSADM, installation SYSOPR, system DBADM, or SQLADM authority, an entry for that package is not added to the cache. However, statistics trace field QTPACNOT is incremented.

If a large value of QTPACNOT is related to many successful package executions under authorization IDs with installation SYSADM, installation SYSOPR, system DBADM or SQLADM authority, as described above, the large value does not indicate that there is a problem with package authorization caching.

Start of changeThe size of the package authorization cache is fixed at 10 MB.End of change

What to do next

To cache more package authorization information, use any of the following strategies:

  • Granting package execute authority to collection.*
  • Granting package authority to a secondary ID or role when running in a trusted context
  • Granting package execute authority to PUBLIC for some packages or collections