Re: Problem: Executing a procedure under a not owner (of procedure) user

Hi,

From what I understand, you are trying to use a stored procedure/package on the different schema objects (tables, sequences, etc.), depending on what user wants to execute them. I believe this is NOT possible. The reason being that when the procedure/package is compiled it is STATICALLY linked to the existing set of schema objects. If you delete a table, used by procedure/package, procedure/package body becomes invalid. Even if you create a table later you have to recompile it.
I don't see any reasonable way to create DYNAMIC stored procedures.

Alexei Fox

UMESH.X.TIWARI_at_x400gw.ameritech.com wrote:

> Have you tried granting 'EXECUTE ' rights from APPLIC_DBA to DIF_USER and
> running APPLIC_DBA owned procedures/packages from under DIF_USER id?
> Also, creating either a public synonym, or a private synonym under
> DIF_USER for the procedure/package that points toward the APPLIC_DBA
> schema owned object may help.
>
> This has never been a problem. Hope this helps.
>
> Umesh
> ----------------
> In article <68e6r5$csh$1_at_news.worldonline.nl>,
> "John Vernes" <vernes.j_at_consunet.nl> wrote:
> >
> >
> > In our database (7.3.3.) we have a user (APPLIC_DBA) under which
> > we store all our packages and stored procedures, tables and views for our
> > application.
> >
> > For example the procedure create_classification in the package
> > classification.
> > The owner of this procedure will be APPLIC_DBA
> >
> > When executing the procedure under the user APPLIC_DBA it will run under the
> > user APPLIC_DBA.
> > This will ALSO happen when we will connect under a different user, for
> > example DIF_USER
> >
> > Refferences in the procedure to a tablename will be made to the tables
> > stored under the user APPLIC_DBA and NOT THE CONNECTED USER DIF_USER.
> >
> > =========================
> >
> > Can anyone tell me if it is possible to change this and in what way.
> >
> > We have an option, but it is a filty one. Namely copying all packages to the
> > current user and recompiling them there. I believe this is NOT something you
> > want to do
> > because of the difficulties in version-management and multiple storage of
> > the same info.
> >
> > Help would be very much appreciated.
> >
> > Thanks
> >
> > John
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Fri Jan 16 1998 - 00:00:00 CST