|
midrange.com
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...
qsrvbas@netscape.
net (Tom Liotta)
Sent by: To
security400-bounc security400@xxxxxxxxxxxx (Security
es@xxxxxxxxxxxx Administration on the AS400 /
iSeries)
cc
11/23/2004 02:56
PM Subject
RE: [Security400] SQL and IT
Controls
Please respond to
Security
Administration on
the AS400 /
iSeries
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
_______________________________________________
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com 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.
