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

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 Sun Jan 04 1998 - 00:00:00 CST