{"id":2051,"date":"2014-02-14T11:00:39","date_gmt":"2014-02-14T16:00:39","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2051"},"modified":"2014-11-13T13:16:42","modified_gmt":"2014-11-13T18:16:42","slug":"deny-vs-revoke-5","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/","title":{"rendered":"DENY vs. REVOKE – Part 5 – Durability"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nWhen durability is mentioned in the context of a relational database management system it usually refers to one of the ACID properties<\/a>. However, in this post it means something different.\n<\/p>\n

\nOver the last few days we have seen a few cases where a DENY<\/span> had a different effect than a REVOKE<\/span>. All had to do with hierarchies. But there is something more fundamental that is different between the two. It is how SQL Server handles the request internally.\n<\/p>\n

Permission Durability<\/h3>\n

\nWhen a GRANT<\/span> in executed, SQL Server creates a new entry in its internal permissions list. That entry there won't get removed until it gets either revoked or overwritten.\n<\/p>\n

\nWhen a REVOKE<\/span> is executed, SQL Server finds the matching entry in the internal permissions list and removes it. That's why it works only on permissions that have been granted exactly the same way the REVOKE<\/span> statement is written. If it was granted on a different securable, a different principal or with a different privilege, SQL Server will not be able to find the entry at the time the REVOKE<\/span> is executed and therefore won't remove it.\n<\/p>\n

\nThe DENY<\/span> statement is different. We have seen examples of that over the last few days. It can affect prior grants without the need to have an exact match. Under the covers that is achieved not by altering the existing granted permissions, but instead by recording the DENY<\/span> in the same permissions list.\n<\/p>\n

\nEvery time SQL Server needs to find out if a user (or login) has the permission to execute a given statement, it looks at that list and gathers all relevant denies. If it finds a matching one, the statement is terminated right away with a \"permission denied\" error. Remember, matching might mean that the deny was made against a different level of one of the security related hierarchies.\n<\/p>\n

\nOnly if no matching deny was found, the grants are checked. If a matching grant is found is statement is executed. Otherwise the same \"permission denied\" error is returned.\n<\/p>\n

Durable DENY Example<\/h3>\n

\nThe easiest way to see that a DENY<\/span> is indeed durable, is to execute it first, before any GRANT on that securable hierarchy was executed. To see that in action let's create a new table in a new schema and also create or usual user, TestUser1<\/span>:\n<\/p>\n

\n[sql]\nCREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
\nCREATE USER TestUser1 FOR LOGIN TestLogin1;
\nGO
\nCREATE SCHEMA TestSchema1;
\nGO
\nCREATE TABLE TestSchema1.tst(id INT);
\nINSERT INTO TestSchema1.tst VALUES(42);
\n[\/sql]\n<\/div>\n

\nNow, before anything else let's execute a DENY SELECT<\/span> on the schema. Even if that is directly followed by a GRANT SELECT<\/span> one the table, access will still be denied:\n<\/p>\n

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

\nNow, the durability goes only so far. If you execute a DENY<\/span> and then a matching GRANT<\/span> for the same permission (on the same securable for the same privilege to the same principal), the DENY<\/span> gets removed from the permissions list and replaced with the GRANT<\/span>. That is the reason why the example in the first part of this series about DENY<\/span> vs. REVOKE<\/span><\/a> which executed alternating GRANT<\/span> and DENY<\/span> statements showed the behavior it did.\n<\/p>\n

Summary<\/h3>\n

\nDurability in the context of permissions, particular the DENY<\/span> statement, means that a denied permission actually gets an entry in SQL Server's internal permissions list. Therefore it does not matter if the DENY<\/span> was executed before or after the GRANT<\/span> if they happened on different hierarchy levels. However, they do overwrite each other if executed for the same permission.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

A DENY is a durable permission, similar to a GRANT. Find out what that means and how it affects your SQL Server permissions 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_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},"jetpack_post_was_ever_published":false},"categories":[104,5,34,27],"tags":[103,275,88,50,98,38,58,15],"class_list":["post-2051","post","type-post","status-publish","format-standard","hentry","category-deny-vs-revoke","category-general","category-security","category-series","tag-deny","tag-deny-vs-revoke","tag-grant","tag-permission","tag-revoke","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nDENY vs. REVOKE - Part 5 - Durability - sqlity.net<\/title>\n<meta name=\"description\" content=\"A DENY is a durable permission. That means it gets recorded the same way as a GRANT. It even can precede a matching GRANT, as long as they aren't identical.\" \/>\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\/2051\/deny-vs-revoke-5\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DENY vs. REVOKE - Part 5 - Durability - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"A DENY is a durable permission. That means it gets recorded the same way as a GRANT. It even can precede a matching GRANT, as long as they aren't identical.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/\" \/>\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-14T16:00:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:16:42+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_is_durable.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\\\/2051\\\/deny-vs-revoke-5\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"DENY vs. REVOKE – Part 5 – Durability\",\"datePublished\":\"2014-02-14T16:00:39+00:00\",\"dateModified\":\"2014-11-13T18:16:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/\"},\"wordCount\":639,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_is_durable.jpg\",\"keywords\":[\"DENY\",\"DENY vs. REVOKE\",\"GRANT\",\"Permission\",\"REVOKE\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"DENY vs. REVOKE\",\"General\",\"Security\",\"Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/\",\"name\":\"DENY vs. REVOKE - Part 5 - Durability - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_is_durable.jpg\",\"datePublished\":\"2014-02-14T16:00:39+00:00\",\"dateModified\":\"2014-11-13T18:16:42+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"A DENY is a durable permission. That means it gets recorded the same way as a GRANT. It even can precede a matching GRANT, as long as they aren't identical.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_is_durable.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/DENY_is_durable.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2051\\\/deny-vs-revoke-5\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DENY vs. REVOKE – Part 5 – Durability\"}]},{\"@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 vs. REVOKE - Part 5 - Durability - sqlity.net","description":"A DENY is a durable permission. That means it gets recorded the same way as a GRANT. It even can precede a matching GRANT, as long as they aren't identical.","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\/2051\/deny-vs-revoke-5\/","og_locale":"en_US","og_type":"article","og_title":"DENY vs. REVOKE - Part 5 - Durability - sqlity.net","og_description":"A DENY is a durable permission. That means it gets recorded the same way as a GRANT. It even can precede a matching GRANT, as long as they aren't identical.","og_url":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-14T16:00:39+00:00","article_modified_time":"2014-11-13T18:16:42+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_is_durable.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\/2051\/deny-vs-revoke-5\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"DENY vs. REVOKE – Part 5 – Durability","datePublished":"2014-02-14T16:00:39+00:00","dateModified":"2014-11-13T18:16:42+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/"},"wordCount":639,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_is_durable.jpg","keywords":["DENY","DENY vs. REVOKE","GRANT","Permission","REVOKE","security","security management","SQL Server"],"articleSection":["DENY vs. REVOKE","General","Security","Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/","url":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/","name":"DENY vs. REVOKE - Part 5 - Durability - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_is_durable.jpg","datePublished":"2014-02-14T16:00:39+00:00","dateModified":"2014-11-13T18:16:42+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"A DENY is a durable permission. That means it gets recorded the same way as a GRANT. It even can precede a matching GRANT, as long as they aren't identical.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_is_durable.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/DENY_is_durable.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2051\/deny-vs-revoke-5\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"DENY vs. REVOKE – Part 5 – Durability"}]},{"@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-x5","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2051","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=2051"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2051\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}