RE: Using QCMDEXC in an SQL SELECT statement Was: Disabled User Profiles -- MIDRANGE-L

Thanks for the information. I guess I knew this, but the extent of what one
can do with it was not evident to me.

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 Charles Wilt
Sent: Monday, March 14, 2011 2:28 PM
To: Midrange Systems Technical Discussion
Subject: Re: Using QCMDEXC in an SQL SELECT statement Was: Disabled User
Profiles

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.