In Oracle 7 roles do not work in stored procedures. The reason behind this
is is roles are dynamic and you could get into a situation your procedure
compiles and is stored in p-code, you change your role (the procedure is NOT
recompiled) and it doesn't work anymore. In Oracle 8 you can define a
procedure either with definer's rights (the old situation) or with invokers
rights, the procedure will assume the privileges of the user running it.
You don't specify on which release you are, but in Oracle 7 the only way out
is direct grants on the respective objects (and NOT select any table), or
upgrade to Oracle 8.
Best regards,
--
Sybrand Bakker, Oracle DBA
Konstantin Kivi <konst_at_sirena2000.ru> wrote in message
news:87so1asv0x.fsf_at_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 Sat Dec 11 1999 - 00:26:09 CST
