Re: PL/SQL procedures and Grants

It is well known limitation that youu cannot use the objects in the stored procedures if you were granted access to them through the role. There is an explanation that has to do with some integrity issues, but the fact remains - you can't do it.
The limited alternative would be to create procedures dialing with tom's objects in his schema and grant execute permission on them to the role. Hope this helps.

Alexei Fox

ABB Network Partner AB wrote:

> Hi
> 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