{"id":1967,"date":"2014-02-04T11:00:56","date_gmt":"2014-02-04T16:00:56","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1967"},"modified":"2014-11-13T13:18:41","modified_gmt":"2014-11-13T18:18:41","slug":"revoke-grant-with-grant-option","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/","title":{"rendered":"How to REVOKE a GRANT … WITH GRANT OPTION"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nA few days ago we investigated the special WITH GRANT OPTION<\/span> syntax of the GRANT<\/span> statement<\/a> that can be used to delegate permission management. Today I want to look at what happens when you are trying to REVOKE<\/span> a permission that was granted using WITH GRANT OPTION<\/span>.\n<\/p>\n

WITH GRANT OPTION Example<\/h3>\n

\nJust as a quick refresher, if you grant a permission specifying WITH GRANT OPTION<\/span> to a principal, like this:\n<\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser1 WITH GRANT OPTION;
\n[\/sql]\n<\/div>\n

\nThat principal can turn around and grant that same permission to someone else:\n<\/p>\n

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

\nAs you can see, TestUser1<\/span> was able to GRANT<\/span> the SELECT<\/span> permission to TestUser2<\/span>. Now let's try to REVOKE<\/span> that SELECT<\/span> privilege from TestUser1<\/span> again.\n<\/p>\n

Revoking a Grantable Permission<\/h3>\n

\nWhen asked the question of how to revoke a grantable permission, meaning a permission that was granted specifying WITH GRANT OPTION<\/span>, the solution that comes to mid first is to just execute a straight forward REVOKE<\/span>. However, I wouldn't write a post about this, if it were that simple. Let's try it out:\n<\/p>\n

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

\nThe error says that we have to specify the CASCADE<\/span> option when revoking a grantable permission.\n<\/p>\n

\nA quick check in the manual reveals that the syntax of the CASCADE<\/span> option is very unassuming:\n<\/p>\n

\n[sql]\nREVOKE SELECT ON OBJECT::dbo.tst FROM TestUser1 CASCADE;
\n[\/sql]\n<\/div>\n

\nThe CASCADE<\/span> key word is just appended to the end of the REVOKE<\/span> statement. The effect this addition has however is everything but unassuming. It specifies that all permissions granted based on the permission to be removed should be removed too. And, as you can see in the error message above, it is not optional. To understand what this means let's look at an example.\n<\/p>\n

REVOKE with CASCADE<\/h3>\n

\nIn the first example in this post we had granted TestUser1<\/span> a grantable SELECT<\/span> privilege. TestUser1<\/span> then granted the same privilege to TestUser2<\/span>. Now let's execute a cascading REVOKE<\/span> and watch TestUser2<\/span> closely:\n<\/p>\n

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

\nAs you can see, even though the REVOKE<\/span> statement specified to revoke from TestUser1<\/span>, the permission that was granted to TestUser2<\/span> was removed too.\n<\/p>\n

\nKeep that in mind when planning to delegate your permission management. If the person you are delegating to ever falls in disgrace, you cannot revoke the grantable permission without undoing all the work that was done based on it.\n<\/p>\n

Summary<\/h3>\n

\nWhen revoking a permission that was granted specifying WITH GRANT OPTION<\/span>, SQL Server forces us to specify the CASCADE<\/span> keyword. That causes all permissions that had been granted based on the one we are revoking to be revoked too.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

When revoking a permission that was granted with the WITH GRANT OPTION, you cannot just revoke the privilege itself. Instead SQL Server forces us to remove all permissions that were granted based on it too. Read on to see how this might impact your permission management.<\/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-1967","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":"\nHow to REVOKE a GRANT ... WITH GRANT OPTION - sqlity.net<\/title>\n<meta name=\"description\" content=\"When revoking a permission that was granted with the WITH GRANT OPTION, SQL Server forces us to remove all permissions that were granted based on it too.\" \/>\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\/1967\/revoke-grant-with-grant-option\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to REVOKE a GRANT ... WITH GRANT OPTION - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"When revoking a permission that was granted with the WITH GRANT OPTION, SQL Server forces us to remove all permissions that were granted based on it too.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/\" \/>\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-04T16:00:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:18:41+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/GRANT_WITH_GRANT_OPTION_in_action.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\\\/1967\\\/revoke-grant-with-grant-option\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to REVOKE a GRANT … WITH GRANT OPTION\",\"datePublished\":\"2014-02-04T16:00:56+00:00\",\"dateModified\":\"2014-11-13T18:18:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/\"},\"wordCount\":493,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/GRANT_WITH_GRANT_OPTION_in_action.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\\\/1967\\\/revoke-grant-with-grant-option\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/\",\"name\":\"How to REVOKE a GRANT ... WITH GRANT OPTION - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/GRANT_WITH_GRANT_OPTION_in_action.jpg\",\"datePublished\":\"2014-02-04T16:00:56+00:00\",\"dateModified\":\"2014-11-13T18:18:41+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"When revoking a permission that was granted with the WITH GRANT OPTION, SQL Server forces us to remove all permissions that were granted based on it too.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/GRANT_WITH_GRANT_OPTION_in_action.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/GRANT_WITH_GRANT_OPTION_in_action.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1967\\\/revoke-grant-with-grant-option\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to REVOKE a GRANT … WITH GRANT OPTION\"}]},{\"@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":"How to REVOKE a GRANT ... WITH GRANT OPTION - sqlity.net","description":"When revoking a permission that was granted with the WITH GRANT OPTION, SQL Server forces us to remove all permissions that were granted based on it too.","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\/1967\/revoke-grant-with-grant-option\/","og_locale":"en_US","og_type":"article","og_title":"How to REVOKE a GRANT ... WITH GRANT OPTION - sqlity.net","og_description":"When revoking a permission that was granted with the WITH GRANT OPTION, SQL Server forces us to remove all permissions that were granted based on it too.","og_url":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-04T16:00:56+00:00","article_modified_time":"2014-11-13T18:18:41+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/GRANT_WITH_GRANT_OPTION_in_action.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\/1967\/revoke-grant-with-grant-option\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to REVOKE a GRANT … WITH GRANT OPTION","datePublished":"2014-02-04T16:00:56+00:00","dateModified":"2014-11-13T18:18:41+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/"},"wordCount":493,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/GRANT_WITH_GRANT_OPTION_in_action.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\/1967\/revoke-grant-with-grant-option\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/","url":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/","name":"How to REVOKE a GRANT ... WITH GRANT OPTION - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/GRANT_WITH_GRANT_OPTION_in_action.jpg","datePublished":"2014-02-04T16:00:56+00:00","dateModified":"2014-11-13T18:18:41+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"When revoking a permission that was granted with the WITH GRANT OPTION, SQL Server forces us to remove all permissions that were granted based on it too.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/GRANT_WITH_GRANT_OPTION_in_action.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/GRANT_WITH_GRANT_OPTION_in_action.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1967\/revoke-grant-with-grant-option\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to REVOKE a GRANT … WITH GRANT OPTION"}]},{"@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-vJ","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1967","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=1967"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1967\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}