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

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