Re: Why procedure cannot see table?

Daniel Pinyol wrote:
>
> Ed Bruce wrote:
> >
> > Patrick Tan wrote:
> > >
> > > It is a bug in Oracle 7.1. It should be fixed in Oracle 7.2+
> > >
> > > Patrick
> >
> > This is supposedly a security feature. I used to have the explaination
> > as to why it is and I was convinced at the time. But it is not
> > considered a bug and according to Oracle it was a deliberate choice.
> >
> > --
> >
> > Ed Bruce
> > <Bruce_at_ha.hac.com>
> > <ebruce_at_iquest.com>
>
> Does anybody know any workaround? (Different from granting the
> object directly to the user, of course) There exists the procedure
> dbms_session.set_role(role). But it won't avoid the compilation error,
> since the server will complain that it doesn't know the object.
>
> Does anybody know why Oracle chose this option?
>
> Thanks.
>
> P.S. I'll appreciate an email answer to dpinol_at_cesca.es
>
>
> -----------------------------------------------------------------------
> 4 Daniel Pinyol Laserna E-Mail: dpinol_at_cesca.es
> -----------------------------------------------------------------------
Hello Daniel.

I'm guessing the tables are owned by one schema, while a developer in another schema wants to write a stored procedure or else with those.

The workaround I tried was having private synonyms for the programmer pointing towards the tables and... from the tables'schema grant access on those synonyms!!! i.e.:

1. create private syns for the developer pointing to the tables
2. connect as tables'owner
3. GRANT SELECT,INSERT,UPDATE,DELETE ON developper.synonym TO developer;

You'll notice how it was granted on a synonym belonging to someone else. Very strange! But I was getting desperate and it worked. I still haven't found out why yet. If anyone has an idea...

Sylvie Bérubé
sberube_at_aircanada.ca Received on Sun Jan 12 1997 - 00:00:00 CST