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

[CRPence <CRPbottle@xxxxxxxxx>]

On 3/14/11 10:49 AM, paultherrien@xxxxxxxxxxxxxxxxxx wrote:
> <<SNIP>> 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.  <<SNIP>>

  Anyone with access to each of the SQL, the SQL CALL, and the *PGM 
QCMDEXC will be able to execute any CL command to which they are also 
authorized.  That can be accomplished using the implicitly available 
External Stored Procedure QCMDEXC in QSYS by issuing the following SQL 
request [for example]:

    call qsys/qcmdexc ('WRKJOB' , 0000000006.00000)

  The above SQL CALL can function because the first expression treated 
as a character string char(5) and the second treated as a packed(15,5) 
by the SQL are expected-for or compatible-with the parameters of that 
program.  Any program with only input parameters and compatible type can 
be called using the implied SP definition given the authority for each 
of the SQL CALL, the *PGM being called, and the actions requested by the 
called program.  If the called program adopts authority however, then 
what that program invokes need not be directly authorized to the user 
that issued the CALL.  However note also that any adopted authority from 
before the SQL CALL or invocation of a SQL UDF is "dropped" for the 
invoked program; i.e. only adoption as defined to the called program is 
available to that program, not any adopted authority prior to the SQL 
CALL or UDF invocation, although any adopted authority to either the 
static or dynamic user profile could have enabled access to the program 
which was invoked by the user.

  Making the QCMDEXC or a similar feature available to perform the CL 
request directly via a UDF rather than only by SP just makes the access 
to the CL much more convenient especially against OUTFILE [output file] 
data.

Regards, Chuck