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.

Figure 2. Expand the Database group

Figure 3. Change the QIBM_DB_SQLADM function usage settings

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.