DB2 graphic  QMF Version 8

Object views

The following views are created when objects are created. It is these views that determine how QMF for Windows lists are built:

You can alter these views when you install QMF for Windows to tailor the list building. These views differ based on the DB2 platform.

QMF for Windows lists for the extended catalog are built by the views that are created when you select the Create Objects option. For queries, forms, and procs, the view Q.RAA_OBJECT_VIEW_X is the key and it interacts with other views and tables.

The sample object view is for the DB2 z/OS catalog server:

Table 31. Object Views: Q.RAA_OBJECT_VIEW_X
Q.RAA_OBJECT_VIEW_X
Look at the catalog_ids and owner names in Q.RAA_OBJ_DIR_X and display or fail to display each object based on the conditions below. "Yes" to any of the following questions causes the query, procedure or form to be displayed in the list:
  1. Does the object owner match the current user ID?
  2. Does the object owner match the current SQLID?
  3. Does the object owner match the secondary authorization ID of the current user ID having the same catalog ID?
  4. Is the object SHARED? (Restricted = N)
  5. Is the user a SYSADM having the same catalog _ID?

Table 32. Object Views: RDBI.USER_ADMIN_VIEW_X
RDBI.USER_ADMIN_VIEW_X
This view determines whether the user or a secondary authorization ID of the user is a SYSADM. It checks RDBI.ADMIN_VIEW and RDBI.CATALOG_DIR_X for:
  • The current user ID or SQLID
  • Secondary authorization ID of the current user having the same catalog_ID in RDBI.CATALOG_DIR_X

Table 33. Object Views: RDBIUSER_AUTHID_VIEW_X
RDBI.USER_AUTHID_VIEW_X
This view retrieves all rows from the base table RDBI.AUTHID_VIEW_X, where the primary ID is that of the current user. It will list all secondary authorization IDs for the current user.

Table 34. Object Views: RDBI.ADMIN_VIEW
RDBI.ADMIN_VIEW
This view obtains a list of GRANTEEs from the base table SYSIBM.SYSUSERAUTH. It takes GRANTEEs who have been granted G or Y for SYSADMAUTH.

Table 35. Object Views: RDBI.CATALOG_DIR_X
RDBI.CATALOG_DIR_X
This table contains all rows of internal identifier of the distinct catalog and name of the distinct catalog.

Table 36. Object Views: RDBI_AUTHID_VIEW_X
RDBI_AUTHID_VIEW_X
This table copies all rows from the base table RDBI.AUTHID_TABLE_X.

Table 37. Object Views: SYSIBM.SYSUSERAUTH
SYSIBM.SYSUSERAUTH

Table 38. Object Views: RDBI_AUTHID_TABLE_X
RDBI_AUTHID_TABLE_X
A base table listing primary and secondary authorization IDs. This table must be populated by the system or database administrator. QMF for Windows does not have access to the preexisting RACF/DB2 relationships during or after installation, unless they are stored in a custom DB2 table of the administrator's design.

For queries, forms, and procedures, the view Q.RAA_OBJECT_VIEW_X is the key and it interacts with the other views and tables, as shown below.

