Re: roles and stored procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/11/27
Message-ID: <3660b526.10210822@192.86.155.100>#1/1

A copy of this was sent to bolletje <r.vegmond_at_hexapole.nl> (if that email address didn't require changing) On Fri, 27 Nov 1998 11:38:35 +0100, you 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???????
>

there are a couple of reasons. 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

so what the above showed that granting/revoking a role to/from a user did nothing to that users schema. No problems, no invalid objects. Now, lets grant some privilege and then revoke it from the same user:

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

non default. if I have a non-default role and I enable it and I compile a procedure that relies on those privileges, when I log out I no longer have that role -- should my procedure become invalid -- why? why not? you could argue both sides.
password protected -- same issues as above.

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)...

hope this helps..

>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

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
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 Fri Nov 27 1998 - 00:00:00 CST