RE: SQL and IT Controls

Chuck:

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:

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

and

  ovrdbf  file  tofile(production/prodfile) ovrscope(*job)
  STRSQL
    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:

>Group:
>
>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 appreciated!
>
>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,
>visit: http://lists.midrange.com/mailman/listinfo/security400
>or email: Security400-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/security400.
>
>


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


__________________________________________________________________
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

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 http://channels.netscape.com/ns/search/install.jsp