Re: Grant Execute to Packages

when the procedure or package is compiled, it gets compiled under the "owner", and any unqualified object reference will be resolved to an object in the owner's schema (or synonym or public synonym). when the procedure is run by another user, the compiled sql statement is still referring to the procedure owner's object.

there is a new feature in 8i "invoker's" privilege or some such thing, but i don't believe that will fix the problem.

you are going to have to use "dynamic" sql to get the behavior you want... resolving object names to objects in the schema of the user running the procedure.

HTH "Sally Madeira" <sallym1_at_home.com> wrote in message news:WP0U6.65120$v5.5166002_at_news1.rdc1.ct.home.com...
> 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
>
>
>
>
>
>
Received on Sat Jun 09 2001 - 14:56:15 CDT