Start of change

Authority Options for SQL Analysis and Tuning

This topic describes the authority options for SQL analysis and tuning.

DB2® for i has a rich set of commands, stored procedures, APIs and tools for analysis and tuning of the performance aspects of database applications. Previously, a system security officer would need to grant *JOBCTL user special authority to enable database analysts and database administrators to use the database tools. Since *JOBCTL authority allows a user to change many system critical settings that are unrelated to database activity, it was not an easy decision for security officers to grant this authority. In some cases, it was an easy decision and *JOBCTL was not granted to database analysts, thus prohibiting the use of the full set of database tools.

Note: For more information about setting overrides for the QAQQINI file refer to the following link: QAQQINI file override support.

Now the security officer has additional capability to authorize access to database analysis tools and the SQL Plan Cache. DB2 for i which takes advantage of the function usage capability available in the operating system. A new function usage group called QIBM_DB has been created with two function IDs in the QIBM_DB group:

  1. QIBM_DB_SQLADM (IBM i Database Administrator tasks)
  2. QIBM_DB_SYSMON (IBM i Database Information tasks)

The security officer now has flexibility to grant authorities by either; granting *JOBCTL special authority or authorizing a user or group to the IBM i Database Administrator Function through Application Administration in System i® Navigator of IBM® Navigator for i. The Change Function Usage (CHGFCNUSG) command, with a function ID of QIBM_DB_SQLADM, can also be used to change the list of users that are allowed to perform Database Administration operations. The function usage controls allow groups or specific users to be allowed or denied authority. The CHGFCNUSG command also provides a parameter which can be used to grant function usage authority to any user that has *ALLOBJ user special authority. (e.g. ALLOBJAUT(*USED))

The Database Administrator function is needed whenever a user is analyzing and viewing SQL performance data. Some of the more common functions are displaying statements from the SQL Plan Cache, analyzing SQL Performance Monitors and SQL Plan Cache Snapshots, and displaying the SQL details of a job other than your own.

The database administrator function usage is an alternative to granting *JOBCTL, but it does not replace the requirement of having the correct object authority. To enable database administrator tasks which are unrelated to performance analysis, refer to the specific task for details on the authorization requirements. For example, to allow an administrator to reorganize a table, they must have object authorities granted, which are not covered by QIBM_DB_SQLADM.

In addition to QIBM_DB_SQLADM, the Change Function Usage (CHGFCNUSG) command, with a function ID of QIBM_DB_SYSMON, can also be used to change the list of users that are allowed to perform Database Information operations.

The Database Information function provides much less authority than Database Administrator.The primary use is to allow a user to examine high-level database properties. For example, a user that does not have *JOBCTL or QIBM_DB_SQLADM, could be allowed to view the SQL Plan Cache properties if granted authority to QIBM_DB_SYSMON.

Note: For more information about using Application Administration refer to the following link: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/rzahg/icmain.htm .

To work with QIBM_DB database group function usage from System i Navigator, follow these steps:

  1. Launch Application Administration as shown in figure 1.
  2. Expand the ‘IBM i' and ‘Database' folders under the Host Applications tab as shown in figure 2.
  3. Customize the Database Administrator (QIBM_DB_SQLADM) function usage as shown in figure 3.

In this example, the security officer determined that they wanted to set up a group called Dbagroup that would contain all the users that they wanted to give this level of authority. And they explicitly wanted to deny access to Slfuser. Now the security officer has one convenient and easily monitored place to view and authorize users to these functions.

Figure 1. Launch Application Administration.

Screen capture of Figure 1 window

Figure 2. Expand the Database group

Screen capture of Figure 2 window

Figure 3. Change the QIBM_DB_SQLADM function usage settings

Screen capture of Figure 3 window

Note: The Security Reference has more information about the function usage commands: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/rzahg/icmain.htm.

Table 1 describes some of the authorization changes related to DB2 commands, Stored Procedures, and APIs.

Table 1. Authorization requirements for Database performance and analysis
User Action *JOBCTL QIBM_DB_SQLADM QIBM_DB_SYSMON No Authority
SET CURRENT DEGREE (SQL statement) Allowed Allowed Not Allowed Not Allowed
CHGQRYA command targeting a different user's job Allowed Allowed Not Allowed Not Allowed
STRDBMON or ENDDBMON commands targeting a different user's job Allowed Allowed Not Allowed Not Allowed
STRDBMON or ENDDBMON commands targeting a job that matches the current user Allowed Allowed Allowed Allowed
QUSRJOBI() API format 900 or System i Navigator's SQL Details for Job Allowed Allowed Allowed Not Allowed
DUMP PLAN CACHE PROPERTIES procedure Allowed Allowed Allowed Not Allowed
Visual Explain within Run SQL Scripts Allowed Allowed Allowed Allowed
Visual Explain outside of Run SQL Scripts Allowed Allowed Not Allowed Not Allowed
ANALYZE PLAN CACHE procedure Allowed Allowed Not Allowed Not Allowed
DUMP PLAN CACHE procedure Allowed Allowed Not Allowed Not Allowed
MODIFY PLAN CACHE procedure Allowed Allowed Not Allowed Not Allowed
MODIFY PLAN CACHE PROPERTIES procedure (currently does not check authority) Allowed Allowed Not Allowed Not Allowed
CHANGE PLAN CACHE SIZE procedure (currently does not check authority) Allowed Allowed Not Allowed Not Allowed
START PLAN CACHE EVENT MONITOR procedure Allowed Allowed Not Allowed Not Allowed
END PLAN CACHE EVENT MONITOR procedure Allowed Allowed Not Allowed Not Allowed
END ALL PLAN CACHE EVENT MONITORS procedure Allowed Allowed Not Allowed Not Allowed

End of change