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

You did not perform the test properly -- try writing a stored procedure instead of an anonymous PL/SQL block.

It is a known restriction in PL/SQL that permissions have to be explicitly granted (i.e., instead of through a role). I believe this is intended behavior since whoever executes the procedure assumes the privileges of the procedure owner w/in the execution of the procedure.

John D. Hoang
Innovative Solutions Consulting, Inc.

In article <68mjub$hk7$1_at_svr-c-01.core.theplanet.net>,   "Lawrence Simela" <lsimela_at_mahalini.prestel.co.uk> 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
> >

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet