{"id":1986,"date":"2014-02-07T11:00:16","date_gmt":"2014-02-07T16:00:16","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1986"},"modified":"2014-11-13T13:18:20","modified_gmt":"2014-11-13T18:18:20","slug":"revoke-cascade-not-working-for-control","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/","title":{"rendered":"REVOKE CASCADE does not work on CONTROL permission"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nOver the last few days we looked in detail at the effect that the CASCADE<\/span> keyword has on the REVOKE<\/span> statement, for example in How to REVOKE a GRANT ... WITH GRANT OPTION<\/a>.\n<\/p>\n

\nToday I would like to point you to a case in which the CASCADE<\/span> keyword does not have the expected effect on the permissions hierarchy. In fact, in this special case it does not have any effect at all. The case I am talking about is the CONTROL<\/span> privilege.\n<\/p>\n

The CONTROL privilege<\/h3>\n

\nThe CONTROL<\/span> privilege is the highest privilege that can be granted on any securable in SQL Server. It gives the grantee full \"control\" over the securable. In particular, it implies any other privilege. You can grant CONTROL<\/span> on almost any securable. One notable exception is that you can't grant it on a column.\n<\/p>\n

\nOne of the things you can do if you have been granted CONTROL<\/span> on a securable is granting any permission on that same securable to any other principal.\n<\/p>\n

\nAs we have seen before, if a permission is revoked in SQL Server, the default behavior is to also revoke all permissions that were granted based on that specific permission. This is indicated by the CASCADE<\/span> keyword, which is in most cases required. Let's see however what happens with the CONTROL<\/span> permission:\n<\/p>\n

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

\nTestUser1<\/span> was granted CONTROL<\/span> on dbo.tst1<\/span>. Armed with that permission TestUser1<\/span> then granted SELECT<\/span> on the table to TestUser2<\/span>. Afterwards the CONTROL<\/span> permission is revoked from TestUser1<\/span>. However, Testuser2<\/span> is still able to access the table.\n<\/p>\n

Now, the first thing to note is that the CASCADE<\/span> keyword was not specified. It obviously was not required. Maybe the problem is that the child permission was not actually CONTROL<\/span> but rather SELECT<\/span>. Let's see what happens if TestUser1<\/span> grants CONTROL<\/span> to TestUser2<\/span> instead:\n<\/p>\n

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

\nNo luck - the child permission did not get removed and the CASCADE<\/span> keyword seems still to be optional. Maybe actually specifying CASCADE<\/span> will help? Let's see:\n<\/p>\n

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

\nStill no luck. Even though we asked SQL Server explicitly - by specifying the CASCADE<\/span> keyword - to remove all child permissions when revoking TestUser1<\/span>'s permission, the CONTROL<\/span> privilege was not removed from TestUser2<\/span>.\n<\/p>\n

\nThere is one final option we can try. All the examples in my prior posts for which CASCADE<\/span><\/a> was working were looking at permissions that were granted specifying WITH GRANT OPTION<\/span>. So let's give that a try:\n<\/p>\n

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

\nAs you can see, even this last option does not work. So we can take away from this execise, that the CASCADE<\/span> keyword does not work when revoking the CONTROL<\/span> permission.\n<\/p>\n

Summary<\/h3>\n

\nWhen revoking a grantable permission SQL Server forces us to remove all child permissions as well. However, in the case of the CONTROL<\/span> privilege, this is not the case. We cannot even ask SQL Server to automatically revoke child permissions. Instead, in this case the CASCADE<\/span> keyword on the REVOKE<\/span> statement does not have any effect.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

REVOKE together with the CASCADE keyword removes a permission and also all child permissions that were granted based on it. Read on to see how this plays out if the permission in question is the CONTROL privilege.<\/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_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[5,34,99],"tags":[101,88,50,102,98,100,38,58,15],"class_list":["post-1986","post","type-post","status-publish","format-standard","hentry","category-general","category-security","category-security-pitfalls","tag-control-permission","tag-grant","tag-permission","tag-permission-hierarchy","tag-revoke","tag-revoke-cascade","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nREVOKE CASCADE does not work on CONTROL permission - sqlity.net<\/title>\n<meta name=\"description\" content=\"REVOKE CASCADE is supposed to automatically revoke all child permission of the revoked permission. However, for the CONTROL privilege this does not work.\" \/>\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\/1986\/revoke-cascade-not-working-for-control\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"REVOKE CASCADE does not work on CONTROL permission - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"REVOKE CASCADE is supposed to automatically revoke all child permission of the revoked permission. However, for the CONTROL privilege this does not work.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/\" \/>\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-07T16:00:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:18:20+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"REVOKE CASCADE does not work on CONTROL permission\",\"datePublished\":\"2014-02-07T16:00:16+00:00\",\"dateModified\":\"2014-11-13T18:18:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/\"},\"wordCount\":569,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg\",\"keywords\":[\"CONTROL permission\",\"GRANT\",\"Permission\",\"permission hierarchy\",\"REVOKE\",\"REVOKE CASCADE\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\",\"Security Pitfalls\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/\",\"name\":\"REVOKE CASCADE does not work on CONTROL permission - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg\",\"datePublished\":\"2014-02-07T16:00:16+00:00\",\"dateModified\":\"2014-11-13T18:18:20+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"REVOKE CASCADE is supposed to automatically revoke all child permission of the revoked permission. However, for the CONTROL privilege this does not work.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1986\\\/revoke-cascade-not-working-for-control\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"REVOKE CASCADE does not work on CONTROL permission\"}]},{\"@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 does not work on CONTROL permission - sqlity.net","description":"REVOKE CASCADE is supposed to automatically revoke all child permission of the revoked permission. However, for the CONTROL privilege this does not work.","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\/1986\/revoke-cascade-not-working-for-control\/","og_locale":"en_US","og_type":"article","og_title":"REVOKE CASCADE does not work on CONTROL permission - sqlity.net","og_description":"REVOKE CASCADE is supposed to automatically revoke all child permission of the revoked permission. However, for the CONTROL privilege this does not work.","og_url":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-07T16:00:16+00:00","article_modified_time":"2014-11-13T18:18:20+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"REVOKE CASCADE does not work on CONTROL permission","datePublished":"2014-02-07T16:00:16+00:00","dateModified":"2014-11-13T18:18:20+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/"},"wordCount":569,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg","keywords":["CONTROL permission","GRANT","Permission","permission hierarchy","REVOKE","REVOKE CASCADE","security","security management","SQL Server"],"articleSection":["General","Security","Security Pitfalls"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/","url":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/","name":"REVOKE CASCADE does not work on CONTROL permission - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg","datePublished":"2014-02-07T16:00:16+00:00","dateModified":"2014-11-13T18:18:20+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"REVOKE CASCADE is supposed to automatically revoke all child permission of the revoked permission. However, for the CONTROL privilege this does not work.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/REVOKE_CONTROL_does_not_revoke_child_SELECT_permission.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1986\/revoke-cascade-not-working-for-control\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"REVOKE CASCADE does not work on CONTROL permission"}]},{"@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_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-w2","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1986","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=1986"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1986\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1986"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1986"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1986"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}