{"id":2069,"date":"2014-02-16T11:00:41","date_gmt":"2014-02-16T16:00:41","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2069"},"modified":"2014-11-13T13:16:31","modified_gmt":"2014-11-13T18:16:31","slug":"deny-cascade","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/","title":{"rendered":"DENY CASCADE does not CASCADE the DENY"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nOne of the more rarely used forms of the DENY<\/span> statement is DENY CASCADE<\/span>. I have to admit that you would need to use it only in kind of an edge case. However, if you ever come across it, it does not actually do what it says is does.\n<\/p>\n

DENY CASCADE Example<\/h3>\n

\nTo see what it is actually doing let's look at an example. First we need two users, a schema and a table:\n<\/p>\n

\n[sql]\nCREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
\nCREATE LOGIN TestLogin2 WITH PASSWORD='********', CHECK_POLICY = OFF;<\/p>\n

CREATE USER TestUser1 FOR LOGIN TestLogin1;
\nCREATE USER TestUser2 FOR LOGIN TestLogin2;
\nGO
\nCREATE SCHEMA TestSchema1;
\nGO
\nCREATE TABLE TestSchema1.tst(id INT);
\nINSERT INTO TestSchema1.tst VALUES(42);
\n[\/sql]\n<\/p><\/div>\n

\nThe DENY CASCADE<\/span> statement looks similar to the REVOKE CASCADE<\/span> statement that I described two weeks ago in How to REVOKE a GRANT \u2026 WITH GRANT OPTION<\/a>. It seems to have to do with permissions that where granted based on a grantable grant. So to start out we are going to GRANT SELECT WITH GRANT OPTION<\/span> on our table to TestUser1<\/span> and have TestUser1<\/span> then GRANT SELECT<\/span> to TestUser2<\/span>:\n<\/p>\n

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

\nWe know already that we cannot just call REVOKE<\/span> on the grantable permission but instead have to use REVOKE CASCADE<\/span>. Let's try if we can simply DENY<\/span> the permission:\n<\/p>\n

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

\nSQL Server does throw the same error message for both REVOKE<\/span> and DENY<\/span> when used without the CASCADE<\/span> keyword against a grantable permission. So let's try to use it with that keyword:\n<\/p>\n

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

\nIt actually did invalidate the permission of TestUser2<\/span> too. So the CASCADE<\/span> keyword does cause some kind of cascading. But what does it actually do with those child level permissions? If you just go by the sound of the statement itself, you are probably thinking that it denies the permission to TestUser1<\/span> and all users that TestUser1<\/span> has granted it to. However, that is not the case.\n<\/p>\n

\nDENY CASCADE<\/span> merely executes a REVOKE CASCADE<\/span> followed by a simple DENY<\/span>. The DENY<\/span> itself does not get cascaded.\n<\/p>\n

\nTo confirm that, we need to use an indirect testing technique. In my part 3 of my series comparing the DENY<\/span> and REVOKE<\/span> statements we talked about Hierarchies of securables<\/a>. There we saw that a GRANT<\/span> on say a schema gets overridden by a DENY<\/span> on the table itself.\n<\/p>\n

\nWe can re-confirm that quickly by granting SELECT<\/span> on TestSchema1<\/span> to TestUser1<\/span>:\n<\/p>\n

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

\nAs expected, that GRANT<\/span> did not cause TestUser1<\/span> to be able to access the table. Now, if the SELECT<\/span> privilege was actually denied to TestUser2<\/span> as well, we should see the same behavior when granting SELECT<\/span> on the schema to TestUser2<\/span>. Let's try:\n<\/p>\n

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

\nAs you can see, a simple GRANT SELECT<\/span> on TestSchema1<\/span> to TestUser2<\/span> gives TestUser2<\/span> access to the table again. That means, what the DENY CASCADE<\/span> did to the SELECT<\/span> privilege of TestUser2<\/span> cannot have been a DENY<\/span>, only a REVOKE<\/span>.\n<\/p>\n

Summary<\/h3>\n

\nWhile DENY CASCADE<\/span> might sound like the DENY<\/span> is being cascaded, that is not actually the case. Instead it just acts like a REVOKE CASCADE<\/span> followed by a simple DENY<\/span> without the CASCADE<\/span> option.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

What the DENY CASCADE statement does seems obvious. But is it? Read on to see what DENY CASCADE actually does and if that matches your expectation.<\/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":[103,113,88,98,100,38,58,15],"class_list":["post-2069","post","type-post","status-publish","format-standard","hentry","category-general","category-security","category-security-pitfalls","tag-deny","tag-deny-cascade","tag-grant","tag-revoke","tag-revoke-cascade","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nDENY CASCADE does not CASCADE the DENY - sqlity.net<\/title>\n<meta name=\"description\" content=\"What the DENY CASCADE statement does seems obvious. But is it? Read on to see what DENY CASCADE actually does and if that matches your expectation.\" \/>\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\/2069\/deny-cascade\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DENY CASCADE does not CASCADE the DENY - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"What the DENY CASCADE statement does seems obvious. But is it? Read on to see what DENY CASCADE actually does and if that matches your expectation.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/\" \/>\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-16T16:00:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:16:31+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/grantable_GRANT_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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"DENY CASCADE does not CASCADE the DENY\",\"datePublished\":\"2014-02-16T16:00:41+00:00\",\"dateModified\":\"2014-11-13T18:16:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/\"},\"wordCount\":625,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/grantable_GRANT_in_action.jpg\",\"keywords\":[\"DENY\",\"DENY CASCADE\",\"GRANT\",\"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\\\/2069\\\/deny-cascade\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/\",\"name\":\"DENY CASCADE does not CASCADE the DENY - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/grantable_GRANT_in_action.jpg\",\"datePublished\":\"2014-02-16T16:00:41+00:00\",\"dateModified\":\"2014-11-13T18:16:31+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"What the DENY CASCADE statement does seems obvious. But is it? Read on to see what DENY CASCADE actually does and if that matches your expectation.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/grantable_GRANT_in_action.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/grantable_GRANT_in_action.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2069\\\/deny-cascade\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DENY CASCADE does not CASCADE the DENY\"}]},{\"@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":"DENY CASCADE does not CASCADE the DENY - sqlity.net","description":"What the DENY CASCADE statement does seems obvious. But is it? Read on to see what DENY CASCADE actually does and if that matches your expectation.","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\/2069\/deny-cascade\/","og_locale":"en_US","og_type":"article","og_title":"DENY CASCADE does not CASCADE the DENY - sqlity.net","og_description":"What the DENY CASCADE statement does seems obvious. But is it? Read on to see what DENY CASCADE actually does and if that matches your expectation.","og_url":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-16T16:00:41+00:00","article_modified_time":"2014-11-13T18:16:31+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/grantable_GRANT_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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"DENY CASCADE does not CASCADE the DENY","datePublished":"2014-02-16T16:00:41+00:00","dateModified":"2014-11-13T18:16:31+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/"},"wordCount":625,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/grantable_GRANT_in_action.jpg","keywords":["DENY","DENY CASCADE","GRANT","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\/2069\/deny-cascade\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/","url":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/","name":"DENY CASCADE does not CASCADE the DENY - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/grantable_GRANT_in_action.jpg","datePublished":"2014-02-16T16:00:41+00:00","dateModified":"2014-11-13T18:16:31+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"What the DENY CASCADE statement does seems obvious. But is it? Read on to see what DENY CASCADE actually does and if that matches your expectation.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2069\/deny-cascade\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/grantable_GRANT_in_action.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/grantable_GRANT_in_action.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2069\/deny-cascade\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"DENY CASCADE does not CASCADE the DENY"}]},{"@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-xn","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2069","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=2069"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2069\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2069"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2069"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2069"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}