Re: Using QCMDEXC in an SQL SELECT statement Was: Disabled User Profiles

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You can with a CALL...

Without doing doing anything, you can use

CALL QCMDEXC ('SOMECLCMD', 0000000009.00000);

As long as the interface you're using supports the calling of stored procedures.

In general. every *PGM object on the box can be called as a stored
procedure without defining it using the CREATE STORED PROCEDURE...
Basically, the only reason for CREATE STORED PROCEDURE is to add some
extra functionality, particularly with respect to parameter handling.

Charles

On Mon, Mar 14, 2011 at 1:49 PM,  <paultherrien@xxxxxxxxxxxxxxxxxx> wrote:
> I am sorry, I seem to have opened a wound. (?) I believe I must have
> misunderstood.  You created QCMDEXC as a UDF.  My initial thought when I saw
> your example was that one could execute CL commands directly out of SQL.
> That would seem to be a security issue.
>
> As long as the UDF QCMDEXC is unique to your shop then that is fine.  If you
> are certain that the QCMDEXC will only ever be run by you then that is fine.
> If you are certain that no one will be able to run SQL in your shop as
> *SECADM except trusted resources, then that is fine.
>
> For the record, I am all for code and utilities that make our jobs easier.
> Your use of UDFs is creative and aggressive.
>
>
> Paul Therrien
> Andeco Software, LLC
> 932 Saint Johns Dr
> Maryville, TN 37801
> 225-229-2491
> paultherrien@xxxxxxxxxxxxxxxxxx
> www.andecosoftware.com
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: Monday, March 14, 2011 11:36 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Using QCMDEXC in an SQL SELECT statement Was: Disabled User
> Profiles
>
> Paul,
>
> Do not rely upon "security by obscurity".  Creating QCMDEXC as a function
> is not a security breach.  If you do not have access to DLTUSRPRF you
> still cannot run it by using the User Defined Function (UDF) QCMDEXC.  The
> only thing that the function does is allow you to run it easier.  We
> created is as a function for the myriad of CL programs we had written that
> basically did
> DSP... OUTPUT(*OUTFILE) ...
> DCLF ...
> READ:
>  RCVF
>  MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(EOF))
>  /* Do something */
>  GOTO       CMDLBL(READ)
> EOF:
> EXIT:
> ENDPGM
>
> Is the ability to write a CL program a security breach?  Should that be
> something that antivirus programs should stop?  By default anyone can
> write a CL program.  You do not need the application tools on your system.
>  All systems have EDTF and CRTCLPGM.  If you find the SQL UDF a security
> breach because it can be used to run DLTUSRPRF then I suggest you stop
> giving all your users *SECADM and *ALLOBJ.  That's a step in the right
> direction.
>
> Is having the UDF a breach because it doesn't slow down the developer and
> make him think?  Then, by all means, let's put other impediments in his
> path.  Let's ban CL and force them to use MI.  After all, did you see
> anything in the CL sample above that did any comparison?  What was to stop
> this CL program from forgetting the "where" and deleting all user
> profiles?
>
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   <paultherrien@xxxxxxxxxxxxxxxxxx>
> To:     "'Midrange Systems Technical Discussion'"
> <midrange-l@xxxxxxxxxxxx>
> Date:   03/14/2011 10:56 AM
> Subject:        RE: Using QCMDEXC in an SQL SELECT statement Was: Disabled
> User    Profiles
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> This is the kind of thing anti-virus software would look to prevent one
> from
> running.
> That you can run a command this way in SQL seems to be a very serious
> security breach.
>
>
> Paul Therrien
> Andeco Software, LLC
> 932 Saint Johns Dr
> Maryville, TN 37801
> 225-229-2491
> paultherrien@xxxxxxxxxxxxxxxxxx
> www.andecosoftware.com
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: Monday, March 14, 2011 9:52 AM
> To: Midrange Systems Technical Discussion
> Subject: Using QCMDEXC in an SQL SELECT statement Was: Disabled User
> Profiles
>
> http://archive.midrange.com/midrange-l/201004/msg00930.html
>
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   "McGovern, Sean" <Sean.McGovern@xxxxxxxxxxxx>
> To:     "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> Date:   03/14/2011 09:38 AM
> Subject:        RE: Disabled User Profiles
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Can you run QCMDEXC through a SELECT statement ?
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: 14 March 2011 13:04
> To: Midrange Systems Technical Discussion
> Subject: RE: Disabled User Profiles
>
> No, it's just a file we routinely run DSPUSRPRF's output to.
>
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   "McGovern, Sean" <Sean.McGovern@xxxxxxxxxxxx>
> To:     "Midrange Systems Technical Discussion"
> <midrange-l@xxxxxxxxxxxx>
> Date:   03/14/2011 06:28 AM
> Subject:        RE: Disabled User Profiles
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Is routines/userlist something new at 7.1 ?
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: 09 March 2011 20:46
> To: Midrange Systems Technical Discussion
> Subject: RE: Disabled User Profiles
>
> Program?  Who needs a stinking program?
>
> SELECT upuprf, upstat, qcmdexc('DLTUSRPRF ' CONCAT UPUPRF)
> FROM routines/userlist
> WHERE UPSTAT='*DISABLED'
>
> Whether or not doing it this way is a good idea, I'll leave up to you.
> (ie, do you really want to delete all disabled user profiles?)
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   "Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxxxxx>
> To:     "Midrange Systems Technical Discussion"
> <midrange-l@xxxxxxxxxxxx>
> Date:   03/09/2011 03:37 PM
> Subject:        RE: Disabled User Profiles
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Forgot to mention that you will need to run this as a user with *SECADM
> authority or you will not "see" them all.
>
> And since you are running this with all of this power, you could
> probably
> develop a program to use this table as a feed to delete these profiles
> as
> well.
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [
> mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Needles,Stephen J
> Sent: Wednesday, March 09, 2011 2:27 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Disabled User Profiles
>
> DSPUSRPRF USRPRF(*ALL)
>          OUTPUT(*OUTFILE)
>          OUTFILE(QTEMP/USERS)
>
> Then run SQL, WRKQRY or what have you to select those rows whose UPSTAT
> value is *DISABLED.
>
> steve
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [
> mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Ostrowski
> Sent: Wednesday, March 09, 2011 2:08 PM
> To: Midrange Systems Technical Discussion
> Subject: Disabled User Profiles
>
> Over time, bad economy, and downsizing, we've ended up with a lot of
> user profiles on the system
> for people that are no longer with the company and have been *DISABLED.
>
> Is there a quick way to get a report out to a file of all disabled user
> profiles as opposed to going through
> them all one by one?
>
> Thanks for any help.
>
>
>
> --
> 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 communication, including attachments, is confidential, may be
> subject
> to legal privileges, and is intended for the sole use of the addressee.
> Any use, duplication, disclosure or dissemination of this communication,
>
> other than by the addressee, is prohibited. If you have received this
> communication in error, please notify the sender immediately and delete
> or
> destroy this communication and all copies.
>
> --
> 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 communication, including attachments, is confidential, may be
> subject
> to legal privileges, and is intended for the sole use of the addressee.
> Any use, duplication, disclosure or dissemination of this communication,
>
> other than by the addressee, is prohibited. If you have received this
> communication in error, please notify the sender immediately and delete
> or
> destroy this communication and all copies.
>
> --
> 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.
>
>
> --
> 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.