Re: Grant issues.
Date: Wed, 16 Jun 1999 12:17:01 GMT
Message-ID: <37679570.2027875_at_newshost.us.oracle.com>
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 (_at_invalid runs a script that lists ALL invalid objects in my schema)
SQL> _at_invalid
no rows selected
SQL> grant imp_full_database to tkyte;
Grant succeeded.
SQL> _at_invalid
no rows selected
SQL> revoke imp_full_database from tkyte; Revoke succeeded.
SQL> _at_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> _at_invalid
no rows selected
SQL> revoke select any table from tkyte; Revoke succeeded.
SQL> _at_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 CorporationReceived on Wed Jun 16 1999 - 14:17:01 CEST