Re: Column authority in STRQMQRY

jb wrote:
My boss is running some qm queries written by another user but recreated by him.

FWiW, it is of no consequence who owns or created a *QMQRY with regard to ability to access a table referenced by the query, since there is no adopted authority feature for that object type except as established by a program object from which the query is invoked. Any user may have restrictions as imposed by the QMQRY profile however.

There are a number of these which create intermediate tables most
likely by STRQMQRY *OUTFILE. The one causing bother runs OK when
he runs it, but when running under within the robot scheduler or
if he runs up to the point of failure as himself then submits the
query running under that profile the sql bombs out. It's just a
simple select with a couple of joins. Heres the error message:
SQL0551 Not authorised to object PMPRDC in SPQ014P type * X90SG.

The object is a column name, SPQ014P is a DDS described PF, Following the * are a pair of reverse imaged blanks & X90SG is the start of the library name.

Concentrating on the message replacement text is probably not worthwhile, as it is probably as suspected, that the SQL is setting improper or more likely simply _not setting_ the object type replacement variable, so its result is /garbage/; i.e. what is in memory, not an issue with character conversions. The library name being in memory near associated with the message is likely. So that is presumably a defect, while not germane. A review of the joblog might show a preceding message, and if not one might appear in a TRCJOB of the failure, to track down the condition & message from which the SQL might extract its details for issuing its own message.

The users are nearly the same except the robot user is lacking
*SPLCTL. The users have *ALL authority over the files.

Not to imply relevance in this specific case, but be aware of the QM profile as alluded earlier. Access is via STRQM, to another user however, only as accessed by an /administrator/ level profile.

It looks like it's all gone to pot & substituted the wrong
details into the message or more likely the right details into
the wrong message. He tells me that changing something around
makes a different field name come out in the message. We have
looked & the CCSIDs seem OK.

Does anyone have any ideas?

If there might be column authorities which are [in]correctly preventing the access to the file, review for column authorities. There is a parameter on DSPOBJAUT to enable requesting inclusion of the column authority details for database *FILE objects. With user private authorities assigned to columns accessed by the SQL request, the users would need to have the same private authority to the column(s) to operate the same.

It seems odd however, that the job would not just be run with the expected user; i.e. why would a job be assigned to run with the generic ROBOT user when in fact the work is being performed on behalf of a specific user.?

Regards, Chuck
.

Relevant Pages

  • Re: Access: Protect data in tables from copy and export...
    ... Say that user NO has no direct access to table T, ... User NO will not be able to run this query, under any circumstances, afaik: ... If you add WITH OWNERACCESS OPTION, ... OPTION query, he is delegating his read authority to table T, to owner NO, ...
    (microsoft.public.access.forms)
  • Re: Access: Protect data in tables from copy and export...
    ... query owned by someone else. ... > If you add WITH OWNERACCESS OPTION, ... > OPTION query, he is delegating his read authority to table T, to owner NO, ... >> had a user with only read/write permissions able to create a query and ...
    (microsoft.public.access.forms)
  • Finding Recently Logged on Users
    ... I can query on event log ID 528 in the security log, ... but I want to exclude the nt authority \ network service, ... to query and exclude the other user ID's. ...
    (microsoft.public.windows.server.scripting)
  • Re: Dynamic WHERE statement or criteria
    ... When you have a parameter in a query, SET the .PARAMETER object variable before opening the RecordSet. ... The field name is 'Authority Type', ... Dim stDocName As String ... MsgBox thisAuthority ...
    (microsoft.public.access.modulesdaovba)
  • Re: Adopted authority together with Dynamic SQL
    ... Without actually reviewing where the data comes from and how it is used in each dynamically formed SQL statement, there is little assurance that none of those uses could possibly effect a negative outcome [due to having adopted the authority when that statement runs]. ... Overlooking the case of the user possibly having more authority than required irrespective of any known requirements, I can think of no technical problems with defaulting to compile all programs with adoption. ...
    (comp.sys.ibm.as400.misc)