RE: Detect controls for program changes -- MIDRANGE-L

Spoke too soon.  I can see the date of the access plan change, but it's not
gonna help.  Some access plan last save date/time occurrences will update
the change date/time of the program object, but some apparently will not.
                                                                        
I already have a case where a program shows a ghost change, but the
PRTSQLINF shows the access plan changed long after the fact.  In one
instance PRTSQLINF for a program shows a SQL4021 (access plan last saved)
message from 1/28, but the change date on the program object is 1/12.
                                                                        
I would have to capture the PRTSQLINF at the time of the object change,
otherwise the access plan change date/time could be overlayed by another
program call and access plan change -- one that does not change the object.

Thanks anyway.

-Jim

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jim Damato
Sent: Friday, January 28, 2005 2:14 PM
To: 'midrange-l@xxxxxxxxxxxx'
Subject: Re: Detect controls for program changes


I'm not at my desk to see for myself, but I've got someone working with this
problem on our system.  He says PRTSQLINF doesn't show a date.

Are we missing something?


--------------------------
Sent from me, on my BlackBerry Handheld Wireless


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx <midrange-l-bounces@xxxxxxxxxxxx>
To: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx>
Sent: Fri Jan 28 13:47:49 2005
Subject: Re: Detect controls for program changes

Actually, this may be the best work-around yet.  If our detect control
report finds a change in an RPGLE SQL program we should be able to
programmatically capture the PRTSQLINF, possibly note it in the report, and
preserve it as change documentation for the audit.

Thank you!

Jim
--------------------------
Sent from me, on my BlackBerry Handheld Wireless


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx <midrange-l-bounces@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Fri Jan 28 13:30:31 2005
Subject: RE: Detect controls for program changes

FWIW, if you run the PRTSQLINF command on the program it will tell you the 
date/time the access plan was last updated, and this should match the new 
change date/time exactly -- so that is a bit of an auditing comfort.  Of 
course that being said, it doesn't mean someone didn't make some other 
nasty change to the program prior to the access plan being updated.

Hopefully, one of you will have some success with a DCR.

Mark



midrange-l-bounces+markp=softlanding.com@xxxxxxxxxxxx wrote on 01/28/2005 
02:24:23 PM:

> Jim,
> 
> I think your bullets point out most of my thoughts.
> 
> Here we have our programs in a library separate from the data.  The 
users 
> only have *use authority to the library containing programs.  I created 
a 
> simple SQLRPGLE program.  Verified that the user couldn't change the 
> program with CHGPGM, etc.  Ran the program and the changed date and time 

> did change.  Therefore:
> - Overhauling security doesn't matter.  It will change it anyway.
> - The system is making the change, somehow.  The user does not need 
> authority.
> - Since security is overridden you are not damning your programs to less 

> than optimal SQL.
> - Your auditing is broken.
> 
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> 
> 
> 
> 
> Jim Damato <jdamato@xxxxxxxxxxxxxxxxx> 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 01/28/2005 01:47 PM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> 
> To
> "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
> cc
> 
> Subject
> RE: Detect controls for program changes
> 
> 
> 
> 
> 
> 
> IBM support suggested that I submit a DCR, and maybe I will.  My 
> experience
> with DCR's has been "if we won't admit it's a bug we probably won't 
think
> its necessary to change the design either".  Admitting you have a 
problem 
> is
> the first step toward recovery -- If they'd develop a PTF we could fix 
our
> detect report quickly.  Even if IBM accepts the DCR, credibility in
> management and our audit dept. will be completely shot before we get
> resolution.
> 
> IBM did say:
> 
> "The update to the access plan can possibly be prevented by 
> ensuring the object is always locked or ensuring users have only *USE to
> the program while at the same time ensuring no users with *ALLOBJ spcaut
> or any sufficiently adopting programs call the program."
> 
> I liked the idea about locking the object.  I'll get right to work 
> figuring
> out a way to maintain locks on all production programs.
> 
> The problems with ensuring users only have *USE access are:
> 
> -I'd have to completely overhaul security
> -That security method is not supported by the product environment
> -If IBM is saying that the system is making the change to the program 
> howcum
> the user needs authority?
> -If I use one of their work-arounds to block the system from making the
> change, aren't I damning my applications to less-than-optimal SQL?
> 
> The sad thing is that our methodology for auditing changes works for 
VMS,
> Unix, and Windows.  The AS/400 is the one system where we can't meet SOX
> compliance for change control.  And Rochester apparently is comfortable 
> with
> that.  For our company this becomes one more justification for folks to 
> say
> that the AS/400 is the old legacy system that can't keep pace with the
> other, better platforms, and that it doesn't support open methods.
> 
> 
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
> Sent: Friday, January 28, 2005 11:39 AM
> To: Midrange Systems Technical Discussion
> Subject: Re: Detect controls for program changes
> 
> 
> I wonder, if the user running the program does not have access to change 

