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

In the meanwhile i found the answer with some help from Oracle. The use of roles in plsql procedures/packages/functions is not supported and will not
be unfortunately !!

This means every table or procedure that will be used by other users than the owner
must be granted explicitly (without roles) to other users. As usual it's somewhere in
the f-manuals and concerning this see the server concepts manual.

Jeroen

Jeroen van Sluisdam wrote:

> 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