Re: QUSER not authorized when calling sp

[Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>]

Then that is most likely picking up the profile that started the prestart
job, not the user that the job switches to.

I know others have suggested checking the various security bits that
control DDM and the like.  Can you set up a DDM over say a source file and
see if that works?  If not I would get that to work then try your code
again.

--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On Mon, Mar 21, 2022 at 8:41 AM Robert Rogerson <rogersonra@xxxxxxxxx>
wrote:

> Jim, I don't specify the user QUSER anywhere in the job on production or on
> the development machine.  The user that calls the stored procedure on the
> production machine is a valid user  (which we use for many batch jobs).  I
> found the QUSER profile while trying to debug the stored procedure on the
> development machine in the PSDS of the external program for the stored
> procedure.
>
> Thanks,
>
> Rob
>
> On Sun, Mar 20, 2022 at 6:51 AM Jim Oberholtzer <
> midrangel@xxxxxxxxxxxxxxxxx>
> wrote:
>
> > Another question is why QUSER?   I would consider creating a profile that
> > would be used for this type of cross platform communications.   Using IBM
> > supplied user profiles for things like this are usually discouraged.
> >
> > Jim Oberholtzer
> > Agile Technology Architects
> >
> >
> >
> > > On Mar 20, 2022, at 5:03 AM, Jack Kingsley <iseriesflorida@xxxxxxxxx>
> > wrote:
> > > What are you using to do this DDM??  Also, what state is QUSER on the
> > dev
> > > box.
> > >
> > > > On Sat, Mar 19, 2022 at 9:37 PM Robert Rogerson <rogersonra@xxxxxxxxx
> >
> > > > wrote:
> > > >
> > > > Ok, I'm stumped.  I'm getting an an sqlstate=08004, sqlcode= -30060
> > User is
> > > > not authorized to relational database when calling a stored procedure
> > on a
> > > > remote IBM i.
> > > >
> > > > Here's the scenario.  Both IBM i systems are at V7R3M0 and were
> brought
> > up
> > > > to date on PTFs last week.
> > > > I have an external sp on the DEV box (mylib.INSERT_REC) and it's
> failing
> > > > when running an INSERT statement
> > > > exec sql INSERT INTO mylib.tableA
> > > >               SELECT * FROM prod.lib.table WHERE ....
> > > >
> > > > If I call the sp on the DEV machine it works perfectly.  I print to
> > qprint
> > > > after the sql runs and  it prints
> > > >
> > > > In iACS Run Sql Scripts
> > > > CALL INSERT_REC;
> > > >
> > > > Produces output:
> > > > PREPSTMT: INSERT INTO mylib.tableA
> > > >               SELECT * FROM prod.lib.table WHERE ...
> > > > SQLSTATE: 00000
> > > > SQLCODE: 0
> > > > SQLERROR: 1 rows inserted in TABLEA in mylib
> > > > User: QUSER (from psds)
> > > >
> > > > If I call the sp on the PROD machine it fails. When calling on the
> PROD
> > > > machine it is called in an RPGLE program using three part naming
> > > > Exec sql CALL dev.mylib.INSERT_REC;
> > > >
> > > > Produces output
> > > > PREPSTMT: INSERT INTO mylib.tableA
> > > >               SELECT * FROM prod.lib.table WHERE ...
> > > > SQLSTATE: 08004
> > > > SQLCODE: -30060
> > > > SQLERROR: User is not authorized to relational database.
> > > > User: QUSER (from psds)
> > > >
> > > > I've spent all day trying to figure this out but haven't had any luck.
> > > > Does anyone have an idea what I am doing wrong or don't understand?
> > > >
> > > > Thanks for all thoughts...
> > > >
> > > > Rob
> > > > --