Re: Roles are not granted in PL/SQL ???

A copy of this was sent to Yann Chevriaux <chevriaux_at_theleme.com> (if that email address didn't require changing) On Wed, 03 Nov 1999 14:50:30 +0100, you wrote:

>Hi.
>I've got a problem with roles.
>I first develop an application with Forms 4.5 on Personnal Oracle
>7.3.2.2.0
>It works and there are several roles defined and dispatched over some
>users.
>

see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

>Some days ago, this application has been transferred on our Oracle
>Server:
>(NT 4, Oracle 7.3.2.3.1).
>
>And ... it doesn't work anymore.
>

it never worked -- with PO7 or not. If you check the granted privs on the PO7 database -- you'll discover the owner of the procedure either had:

• direct grants
• broad system privs like "execute any procedure, select any table"

granted directly to them.

>In fact I discover that procedures, functions and packages can't be
>compilated !
>
>The error occurs when I try to access objects for which I got privileges
>trough a role.
>
>i.e.:
>
>Assume I've been granted role DBA:
>
[snip]

>
>Does it mean that privileges are not viewable trough roles when using in
>procedures, functions or packages ?
>As it works with PO7, is there some parameter I could change ?
>

Yes it does.
It has always been this way.
It was no different in PO7.

In Oracle8i, release 8.1, there is the ability to run a procedure with 'invokers' rights so that objects are accessed with the privs & roles of the CURRENTLY LOGGED IN user but this is a very different model (eg: a procedure that did a select * from emp would require anyone that runs it to have SELECT on EMP either from a role or directly -- very very different from the way procedures work with definers rights)

>Please Help !!!!!
>
>Yann.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation