Re: PL/SQL bug?

From: <bschafer_at_ocvaxc.cc.oberlin.edu>
Date: 29 Apr 93 18:01:22 EDT
Message-ID: <1993Apr29.180122.1_at_ocvaxc.cc.oberlin.edu>


> It seems that if I try to access two tables in different schemas via PL/SQL, it
> doesn't work. (One update / one select - I haven't tried other permutations). Has
> anyone else come across this feature and is there a workaround / patch for it?
> Yes, I _do_ have access to the tables; I can update quite happily from forms or
> SQL*plus; it's just the PL/SQL procedure which doesn't work....
>
> --
> John Peach
> Chevron (UK) Ltd.
> Ninian House, Crawpeel Road, Altens, Aberdeen, AB1 4LG, Scotland.
> Internet: epeas_at_chevron.com Phone: +44 224 242637

The problem is a bug which I have communicated to Oracle. It seems that when a table not in the schema of the user executing the procedure is accessed, it will fail with the ORA-00942 error. Outside of PL/SQL the table can be accessed normally.

The problem is with mediated privileges (privileges which are mediated via a role). For example, if TABLE A has been granted to ROLE X, and ROLE X has been granted to USER M, then privileges on TABLE A are mediated via ROLE X to USER M. Mediated privileges will not work within PL/SQL blocks.

The workaround is one of two. First, DIRECTLY grant the INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, SELECT ANY TABLE system privileges to USER M. The second solution is to DIRECTLY grant privileges on TABLE A to USER M.

If you have further problems, E-MAIL me privately.

BSCHAFER_at_OCVAXC.CC.OBERLIN.EDU Received on Fri Apr 30 1993 - 00:01:22 CEST

Original text of this message