role problem

From: Konstantin Kivi <konst_at_sirena2000.ru>
Date: 10 Dec 1999 17:57:50 +0300
Message-ID: <87so1asv0x.fsf@konst.develop.sirena2000.ru>

I have a trouble accesing
another user's table from pl/sql
when the right to do so are granted
through the role.
(pure sql works fine)

Here is the example

create role vasya;
grant connect, resource,select any table to vasya; grant vasya to scott;

then run this as scott

  1 create or replace procedure petya as   2 a integer;
  3 begin
  4 select 1 into a from sirena.pnr where rownum<2;   5* end petya;
SQL> / Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PETYA:

LINE/COL ERROR

-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/22     PLS-00201: identifier 'SIRENA.PNR' must be declared

SQL> However if I grant 'select any table ' to scott directly the following example compile and work fine. What is wrong here and how it can be fixed?

--
Sincerely Yours, Konstantin Kivi, Russia, konst_at_sirena2000.ru Received on Fri Dec 10 1999 - 08:57:50 CST