Interesting. It is currently set to *SYS and I will assume *NAMING since I didn't override this (not finding the value in the DSPPGM results). It is not running a dynamic SQL.
While this makes sense based on the help text, I have to question it. If create a program with the same parameters
not dynamic SQL,
*SYS,
*NAMING,
owned by *SECOFR profile
adopts authority
and this program selects from a PF that I do not have access to, the data is retrieved.
Shouldn't it work the same way with a PF and with qsys2.object_privileges?
Also, I have done dynamic SQL select statements reading from a PF with the same parameters (DYNUSRPRF = *USER) and the program adopt worked for that also.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mark Waterbury
Sent: Friday, April 5, 2024 9:02 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: No entry for *PUBLIC on library
Hi, John,
I suspect you may be using "embedded SQL" in a program that adopts authority?
Prompt the CRTSQLRPGI command, press F10=Additional parameters, and page down until you see:
User profile . . . . . . . . . . *NAMING *NAMING, *USER, *OWNER Dynamic user profile . . . . . . *USER *USER, *OWNER
Put the cursor in each field, and press F1=Help, to read about each.
User profile (USRPRF) - Help
Specifies the user profile that is used when the compiled program object and SQL package object is run, including the authority that the program object or SQL package has for each object in static SQL statements. The profile of either the owner or the user is used to control access to objects.
*NAMING
The user profile is determined by the naming convention. If the
naming convention is *SQL, USRPRF(*OWNER) is used. If the naming
convention is *SYS, USRPRF(*USER) is used.
*USER
The profile of the user running the program or SQL package is used.
*OWNER
The user profiles of both the owner and the user are used when the
program or SQL package is run.
Dynamic user profile (DYNUSRPRF) - Help
Specifies the user profile used for dynamic SQL statements.
*USER
Local dynamic SQL statements are run under the profile of the
program's user. Distributed dynamic SQL statements are run under
the profile of the application server job.
*OWNER
Local dynamic SQL statements are run under the profile of the
program's owner. Distributed dynamic SQL statements are run under
the profile of the SQL package's owner.
Note how they tend to overlap somewhat and can interact in curious ways.
Hope that helps,
Mark S. Waterbury
On Friday, April 5, 2024 at 08:27:01 AM EDT, smith5646midrange@xxxxxxxxx <smith5646midrange@xxxxxxxxx> wrote:
Now I'm more confused.
I am running a program that is owned by a *SECOFR profile and adopts its authority. I have checked both parts of that statement and they are correct.
It is doing an SQL "select * from qsys2.object_privileges". When it runs, it returns only my group profile. Does this SQL select statement not utilize the adopted authority?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
