Re: stored procedure that crosses schema

From: Roy Jones <docjones_at_bellsouth.net>
Date: Sun, 29 Nov 1998 16:03:11 GMT
Message-ID: <36616efd.2142900_at_news1.lig.bellsouth.net>

Here is information reguarding your problem:

        All privileges that a stored procedures needs must be granted directly to the owner of the stored procedure. Any privileges the owner gains through roles will not be available to the stored procedure. We have 300+ stored procedures and found this out the hard way.

Roy

---

On 16 Nov 1998 15:39:03 -0600, jdsharp_at_bigfoot.com (Jason Sharp) wrote:

>I'm trying to write a stored procedure that crosses schema boundaries.
>For example when I compile the following stored procedure I receive
>the error, PLS-00201: identifier 'IOR.PM_BUNDLE' must be declared.
>The RPC owner is "projtrak" and the table owner is "ior".
>
>* Both schemas reside in the same database.
>* Both schemas have select any table privileges via the same role.
>
>If I explicitly grant schema "projtrak", select privileges on the
>"ior" table pm_bundle the RPC compiles and executes.
>
>We are trying to avoid granting select, update, etc privileges to any
>non-developer either directly or through a role. All table access
>should be through stored procedures. Therefore, is there another way
>to accomplish this without granting select privileges to "projtrak"?
>
>CREATE PROCEDURE projtrak.GET_PC_TEST
>
> (my_cur in out projtrak.pc_test.ior_rec)
>
>AS
>BEGIN
>
> open my_cur for
> select a.bundle
> from ior.pm_bundle a;
>
>END;
>
Received on Sun Nov 29 1998 - 17:03:11 CET