Re: Stored Procedures and Roles

In article 9389_at_nosc.mil, Louise Miller <miller_at_louise.ucsd.edu> writes:
>edward_at_igate1.hac.com (Ed Bruce) wrote:
>><snippped>
>>
>>I got this to work but only after discovering that stored procedures do
>>not get any system privileges from roles. I was using my personal schema
>>which has the 'create any synonym' and 'drop any synonym' system
>>privileges. When I got the package working it was moved into a schema that
>>has the DBA role. I kept getting insufficient privilege errors. I got the
>>DBA to grant the 'create any synonym' and 'drop any synonym' privileges to
>>the schema. Now everything works fine.
>
>This is interesting. We have database procedures which access tables
>whose synonyms are dropped daily. We discovered the same thing about
>granting from roles, and we resolved the problem by granting the synonyms
>directly.
>
>Hmmm.......
>
>Louise Miller

I'm not sure if you are aware of this or not, but stored procedures, stored functions, and database triggers execute under the schema that created them, not the schema that invokes them. Any grants and other pertinent actions need to be performed under the creating schema. The only grants that need to be performed under the using schema are to execute the procedures/functions.

Hope this helps.

Later,
TT Received on Fri Nov 17 1995 - 00:00:00 CET