IBM Query Management Facility (QMF)

QMF for TSO/CICS: QMF Administrator Authority, those pesky -551’s and a FIX!!!

By Robin Zalud posted Thu December 17, 2020 02:28 PM

  

Jan left the company. She had the greatest QMF query she would run that produced the project report used each week in the department meeting. You have access to all the tables used in the query. You know the name of the QMF query (JAN.SUPER_QUERY) but you can’t see it, because Jan did not save it with the SHARE=YES option.  When you try to display Jan’s query, you get QMF error message DSQ20355 ‘You are not authorized to use JAN.SUPER_QUERY.’

 

What a bummer! Jan had excellent SQL skills and did some tricks you wish you could remember. How can QMF help with this situation?

 

QMF Administrator Authority to the rescue!

QMF Administrator Authority allows users to execute the DISPLAY, SAVE, ERASE, IMPORT and EXPORT commands on QMF objects (queries, forms, procedures and analytics) that are owned by other users. The QMF objects do not have to be shared with other users, in other words, those objects do not need to be saved with the SHARE=YES parameter.  While QMF Administrator authority allows QMF users to DISPLAY, SAVE, ERASE, IMPORT and EXPORT other users non-shared objects, it does not and cannot override any Db2 authorities to access data from the objects. In other words, if I do not have the proper Db2 authority to see any of the tables in JAN.SUPER_QUERY, then while I can DISPLAY the query and look at it, I will not be able to run the query.  Your company data continues to be safe.

 

How can I tell if I am a QMF Administrator?

 

When a user is determined to be a QMF Administrator, the QMF state global variable DSQAO_QMFADM is set to a value of ‘1’. From the SHOW GLOBALS command, a user considered to be a QMF Administrator would see:

Back to those pesky -551’s…

 

Although the QMF Administrator authority feature is quite popular with our QMF users, it has come with some problems for a segment of our QMF clientele.

 

QMF Administrator authority determination comes during initialization of QMF. During the time period from which you invoke your CLIST, EXEC or choose that QMF option off an ISPF panel to the time you actually see the QMF Home Panel is when the magic happens. QMF attempts to PREPARE (but not EXECUTE) an INSERT and then a DELETE into the Q.PROFILES catalog table. If your userid has at least one of those privileges, then you are granted QMF Administrator authority and your DSQAO_QMFADM global variable is set to the value ‘1’.

 

If your userid does NOT have INSERT or DELETE privilege to Q.PROFILES, QMF receives an SQL Code -551 (authority error) from Db2. QMF does not externalize this SQL code -551 error in any way. The user is just determined to not have QMF Administrator authority and that user’s DSQAO_QMFADM global variable is set to ‘0’. This user does not have the benefits of QMF Administrator authority.

The problem:

 

Oh, that SQL code -551 from Q.PROFILES. Unfortunately, the SQL code -551 has shown up on some of our client’s internal security reports. For our Db2 external security users, their end users see the ICH408I error messages.

The current ways we have seen to address this issue are one or a combination of the following:

  • Create internal exceptions for the Q.PROFILES SQL code -551
  • Turn off profile messages
  • Grant users INSERT authority on Q.PROFILES
  • Turn off QMF Administrator authority checking through the DSQUOPTS exit routine

Other users would like to have QMF Administrator authority but are not allowed to have the INSERT or DELETE privilege to Q.PROFILES.

 

For this segment of our QMF clientele we introduce…the FIX:

 

 

Some of our QMF users requested that we make QMF Administrator authority determination an option in a user’s QMF profile.  Well that works for us!  Take a look at QMF for TSO/CICS 12.1 APAR PH31508. 

 

After applying the fix for PH31508, preference for QMF Administrator authority can be set for users via their QMF profile. QMF profiles reside in the QMF catalog table Q.PROFILES. When the user’s profile column MODEL contains the characters ‘A2’ anywhere in the column, a user automatically has QMF Administrator authority.  QMF will bypass Q.PROFILES INSERT and DELETE checking. DSQUOPTS will be bypassed. No more SQL code -551 problems.

 

Users wishing to completely suppress QMF Administrator authority and the checking itself, can update Q.PROFILES.MODEL column to have the value A0. 

 

For more details, see the APAR text and the updated QMF publications.

 

As always, if you have any questions or comments, comment on this post or send email to Robin Zalud at rzalud@rocketsoftware.com.

0 comments
10 views

Permalink