The SQL GRANT statement

You can use the SQL GRANT statement to grant SQL SELECT, UPDATE, INSERT, DELETE, and other privileges on tables or views.

For example, suppose user JONES needs to use the Change mode of the Table Editor for a table called ORDER_BACKLOG. To grant JONES the UPDATE privilege on the ORDER_BACKLOG table, issue the following statement:

GRANT UPDATE ON ORDER_BACKLOG TO JONES WITH GRANT OPTION

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

Use the keyword PUBLIC to grant SQL privileges to all local QMF users. For example, use the following statement to grant the INSERT privilege on the ORDER_BACKLOG table to all users in the local database, and allow each of those users to grant the INSERT privilege to other users:

GRANT INSERT ON ORDER_BACKLOG TO PUBLIC WITH GRANT OPTION

To make an object available to local and remote users for DB2® for z/OS® subsystems that have distributed data that is enabled, grant authority to PUBLIC AT ALL LOCATIONS. For example, the following statements give the SELECT privilege on the table Q.STAFF:

GRANT SELECT ON TABLE Q.STAFF TO PUBLIC
GRANT SELECT ON TABLE Q.STAFF TO PUBLIC AT ALL LOCATIONS

Q.STAFF is one of the QMF sample tables. Similar statements are run for all of the QMF sample tables during QMF installation so that all users have the SELECT privilege on the sample tables.

Tip: To make maintenance tasks more efficient, a single SQL query can contain multiple SQL maintenance statements. To create a query that includes multiple statements, place a semicolon between statements and set the DSQEC_RUN_MQ global variable to 1. Information about how to create queries that include multiple statements as well as restrictions on the types of statements that can be used is included in the description of the RUN command. Alternatively, you can create a template query that uses QMF variables in place of the parts of the query that frequently change (such as the type of privilege, the object name, and the authorization ID). You might also consider using a QMF procedure to do the task if there is more than one query.