Re: PL/SQL error ..Cannot find Table..

From: (wrong string) érubé <sberube_at_cam.org>
Date: 1996/10/15
Message-ID: <sberube.1.012380D6_at_cam.org>#1/1

In article <johnv.0a5l_at_acix.DIALix.oz.au> johnv_at_acix.DIALix.oz.au (John Verhoeven) writes:
>Subject: Re: PL/SQL error ..Cannot find Table..
>From: johnv_at_acix.DIALix.oz.au (John Verhoeven)
>Date: Thu, 3 Oct 96 21:54:51 +0800
 

>In article dated Wed, 02 Oct 1996 09:37:16 -0400, janet (janet_at_telesph.com) wrote:
>> Srinivasa Rao Vaikuntam wrote:
>> >
>> > We are working on a project, where several developers are coding
>> > procedures. However, the developers log with their own username and
>> > access the schema's objects.
>> >
>> > through SQL*Plus, PL/SQL gives a compilation error, saying that the
>> > table/object could not be found.
>>
>> srini,
>> have you tried a synonym? just alias tablename as owner.tablename
>> for all the developers usernames.
 

>The problem is not that. Under 7.1.6.2 I've found that from within a
>stored procedure, function or package you can't make use of any table
>privileges you get through a role. Your only option is to grant the table
>privileges direct to the owner of the package. Grr!
 

>Even Data Browser can't save a query to the database where privileges on
>the tables used is the query are gained through the role.

We did find a solution to this problem. You can still grant the authorities on the tables through a role. However, each programmer must have a set of private syns pointing towards those tables.

They will then be able to select from them but the same select will still not work if inside a procedure, function or package. So, go in as the tables' owner and grant authorities on the private syns to the developers (Yes! I know it sounds strange but it does work)

i.e. SYSTEM owns a table which Peter wants to insert into.

       Create a role and as SYSTEM grant the select and insert authority to it.
       Grant role to Peter.

       Now Peter can create a private synonym to SYSTEM.table and could select 

and insert on SYSTEM.table. However, if he tries to do the same in a function it will fail with the Cannot find Table.

       
       Then do as SYSTEM:           
             GRANT SELECT,INSERT ON PETER.table_syn TO PETER;     (strange!)

Those grants could maybe given through a role but I never tried it. I never tried it neither with public syns. Maybe it could work and it would be better than with those private syns.

Hope it can help
Sylvie Bérubé
sberube_at_aircanada.ca Received on Tue Oct 15 1996 - 00:00:00 CEST