1

Let's suppose a user, "Bob", gets SELECT privilege from John and Bob also gets SELECT privilege from another user Joe but this time with GRANT option.

What happens if someone revokes the select privilege with cascade? Will the select privilege be removed from Bob's account and all the accounts that Bob gave the privilege to (even if they had it before from somewhere else?)?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
pi012
  • 13
  • 2
  • I recommend you ask this on Serverfault. StackOverflow is for programming questions, and SF is for sysadmins. I think the knowledge there may be more inline with this question. – JNK Jul 05 '11 at 20:37

1 Answers1

0

Yes, it will. Taken from http://msdn.microsoft.com/en-us/library/ms187728(v=sql.90).aspx

CASCADE

Indicates that the permission that is being revoked is also revoked from other principals to which it has been granted by this principal. When you are using the CASCADE argument, you must also include the GRANT OPTION FOR argument.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Doesn't address the point of his question, though, which is basically `If Bob has SELECT permission from 2 users, will a CASCADE revoke from one of those remove his permission ENTIRELY` – JNK Jul 05 '11 at 20:32
  • That will be it right? If Bob's select priv is revoked then the priv will be revoked from him as well as to those account where he have granted select. – Rahul Jul 05 '11 at 20:35
  • Yes I did read that before, but what if the user that Bob grants the select privilege already has it from being part of a group? There would be a conflict and I don't know what would happen.. On msdn I read that the more restricting command will be applied.. So if this is true, now the whole group looses the Select privilege? It seems crazy to me.. I must have misunderstood something.. – pi012 Jul 05 '11 at 20:36
  • No, I think it's not, confusing. On REVOKE permission will be effect in BOB's account and the BOB_GIVEN_USER account. Why the other group from which also BOB has got the privilege should get affected. – Rahul Jul 05 '11 at 20:40
  • "Why the other group from which also BOB has got the privilege should get affected." I didn't mean the group Bob was a part of.. I mean the group of the user that Bob granted the Select permission to.. If Jack is part of a group that has the select and Bob gives the permission to Jack again.. After Bob's Select privileges are rejected will Jack's group still have the privilege ? I forgot to add a to in the other post sorry.. – pi012 Jul 05 '11 at 20:45
  • Well, as per MSDN if BOB's gone then JACK's will also be gone. That's what the document says ... right. – Rahul Jul 05 '11 at 20:47
  • Ok, and the group? Jack is still part of the group.. What will the other members still have the Select privilege ? – pi012 Jul 05 '11 at 20:49
  • @Rahul - Bob gets permissions from Jack. Bob also gets permissions from Pam. If Jack **ONLY** has his permissions revoked, will Bob still have permissions? One of his grantors has been revoked but the other has not. That's the question/issue. – JNK Jul 05 '11 at 20:49
  • @JNK and @pi012, not sure unless I could try it somewhere. Per MSDN `if JACK's revoked then Bob's also will be revokes` cause of CASCADE but here the situation is CORELATED. No idea. – Rahul Jul 05 '11 at 20:56
  • @pi012, please let me know what the answer you get. sounds, interesting to me. – Rahul Jul 05 '11 at 21:04