Re: roles + pl/sql

Why is it that roles are not used during the compilation of a procedure? It has to do
with the dependency model. Oracle is not storing exactly WHY you are allowed to access T
¡V only that you are (directly able to -- not via a role). Any change to your privileges
that might cause access to T to go away will cause the procedure to become invalid and
necessitate its recompilation. Without roles ¡V that means only ¡§REVOKE SELECT ANY
TABLE¡¨ or ¡§REVOKE SELECT ON T¡¨ from the definers account or from PUBLIC. With roles
¡V it greatly expands the number of times we would invalidate this procedure. If some
role that was granted to some role that was granted to this user was modified, this
procedure might go invalid, even if we did not rely on that privilege from that role.
ROLES are designed to be very fluid when compared to GRANTS given to users as far as
privilege sets go. For a minute, let¡¦s say that roles did give us privileges in stored
objects. Now, most any time anything was revoked from ANY ROLE we had, or any role any
role we have has (and so on -- roles can and are granted to roles) -- many of our objects
would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly
your entire database must be recompiled!

Consider the impact of revoking some system privilege from a ROLE, it would be like doing
that to PUBLIC now -- don't do it, just think about it (if you do revoke some powerful
system privilege from PUBLIC, do it on a test database). Revoking SELECT ANY TABLE from
PUBLIC for example would cause virtually every procedure in the database to go invalid.
If procedures relied on roles ¡V virtually every procedure in the database would
constantly become invalid due to small changes in permissions. Since one of the major
benefits of procedures is the ¡§compile once, run many¡¨ model ¡V this would be
disastrous for performance."

Cheers

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

On Mon, Dec 20, 2010 at 6:13 PM, Stephens, Chris <Chris.Stephens_at_adm.com> wrote:
>
> I just went through the ‘roles are not active in pl/sql’ speech with a
developer.
>
>
>
> His response was that someone at Oracle really needs to fix that.
>
>
>
> My first reaction was ‘I’m sure there is a very good reason for it to be
that way’ but I can’t really think of why it is that way.
>
>
>
> Are roles disabled in pl/sql for some reason other than that’s just the
way oracle implemented it?
>
>
>
> chris
>
> CONFIDENTIALITY NOTICE:
> This message is intended for the use of the individual or entity to which
it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.
>
>

--
http://www.freelists.org/webpage/oracle-l