{"id":1980,"date":"2014-02-06T11:00:53","date_gmt":"2014-02-06T16:00:53","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1980"},"modified":"2014-11-13T13:18:28","modified_gmt":"2014-11-13T18:18:28","slug":"revoke-cascade-revoking-entire-grant-hierarchies","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/","title":{"rendered":"REVOKE CASCADE: Revoking an Entire Grant Hierarchy"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nAfter writing about the post about the CASCADE<\/span> keyword on the REVERT<\/span> statement (How to REVOKE a GRANT ... WITH GRANT OPTION<\/a>) a few days ago I received the following question:\n<\/p>\n

\nDoes the CASECADE<\/span> keyword imply that permissions over multiple levels will be removed?\n<\/p>\n

\nIt certainly sounds like that, so let's take a look.\n<\/p>\n

Creating a Hierarchy of Grants<\/h3>\n

\nFirst 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:\n<\/p>\n

\n\"Multi<\/a>\n<\/p>\n

\nAs you can see, it is possible. After we granted the SELECT<\/span> privilege WITH GRANT OPTION<\/span> to TestUser1<\/span>, TestUser1<\/span> was able to grant that same permission, again WITH GRANT OPTION<\/span> to TestUser2<\/span> who in turn granted it to TestUser3<\/span>.\n<\/p>\n

Revoking a Hierarchy of Grants<\/h3>\n

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

\n\"Cascading<\/a>\n<\/p>\n

\nIt did. So the CASCADE<\/span> keyword indeed implies that SQL Server will automatically traverse a multilevel GRANT<\/span> hierarchy and remove all permissions that were granted building on the root permission, the one we are revoking directly.\n<\/p>\n

Summary<\/h3>\n

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

\nWhen I wrote the post about delegating your permission management<\/a>, I suggested that the use of WITH GRANT OPTION<\/span> 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.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Does the CASCADE keyword on the REVOKE statement imply that you can build an entire hierarchy of grantable grants using GRANT … WITH GRANT OPTION? What does that mean for your permission management plan? Find out now.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,34],"tags":[88,50,98,100,38,58,15],"class_list":["post-1980","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-grant","tag-permission","tag-revoke","tag-revoke-cascade","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nREVOKE CASCADE: Revoking an Entire Grant Hierarchy - sqlity.net<\/title>\n<meta name=\"description\" content=\"A grantable grant ca be used to build an entire grant hierarchy. But be careful, revoking the root perission will cause the entire hierarchy to get revoked.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"REVOKE CASCADE: Revoking an Entire Grant Hierarchy - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"A grantable grant ca be used to build an entire grant hierarchy. But be careful, revoking the root perission will cause the entire hierarchy to get revoked.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-02-06T16:00:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:18:28+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Multi_Level_Grant.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"REVOKE CASCADE: Revoking an Entire Grant Hierarchy\",\"datePublished\":\"2014-02-06T16:00:53+00:00\",\"dateModified\":\"2014-11-13T18:18:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/\"},\"wordCount\":440,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Multi_Level_Grant.jpg\",\"keywords\":[\"GRANT\",\"Permission\",\"REVOKE\",\"REVOKE CASCADE\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/\",\"name\":\"REVOKE CASCADE: Revoking an Entire Grant Hierarchy - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Multi_Level_Grant.jpg\",\"datePublished\":\"2014-02-06T16:00:53+00:00\",\"dateModified\":\"2014-11-13T18:18:28+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"A grantable grant ca be used to build an entire grant hierarchy. But be careful, revoking the root perission will cause the entire hierarchy to get revoked.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Multi_Level_Grant.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Multi_Level_Grant.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1980\\\/revoke-cascade-revoking-entire-grant-hierarchies\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"REVOKE CASCADE: Revoking an Entire Grant Hierarchy\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"REVOKE CASCADE: Revoking an Entire Grant Hierarchy - sqlity.net","description":"A grantable grant ca be used to build an entire grant hierarchy. But be careful, revoking the root perission will cause the entire hierarchy to get revoked.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/","og_locale":"en_US","og_type":"article","og_title":"REVOKE CASCADE: Revoking an Entire Grant Hierarchy - sqlity.net","og_description":"A grantable grant ca be used to build an entire grant hierarchy. But be careful, revoking the root perission will cause the entire hierarchy to get revoked.","og_url":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-06T16:00:53+00:00","article_modified_time":"2014-11-13T18:18:28+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Multi_Level_Grant.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"REVOKE CASCADE: Revoking an Entire Grant Hierarchy","datePublished":"2014-02-06T16:00:53+00:00","dateModified":"2014-11-13T18:18:28+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/"},"wordCount":440,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Multi_Level_Grant.jpg","keywords":["GRANT","Permission","REVOKE","REVOKE CASCADE","security","security management","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/","url":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/","name":"REVOKE CASCADE: Revoking an Entire Grant Hierarchy - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Multi_Level_Grant.jpg","datePublished":"2014-02-06T16:00:53+00:00","dateModified":"2014-11-13T18:18:28+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"A grantable grant ca be used to build an entire grant hierarchy. But be careful, revoking the root perission will cause the entire hierarchy to get revoked.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Multi_Level_Grant.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Multi_Level_Grant.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1980\/revoke-cascade-revoking-entire-grant-hierarchies\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"REVOKE CASCADE: Revoking an Entire Grant Hierarchy"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-vW","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1980","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1980"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1980\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1980"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1980"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1980"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}