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 Figure 77to 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 in Figure 78 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.