> the program, does it still modify the program anyway with the new access 

> path information?  Of so, how?  Perhaps you could submit that as a 
> security breach?  Could that same method be used to patch other areas of 

> the program object?
> 
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> 
> 
> 
> 
> rob@xxxxxxxxx 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 01/28/2005 12:31 PM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> 
> To
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> cc
> 
> Subject
> Re: Detect controls for program changes
> 
> 
> 
> 
> 
> 
> I hear you.  Our change management software always reports "This object 
> has been changed outside of Turnover..." everytime we promote a SQLRPGLE 

> program.  Granted we don't do SOX (Chapter S corporation).  Perhaps, 
with 
> the advent of the SOX hassle you should submit a DCR to come up with a 
way 
> 
> 
> to determine if the change was strictly SQL access path related or what.
> 
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
> 
> 
> 
> 
> 
> Jim Damato <jdamato@xxxxxxxxxxxxxxxxx> 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 01/28/2005 11:41 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> 
> To
> "'MIDRANGE-L@xxxxxxxxxxxx'" <MIDRANGE-L@xxxxxxxxxxxx>
> cc
> 
> Subject
> Detect controls for program changes
> 
> 
> 
> 
> 
> 
> Is anyone else running detect control reports for audit compliance or 
SOX?
> We're running a daily report to detect changed program objects (among 
> other
> things), so that the previous day's changes may be validated against our
> change control documentation.  (Five years ago I couldn't imagine that 
I'd
> ever have to do this)  The report is driven off of objects' change date 
> and
> time from DSPOBJD to outfile.
> 
> We're getting "ghost" changes periodically -- unaccounted program 
changes.
> When we started cross-referencing the report against the object audit
> journal the journal confirmed the ghosts.  There is no object audit 
> journal
> activity to account for the object change.
> 
> It turns out that the ghost changes occur in RPGLE programs with 
embedded
> SQL.  Apparently these programs have  built-in workspace for SQL 
> processing.
> At run-time the program might have to resize the workspace if the data 
set
> requires such a change.  As a result the program object's change date is
> updated, but no activity is logged to the object audit journal.  I got 
all
> this information when I opened a PMR.  The explanation for the lack of a
> journal entry was "the system is changing the program for the systems 
own
> use".  The PMR was closed with everyone's favorite cop-out -- "working 
as
> designed".
> 
> I'm stuck.  Every time we go through an audit one of the big accounting
> firms asks for a list of programs with their create and change dates. 
The
> auditors cross reference the last change dates against our change 
control
> paperwork.  I can't see a way to really audit change control on the 
AS/400
> because there's no way to differentiate between an undocumented program
> change and an internally generated system change.
> 
> Is anyone approaching such audits differently?
> 
> -Jim
> 
> James P. Damato
> Manager - Systems Administration
> Dollar General Corporation
> <mailto:jdamato@xxxxxxxxxxxxxxxxx>
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
> 
> 
____________________________________________________________________________
_
> Scanned for SoftLanding Systems, Inc. by IBM Email Security Management 
> Services powered by MessageLabs. 
> 
____________________________________________________________________________
_


____________________________________________________________________________
_
Scanned for SoftLanding Systems, Inc. by IBM Email Security Management
Services powered by MessageLabs. 
____________________________________________________________________________
_
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.