Re: Two simple questions.

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 09 Oct 2004 12:26:50 -0700
Message-ID: <1097350095.340707@yasure>

Fred Pierce wrote:

> On Wed, 06 Oct 2004 21:54:03 +0100, Gama Franco <tiago_at_cern.ch> wrote:
>
>

>>Hello,
>>
>>1 - Is it possible to run a stored procedure using the privileges 
>>granted to a user through a role? I mean, is there any way to do it?
>>
>>2 - How do I inspect the roles of a user using SQL PLUS?
>>
>>Best regards,
>>  Gama Franco

>
>
> I'm surprised at the "no" answers to 1. If you put the procedures in
> packages and use invoker rights, you certainly can use roles. You do
> have to directly grant privs on dependencies when compiling the code.
> This is assuming you're using 8i or later. To quote Steven Feuerstein
> "...roles are effect at runtime as long as the invoker rights program
> hasn't been called from a definer rights program."
>
> Definer/invoker rights are confusing and can produce "unexpected
> results" so as with most things, careful reading and testing are
> required for success.
>
> fdp
>
> ------------------------------------------
> Fred Pierce (DNRC) - avialantic.com/links/oracle.html
> ------------------------------------------

Why are you surprised? Roles have nothing to do with it. Packages have nothing to do with it. You can not grant execute on stored code via a role as has been discussed in this forum hundreds of times in the last few years.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Sat Oct 09 2004 - 14:26:50 CDT