All QMF users need access to the QMF application plan and packages built by DB2 during QMF installation. The plan and packages enable QMF to run as a DB2 application program. At installation time, the QMF plan and packages are GRANTed EXECUTE to PUBLIC. You can REVOKE and issue specific GRANTs to the user IDs/groups if this is preferred.
You can enable users to use QMF by granting the EXECUTE privilege to PUBLIC or to individual users with the SQL GRANT query. For example, to grant access to user JONES:
GRANT EXECUTE on plan QMF__PLAN to JONES
If you provide access to the QMF plan and packages by individual, you must execute an SQL GRANT statement for each new user.
If you restrict access by individual user, you limit use of the plan and packages to selected DB2 primary or secondary authorization IDs. The difference in refinement shows up when two or more primary authorization IDs have use of the same secondary authorization ID. If you use restricted enrollment to QMF through the profile, then only the primary authorization IDs that have rows in Q.PROFILES have access to QMF. If you restrict access to QMF based on granting EXECUTE privilege to specific authorization IDs, then anyone who has these authorization IDs as their primary or secondary authorization IDs has access to QMF.
After you dispose of a user's queries, forms, and procedures, you need to remove the user's access to the QMF application plan and packages if you granted the access individually. You can run the following queries:
REVOKE EXECUTE on plan 'QMF__PLAN' FROM 'JONES' REVOKE EXECUTE on package 'QMF__PACKAGE' FROM 'JONES'
Revoke the EXECUTE authority on all packages used by QMF.
If the user's EXECUTE privilege was granted more than once, you must revoke each grant individually using the following queries:
REVOKE EXECUTE on plan 'QMF__PLAN' FROM 'JONES' by all REVOKE EXECUTE on package 'QMF__PACKAGE' FROM 'JONES' by all
You must have SYSADM authority on OS/390 to revoke a GRANT.
If the user you are removing is a former QMF administrator who granted access to the QMF plan and packages to other users, removing access from the administrator also removes access for those users.
If other users share the same authorization ID of the former user, do not revoke access to the plan and packages from the authorization ID. If you do, the users sharing the authorization ID will no longer be able to use QMF.
The DB2 privileges required to run QMF queries, the Table Editor, and QMF commands are the same privileges needed to run the underlying SQL statements.
Distributing DB2 privileges is a two-step process:
To assign and revoke privileges:
Not every query run in a QMF session requires DB2 privileges. Those that do not are called static queries and are in the QMF code. QMF uses these queries, for example, to update its own control tables. Users who have nothing to do with QMF administration do not need DB2 privileges on these tables.
The privilege to run dynamic queries comes exclusively from the user. Dynamic queries include all queries executed with the RUN command. They also include certain queries formulated on behalf of the user by QMF. For example, the user issues the DISPLAY command to see the contents of a table.
DB2 privileges required for QMF commands, for prompted and QBE queries, and for the Table Editor are the same as those listed for SQL in SQL privileges required to access objects.
You provide DB2 privileges by running GRANT queries that give DB2 privileges to one or more authorization IDs. For example, the following query grants the SELECT and UPDATE privileges on the table SMITH.TABLEA to the authorization IDs JONES and JOHNSON:
GRANT SELECT, UPDATE ON TABLE SMITH.TABLEA TO JONES, JOHNSON
Run REVOKE queries to withdraw grants of DB2 privileges. You can always withdraw grants for which your SQL authorization ID is the grantor. For example, in a QMF session, the user's current SQL authorization ID is JONES. JONES had previously granted the SELECT privilege on the table SMITH.TABLEA to BAKER. The following query withdraws this grant of the privilege:
REVOKE SELECT ON TABLE SMITH.TABLEA FROM BAKER
If you revoke a privilege from a grantee and find that the grantee still has the privilege, that grantee received the privilege from another user.
You can make grants to PUBLIC and to individuals. Granting a privilege to PUBLIC makes it available to all local users.
To make an object available to remote and local users for DB2 OS/390 subsystems that have distributed data enabled, grant authority to PUBLIC AT ALL LOCATIONS. For example, the following queries 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 sample tables of QMF. This, and similar queries for the other sample tables, are run during QMF installation, so that everyone has SELECT privilege on the sample tables.
The privilege to run a GRANT query must come from the grantor; that is, from the user's current SQL authorization ID. The grantor must have every privilege being granted and must have each privilege with the GRANT option. For example, BAKER wants to grant the SELECT and UPDATE privileges on the table SMITH.TABLEA to JONES. To do this, BAKER must have the SELECT and UPDATE privileges with the GRANT option on the same table.
A GRANT query can include the expression WITH GRANT OPTION. When it does, the privileges are granted with the GRANT option. Without the GRANT option, users cannot grant authority to others. For example, the following queries grant the SELECT privilege on SMITH.TABLEA to JONES and JOHNSON. After the queries are run, only JOHNSON can grant the privilege to others.
GRANT SELECT ON TABLE SMITH.TABLEA TO JONES GRANT SELECT ON TABLE SMITH.TABLEA TO JOHNSON WITH GRANT OPTION
You may have received your DB2 privilege through a SQL GRANT query, from SYSADM authority on OS/390, or because you own the created object. Any DB2 privilege you have might be the result of a chain of grants, beginning with a grant from someone with installation SYSADM authority. Installation SYSADM authority is the highest DB2 for OS/390 authority that anyone can have. During DB2 installation, one or two authorization IDs receive this authority. Users operating with this authority can then grant lesser privileges to others, to be granted in turn to others, and so on.
To grant a specific privilege, one of your authorization IDs must have the privilege to do so, and this ID must be your current SQL authorization ID. If this ID is not your current SQL authorization ID, logon to that ID, or if possible, run the SET CURRENT SQLID query.
The most commonly used privileges for a table are SELECT, INSERT, UPDATE, and DELETE. When you grant the SELECT privilege on a table, the grantee can select data from it in a SELECT query or subquery. When you grant the INSERT, UPDATE, or DELETE privilege on a table, the user can modify the table's data.
If you own a given table, you have all the table privileges with the GRANT option.
View access can be granted to screen-sensitive data, to read only, and to create.
You can use views in place of the tables they represent to screen sensitive data from the viewers. For example, you want to create a view based on the table SMITH.STAFF, which contains personnel information. Each row in the table represents an employee. For each row, you want the view to show the employee's name, department, job classification, and years of service. You do not want it to show the employee's salary and commission.
You create such a view with the following query:
CREATE VIEW VIEWA AS
SELECT NAME, DEPT, JOB, YRS
FROM SMITH.STAFF
Granting a privilege for a view begins with the owner of the view. In this book, the owner of the view is assumed to be the creator. The privileges the owner can grant depend on the privileges the owner has on the underlying objects of the view. These are the tables and views that are named in the FROM clause of the view's defining query. For example, the underlying object of the view created with this query is the table SMITH.STAFF:
CREATE VIEW VIEWA AS
SELECT NAME, DEPT, JOB, YRS
FROM SMITH.STAFF
The view privileges are SELECT, INSERT, UPDATE, and DELETE. With the SELECT privilege, a person can use the view just like a table in SELECT queries and subqueries. With the other privileges, a person can modify the data in the table that the view represents.
The owner of a view has the SELECT privilege on the view, but might not have other privileges. The other privileges might be lacking if the owner of the view did not have the privilege on the underlying object. Alternatively, the privileges might be lacking because the view is read only.
A view is read only, if the defining query is a join. Queries other than joins can also appear in read only views. For more on read only views, see the description of CREATE VIEW queries in the appropriate DB2 UDB SQL Reference manual.
To create a view, the user's SQL authorization ID must have the SELECT privilege on each of the underlying objects of the view. No other privilege is needed.
If the owner of a view loses the SELECT privilege on one or more of the underlying objects, the view is dropped from the system. Any views using that view as an underlying object are also dropped, and so on.
A person with the GRANT option on some view privilege can grant that privilege to others using the GRANT option. The grantee needs no privilege at all on the underlying objects. This fact makes views useful for screening data: with no privilege on the underlying objects, users granted the SELECT privilege on a view can see only the view. If users need SELECT privilege on the underlying objects, they can bypass the view and query these objects directly.
The owner normally creates one or more tables, and then one or more views of these tables. For each of these views, the owner has SELECT privilege with the GRANT option. If a view is not read only, the owner also has INSERT, UPDATE, and DELETE privileges with the GRANT option. The owner can then grant these privileges to others.
The owner of both the tables and views has a complete set of privileges, with the GRANT option, on the underlying objects. When the underlying objects include views, or objects not owned by the owner of the view, the privileges the owner holds on the underlying objects may vary widely.
In this situation, the following rules apply:
Suppose that, after creating a database, you want someone else to maintain it. With proper DB2 authority, you can grant that user DBADM authority over the database. With this authority, the user can perform maintenance tasks, such as:
The holder of this authority also has a full set of privileges on the database tables, no matter who actually owns them. For example, if you want authorization ID JONES to have the power to maintain the database DBASEA, run this query:
GRANT DBADM ON DATABASE DBASEA TO JONES
You can run this query if your SQL authorization ID has SYSADM authority or is the owner of the database.
DBADM authority on a database also has the CREATETS privilege, which lets you create table spaces for the database , and the CREATETAB privilege, which lets you create tables in the database.
If you can grant DBADM authority on a database, you can also grant lesser privileges. Moreover, anyone having DBADM authority with the GRANT option on the database can do the same. For example, if you want the authorization ID JONES to have the power to grant lesser privileges on database DBASEA, run this query:
GRANT DBADM ON DATABASE DBASEA TO JONES WITH GRANT OPTION
Use the IMPORT command sparingly in CICS, because it can affect the performance of other users in the same address space. Also, QMF uses OS QSAM services GET/PUT. This can lock out other QMF users in the same CICS region during I/O operations.
QMF must have the DB2 privileges to run the queries that result from the SAVE and IMPORT commands. The privilege must come from the user, as if the user were running the queries through the RUN command. For example, a user must have the INSERT privilege on a table or an authority implying the INSERT privilege before QMF can run the INSERT queries on that table.
The privileges needed depend in part on whether the user is creating his or her own tables or tables for other users.
When users create tables for others, the qualifier (the owner of the object) must be the user's primary or secondary authorization ID. In creating a table for another user, other privileges might let the appropriate CREATE table query run, but might not let INSERT queries run.
When users create their own tables after the table structure is created, the users automatically have the necessary INSERT privilege. All that is needed is the privilege to run the CREATE TABLE query. The minimum privilege to do this depends on which table space option was chosen:
The user of the default DB2 OS/390 database, DSNDB04, might already have some of these privileges. During DB2 installation, the CREATETAB and CREATETS privileges for the default database were granted to PUBLIC. A user of the default database, operating under the implicit table space option, automatically has the minimum authority to create tables. If, instead, this user operates under the explicit table space option, only the USE privilege must be granted.
Note: The database might be the DB2 OS/390 default database (DSNDB04). However, it should not be one of the databases used exclusively by DB2 itself: DSNDB01, DSNDB03, or DSNDB05.
Through one or more of the following queries, you can grant the privileges that your user lacks:
GRANT CREATETAB ON DATABASE &dbname TO &authid GRANT CREATETS ON DATABASE &dbname TO &authid GRANT USE OF TABLESPACE &dbname.&tbspname TO &authid
where:
Do not enclose these values in quotation marks. For example, if you want to grant USERA the CREATETAB privilege on the database DATABSE2, run this query:
GRANT CREATETAB ON DATABASE DATABASE2 TO USERA
You have the authority to run these queries if you have the privileges they grant, and you hold these privileges with the GRANT option. This is true if you have SYSADM or SYSCTRL (for DB2 2.3) authority or if you have DBADM, DBCTRL, or DBMAINT authorities with the GRANT option.
If your SQL authorization ID has SYSADM authority, you can revoke the grants of others. This gives you a way of revoking privileges, even if they are a result from multiple grants. For example, BAKER has the SELECT privilege on the table SMITH.TABLEA. JONES wants to remove this privilege from BAKER, but does not know who the grantors are. JONES, who has SYSADM authority, has the authority to run the following query:
REVOKE SELECT ON TABLE SMITH.TABLEA FROM BAKER BY ALL
BY ALL removes every grant of the privilege.
You can withdraw a grant of privilege from PUBLIC, just as you can from a single authorization ID. Doing so, however, does not remove this privilege from those who gained the privilege from another source.
You cannot remove a table privilege from the owner of a table. Nor can you remove an implied database privilege, such as CREATETAB, from someone with, for example, DBADM authority over a database. For more on what you can and cannot do with a REVOKE query, see the DB2 UDB for OS390 Administration Guide Also, see the description of the REVOKE command in the DB2 UDB for OS390 SQL Reference manual.
Revoking a DB2 privilege might withdraw it from more users than you intended. This is known as the cascade effect , because some authorities depend on the existence of others. For example, a privilege held because of a single grant is lost if the grantor loses that privilege. BAKER has the SELECT privilege with the GRANT option on SMITH.TABLEA. BAKER grants this privilege to JOHNSON and JONES. For JOHNSON and JONES, this is the only source of this privilege. A REVOKE query now removes this privilege from BAKER. As a result, the query removes this privilege from JOHNSON and JONES.
The loss of privileges can spread to many users, especially if some of those who lost privileges had granted privileges to others. With this loss of privileges might come other losses as well:
Both the cascade effect and ineffective revoking of grants are more likely when many users have the ability to grant DB2 privileges.
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.
Using Table 43, locate the QMF command your users need to use and grant them the required SQL privilege on the table or view they are working with.
| This QMF command: | Requires this SQL privilege on objects referenced by the command: |
|---|---|
| DISPLAY table/view | SELECT |
| DRAW table/view | SELECT |
| EDIT TABLE table/view | The necessary privileges depend on the Table Editor mode. |
| EXPORT TABLE table/view | SELECT |
| IMPORT TABLE table/view | If the table exists, SELECT, DELETE, and INSERT. To include a comment, you must have either ownership of the table or DBADM authority for the table's database. If the table does not exist, you must have either the CREATETAB privilege or DBADM authority for the database or the USE privilege for the table space specified in the SPACE field of your user's profile. |
| PRINT table/view | SELECT |
| RUN query | Whatever privileges are used in the query |
| RUN procedure | Whatever privileges are used in the commands in the procedure |
| SAVE DATA | If the table exists, SELECT, DELETE, and INSERT. To include a comment, you must have either ownership of the table or DBADM authority for the table's database. If the table does not exist, you must have either the CREATETAB privilege or DBADM authority for the database or the USE privilege for the table space specified in the SPACE field of your user's profile. |
| LIST table/view | SELECT |
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.
Using Table 44, locate the type of query your users need and grant them the SQL privilege on the table or view against which the query runs.
| Users using this type of query: | Need this SQL privilege: |
|---|---|
| PROMPTED | SELECT |
| QBE I. | INSERT |
| QBE P. | SELECT |
| QBE U. | UPDATE |
| QBE D. | DELETE |
For more information on prompted or QBE queries, see Using QMF .
Using Table 45, 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.
| Users using this Table Editor function: | Need this SQL privilege on tables or views being edited: |
|---|---|
| ADD | INSERT |
| SEARCH | SELECT |
| CHANGE | UPDATE |
| DELETE | DELETE |
For more information on the Table Editor, see Using QMF.
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 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.
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.
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:
For QMF Version 7.2 the use of the value '0' is only effective with the database server DB2 for OS/390 Version 4 or higher.
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.
[ Previous Page | Next Page | Contents | Index ]