PL/SQL procedures and Grants

Hi
[Quoted] I've got a question about accessing tables from a procedure where the table is created owned by someone else.

Is there no way for functions,procedures and packages to use privileges given to a user through a role ?

I'm using Oracle server 7.3.2.2.0 with PL/SQL 2.3.2.2.0

An example of the problem
I've got 2 oracle users Tom and Pam

Tom has created the following table,
role and he grants to the role

create table xxx
(value1 number(10),
 value2 number(10));

create role tom_role;

grant all on xxx to tom_role;

grant tom_role to pam;

If pam tries to access the table tom.xxx everything seems fine.

select * from tom.xxx;

pam tries to create a procedure that uses the xxx table

create or replace procedure calc is
  cursor cr_calc is

     select value1 
     from tom.xxx;

   res number(15) := 0;

begin

   for cr_res in cr_calc loop

      res := res + cr_res.value1;
   end loop
end calc;
/

When installing the procedure an error occurs: PLS-00201identifier 'TOM.XXX' must be declared.

If tom instead grants directly to pam
grant all on xxx to pam;
and pam installs the procedure everything works.

/Joakim
Received on Fri Feb 06 1998 - 00:00:00 CET