| 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: grant update with grant option
A copy of this was sent to yewpc_at_rnd.celcom.com.my
(if that email address didn't require changing)
On Fri, 26 Nov 1999 01:03:13 GMT, you wrote:
>I actually tested this up in database and the result is
>with admin option when the grantor revokes privilege, dependent grants
>are not revoked but
>with grant option it is. when the grantor revokes privlege, it revokes
>all the dependent granted privilege.
>So the reference manual is correct or this is a bug in Oracle ?
>
From the Oracle documentation (SQL Reference, section on revokes, v7.3 and 8.0 doc set)
<quote>
Cascading Revokes FOR OBJECT PRIVS
Revoking an object privilege that a user has either granted or exercised to define an object or a referential integrity constraint has the following cascading effects:
o If you revoke an object privilege from a user who has granted the
privilege to other users or roles, Oracle7 also revokes the
privilege from the grantees.
</quote>
from the same manual, Oracle8i release 8.1:
<quote>
... object_priv is an object privilege to be revoked. You can substitute any of
the following values:
ALTER, DELETE, EXECUTE, INDEX, INSERT, READ, REFERENCES, SELECT, UPDATE.
o If you revoke a privilege from a user, Oracle removes the privilege from the
user’s privilege domain. Effective immediately, the user cannot exercise the
privilege.
o If that user has granted that privilege to other users or roles, Oracle also revokes the privilege from those other users or roles. </quote>
In short, the GRANTS granted recursively with the "grant option" ARE removed. The REVOKE documentation for SYSTEM PRIVS (where the with admin option applies) does not contain any of this type of material.
In short:
o with grant option = cascading revoke
o with admin option = non-cascading revoke
>
>In article <943556105.5514.0.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
>> with grant option gives the grantee the right to pass the grant on.
>> Relationships are NOT maintained.
they are maintained -- there is a grantee/grantor in the priv table.
>> Whenever the orginal grantor revokes privilege, dependent grants are
>NOT
>> revoked.
they are. privs granted with the ADMIN option (system privs, not object privs) are not revoked. those granted via the with grant option are..
>> For me this is one of the reason NEVER to use with grant option.
>> Hth
>>
here is a small test you can run to see this behaviour. we create three users A, B, and C.
all have connect, resource.
A has been granted create any procedure WITH ADMIN OPTION.
A gives B an object priv with the grant option. A gives B create any procedure with admin option.
B passes these two onto C.
C is able to give scott access to the object C has the create any procedure priv (as demonstrated by his ability to create a procedure in A's schema)
A revokes these 2 privs from B.
C has lost the ability to grant on the object
C has lost ability to see the object
C can *still* create any procedure......
drop user a cascade; drop user b cascade; drop user c cascade;
grant connect, resource to a identified by a; grant create any procedure to a with admin option;
grant connect, resource to b identified by b; grant connect, resource to c identified by c;
connect a/a
create table t ( x int );
grant all on t to b with grant option;
grant create any procedure to b with admin option;
connect b/b
grant all on a.t to c with grant option;
grant create any procedure to c with admin option;
connect c/c
desc a.t
grant all on a.t to scott;
revoke all on a.t from scott;
create procedure a.p1 as begin null; end;
/
connect a/a
revoke all on t from b;
revoke create any procedure from b;
connect c/c
grant all on a.t to scott;
grant all on a.t to scott with grant option;
create procedure a.p2 as begin null; end;
/
connect system/manager
drop user a cascade; drop user b cascade; drop user c cascade; ------------------------------------------------------>> > cascading effect to revoke privilege" this is not right right ?
>> --
>> Sybrand Bakker, Oracle DBA
>> <yewpc_at_rnd.celcom.com.my> wrote in message
>> news:81imvm$4a6$1_at_nnrp1.deja.com...
>> > Question,
>> > From the reference manual of OCP7 by Couchman.
>> > "The granting privileges with admin option or grant option has no
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
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 Fri Nov 26 1999 - 08:26:06 CST
 |
 |