Re: how to execute a package in my schema context

Norbert Winkler wrote:
> Hi,
>
> I've written a package that works with a special set of tables I'm using
> in different schemas (i.e. MY_WORK).
> Now I'd like to create it in a special schema (i.e. SERVICE) to avoid the
> redundance.
> But executing it in MY_WORK it tries to modify the tables in SERVICE.
> It is so by design, I understand it. Normal usage of packages requires it
> in that way.
> But is there a possibility to share the funcionality of a package for the
> same tables in different schemas.

By default PL/SQL objects such as procedures, functions, and packages run under AUTHID DEFINER rights. What you need to do is modify your objects to run under AUTHID CURRENT_USER.

Go to Morgan's Library at www.psoug.org and look up PROCEDURES. On that page, if you scroll down, you will find a demo under AUTHID that shows what you describe and how to take control. The default, if you don't specify is AUTHID DEFINER ... quite often not a good idea.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org