RE: Service program and adopting authority -- MIDRANGE-L

I was on vacation so I stepped away from this for a while.

I did some further testing and swapped the EXEC SQL EXECUTE IMMEDIATE :sqlString for a system() function. The program works as expected with the system() function. It adopts the calling program's authority. It does not adopt with the EXEC SQL EXECUTE IMMEDIATE.

So, it looks like I am back to using copybooks or maybe modules but I'm not able to use a service program for this.

-----Original Message-----
From: smith5646midrange@xxxxxxxxx <smith5646midrange@xxxxxxxxx>
Sent: Wednesday, April 22, 2026 9:44 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Service program and adopting authority

This is what I was finding and I was hoping that I was just overlooking something. I guess will abandon the service program approach and go back including copybooks for the code.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Monday, April 20, 2026 12:14 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Service program and adopting authority

Yes, but that's not the same as USEADPAUT...

Charles

On Sun, Apr 19, 2026 at 2:30 PM Mark Waterbury via MIDRANGE-L < midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi, Charles,

To clarify further, CRTSQLRPGI does support a USRPRF parameter. Here
is the help text for that:

User profile (USRPRF) - Help

Specifies the user profile that is used when the compiled program
objec and SQL package object is run, including the authority that the
program object or SQL package has for each object in static SQL
statements. Th 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.

*NAMING is the default. Note carefully the last sentence under "*NAMING"
above.

This can easily cause confusion if not set up correctly and run with
the *SYS naming convention..

Hope that helps,

Mark S. Waterbury

On Sunday, April 19, 2026 at 03:47:14 PM EDT, Charles Wilt <
charles.wilt@xxxxxxxxx> wrote:

I don't believe that SQL supports a USEADPAUT parm like regular
programs do, so they can't make use of authority adopted previously in
the call stack.

SQL Statements run either under the authority of the current user or
the program's owner.

I'm wondering if swapping profiles in the program that adopts QSECOFR
might work.

Charles

On Sat, Apr 18, 2026 at 2:12 PM <smith5646midrange@xxxxxxxxx> wrote:

Apologies but what am I setting in the options?

I do not want the service program to adopt its owner authority. I
want

it

to adopt the calling program's authority. If a program (not the
service
program) is owned by QSECOFR and adopts its authority, when that
program calls a function in the service program, that function
should run as SECOFR. However, if the calling program does not
adopt QSECOFR

authority,

when that program calls a function in the service program, it will
run as the user.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf
Of Niels Liisberg
Sent: Saturday, April 18, 2026 7:31 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx

Subject: Re: Service program and adopting authority

Look at the exec sql set option… i think you can change it there.

I have a similar sql stored procedure that does the same, there I
use the set option…

fre. 17. apr. 2026 kl. 20.07 skrev <smith5646midrange@xxxxxxxxx>:

I have a service program with a lot of miscellaneous functions.
One of these functions is an "ExecuteCommand" function using SQL.
It uses SQL so it can reach out to other systems to execute commands.

From SYSTEMA, it can do a CALL PGMA on SYSTEMB using the following

logic.

EXEC SQL CONNECT TO SYSTEMB;

ExecuteCommand('CALL PGMA');

ExecuteCommand does

Build sqlString which then looks like this.CALL
QSYS2/QCMDEXC('CALL PGMA');

EXEC SQL EXECUTE IMMEDIATE :sqlString;

Return sqlcode = 0;

I have an RPGLE program (not SQLRPGLE) that is owned by a *SECOFR
profile and adopts *OWNER authority. When it calls the
ExecuteCommand function in the service program, it appears that it
is not adopting any authority from the calling program.

I do not want the service program to adopt *OWNER authority
because then any program could call ExecuteCommand and do major
damage if they want.ExecuteCommand('DLTLIB ProdLib').

Is there a way to have the service program adopt the authority of
the calling RPGLE program (Use adopted authority = *YES already)
when it does the ExecuteCommand? Is my problem because of the
service program or the SQL inside of it?

--
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.

--
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.

--
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.

--
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.

--
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.

--
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.