Tom, Excellent points. Especially about how the activity could be masked or manipulated. Full database journaling or triggers on all my files are just not feasible at this point. As with most control-related issues, something is better than nothing... even though a savvy user (technical individual) could perform fraudulent activity. And certainly, I am not suggesting that there aren't other tools/issues besides STRSQL and DFU. We have done our due diligence to protect the PC client activities, segregate duties, programmer access, etc... I am really trying to come in with an excellent IT controls effort for SOX. Chuck -----Original Message----- From: security400-bounces@xxxxxxxxxxxx [mailto:security400-bounces@xxxxxxxxxxxx]On Behalf Of Tom Liotta Sent: Tuesday, November 23, 2004 3:57 PM To: Security Administration on the AS400 / iSeries Subject: RE: [Security400] 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 _______________________________________________ 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.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.