Authorization Management

This topic shows detailed information about Statistics - Authorization Management.

There are three authorization caches, located in the EDM pool:

Plan, one cache per plan
Package, one per subsystem
Routine, for stored procedures and user-defined functions, one per subsystem

Allied threads (CICS, IMS, TSO, batch) are checked for EXECUTE authority at plan level. The package, and routine authorization caches only check EXECUTE authority for distributed applications.

The size of the plan authorization cache is set at BIND time, with the option CACHESIZE. When this is not specified, the default is taken from the ZPARM AUTHCACHE.

If you run the plan infrequently, or if authority to run the plan is granted to PUBLIC, you might want to turn off caching for the plan so that Db2 does not use unnecessary storage. In this case specify a value of 0 for the CACHESIZE option.

Any plan that you run repeatedly is a good candidate for tuning by using the CACHESIZE option. Also, if you have a plan that a large number of users run concurrently, you might want to use a larger CACHESIZE.

The size of the package authorization cache is determined by ZPARM CACHEPAC.

The size of the routine authorization cache is determined by ZPARM CACHERAC.

Statistics - Authorization Management

The field labels shown in the following sample layout of Statistics - Authorization Management are described in the following section.


AUTHORIZATION MANAGEMENT     QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
TOTAL AUTH ATTEMPTS             53.00     0.29      N/C     1.61
TOTAL AUTH SUCC                 53.00     0.29      N/C     1.61
PLAN-AUTH SUCC-W/O CATALOG       0.00     0.00      N/C     0.00
PLAN-AUTH SUCC-PUB-W/O CAT       0.00     0.00      N/C     0.00
PLAN-AUTH UNSUCC-CACHE           0.00     0.00     0.00     0.00
PLAN-AUTH OVERWRT - AUTH ID      0.00     0.00     0.00     0.00

PKG-AUTH SUCC-W/O CATALOG       16.00     0.09      N/C     0.48
PKG-AUTH SUCC-PUB-W/O CAT        0.00     0.00      N/C     0.00
PKG-AUTH UNSUCC-CACHE            1.00     0.01      N/C     0.03
PKG CACHE OVERWRT - AUTH ID      0.00     0.00      N/C     0.00
PKG CACHE OVERWRT - ENTRY        0.00     0.00      N/C     0.00

RTN-AUTH SUCC-W/O CATALOG       12.00     0.07      N/C     0.36
RTN-AUTH SUCC-PUB-W/O CAT       12.00     0.07      N/C     0.36
RTN-AUTH UNSUCC-CACHE            4.00     0.02      N/C     0.12
RTN CACHE OVERWRT - AUTH ID      0.00     0.00      N/C     0.00
RTN CACHE OVERWRT - ENTRY        0.00     0.00      N/C     0.00
RTN CACHE - ENTRY NOT ADDED      0.00     0.00      N/C     0.00

TOTAL AUTH ATTEMPTS

The number of authorization checks performed for plans, packages, and stored procedures since Db2 was started. This includes successful and failed checks.

Field Name: QTAUCHK

TOTAL AUTH SUCC

The number of successful authorization checks performed on plans, packages, and stored procedures, since Db2 was started.

Field Name: QTAUSUC

PLAN-AUTH SUCC-W/O CATALOG

The number of successful authorization checks that do not use the Db2 catalog (including plan cache checks and public checks).

Background and Tuning Information

For transaction level security, ENABLE and DISABLE on BIND PACKAGE should be used to ensure adequate security. Granting execute authority on the plan to public should be adequate.

Field Name: QTAUCCH

PLAN-AUTH SUCC-PUB-W/O CAT

The number of successful authorization checks based on EXECUTE authority granted to PUBLIC.

Field Name: QTAUPUB

PLAN-AUTH UNSUCC-CACHE

The number of unsuccessful checks for plan EXECUTE privilege made using the plan authorization cache because an applicable entry was not found in the cache.

Field Name: QTAUCNOT

PLAN-AUTH OVERWRT - AUTH ID

The number of times Db2 overwrote an authorization ID in the plan authorization cache.

Field Name: QTAUCOW1

PKG-AUTH SUCC-W/O CATALOG

The number of successful package EXECUTE authorization checks without accessing the Db2 catalog.

Field Name: QTPACAUT

PKG-AUTH SUCC-PUB-W/O CAT

The number of successful package EXECUTE authorization checks without accessing the Db2 catalog. Package EXECUTE authority was granted to PUBLIC in the package authorization cache.

Field Name: QTPACPUB

PKG-AUTH UNSUCC-CACHE

The number of unsuccessful package EXECUTE authorization checks in the package authorization cache. No applicable entry was found in the cache and Db2 catalog access was used.

Field Name: QTPACNOT

PKG CACHE OVERWRT - AUTH ID

The number of times an authorization ID was overwritten to add another one to the package authorization cache.

Field Name: QTPACOW1

PKG CACHE OVERWRT - ENTRY

The number of times an entry for a collection-ID or package-ID was overwritten to add another one to the package authorization cache.

Field Name: QTPACOW2

RTN-AUTH SUCC-W/O CATALOG

The number of times the routine authorization cache was checked successfully of EXECUTE authority on a stored procedure or user-defined function. The Db2 catalog was not accessed. This counter includes the number of PUBLIC authorization checks.

Field Name: QTRACAUT

RTN-AUTH SUCC-PUB-W/O CAT

Number of successful authorization checks for user-defined function or stored procedure execution authority when that authority is held by PUBLIC. The Db2 catalog was not checked.

Field Name: QTRACPUB

RTN-AUTH UNSUCC-CACHE

Number of unsuccessful authorization checks for user-defined function or stored procedure EXECUTE authority because no applicable entry was found in the routine authorization cache.

Field Name: QTRACNOT

RTN CACHE OVERWRT - AUTH ID

Number of times that Db2 overwrote an authorization ID in the routine authorization cache.

Field Name: QTRACOW1

RTN CACHE OVERWRT - ENTRY

Number of times that Db2 overwrote a routine entry in the routine authorization cache.

An entry in the routine authorization cache can refer to a function or procedure or to all functions or procedures within a specific schema.

Field Name: QTRACOW2

RTN CACHE - ENTRY NOT ADDED

Number of times that Db2 could not add an entry to the routine authorization cache.

An entry in the routine authorization cache can refer to a function or procedure or to all functions or procedures within a specific schema.

Field Name: QTRACNAC