doubt on GRANT

kumar_ngl · New User Joined: 05 Aug 2005 Posts: 50 Location: chennai

Dear Experts,

Plz clear my doubt......

i'm giving grant option to xxx

GRANT ALL PRIVILAGES ON KUMAR.EMP TO XXX WITH GRANT OPTION.

xxx is granting to YYY

GRANT SELECT ON KUMAR.EMP TO YYY.

some time later ....KUMAR is revoking the GRANT from XXX
is it possible, if so will the YYY retain the grant on select kumar.emp.

its urgent...

kumar.p.v

guptae · Posted: Thu Dec 29, 2005 1:11 pm

kumar_ngl · New User Joined: 05 Aug 2005 Posts: 50 Location: chennai

dear guptae,

I didn;t get the clear pic about your answer "No"..Please gimme a detailed explantaion if possible...thanx

kumar.p.v

anilbatta · New User Joined: 13 Nov 2005 Posts: 22

Hi

I Think the GRANT permissions will be given only by the Database administrator and not be given by the users.

Suppose, if you are the Database Administrator, then you can grant the permission like this:

GRANT SELECT ALL FROM KUMAR.EMP to TTT
IN (GRANT SELECT FROM KUMAR.EMP TO YYY);

Try this out and let's check whether this will work or not.
And moreover be clear on the question you provide to the users.

Regards
Anilbatta

nikyojin · New User Joined: 05 Oct 2005 Posts: 94

Hi Kumar,
Only the System Database Administrator has the Highest privilege to provide "Grant Authority".
But as per ur query suppose Kumar is the DB Admin. Hence if Kumar revokes the Grant option from TTT all the Privileges provided by TTT will be revoked. So YYY will not retain the select option.
This is my logical explanation....Let's hope we get some further help from our forum friends.....

yeshao · New User Joined: 28 Dec 2005 Posts: 2

Hi

The WITH GRANT OPTION clause of the GRANT statement allows an ID to pass the granted privilege to others. If the privilege is removed from the ID, its deletion can cascade to others. When a privilege is removed from authorization ID TTT, it is also removed from any ID to which TTT granted it, unless that ID also has the privilege from some other source.
An ID with SYSADM or SYSCTRL authority can revoke a privilege that has been granted by another ID.