Re: Encapsulating Stored Procedures with adoptedauthority -- SECURITY400

Hello, George:

Just because you specified USRPRF(*OWNER) on the CRTCLPGM command does not guarantee that the program will adopt authority.

If there happened to be a previous version of the same program that already existed in the target library, that had USRPRF(*USER) specified, and you used CRTCLPGM with REPLACE(*YES) then the compiler "ignores" your request for USRPRF(*OWNER) and instead forces the new *PGM to have the same attributes as the existing program -- another way to say this is that the new version of the *PGM inherits the value of USRPRF from the existing previous version when REPLACE(*YES) is used.

Please issue DSPPGM targetlib/progname to verify whether or not you see User Profile . . . : *OWNER listed.

You can force the USRPRF to be *OWNER in one of two ways:

1. issue CHGPGM targetlib/progname USRPRF(*OWNER) before recompiling, or

2. delete the existing *PGM from the targetlib before issuing CRTCLPGM with USRPRF(*OWNER).

Then try your tests again and report the results to the list.

All the best,

Mark S. Waterbury

> Vanderhook, George wrote:

Chuck,
Thank you for your response. DSPPGM shows the CL to have USRPRF
= *OWNER and USE ADOPTED AUTHORITY = *YES. I created this program using
a security administrator sign-on where I'm able to use the CHGUSRPRF
command. However, when I sign on as a regular user, and call the CL, I
get a message in my joblog saying, "*SECADM required to create or change
user profiles." I'm calling the CL directly so I'm not sure what could
be happening. Does the system look at the authority on both sign-ons
when it does its authority check, or is it just the owner of the CL?
My intentions with this are to put make this CL a stored
procedure which is called via a Call statement through a JDBC
connection. Could this possibly not work from the iSeries but, instead
work from the JDBC connection?

Thanks for the help,

George

-----Original Message-----
From: security400-bounces@xxxxxxxxxxxx
[mailto:security400-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Sunday, March 07, 2010 1:38 PM
To: security400@xxxxxxxxxxxx
Subject: Re: [Security400] Encapsulating Stored Procedures with
adoptedauthority

DSPPGM of the CLP shows the proper adoption; i.e. *OWNER and the owner is the expected *USRPRF name which has the necessary object & special authorities to perform the requested CHGUSRPRF? Does the CLP function outside of the SQL? That is, does a CL request to CALL CLP function, whereas the SQL request to CALL CLP does not function? What is the error encountered that suggests an inability to "get the authority to work" correctly? As an external procedure with a LANGUAGE other than SQL, there is no support for SET OPTION; i.e. the manner of effecting "options" are specific to a language, such that for a CLP the USRPRF(*OWNER) is what gives the adopted authority setting on a request to CRTCLPGM REPLACE(*NO). Note: the DSPPGM noted first should be verified, because the default is REPLACE(*YES) such that if a prior version of the CLP existed before CRTCLPGM was issued, and only some later invocation include the option USRPRF(*OWNER), the program will not have that setting regardless that it was requested; i.e. a diagnostic is logged to suggest that the request was ignored.

Regards, Chuck

Vanderhook, George wrote:

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?

_______________________________________________
This is the Security Administration on the AS400 / iSeries (Security400)
mailing list
To post a message email: Security400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/security400
or email: Security400-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/security400
or email: Security400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/security400.