{"id":2009,"date":"2014-02-10T11:00:06","date_gmt":"2014-02-10T16:00:06","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2009"},"modified":"2014-11-13T13:17:22","modified_gmt":"2014-11-13T18:17:22","slug":"deny-vs-revoke-3","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/","title":{"rendered":"DENY vs. REVOKE – Part 3 – Hierarchies of Securables"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nIn this little series we are comparing the DENY<\/span> statement with the REVOKE<\/span> statement. Yesterday we looked at database and server roles and how the DENY<\/span> statement can help fine-tune your role based security management<\/a>.\n<\/p>\n

Today I would like to look at a different kind of hierarchy, the hierarchy of objects or securables.\n<\/p>\n

Object Hierarchy Example<\/h3>\n

\nIn SQL Server every object is part of a schema that in turn lives in a database which resides on the SQL Server instance. This provides a natural hierarchy that we can use for permission management. The example today is going to look at this type of database object hierarchy. However, there are other securable hierarchies and the principle discussed today works for those too.\n<\/p>\n

\nTo try out permission management in the context of an object hierarchy, we are going to first create a schema and a table within that schema (and also our usual user):\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 we are going to GRANT SELECT<\/span> on the table to TestUser1<\/span> and check that the table is now readable for this user:\n<\/p>\n

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

\nSo far we used just the GRANT<\/span> statement in its simplest form, and as you can see, there were no surprises.\n<\/p>\n

\nIf you followed along over the last few days, the next test might seem obvious to you, but for completeness I am going to include it here anyway. In this test we are going to use REVOKE<\/span> to revoke SELECT<\/span> on the schema from TestUser1<\/span>. However, as the permission was originally granted on the table and not on the schema, we expect the REVOKE<\/span> to have no effect on TestUser1<\/span>'s ability to access the table.\n<\/p>\n

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

\nAs expected, the REVOKE<\/span> did not do anything.\n<\/p>\n

\nNow it is time to see if the DENY<\/span> changes something:\n<\/p>\n

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

\nIt did. Because we denied access to the schema, TestUser1<\/span> was not able to select from the table anymore, even though we had explicitly granted access to that table before.\n<\/p>\n

\nAs with hierarchies of security principals, a DENY<\/span> anywhere in a hierarchy of securables overrules any number of grants on other levels in that hierarchy.\n<\/p>\n

\nThe example above you are not likely to encounter in the real world. The more common use-case is to grant a principal access to a schema but exclude that one important table. To achieve that you would execute GRANT ... ON SCHEMA::...<\/span> and then DENY ... ON OBJECT::...<\/span> . Either way, it is not important if the DENY<\/span> happened on a higher or lower level in the hierarchy than the GRANT<\/span>. The DENY<\/span> always overrules the GRANT<\/span>.\n<\/p>\n

Summary<\/h3>\n

\nSimilar to hierarchies of security principals, a DENY<\/span> anywhere in a hierarchy of securables like table, schema, database and server, overrides a GRANT<\/span> in other places of that hierarchy.\n<\/p>\n

DENY vs. REVOKE Series<\/h3>\n

\nThis post is part of a five-part series comparing the DENY<\/span> and the REVOKE<\/span> statements.
\nBelow is a list of links to the posts that are already available.\n<\/p>\n

\n
\n

DENY vs. REVOKE – Part 1 – Are they just synonyms?<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 2 – Hierarchies of Principals<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 3 – Hierarchies of Securables<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 4 – Hierarchies of Privileges<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY vs. REVOKE – Part 5 – Durability<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n<\/div>\n","protected":false},"excerpt":{"rendered":"

DENY can be used to fine tune permission management in hierarchies of securables. A common securable hierarchy is table, schema, database, server. Read on to see how a DENY on one level of such a hierarchy can override a GRANT on another level.<\/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":[104,5,34,27],"tags":[103,88,98,38,58,15],"class_list":["post-2009","post","type-post","status-publish","format-standard","hentry","category-deny-vs-revoke","category-general","category-security","category-series","tag-deny","tag-grant","tag-revoke","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nDENY vs. REVOKE - Part 3 - Hierarchies of Securables - sqlity.net<\/title>\n<meta name=\"description\" content=\"DENY can be used to fine tune permission management in hierarchies of securables. See how a schema level DENY can override an object level GRANT and vice versa.\" \/>\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\/2009\/deny-vs-revoke-3\/\" \/>\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 3 - Hierarchies of Securables - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"DENY can be used to fine tune permission management in hierarchies of securables. See how a schema level DENY can override an object level GRANT and vice versa.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/\" \/>\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-10T16:00:06+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:17:22+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Successful_GRANT_on_table.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\\\/2009\\\/deny-vs-revoke-3\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"DENY vs. REVOKE – Part 3 – Hierarchies of Securables\",\"datePublished\":\"2014-02-10T16:00:06+00:00\",\"dateModified\":\"2014-11-13T18:17:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/\"},\"wordCount\":607,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Successful_GRANT_on_table.jpg\",\"keywords\":[\"DENY\",\"GRANT\",\"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\\\/2009\\\/deny-vs-revoke-3\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/\",\"name\":\"DENY vs. REVOKE - Part 3 - Hierarchies of Securables - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Successful_GRANT_on_table.jpg\",\"datePublished\":\"2014-02-10T16:00:06+00:00\",\"dateModified\":\"2014-11-13T18:17:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"DENY can be used to fine tune permission management in hierarchies of securables. See how a schema level DENY can override an object level GRANT and vice versa.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Successful_GRANT_on_table.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Successful_GRANT_on_table.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2009\\\/deny-vs-revoke-3\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DENY vs. REVOKE – Part 3 – Hierarchies of Securables\"}]},{\"@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 3 - Hierarchies of Securables - sqlity.net","description":"DENY can be used to fine tune permission management in hierarchies of securables. See how a schema level DENY can override an object level GRANT and vice versa.","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\/2009\/deny-vs-revoke-3\/","og_locale":"en_US","og_type":"article","og_title":"DENY vs. REVOKE - Part 3 - Hierarchies of Securables - sqlity.net","og_description":"DENY can be used to fine tune permission management in hierarchies of securables. See how a schema level DENY can override an object level GRANT and vice versa.","og_url":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-10T16:00:06+00:00","article_modified_time":"2014-11-13T18:17:22+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Successful_GRANT_on_table.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\/2009\/deny-vs-revoke-3\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"DENY vs. REVOKE – Part 3 – Hierarchies of Securables","datePublished":"2014-02-10T16:00:06+00:00","dateModified":"2014-11-13T18:17:22+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/"},"wordCount":607,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Successful_GRANT_on_table.jpg","keywords":["DENY","GRANT","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\/2009\/deny-vs-revoke-3\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/","url":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/","name":"DENY vs. REVOKE - Part 3 - Hierarchies of Securables - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Successful_GRANT_on_table.jpg","datePublished":"2014-02-10T16:00:06+00:00","dateModified":"2014-11-13T18:17:22+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"DENY can be used to fine tune permission management in hierarchies of securables. See how a schema level DENY can override an object level GRANT and vice versa.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Successful_GRANT_on_table.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Successful_GRANT_on_table.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2009\/deny-vs-revoke-3\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"DENY vs. REVOKE – Part 3 – Hierarchies of Securables"}]},{"@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-wp","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2009","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=2009"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2009\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2009"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2009"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2009"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}