Re: Grant issues.

Mehul,

BTW, the same dependency issue also applies to views (must have direct privs on another users table/view to create a view on that object.)

Thomas Kyte wrote:

> A copy of this was sent to "mehul" <mzaveri_at_eppco.co.ae>
> (if that email address didn't require changing)
> On Wed, 16 Jun 1999 12:10:17 +0400, you wrote:
>
> >Hi friends,
> >Can some body help??
> >
> >Case :-
> >
> > Platform :- Oracle Enterprise 8.0.4.2.1 on Aix 4.3.
> >
> > Role dvl for developers,
> > granted all possible object level privileges
> >(select,insert,update,delete,alter)
> > to dvl role for tables of other schemas.
> > granted create table, create procedure rights to allow develper to create
> >thier
> > own tables and/or procedures etc.
> >
>
> roles are never enabled during the execution of a procedure.
>
> Try this:
>
> SQL> set role none;
> SQL> "statement you want to test to see if it'll work in a procedure"
>
> If you can do it in plus with no roles you can do it in a procedure. If you
> can't, you must have the privelege from a role and hence won't be able
> to do it in a procedure.
>
> You probably have the privelege to do what you are trying to do in the procedure
> via a role. Grant the privelege directly to the owner of the procedure and
> it'll work.
>
> grant <priv> on <whatever> to <OWNER>;
>
> there are a couple of reasons why this is the case. 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)...
>
> > connect as developer (This user has default role of dvl)
> > create a procedure p1 trying to acces other_schema.table_name;
> > While compiling p1, error message appears about lack of privileges.
> >
> > Now the problem is how do you give object level grants of other schemas to
> > dvl role? so that p1 procedure can be compiled w/o. errors.
> >
> > Although the following works
> > connect as other_schema
> > grant select on table_name to developer with grant option
> > grant insert on table_name to developer with grant option
> > grant update on table_name to developer with grant option
> > grant delete on table_name to developer with grant option
> > We do not want to give any grants directly to developer user (As far as
> >possible).
> > The grants should be given to dvl role who in turns has been granted dvl
> >role.
> >
> > I am not able to understand, why database procedure needs this special
> >treatment?
> > What other alternates can be deviced to manage such granting issues?
> >
> > Any solutions/ suggestions/ advices would be highly appreciated. Also if I
> >can be pointed to some white paper which explains proper management of grant
> >related issues?
> >
> > Thanks in advance.
> >
> >Kind regards,
> >Mehul Zaveri
> >Oracle DBA
> >Email:mzaveri_at_eppco.co.ae
> >Emirates Petroleum Products Company.
> >Dir:+9714-3031557,Brd:+9714-372131
> >Dubai.
> >
> >
>
> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
> Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
>
> Current article is "Fine Grained Access Control", added June 8'th
>
> 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

Received on Wed Jun 16 1999 - 22:55:46 CDT