RE: Given a list of objects, run SQL Queries on it

I would think the better question would be, "Who is not a limited user"? No limited user should have access to STRDFU. The same logic should apply to any object the auditors are interested in--i.e., The user may have access to a table, but they have to use a program to get to the data. The program should control whether or not they can update the data. And if they can update it, the program should validate that the update is a valid thing to do.

It gives me the shudders to think about standard users with access to STRDFU or other methods to update data from a command line.

Just how familiar are your auditors with IBM i?

Thanks,
Michael Quigley
Computer Services
www.TheWay.org

-----Original Message-----
message: 1
date: Tue, 25 Nov 2025 20:28:22 +0000 (UTC)
from: cesco via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
subject: Re: Given a list of objects, run SQL Queries on it

The equivalent is querying?QSYS2.OBJECT_PRIVILEGES .I personally use for
this things interactive SQLWorkbench/J (it's opensource). It is still java, it's
jdbc based, and has some smarts for the ibmi (it uses native functions to i.e.
retrive table source, you can see the job number servicing your conn...) and
you can use it? then for many other sql databases. BLOB handling is also
much better than ACS (you can see right there the image preview, the hex,
text, save all the blobs to files...).
Compared to ACS it allows for easy export of the result set on an excel (or
same excel but different sheet), with just a command before the SQL select
(@wbexport), so basically you run the macro saved in the tool, and it
generates the excel (besides the result set on video), so basically with a click
you can have an "audit package".
my 2c

On Tuesday, November 25, 2025 at 04:50:21 PM GMT+1, Jim Oberholtzer
<midrangel@xxxxxxxxxxxxxxxxx> wrote:

It?s audit season, and auditors being auditors want quite a bit of almost
useless information from a long list of objects, IE: DSPOBJAUT on command
STRDFU.? A) it?s already locked out from all users (where it even exists)
except a very small list of *ALLOBJ users, B) I removed it from the production
systems.? But the auditors still want to see a null data set.?

So,? what I would like to build? is an SQL based procedure that will run all the
various reports they want.? The reports would be separate Excel
spreadsheets for each object.?

Read list of objects
Run a series of SQL commands to get data (IE DSPOBJAUT, sql version not the
command) Create the spreadsheet Loop back to the list until EOF.

My limited SQL abilities stop me from doing this in SQL.? I could do it in CLLE
but the auditors would reject it since they cannot spell IBM i without being
spotted the ?I?.? The can read SQL.

Suggestions?


--
Jim Oberholtzer
Agile Technology Architects