Re: Encapsulating Stored Procedures with adoptedauthority

Please stop emailing me. I've done multiple request to be removed

Sent from my iPhone

On Mar 9, 2010, at 9:52 AM, "Vanderhook, George" <[email protected]> wrote:

Chuck, 
   Thank you very much for your help.  The CL's owner was actually
the user group rather then the *SECADM sign-on.  Once I changed this via
CHGOBJOWN, it worked like a charm.  Thank you for your help.  

George

-----Original Message-----
From: security400-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]
[mailto:security400-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]] On Behalf Of
plancor-j07EnEdxwtFsCmaw9lVm/[email protected]
Sent: Monday, March 08, 2010 11:08 AM
To: security400-Zwy7GipZuJhWk0Htik3J/[email protected]
Subject: Re: [Security400] Encapsulating Stored Procedures with
adoptedauthority

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.

_______________________________________________
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.




      
_______________________________________________
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.