Note:
This view is for the Object Listing Option:
"Include only those objects that any of the user's primary or
secondary authorization IDs are authorized to access. This
option requires the most database resources when producing lists." 
Table 39. Create View Q.RAA_OBJECT_VIEW_X
CREATE VIEW Q.RAA_OBJECT_VIEW_X ( CATALOG_ID, OWNER, NAME, TYPE, SUBTYPE, OBJECTLEVEL, RESTRICTED, MODEL, REMARKS DELETED_BY_USER, DELETED_BY_SQLID, DELETED_TIMESTAMP ) AS SELECT A.CATALOG_ID, A.OWNER, A.NAME, A.TYPE, A.SUBTYPE, A.OBJECTLEVEL, A.RESTRICTED, A.MODEL, B.REMARKS, A.DELETED_BY_USER, A.DELETED_BY_SQLID, A.DELETED_TIMESTAMP FROM Q.RAA_OBJ_DIR_X A, Q.RAA_OBJ_REM_X B WHERE (A.CATALOG_ID = B.CATALOG_ID AND A.OWNER = B.OWNER AND A.NAME = B.NAME AND A.DELETED_TIMESTAMP IS NULL AND B.DELETED_TIMESTAMP IS NULL) AND (A.RESTRICTED = 'N' OR A.OWNER IN (USER, CURRENT SQLID) OR A.OWNER IN (SELECT C.SECONDARY_ID FROM RDBI_USER_AUTHID_VIEW_X C WHERE C.CATALOG_ID = A.CATALOG_ID) OR EXISTS (SELECT D.AUTHID FROM RDBI.USER_ADMIN_VIEW_X D WHERE D.CATALOG_ID = A.CATALOG_ID) )
Table 40. Create VIEW RDBI.USER_ADMIN_VIEW_X
CREATE VIEW RDBI.USER_ADMIN_VIEW_X ( CATALOG_ID, "AUTHID" ) AS SELECT B.ID, A."AUTHID" FROM RDBI.ADMIN_VIEW A., RDBI.CATALOG_DIR_X B WHERE A."AUTHID" IN (USER, CURRENT SQLID) OR A."AUTHID" IN (SELECT C.SECONDARY_ID FROM RDBI.USER_AUTHID_VIEW_X C WHERE B.ID = C.CATALOG_ID)
Table 41. Create View RDBI.USER_AUTHID_VIEW_X
CREATE VIEW RDBI.USER_AUTHID_VIEW_X ( CATALOG_ID, PRIMARY_ID, SECONDARY_ID ) AS SELECT A.CATALOG_ID, A.PRIMARY_ID, A.SECONDARY_ID FROM RDBI.AUTHID_VIEW_X A WHERE A.PRIMARY_ID = USER
Table 42. Create View RDBI.ADMIN_VIEW
CREATE VIEW RDBI.ADMIN_VIEW ( "AUTHID" ) AS SELECT A.GRANTEE FROM SYSIBM.SYSUSERAUTH A WHERE A.SYSADMAUTH IN ('Y', 'G')
Table 43. Create Table RDBI.CATALOG_DIR_X
CREATE TABLE RDBI.CATALOG_DIR_X ( ID SMALLINT NOT NULL NAME VARCHAR (128) NOT NULL ) IN RDBIDBX.RDBITSX1 CCSID EBCDIC
Table 44. Create View RDBI.AUTHID_VIEW_X
CREATE VIEW RDBI.AUTHID_VIEW_X ( CATALOG_ID, PRIMARY_ID, SECONDARY_ID ) AS SELECT A.CATALOG_ID, A.PRIMARY_ID, A.SECONDARY_ID FROM RDBI.AUTHID_TABLE_X A
Table 45. Create SYSIBM.SYSUSERAUTH
SYSIBM.SYSUSERAUTH
Table 46. Create Table RDBI.AUTHID_TABLE_X
CREATE TABLE RDBI.AUTHID_TABLE_X ( CATALOG_ID SMALL INIT NOT NULL, PRIMARY_ID CHAR (8) NOT NULL, SECONDARY_ID CHAR (8) NOT NULL ) IN RDBIDBX.RDBITSX CCSID EBCDIC
Note:
This table must be populated by the system or database administrator. QMF for Windows does not have access to the pre-existing RACF/DB2 relationships during or after installation.

For other tables, the view RDBI.TABLE_VIEW2_X is the key and interacts with the other views and tables, as shown below.

Table 47. Object Views for Tables

RDBI.TABLE_VIEW2_X constructs a list of tables. First, it checks for tables listed in the base table SYSIBM.SYSTABAUTH. All tables in SYSIBM.SYSTABAUTH have, by definition, already had some type of authority granted to at least one user. SYSIBM.SYSTABAUTH must answer "Yes" to one of the following questions:

  1. Is the GRANTEE = the current user?
  2. Is the GRANTEE = the current SQLID?
  3. Is the GRANTEE = PUBLIC?
  4. Is the GRANTEE = PUBLIC*?
  5. Is the GRANTEE = a secondary authorization ID of the current USER having the same catalog_ID?
  6. Is the user a SYSADM having the same catalog_ID?
  7. Is the user a SYSADM having the same catalog_ID?

The table must have one of the following GRANTEE types:

  • ' '
  • U
  • G

The GRANTEE of the table must have Y or G authority on one of the following four categories:

  • DELETEAUTH
  • INSERTAUTH
  • SELECTAUTH
  • UPDATEAUTH
Table 48. Authority to RDBI.USER_ADMIN_VIEW_X
RDBI.USER_ADMIN_VIEW_X

