Re: Encapsulating Stored Procedures with adopted authority

I have created stored procedures with adopted authority.  Mine were based 
upon RPG programs, but the concepts should be the same for CL.  I use the 
Navigator wizard to create my stored procedures instead of writing the SQL 
stmt myself.  However, as an example, when I generate the SQL via 
Navigator for one of the stored procedures, this is what I get:

--  Generate SQL 
--  Version:                    V5R4M0 060210 
--  Generated on:               03/08/10 10:48:42 
--  Relational Database:        SYSTEMA 
--  Standards Option:           DB2 UDB iSeries 
CREATE PROCEDURE WLIBR.RTVBUDGET ( 
        IN REQTYP CHAR(1) , 
        IN ACCT NUMERIC(10, 0) , 
        OUT BUDGET NUMERIC(9, 2) ) 
        LANGUAGE RPGLE 
        SPECIFIC WLIBR.RTVBUDGET 
        NOT DETERMINISTIC 
        MODIFIES SQL DATA 
        CALLED ON NULL INPUT 
        EXTERNAL NAME 'WLIBR/UW999R' 
        PARAMETER STYLE GENERAL ; 
 
COMMENT ON SPECIFIC PROCEDURE WLIBR.RTVBUDGET 
        IS 'Rtv the Budget value in customer database' ; 
 
COMMENT ON PARAMETER SPECIFIC PROCEDURE WLIBR.RTVBUDGET 
( REQTYP IS 'Request Type' , 
        ACCT IS 'Account number' , 
        BUDGET IS 'Budget billing amount' ) ;

Also, to test, I use the "Run SQL Scripts" via Navigator.  To run a stored 
procedure in Navigator, the script is as such (where the ?'s are output 
parameters):

> call WLIBR.RTVBUDGET(1234567890.,'C',?)

Navigator will tell you the attached job name, number and user.  You can 
use this job info to start debug on the attached job so that you can look 
at the joblog to determine what exactly isn't working correctly.  You 
could also use Websphere Development Studio (or whatever today's name is) 
to add a Service Entry Point on the program.
 


From:
security400-request-Zwy7GipZuJhWk0Htik3J/[email protected]
To:
security400-Zwy7GipZuJhWk0Htik3J/[email protected]
Date:
03/06/2010 12:01 PM
Subject:
Security400 Digest, Vol 8, Issue 10
Sent by:
security400-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]



Send Security400 mailing list submissions to
                 security400-Zwy7GipZuJhWk0Htik3J/[email protected]

To subscribe or unsubscribe via the World Wide Web, visit
                 http://lists.midrange.com/mailman/listinfo/security400
or, via email, send a message with subject or body 'help' to
                 security400-request-Zwy7GipZuJhWk0Htik3J/[email protected]

You can reach the person managing the list at
                 security400-owner-Zwy7GipZuJhWk0Htik3J/[email protected]

When replying, please edit your Subject line so it is more specific
than "Re: Contents of Security400 digest..."


Today's Topics:

   1. Encapsulating Stored Procedures with adopted authority
      (Vanderhook, George)


----------------------------------------------------------------------

message: 1
date: Fri, 5 Mar 2010 12:54:16 -0600
from: "Vanderhook, George" <[email protected]>
subject: [Security400] Encapsulating Stored Procedures with adopted
                 authority

I have a CL program that I'm using as a stored procedure.  I need to put
*SECADM authority on this stored procedure so that when accessed by
users on a Windows server, they are able to run the procedure via a JDBC
connection.  My problem is that I cannot get the authority to work.  I
created the CL program with User: *OWNER and created it with a *SECADM
sign-on.  I see how SQL stored procedures can have SET OPTION USRPRF to
do this but when I try this option for an external procedure it doesn't
work.  I'm simply using the STRSQL console from the iSeries and a CREATE
PROCEDURE statement.  Any ideas?

 

George



------------------------------

_______________________________________________
This is the Security Administration on the AS400 / iSeries (Security400) 
digest list
To post a message email: Security400-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/security400
or email: Security400-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/security400.



End of Security400 Digest, Vol 8, Issue 10
******************************************


_______________________________________________
This is the Security Administration on the AS400 / iSeries (Security400) mailing list
To post a message email: Security400-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/security400
or email: Security400-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/security400.