Re: Insufficient privileges error

Joe Mellors <joemellors_at_aol.com> wrote in message news:53566469.0202070820.3fa392e3_at_posting.google.com...
>
> I was just wondering if anyone could offer an explanation for this.
> The only thing I could think of was that for some reason when using
> dynamic sql the user has to explicitly be granted the privileges and
> that when other users with A_USER role do this, Oracle thinks (for
> some bizarre reason) that they are OWNERA as it is through OWNERA's
> object that it is called.
>
> Is it a bug in Oracle ?
>
From the top of my head:

one feature and one bug ?

Assumption:
The dynamic sql is in a stored procedure under OWNERA

The feature:
When any users call a procedure in the OWNERA schema the excecution of that procedure is excecuted with OWNERA's and not the user's privileges.

The bug:
Not shure about this but I seem to remember having read about a 'documented' bug in regards to priviliges and PL_SQL code. Stating that (in som situations?) when PL-SQL code is executed, priviliges granted through roles is 'lost' and that only directly granted priviliges have effect.

If I remembered that correctly it would explain your experience quite nicely, and if i didn't I'm shure someone will come along and correct me ;-)

--
Jon Håvard Grøtterud