Adopting *OWNER Authority in Stored Procedures

[Mark]

Commands such as CRTSQLCI have a parameter DYNUSRPRF that can be set to
*OWNER. This will cause local dynamic SQL statements to adopt the owner's
authority when accessing data. However, when a stored procedure is compiled
using CREATE PROCEDURE the CRTSQLCI command is run automatically.

This situation will arise when using the cwbundbs.exe Script Editor, the Ops
Nav wizards, STRSQL, or RUNSQLSTM, to create a stored procedure. In these
cases does anyone know how to get the resultant CLI program's module to
assign the dynamic user profile as *OWNER?

There are, of course, many other equally inaccessible parameters on the
CRTSQLxxxx commands.

Mark.

[joel eikenhorst]

You can use the set option clause on the create procedure statement:
CREATE PROCEDURE myproc
LANGUAGE SQL
SET OPTION USRPRF=*OWNER

If you are using SQL naming then the default is *OWNER if you are using SYS
naming the default is *USER.

This is described in the following (excellent) redbook:
http://www.redbooks.ibm.com/pubs/pdfs/redbooks/sg246503.pdf

[Mark]

On Tue, 08 Jan 2002 07:55:04 -0600, joel eikenhorst <"jdeiken$nospam$"@us.ibm.com> wrote:

Joel,

Thanks for your reply...

>You can use the set option clause on the create procedure statement:
>CREATE PROCEDURE myproc
>LANGUAGE SQL
>SET OPTION USRPRF=*OWNER
>

Hm... much of the documentation I had already looked at suggested that
the SET OPTION is only allowed in a REXX program... and indeed when I
try what you suggest I get this error:

SQL0199 - Keyword OPTION not expected.
Valid tokens: PATH RESULT CURRENT CONNECTION TRANSACTION.

Does SET OPTION only work under V5R1? I'm still using V4R5.

>If you are using SQL naming then the default is *OWNER if you are using SYS
>naming the default is *USER.

This applies to USRPRF(*NAMING), DYNUSRPRF doesn't have this setting and
it dafaults to *USER.