Theoretical question - updating a file from an ODBC connection - "best way"

[MelissaG]

This is theoretical. Right or wrong, we have business processes that are being run from Access databases. I have a business analyst who is trying to figure out how to update one field in an ISeries file after printing labels. He needs it in real time (or close to real time), it has to be within the confines of our security (SOX-based) -meaning that the users can't have update access to the file via the Access Database. Let's see, what else? We're discussing field level security, but the security administrator looked like a deer in the headlights when we mentioned it. We've also discussed having a stored procedure on the iSeries (which would have to submit a job under a different ID , I guess) or a function or using a trigger or "never ending" job.

So, how would you do this? How would you update fields from Access and still keep your files secure? Does anyone have any thoughts (besides "don't let the users use Access" - that ship sailed a long time ago).

Thanks! Have a good one.

[wegrace]

Melissa,

The System Of Record should always control how its tables are updated.

So, create a stored procedure for this functionality on the as400 that accepts a parameter of a unique key to the table you wish to update.

Compile the stored procedure using sql compile options; DynUsrPrf=*OWNER ,UsrPrf=*OWNER, so that the compiled object is owned by a User id with sufficient authority to update the table.

Make sure the stored procedure's Adopt Authority value is *YES.

Control access to the new stored procedure using User Groups or Authorization Lists (preferred).

Then just call the stored procedure from MS Access.

This is what I've done in the past, and has worked well.

As for field level security, I don't know of a good way.

Walt

[EmmanuelW1]

Walt wrote: "Make sure the stored procedure's Adopt Authority value is *YES."

That setting doesn't matter - try it sometime - run a program with USRPRF(*OWNER) and USEADPAUT(*NO)...the program will still run with its owner's authority.

USEADPAUT controls the transfer of adopted authority to a program from another program that has adopted authority via USRPRF(*OWNER).

Cheers,

Emmanuel

[jtaylor___]

Coming from Access, I assume you're linking directly to your Db2 tables, so I don't see a stored procedure as an option. You can use column level security or ODBC exit points.

[wegrace]

Walt wrote: "Make sure the stored procedure's Adopt Authority value is *YES."

That setting doesn't matter - try it sometime - run a program with USRPRF(*OWNER) and USEADPAUT(*NO)...the program will still run with its owner's authority.

USEADPAUT controls the transfer of adopted authority to a program from another program that has adopted authority via USRPRF(*OWNER).

Cheers,

Emmanuel

When did this change?

[EmmanuelW1]

Walt wrote: "When did this change?"

As far as I know, it has always been this way.

The misconception has existed just as long though. :-)

Cheers,

Emmanuel

[wegrace]

LOL @ misconception.

It has *not* 'always been this way', as I've dealt with this issue on numerous occasions over the last 25 years.

Walt

[john.sev99]

I agree with Emmanuel. People see the "use adopted authority" and assume it needs to be *YES for adopted authority to work. But as far as I'm aware, this has never been the case.
Authority is a cumulative thing. The system will keep adding authority from the profile, groups, authorisation lists etc etc until it has sufficient authority to the object. My suspicion is in the past, setting USEADPAUT to *YES has caused this cumulative effect to give sufficient authority rather than being required.

[Peder Udesen]

Just a thought.
How about turning the proces the other way. You mention a Never Ending Program ( NEP ) or job.
Assume that a status is updated in the Access Database that also should be updated in a file in the iSeries.
Then if you can have access to the Access Database from the iSeries, you can read the status of the updated record and then update the
database via the Never Ending Program.

Scott Klement once created a service program to simplify the use of JDBC drivers from ILE RPG.

Regards
Peder

[MelissaG]

Thank you, all.

My stored procedure is currently compiled with UsrPrf=*OWNER but not DynUsrPrf=*OWNER - so I'll fix that for the one we are currently working on. I'll also definitely search for Scott Klement's information about JDBC drivers and ILE RPG.

Sending virtual chocolate chip cookies to all!
-Melissa

[wegrace]

Walt wrote: "Make sure the stored procedure's Adopt Authority value is *YES."

That setting doesn't matter - try it sometime - run a program with USRPRF(*OWNER) and USEADPAUT(*NO)...the program will still run with its owner's authority.

USEADPAUT controls the transfer of adopted authority to a program from another program that has adopted authority via USRPRF(*OWNER).

Cheers,

Emmanuel

Emmanuel,

I finally had a chance to go and review this, and you are absolutely correct.

Oh, how my mind has begun to betray me as I get older.

Thank you for the clarification.

Walt