This view determines whether the user or a secondary user ID of the user is a SYSADM. It checks the RDBI.ADMIN_VIEW and RDBI.CATALOG_DIR_X for:

  • The current user ID
  • Secondary user ID of the current user having the same catalog ID
Table 49. Authority to RDBI.USER_AUTHID_VIEW_X
RDBI.USER_AUTHID_VIEW_X

This view retrieves all rows from the base table RDBI.AUTHID_VIEW_X where the primary ID is that of the current user. It will list all secondary authorization IDs for the current user.

Table 50. Authority to RDBI.ADMIN_VIEW
RDBI.ADMIN_VIEW

This view retrieves a list of GRANTEEs from the base table SYSIBM.SYSUSERAUTH. It takes GRANTEEs who have been granted G or Y for SYSADMAUTH.

Table 51. Authority to RDBI.AUTHID_VIEW_X
RDBI.AUTHID_VIEW_X

This view copies all rows from the base tableRDBI.AUTHID_TABLE_X.

Table 52. Tables and Views: SYSIBM.SYSUSERAUTH
SYSIBM.SYSUSERAUTH
Table 53. Authority to RDBI.AUTHID_VIEW_X
RDBI.AUTHID_TABLE_X

A base table listing catalogued, and primary and secondary authorization IDs.

Note:
This table must be populated by the system or database administrator. QMF for Windows does not have access to the pre-existing RACF/DB2 relationships during or after installation, unless they are stored in a custom DB2 table of the administrator's design.

Also, for tables, the view RDBI.TABLE_VIEW2_X is the key and interacts with the other views and tables, as shown below.

Table 54. Tables and Views: SYSIBM.SYSTABAUTH
SYSIBM.SYSTABAUTH
Table 55. Tables and Views: RDBI.USER_ADMIN_VIEW_X
RDBI.USER_ADMIN_VIEW_X
CREATE VIEW RDBI.USER_ADMIN_VIEW_X ( CATALOG_ID, "AUTHID" ) AS SELECT B.ID, A."AUTHID" FROM RDBI.ADMIN_VIEW A, RDBI.CATALOG_DIR_X B WHERE A."AUTHID" IN (USER, CURRENT SQLID) OR A."AUTHID" IN (SELECT C.SECONDARY_ID FROM RDBI.USER_AUTHID_VIEW_X C WHERE B.ID = C.CATALOG_ID)
Table 56. Tables and Views: RDBI.USER_AUTHID_VIEW_X
RDBI.USER_AUTHID_VIEW_X
CREATE VIEW RDBI.USER_AUTHID_VIEW_X ( CATALOG_ID, PRIMARY_ID, SECONDARY_ID ) AS SELECT A.CATALOG_ID, A.PRIMARY_ID, A.SECONDARY_ID FROM RDBI.AUTHID_VIEW_X A WHERE A.PRIMARY_ID = USER
Table 57. Tables and Views: RDBI.ADMIN_VIEW
RDBI.ADMIN_VIEW
CREATE VIEW RDBI.ADMIN_VIEW ( "AUTHID" ) AS SELECT A.GRANTEE FROM SYSIBM.SYSUSERAUTH A WHERE A.SYSADMAUTH IN('Y', 'G')
Table 58. Tables and Views: RDBI.AUTHID_VIEW_X
RDBI.AUTHID_VIEW_X
CREATE VIEW RDBI.AUTHID_VIEW_X ( CATALOG_ID, PRIMARY_ID, SECONDARY_ID ) AS SELECT A.CATALOG_ID, A.PRIMARY_ID, A.SECONDARY_ID FROM RDBI.AUTHID_TABLE_X A
Table 59. Tables and Views: SYSIBM.SYSUSERAUTH
SYSIBM.SYSUSERAUTH
Table 60. Tables and Views: RDBI.AUTHID_TABLE_X
RDBI.AUTHID_TABLE_X
CREATE TABLE RDBI.AUTHID_TABLE_X ( CATALOG_ID SMALLINT NOT NULL, PRIMARY_ID CHAR (8) NOT NULL, SECONDARY_ID CHAR (8) NOT NULL ) IN RDBIDBX.RDBITSX2 CCSID EBCDIC
Note:
This table must be populated by the system or the database administrator. QMF for Windows does not have access to the pre-existing RACF/DB2 relationships during or after installation.


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004