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

Are you sure you tried it on one of these programs where the change date 
changed?  I do not think it shows a date unless there is an access plan 
and it has changed.

Mark



midrange-l-bounces@xxxxxxxxxxxx wrote on 01/28/2005 03:13:49 PM:

> 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.
> 
> 
> 
_____________________________________________________________________________
> 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. 
_____________________________________________________________________________