DB2 FOR LUW / DB2FORLUW / B10 - DB2

If USAGE to a workload is granted to a ROLE that is inherited
from a trusted context, that role is ignored during workload
assignment and applications using that role can receive:

SQL5193N  The current session user does not have usage privilege
on any enabled workloads.


For example, assume that a role APP_USER has the following
privileges:

CONNECT ON DATABASE
USAGE ON WORKLOAD SYSDEFAULTWORKLOAD
DATAACCESS ON DATABASE
EXECUTE ON PACKAGE "NULLID"."SQLC8G06"
EXECUTE ON PACKAGE "NULLID"."SQLC8H06"

Then the following trusted context is created:

CREATE TRUSTED CONTEXT APP_SERVER
  BASED UPON CONNECTION USING SYSTEM AUTHID APPCON
  ATTRIBUTES (ADDRESS 'xxxx.xxx.xxx' WITH ENCRYPTION 'NONE',
              ADDRESS 'yyyy.yyy.yyy' WITH ENCRYPTION 'NONE',
              ADDRESS 'zzzz.zzz.zzz' WITH ENCRYPTION 'NONE')
  DEFAULT ROLE APP_USER
  ENABLE
  WITH USE FOR PUBLIC
  WITHOUT AUTHENTICATION;

Finally, all privileges are revoked from the workload
SYSDEFAULTWORKLOAD except for the role APP_USER.

Any application connecting to the database using the trusted
context will receive
SQL5193N when trying to use the workload SYSDEFAULTWORKLOAD.


If if usage is explicitly granted to the workload, for example:

GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO PUBLIC (or
TESTUSR)

then all works fine.

So the trusted context role is not checked by WLM during
workload assignment.

****************************************************************
* USERS AFFECTED:                                              *
* Users using Workload Manager                                 *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* upgrade to DB2 Version 11 Mod 2 Fix pack 2                   *
****************************************************************

Grant usage on Workloads to public or the users requiring the
access such as:
GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO PUBLIC

Grant usage on Workloads to public or the users requiring the
access such as:
GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO PUBLIC

forerunner  : IT17348 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

03.08.2017
11.10.2017
11.10.2017