Re: Grant Execute to Packages

A copy of this was sent to "Sally Madeira" <sallym1_at_home.com> (if that email address didn't require changing) On Fri, 08 Jun 2001 09:25:19 GMT, you wrote:

>I created a Role called StuckyProc so that I could share my packages across
>all users. The Division User is the users that manages all the procedures
>and packages:
>
>Grant Execute on Division.Credit_Package to StuckyProc
>
>I have several users but the users I am working with are Division and
>Credit - Division again being responsible for all the procedures and
>packages and the Credit user has the tables. I have other users that will
>need to also share the packages in The division user. I store everything in
>the division user because other users share the same procedures/packages and
>I do not want to maintain the code in every user (CREDIT, PLANNING, FINANCE
>for example will all share the
>Division.Credit_Package.CreateDailyAdjustments Procedure within the
>package).
>
>When I am in the Credit user I have a procedure that runs the following:
>
>CREATE OR REPLACE PROCEDURE CREDIT.EOMTEST (AsOfDate IN DATE)
>AS
>
>BEGIN
>
> --Libor for Web/Report 41/Yldmmyy Feed/Report 5
> Division.Credit_Package.CreateDailyAdjustments (AsOfDate);
>
>END;
>
>It compiles fine - What this is doing is refreshing a table and inserting
>daily adjustements. The problem I am having (and I thought it was sappose
>to work this way) is that the Credit users DAILYADJ table is still empty
>after I run this but the Division Table DAILYADJ gets populated. What I
>want to be able to do is share the procedures and be able to run them so
>that the USER CREDIT DAILYADJ Table get updated.
>
>Could anyone tell me what I am doing wrong-----
>
>Thanks
>S
>
>
>
>

Definers rights routines (the default) are statically bound to a fixed object at compile time. When you create the procedure -- it'll resolve the references to the objects and statically bind to them.

What you are describing is covered under invokers rights routines.

See
http://technet.oracle.com/doc/oracle8i_816/appdev.816/a77069/07_subs.htm#12848

this is available in Oracle8i and up only.

the other option is to use dynamic SQL but then the owner of the procedure will need access to each and every table it might possibly modify.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp