Hey Alex,
Outstanding info! I guess, as far as I can tell from your info, we
don't need to use the CURRENT FUNCTION PATH in stored SQL functions and
stored SQL procedures. What we have been doing is grant execute on a
schema to a group or user and then ensure the "developers("Oh my!
Phew!) use the schema name in front of the stored SQL function or stored
SQL procedure that they need to call.

GRANT EXECUTE ON PROCEDURE DEPBWS.* TO USER "ED.EDWARDS";

GRANT EXECUTE ON FUNCTION DEPBWS.* TO USER "ED.EDWARDS";


GRANT EXECUTE ON PROCEDURE DEPBWS.* TO USER DECDP;

GRANT EXECUTE ON FUNCTION DEPBWS.* TO USER DECDP;



CALL DEPBWS.UPDATE_SALARY('000010', 75000);





-----Original Message-----
From: Alex Levy
[mailto:db2udbdba-ezmlmshield-x58298404.[Email address protected]
Sent: Tuesday, December 12, 2006 6:34 AM
To: LazyDBA Discussion
Subject: RE: DB2 Version 8.2, Fixpack 7, for Windows (CURRENT FUNCTION
PATH)

Hi Ed
Well one reason for not replying was that I'm not sure of the answer.
I'll
meet your question and raise you two. Comments gratefully received
(particularly you Dan, as you seem to have a grip on this subject):
First, CURRENT PATH only establishes an order of precedence, it doesn't
grant execute permissions.
Second, CURRENT PATH is simply a search path to qualify function names
etc.
supplied without a schema name in dynamic SQL.
You could either use the SET PATH statement or set it in the CLI cfg.
Now the first question is: does a SQL stored procedure count as dynamic
SQL?
IBM removed the need for a separate compiler at V8.2. For SQL
procedures, it
might be reasonable to expect the DBMS to pick up the environment,
including
values in the special register, at CREATE PROCEDURE time; but you could
always specify the INHERIT SPECIAL REGISTERS clause for dependent
objects.
How about external stored procedures? Static SQL will require the
FUNCPATH
bind option, but I'm not sure of external SPs.
All of this can be determined, in principle, by simple experiment. Give
it
to a trainee!

Regards
Alex Levy
Sustainable Software Ltd
http://www.sustainablesoftware.net
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x67556809.[Email address protected]
Sent: 12 December 2006 09:04
To: LazyDBA Discussion
Subject: RE: DB2 Version 8.2, Fixpack 7, for Windows (CURRENT FUNCTION
PATH)


Hey Alex,
You didn't answer the question below? Hehe.


-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x46925600.[Email address protected]
Sent: Monday, December 11, 2006 9:55 AM
To: LazyDBA Discussion
Subject: DB2 Version 8.2, Fixpack 7, for Windows (CURRENT FUNCTION PATH)

To All,
Has anyone used the CURRENT FUNCTION PATH in a stored procedure or
stored function? If so, could you provide me with an example?


BTW, we are using the following code to sort of accomplish what the
CURRENT FUNCTION PATH supposedly does.

GRANT EXECUTE ON PROCEDURE DEPBWS.* TO USER "ED.EDWARDS";

GRANT EXECUTE ON FUNCTION DEPBWS.* TO USER "ED.EDWARDS";


Please take a few minutes to provide feedback on the quality of service
you received. The Department of Education values your feedback as a
customer. Commissioner John L. Winn is committed to continuously
assessing and improving the level and quality of services provided to
you by Department staff. Simply use the link below. Thank you in
advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



-----------------------------------------------
Scanned by 186k-NetCleanse, spam and virus protection




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page