Re: Service program and adopting authority

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.