Granting the USAGE privilege on a workload

For a workload to be associated with a connection, the session user must have the USAGE privilege on that workload. Users with the ACCESSCTRL, DATAACCESS, DBADM, SECADM, or WLMADM authority implicitly have the USAGE privilege on all workloads.

Before you begin

To use the GRANT USAGE ON WORKLOAD statement, you require ACCESSCTRL, SECADM, or WLMADM authority

See Workload management DDL statements for more information about prerequisites.

About this task

When the data server finds a workload that matches the attributes of an incoming connection, the data server checks whether the session user has the USAGE privilege on that workload. If the session user does not have the USAGE privilege on that workload, the data server looks for the next matching workload. (In other words, the workloads for which the session user does not have the USAGE privilege are treated as if they do not exist.) Therefore, the workload USAGE privilege gives you the ability to further control which workload among the matching workloads a user, group, or role should be assigned to. For example, you can define more than one workload with the same connection attributes and grant the USAGE privilege on each of these workloads to only certain users, groups, or roles. For more information, see Workload assignment.

The client can set the client user ID, client application name, client workstation name, and client accounting string (which are some of the connection attributes that are used to assign a connection to a workload) without authorization. Therefore, the workload USAGE privilege also permits you to control which session user has the authority to use a workload.

You can view the USAGE privilege information by querying the SYSCAT.WORKLOADAUTH view.

If you create a database with the RESTRICTIVE option, the USAGE privilege on the SYSDEFAULTUSERWORKLOAD workload is not granted to PUBLIC at database creation time. You must explicitly grant the USAGE privilege on this workload to non-WLMADM and non-DBADM users. If the session user does not have the USAGE privilege on any of the workloads, including SYSDEFAULTUSERWORKLOAD, SQL4707N is returned when the data server attempts to associate a workload with the database connection.

Procedure

To grant the USAGE privilege on a workload:

  1. Use the GRANT USAGE ON WORKLOAD statement.
    You can grant the USAGE privilege to specific users, groups, roles, or PUBLIC. For example, to grant the USAGE privilege on the ACCOUNTS workload to the CPA group, you would issue the following statement:
    GRANT USAGE ON WORKLOAD ACCOUNTS TO GROUP CPA

    You cannot grant the USAGE privilege on the SYSDEFAULTADMWORKLOAD workload. The SYSDEFAULTADMWORKLOAD workload can only be used by ACCESSCTRL, DATAACCESS, DBADM, SECADM, or WLMADM users who issue the SET WORKLOAD TO SYSDEFAULTADMWORKLOAD command.

  2. Commit your changes.
    When you commit your changes, the SYSCAT.WORKLOADAUTH view is updated. Until the GRANT statement is committed, the data server cannot consider the workload when performing workload assignment for the newly authorized users, groups, or roles.