Caching authorization IDs for routines

Db2 authorization can cache roles or primary authorization IDs for handling routines. 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

The routine authorization cache stores roles or authorization IDs with the EXECUTE privilege on a specific routine. A routine is identified as schema.routine-name.type, where the routine name is one of the following names:

  • The specific function name for user-defined functions
  • The procedure name for stored procedures
  • '*' for all routines in the schema
Important: Statistics trace field QTRACNOT records the number of authorization checks for routine execution authority that could not use the routine authorization cache. If a user-defined routine executes successfully because the authorization ID of the process has installation SYSADM or installation SYSOPR authority, or 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 routine is not added to the cache. However, statistics trace field QTRACNOT is incremented.

If a large value of QTRACNOT is related to many successful routine 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 routine authorization caching.

Start of changeThe size of the routine authorization cache is fixed at 10 MBEnd of change

What to do next

To cache more authorization information about routines, use the following strategies:

  • Granting EXECUTE on schema.*
  • Granting package authority to a secondary ID or role when running in a trusted context
  • Granting routine execute authority to PUBLIC for some or all routines in the schema.