Re: roles and stored procedure

>After trying a few options I lost all hope and I called Oracle to give
>me an answer, it was right, a privilige granted by a role doesn't work
i>nside a stored procedure.
>Ok for that, I have to give all users directly those rights (why use
>roles anyway)
>but why is it different to grant a privilige through a role or
>directly???????

Hi,

I'm not sure if this is what you want but look at the Application Developers Guide Chapter 17.
There you find the following text:
The DBMS_SESSIONS.SET ROLE procedure behaves similarly to the SET ROLE statement and can be accessed from PL/SQL. You cannot call SET_ROLE from a stored
procedure. This restriction prevents a stored procedure from changing its security domain during
its execution. A stored procedure executes under the security domain of the creator of the procedure....

Following the above text there is a sample to use stored procedures TOGETHER with roles.

regards,
  -Marco Koch

bolletje wrote:

> I'm making a few stored procedures with oracle to access a users table.
> to allow other users to access that table I have to give them rights to
> do that. My idea was to grant them those rights by creating a Role for
> it. That role contains the rights to access the table.
> I thought (how stupid of me) that a privilige granted through a role was
> the same as a direct granted privilige (for example the SELECT
> privilige). NOT!!
> After trying a few options I lost all hope and I called Oracle to give
> me an answer, it was right, a privilige granted by a role doesn't work
> inside a stored procedure.
> Ok for that, I have to give all users directly those rights (why use
> roles anyway)
>
> but why is it different to grant a privilige through a role or
> directly???????
>
> I realy need to know 'cause I need to tell other people why, Oracle
> couldn't say for them self, other people I know couldn't say either, I
> hope someone can give me the sollution.
>
> Raymond

                                \\|//
                                (o o)
   __________________________ooO-(_)-Ooo___________________________
  /\                                                               \
  \_|  Marco Koch Informatik                                        |
    |  Marco Koch               Tel: +49-(0)761-73913               |
    |  Guenterstalstr.16        Tel: +49-(0)761-2020283 priv.       |
    |  79100 Freiburg           Tel: +49-(0)761-203-8126 university |
    |  Germany                                                      |
    |  email: hkoch_at_freiburg.netsurf.de                             |
    |  email: koch_at_informatik.uni-freiburg.de  (university)         |
    |                 |
    | ______________________.oooO___________________________________|
    \_/_____________________(   )___Oooo.__________________________/
                             \ (    (   )
                              \_)    ) /