Protecting catalog tables and granting user permissions

The third step to configuring a relational data source to use a QMF catalog is to specify whether the QMF catalog tables will be protected from unauthorized users and to specify the users that will have permission to access the tables.

About this task

Several tables in the QMF catalog store sensitive information that should not be available to the public. You can choose to protect the QMF catalog tables. In protection mode, the QMF catalog tables are accessed using a collection of stored procedures or SQL packages depending on what the database that is hosting the QMF catalog supports. Users of the QMF catalog must then be granted permission to run the stored procedures or static SQL packages.

To protect the QMF catalog tables:

Procedure

  1. Open the Protect QMF Catalog tables page of the QMF Catalog Wizard.
  2. To specify the type of protection that will be applied to the QMF catalog tables, select one of the following from the Connect using Protected Mode radio group:
    • Never: You select this option to specify that no protection will be placed on the QMF catalog tables. This method will expose the QMF catalog tables to unauthorized use. With no protection the QMF catalog tables can be accessed by any user using dynamic queries. When the database administrator grants permissions to a user to access the QMF catalog that resides on the database, that permission will extend to the whole QMF catalog including the tables in the QMF catalog that store sensitive information.
    • If possible: You select this option to specify that the QMF catalog tables will be protected using either stored procedures or static SQL packages if they are available on the data source. You will specify the users that can run the stored procedures or static SQL packages. If a set of stored procedures or static SQL packages is not available, access to the QMF catalog tables will be as if they are unprotected.
    • Always: You select this option to specify that the QMF catalog tables will always be protected using either stored procedures or static SQL packages. You will specify the users that can run the stored procedures or static SQL packages. If a set of stored procedures or static SQL packages is not available, the query to access the QMF catalog tables will fail.
  3. If you selected If possible or Always from the Connect using Protected Mode radio group, the Protect check box becomes available.
  4. Select the Protect check box. The protection method options become available.
  5. Select one of the following protection methods:
    • Select Stored procedures to specify that you will use stored procedures to protect the QMF catalog tables. You can select this option if the repository storage tables are located on one of the following databases:
      • DB2® UDB LUW V9 and above
      • DB2 z/OS® V9 and above
      • DB2 iSeries (when accessed with IBM Toolbox JDBC driver)
    • Select Static SQL packages to specify that you will use static SQL packages to protect the QMF catalog tables. You can select this option if the repository storage tables are located on a Db2 database that you will connect to using the IBM® DB2 Universal driver for JDBC or the Direct DRDA connectivity provided by the product (the latter option is available for Db2 for z/OS connectivity only).
  6. Type, or select from the drop-down list, the name that you want to use to identify the collection of stored procedures or static SQL packages in the Collection ID field.
  7. Optionally you can type the owner name in the Owner ID field, if you work with Db2® databases. The Owner ID provides the administrator privileges to the user who operates under the login without SYSADM authority.
  8. Click Create. The stored procedures are created or the static SQL packages are bound. A message is issued that informs you of the success of either process. You can also use Delete to remove a collection of stored procedures or static packages.
  9. You must specify which users will have permission to run the stored procedures or static SQL packages for the QMF catalog tables on this database. To grant permission to all users, highlight PUBLIC in the User IDs list and click Grant. To grant permission to specific users, type their user IDs in the field, highlight one or more of the user ID(s) and click Grant. A message is issued that informs you that the selected user IDs have been granted permission to run the stored procedures or static SQL packages. Optionally, you can revoke permission to run the stored procedures or SQL packages from any user that is listed in the User IDs list box. To revoke permission from one or more users, highlight one or more of the user IDs and click Revoke. A message is issued informing you that permission to run the stored procedures or static SQL packages has been revoked from the selected user IDs.
  10. Click Next. The Select QMF Catalog page of the QMF Catalog Wizard opens.