Controlling access on VSE

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 49, 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 privileges for 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 50, 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 51, 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.

Granting and revoking SQL privileges

Users automatically own any objects they create and save in the database (unless they create a table with a different owner). The owner of an object automatically has all SQL privileges on objects he or she owns, and can grant (or revoke) these privileges to other users. Anyone with DB2 administrator authority can grant or revoke SQL privileges for any object in the database. The user Q has this authority, and is predefined to DB2 during QMF installation.

When granting or revoking privileges on objects you do not own, qualify the object with the SQL authorization ID of the owner:

JONES.ORDER__BACKLOG

SQL authorization IDs can be implicit qualifiers. Queries can contain unqualified table, view, and index names. QMF commands can contain unqualified query, procedure, and form names. In these cases, the user's SQL authorization ID serves as the implicit qualifier. For example, a user is operating with JONES as the current SQL authorization ID. During the session, the user issues the command:

RUN QUERYA (FORM=FORMA

which runs the following SQL query:

SELECT * FROM TABLEA

The RUN command refers to the query JONES.QUERYA and the form JONES.FORMA. The SELECT command refers to the table JONES.TABLEA.

If you create a table, view, index, or alias with an unqualified name, your current authorization ID becomes the owner of the object. That ID must have the privileges needed to create the object.

You must have DBA authority to create a table, view, or index with a qualified name that is not your current authorization ID.

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 in tFigure 88o 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.

Using the SQL REVOKE statement

Use the SQL REVOKE statement to remove privileges:

REVOKE UPDATE ON ORDER__BACKLOG FROM JONES

Use the PUBLIC keyword to revoke privileges from all QMF users.

DB2 privileges have a cascading structure; privileges revoked from a user are automatically revoked from any additional users to whom that user granted them.

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=NO

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 DB2 for VSE 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.