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

Yup, I stand corrected. Now you and Jeroen metion it.. I do seem to recall this as a restriction... I must say, my preferred approach has always been to have all the packages accessing tables etc in one schema and then grant roles and priviliges on the packages.

Regards
Lawrence Simela
MAHALINI CONSULTING LIMITED jhoang_at_mindspring.com wrote in message
<884024930.1702559749_at_dejanews.com>...
>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
Received on Wed Jan 07 1998 - 00:00:00 CST