REVOKE CASCADE: Revoking an Entire Grant Hierarchy

Introduction

After writing about the post about the CASCADE keyword on the REVERT statement (How to REVOKE a GRANT ... WITH GRANT OPTION) a few days ago I received the following question:

Does the CASECADE keyword imply that permissions over multiple levels will be removed?

It certainly sounds like that, so let's take a look.

Creating a Hierarchy of Grants

First we need to create a hierarchy of grants. To be able to do that we need a grantable permission to imply that the grantee can also grant a grantable permission. So far in all my examples the grantee only ever granted the permission itself. Lets' see if granting a multilevel grantable permission is even possible:

As you can see, it is possible. After we granted the SELECT privilege WITH GRANT OPTION to TestUser1, TestUser1 was able to grant that same permission, again WITH GRANT OPTION to TestUser2 who in turn granted it to TestUser3.

Revoking a Hierarchy of Grants

If we now revoked that permission from TestUser2 using the not optional CASCADE keyword, the permission would automatically be revoked from TestUser3 to as it was granted by TestUser2. The same is true between TestUser1 and TestUser2 as we have seen in my previous post. But if revoking the SELECT privilege from TestUser1 revokes it automatically from TestUser2 then we would expect TestUser3 to lose the permission too. Let's try:

It did. So the CASCADE keyword indeed implies that SQL Server will automatically traverse a multilevel GRANT hierarchy and remove all permissions that were granted building on the root permission, the one we are revoking directly.

Summary

The CASCADE keyword on the REVOKE statement does imply that a grantable GRANT can build a grant-hierarchy. We did confirm that this is indeed the case. We also showed that a REVOKE on the root of that hierarchy will automatically remove all permissions on all levels in that hierarchy.

When I wrote the post about delegating your permission management, I suggested that the use of WITH GRANT OPTION might be a way to simplify your life by delegating parts of the permission management to others. But the more we look at the way cascading revokes work, maybe the use of grantable grants should be an exception. Let me know your thoughts on this.