| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL and Roles (was Re: PL/SQL problem.... (Rebuild Indexes))
A copy of this was sent to eric_at_deptj.demon.co.uk (Eric Junkermann)
(if that email address didn't require changing)
On Fri, 04 Sep 1998 21:22:30 GMT, you wrote:
>On Wed, 19 Aug 1998 18:52:29 +0200, Sybrand Bakker
><postbus_at_sybrandb.demon.nl> wrote:
>
>>Regrettably, Madhu is absolutely right
>>PL/SQL ignores roles. All grants to dba_views are either to a role, or to
>>PUBLIC, which is again indirect.
>>This will probably get resolved in the next version, though the feature already
>>exists quite some time.
>>NB: Sql*plus doesn't suffer from thies problem
>>
><signature and previous messages snipped>
>
>OK, no-one else has taken this up for months, so I will. If I believed in
>HTML postings, the next line would be in VERY large letters:
>
well, I try to follow up on this question every now and again.
>The fact that stored PL/SQL objects ignore privileges obtained through a
>role is NOT a bug and should never be "fixed"!
>
agreed.
>The reasons for this are as follows:
>
>A role is a set of privileges which a user has because the role is active
>at the present time in the users present session.
>
>Roles may be activated and de-activated at will throughout the life of a
>session.
>
>When a stored PL/SQL object is executed, all privileges are checked as if
>the owner was executing it, BUT the execution is not taking place in the
>owner's session, so no roles can be active, nor is it possible to work out
>which roles might be active.
>
>Consequently the only privileges which will work are those the owner has
>been given directly.
>
>There is no reason why the owner should not be given the necessary
>privileges, it does not protect anything to try to use a role in this
>situation. A role in this situation is not even an administrative
>convenience, since no-one else will need exactly that set of privileges.
>
>If you don't agree, please tell us exactly why you need it to be different,
>
I'll just add a little more to this discussion.
There are a couple of reasons why roles are not active in stored, compiled objects. the predominant one is performance.
pl/sql is stored compiled. It uses compile time binding for privs -- at compile time the privelege set is known. Consider the following example (@invalid runs a script that lists ALL invalid objects in my schema)
SQL> @invalid
no rows selected
SQL> grant imp_full_database to tkyte;
Grant succeeded.
SQL> @invalid
no rows selected
SQL> revoke imp_full_database from tkyte; Revoke succeeded.
SQL> @invalid
no rows selected
SQL> grant select any table to tkyte;
Grant succeeded.
SQL> @invalid
no rows selected
SQL> revoke select any table from tkyte; Revoke succeeded.
SQL> @invalid
OBJECT_TYPE OBJECT_NAME STATUS
--------------- ------------------------------ ----------
FUNCTION BLOB2HEX INVALID
COUNTEM INVALID
GETINTYPE INVALID
[every stored object i own is listed here]
...
Since the bindings for privs are figured out at compile time -- the simple act of REVOKING a priv forces all of my objects to become invalid -- they need to be recompiled at some point (the system will do that over time for me). I was able to recompile all of my objects after the revoke and get them all valid again.
ROLES are much more fluid (at least they are designed to be) then users as far as priv sets go. lets say that we let roles give us privs in stored objects then 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) -- all of our objects would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly your entire database must be recompiled!
Also consider that roles may be
I guess the bottom line is:
You have 1,000's or 10,000's of end users. They don't create stored objects. We need roles to manage these people.
You have 1's or 10's of application schema's (things that hold stored objects). For these we not only want to be explicit as to exactly what privileges we need and why (in security terms this is called the concept of 'least privileges' -- you want to specifically say what priv you need and why you need it -- if you inherit lots of junk from roles you cannot do that effectively), but we can actually manage to be explicit since the number of development schemas is SMALL (but end users are large)...
>Regards,
>
>
>Eric
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Sep 05 1998 - 09:06:11 CDT
 |
 |