Controlling access on VM

QMF objects, such as queries and procedures, and functions such as the Table Editor, allow users to access and manipulate data stored in tables in the database. Because this data might be sensitive, you might need to control users' access to certain objects.

SQL privileges required to access objects

Whenever a SELECT query is issued through QMF, either through one of the QMF query interfaces or as a result of commands, such as DISPLAY TABLE or PRINT TABLE, QMF adds FOR FETCH ONLY to the query to improve performance when accessing remote data. Therefore, FOR FETCH ONLY should not be added to SQL queries run through QMF.

SQL privileges required for QMF commands

Using Table 46, locate the QMF command your users need to use and grant them the required SQL privilege on the table or view they're working with. See Granting and revoking SQL privilegesfor examples of SQL GRANT statements.

Not all users can use the SAVE command to create a new table.

For more information on SQL privileges, such as SELECT, INSERT, UPDATE, or DELETE, see the appropriate DB2 SQL Reference manual.

SQL privileges required for prompted and QBE queries

Using Table 47, locate the type of query your users need and grant them the SQL privilege on the table or view against which the query runs.

For more information on prompted or QBE queries, see Using QMF .

SQL privileges required for the table editor

Using Table 48, locate the Table Editor function your users need to use and grant them the SQL privilege on the table or view they need to edit.

For more information on the Table Editor, see Using QMF.

Using the SQL GRANT statement

Use the SQL GRANT statement to grant SQL SELECT, UPDATE, INSERT, and DELETE privileges. For example, suppose user JONES needs to issue the following command:

EDIT TABLE ORDER__BACKLOG (MODE=CHANGE

Assuming you are the owner of the table, use the statement below to grant JONES the SQL UPDATE privilege he needs to edit the ORDER__BACKLOG table in change mode:

GRANT UPDATE ON ORDER__BACKLOG TO JONES WITH GRANT OPTION

WITH GRANT OPTION indicates that JONES can grant to other users any of the SQL privileges you granted him for the ORDER__BACKLOG table.

If you need to run GRANT queries often, use QMF variables in place of parts of the query that frequently change, such as UPDATE, ORDER__BACKLOG, and JONES. Variables are explained in the QMF Reference manual. You might also consider using a QMF procedure to do the task if there is more than one query. Using QMF explains how to create procedures.

Use the keyword PUBLIC to grant SQL privileges to all QMF users. For example, use the statement below to grant INSERT authority on the ORDER__BACKLOG table to all users, and allow each of those users to grant INSERT authority to other users:

GRANT INSERT ON ORDER__BACKLOG TO PUBLIC WITH GRANT OPTION

For more information on the GRANT statement, see the appropriate DB2 SQL Reference manual.

Note: If you grant more than one person INSERT, UPDATE, or DELETE privileges on a database object, and two or more users try to access that object at the same time, there might be contention for resources, causing performance or other problems. If a user is editing a table required during QMF initialization, that table can be locked to prevent QMF from starting for other users.

Sharing QMF objects with other users

You or any QMF user can enable access to QMF queries, forms, and procedures by using the SHARE parameter of the QMF SAVE command.

Specify SHARE=YES when saving an object to allow any other user to display the query and use it in a QMF command that does not replace or erase it. For example, the command below saves the current query as ORDER__QUERY and allows any other user to display and run it:

SAVE QUERY AS ORDER__QUERY (SHARE=YES

The default is defined by the global variable DSQEC_SHARE. See the QMF Reference manual for more information.

The owner of an object can change its shared status at any time, using a DISPLAY command followed by a SAVE command, as shown below:

DISPLAY ORDER__QUERY
SAVE QUERY AS ORDER__QUERY (SHARE=NO

For more information on the SAVE command, see the QMF Reference manual.

Allowing uncommitted read

If you want your QMF session to allow uncommitted read, you can specify a value for the global variable DSQEC__ISOLATION in the Q.SYSTEM__INI procedure.

Uncommitted read can be useful in a distributed environment. However, allowing uncommitted read can introduce non-existent data into a QMF report. Do not allow uncommitted read if your QMF reports must be free of non-existent data.

Values can be:

'0'

Isolation level UR, Uncommitted Read.

'1'

Isolation level CS, Cursor Stability. This is the default.

For QMF Version 7.2 the use of the value '0' is only effective with the database servers DB2 for VM Version 5 or higher.

Setting standards for creating objects

The objects in your installation might be shared among many users, so they should have names that indicate what the object is and how it should be used. Encourage users to provide comments that describe for other users the purpose of queries, forms, procedures, and tables. Tables and views require more maintenance and administration, so consider establishing special guidelines for creating these objects.

For information on how to create comments for QMF and database objects using the SAVE command, see QMF Reference.