Re: Help:plsql problem with roles (pls 201)

Hi again,

I checked everything and there are no misspellings. A remarkable thing is
that an anonymous plsql block works with me fine too, but as soon as i try it with a named blocked: eg create or replace procedure abcd it keeps on failing. Is this really a roles problem ? I know it isn't suppose to
work for executing procedures in other schemas

Thanks again

Jeroen

Lawrence Simela wrote:

> Well, this is a strange one -- I have just done a test as you said and
> it
> worked.
>
> 1. Create public synonym table_name for shema_name.table_name
> 2. Create Role test_role
> 3. grant select, update, delete on table_name to test_role
> 4. Grant test_role to procedure_owner;
> 5. connect as procedure_owner
> 6. created an anonymous plsql block with 'select col1 into dummy from
> table_name'
> and printed the value of dummy. It worked.
>
> I would check for mispellings etc.. otherwise it should work.. it
> works with
> ver 7.3.2.3.1
> in fact the procedure should not even compile if the roles are not set
>
> properly
>
> Regards
> Lawrence Simela
> MAHALINI CONSULTING LIMITED
>
> Jeroen van Sluisdam wrote in message <34AE19B5.8801A03D_at_vrijuit.nl>...
>
> >Hi,
> >
> >I have a plsql procedure with the following statements
> >
> >a b.field%type;
> >
> >select count(*)
> >from b
> >into a;
> >
> >b is in another schema d. There exists a public synonym for b and as
 far
> >as privileges
> >concerned there is a role c and on the table there exists select,
> >update, delete and insert
> >privileges on b for the role c. The user with the plsql procedure has
 

> >role c assigned to
> >him. It's possible for him to do a select in sqlplus on b and it gets
 

> >result back, so why
> >is he getting pls-201 identiefier d.b must be declared.
> >
> >When i directly give select privilege on b to the user the procedure
> >works. Is there
> >anyway to solve this with roles ?
> >
> >Thanks a lot,
> >
> >Jeroen
> >
> >jeroen.van.sluisdam_at_vrijuit.nl
> >
Received on Mon Jan 05 1998 - 00:00:00 CST