So long as there's a CLP wrappering STRSQL then perhaps we could also turn
on journaling when needed on the fly.

One could use the V5R3 Open Database File exit point to have an exit
program receive control for all full file opens on the system.  If the user
is not QSECOFR return immediately (indicating the open is OK).  If QSECOFR
examine to see what files are being opened and if they are not currently
journaled start journaling, send a message with the file name and job name
to some *DTAQ, and return indicating the open is OK.  When the SQL session
is ended and you ENDDBMON also receive the messages from the *DTAQ for the
current job and end journaling for those files.

This approach appeared to work in a very quick test though it does add
additional overhead to each open...

             net (Tom Liotta)                                              
             Sent by:                                                   To 
             security400-bounc         security400@xxxxxxxxxxxx (Security  
             es@xxxxxxxxxxxx           Administration on the AS400 /       
             11/23/2004 02:56                                              
             PM                                                    Subject 
                                       RE: [Security400] SQL and IT        
             Please respond to                                             
             Administration on                                             
                the AS400 /                                                


As has been pointed out, STRSQL and related functions can be tricky to
monitor. Network access is an entirely different matter and can be much
easier to control, although there are even some significant issues at a
couple points for that.

For immediate use, STRSQL can be wrapped in a CLP with STRDBMON/ENDDBMON
commands if you must log SQL statements from those sessions. *SUMMARY is
the way to start and you'd perhaps want to log into a work file that is
immediately massaged to create a permanent log. The 'permanent log' could
be updated via copying relevant record data from the DB monitor output into
QAUDJRN user entries which would be about as secure as any home-grown
function will get. (There's a LOT more data in the DB monitor output than
you'll need. You'll want to get familiar with it.)

However, it seems to me that the real problem is that this still doesn't
provide a log of what actually happened to the data. It only shows that a
particular SQL statement was executed.

Consider the difference between:

    update file set field = 'A' where field = 'a'


  ovrdbf  file  tofile(production/prodfile) ovrscope(*job)
    update file set field = 'A' where field = 'a'
  dltovr  *all  lvl(*job)

Handling the actual SQL function that takes effect is a bit trickier than
simply logging the statement itself. Somewhat fortunately, the DB monitor
isn't fooled; but it takes some logic to pull all the relevant data
together. Especially when subselects, joins, etc., get mixed in together
with overrides or aliases or stored procedure, etc.

And you _still_ don't have a record of what data was actually changed. In
order to know what really happened, you'd need to combine all STRSQL
sessions that might be going on concurrently and you'd need to reconstruct
a trail of consecutive sessions. E.g., in session #1, a record was updated
to change 'A' to 'B'. In session #2, the records with 'B' were updated to
decrease a price field. Then back in session #1, the single record was
changed from 'B' back to 'A'. An audit could be tough. And this doesn't
even begin to account for what was also going on via ODBC, DRDA, RPG... at
the same time.

Without database journaling, you just don't know what happened.

Tom Liotta

"Chuck Bower" <cbower@xxxxxxxxxxxx> wrote:

>We are attempting to wrestle with a final Sarbanes Oxley issue relating to
SQL, specifically using STRSQL via the command line.  While STRSQL is
restricted to those who have a business need to use it, our problem relates
to the potential for data manipulation and changes that are not logged.
Our development staff can use it (but not update production data) but our
security officers can of course use it, AND update production data.  It is
NOT practical for us to journal all of our production data libraries.
While I do have STRSQL command usage logged via the OS/400 audit journal,
(we know who uses it and when) it does not provide the log of activity
within the STRSQL session.
>Are there any tools on the market that can log the activity within the
STRSQL session?   Any feedback you can provide would be GREATLY
>Chuck Bower
>VP of IS
>Coachmen Technology Services, Inc.
>This is the Security Administration on the AS400 / iSeries (Security400)
mailing list
>To post a message email: Security400@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>or email: Security400-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives

Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904

Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at
This is the Security Administration on the AS400 / iSeries (Security400)
mailing list
To post a message email: Security400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: Security400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.