Re: Refering to tables outside your schema - HELP!!!

Patent Pending <firstname.lastname_at_ab.com> wrote:
>tamdy_at_aol.com (TAMDY) wrote:
>>We are trying to refer to a table that exists in a different schema,
>>without
>>prefixing it by the schema name. These are the steps we are taking.
>>
>>1. First we create a public synonym on the table.
>>2. Next we grant select, update, delete and insert on the table to the
>>role
>> DEVELOPER.
>>3. We have already granted the role DEVELOPER to user USER.
>>4. When USER runs her PRO*COBOL program which calls PL/SQL which
>> refers to this table by its synonym, she gets the error
>> ORA-00942 Table or view not found
>>5. If we make the table PUBLIC, the program runs successfully.
 

We had a similar problem trying to access a table in a Forms4.5 stored procedure. I checked with Oracle support and they told me that any privileges granted to a user through a role are ignored inside a PL/SQL stored procedure. They said it was a security feature.  

Try to grant privileges directly to the user (in your original configuration) and I bet it will work.  

Does anyone know where this is documented in the Oracle documentation?  

Walt
wmarek_at_grci.com Received on Fri Jun 30 1995 - 00:00:00 CEST