{"id":1958,"date":"2014-02-03T11:00:30","date_gmt":"2014-02-03T16:00:30","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1958"},"modified":"2014-11-13T13:18:46","modified_gmt":"2014-11-13T18:18:46","slug":"revoking-column-permissions","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/","title":{"rendered":"Security Pitfalls – Revoking Column Permissions"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nYesterday I wrote that the REVOKE<\/span><\/a> statement can only be used to revoke the exact permission that was granted before. There is however one notable exception to this and it has to do with column permissions. But before we investigate that exception further, let's look at the standard behavior.\n<\/p>\n

Mismatching REVOKE Example<\/h3>\n

\nFor this example we are going to first GRANT<\/span> the SELECT<\/span> privilege on the dbo<\/span> schema to TestUser1<\/span>. As we have seen in Using SQL Server Schema Permissions to Simplify Permission Management<\/a>, this will give the user SELECT<\/span> access to all tables in that schema. If we now REVOKE<\/span> the SELECT<\/span> privilege on a particular table, SQL Server won't find a matching (identical) permission to revoke and the statement returns without causing any effect. In particular, TestUser1<\/span> still has read access to that very table:\n<\/p>\n

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

\nThis is the documented and therefore expected behavior of the REVOKE<\/span> statement. It can only remove the exact permission that was granted before. Now let's look at a column permission example.\n<\/p>\n

Revoking a \"Permission Non-Granta\"<\/h3>\n

\nIf we GRANT<\/span> SELECT<\/span> on the id<\/span> column of dbo.tst1<\/span> like this:\n<\/p>\n

\n[sql]\nGRANT SELECT ON OBJECT::dbo.tst1(id) TO TestUser1;
\n[\/sql]\n<\/div>\n

\nAnd if we then REVOKE<\/span> the SELECT<\/span> privilege on the table itself like this:\n<\/p>\n

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

\nWe expect TestUser1<\/span> to still have SELECT access to the id<\/span> column as the two permissions involved did not match. To the contrary however, an access attempt by TestUser1<\/span> fails:\n<\/p>\n

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

\nSo, if you REVOKE<\/span> access on a table, any prior column level grants of the same privilege on the same table (to the same database principal) are also revoked.\n<\/p>\n

\nThat begs the question whether this oddity works the same way in the other direction. To check, we are going to GRANT<\/span> SELECT<\/span> access on the table itself and then REVOKE<\/span> that same access from one of the two columns:\n<\/p>\n

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

\nAnd, as you can see, while TestUser1<\/span> was still able to access the id<\/span> column, access to the column col1<\/span> was denied. To make sense of this special behavior, think of table level granting or revoking as being executed as a series of column level actions. While, as we have seen before, granting on a table and granting on all columns of that table is not the same<\/a>, in this situation it helps to assume it is.\n<\/p>\n

\nUnder the covers, to make this special behavior work, SQL Server is utilizing a mechanism that is not used anywhere else in the permission management logic. So while this looks like an oversight, we can assume that this is indeed intentional. I will write about the details of how this is accomplished in a later post.\n<\/p>\n

Summary<\/h3>\n

\nThe REVOKE<\/span> statement removes only permissions that have been granted exactly alike before. However, there is one exception. Column and table permissions interact in a different way; in fact they behave as if a table level grant or deny is always broken down into a series of column level grants or denies respectively.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

A REVOKE always removes only an exactly alike GRANT. However there is one important exception: The handling of column permissions follows its own rules. Read on to get all the details.<\/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":[88,50,98,38,58,15],"class_list":["post-1958","post","type-post","status-publish","format-standard","hentry","category-general","category-security","category-security-pitfalls","tag-grant","tag-permission","tag-revoke","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nSecurity Pitfalls - Revoking Column Permissions - sqlity.net<\/title>\n<meta name=\"description\" content=\"A REVOKE always removes only an exactly alike GRANT. However there is one important exception: The handling of column permissions follows its own rules.\" \/>\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\/1958\/revoking-column-permissions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Security Pitfalls - Revoking Column Permissions - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"A REVOKE always removes only an exactly alike GRANT. However there is one important exception: The handling of column permissions follows its own rules.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/\" \/>\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-03T16:00:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:18:46+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Revoking_mismatching_permissions_example.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\\\/1958\\\/revoking-column-permissions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Security Pitfalls – Revoking Column Permissions\",\"datePublished\":\"2014-02-03T16:00:30+00:00\",\"dateModified\":\"2014-11-13T18:18:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/\"},\"wordCount\":567,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Revoking_mismatching_permissions_example.jpg\",\"keywords\":[\"GRANT\",\"Permission\",\"REVOKE\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\",\"Security Pitfalls\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/\",\"name\":\"Security Pitfalls - Revoking Column Permissions - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Revoking_mismatching_permissions_example.jpg\",\"datePublished\":\"2014-02-03T16:00:30+00:00\",\"dateModified\":\"2014-11-13T18:18:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"A REVOKE always removes only an exactly alike GRANT. However there is one important exception: The handling of column permissions follows its own rules.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Revoking_mismatching_permissions_example.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/Revoking_mismatching_permissions_example.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1958\\\/revoking-column-permissions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Security Pitfalls – Revoking Column Permissions\"}]},{\"@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":"Security Pitfalls - Revoking Column Permissions - sqlity.net","description":"A REVOKE always removes only an exactly alike GRANT. However there is one important exception: The handling of column permissions follows its own rules.","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\/1958\/revoking-column-permissions\/","og_locale":"en_US","og_type":"article","og_title":"Security Pitfalls - Revoking Column Permissions - sqlity.net","og_description":"A REVOKE always removes only an exactly alike GRANT. However there is one important exception: The handling of column permissions follows its own rules.","og_url":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-03T16:00:30+00:00","article_modified_time":"2014-11-13T18:18:46+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Revoking_mismatching_permissions_example.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\/1958\/revoking-column-permissions\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Security Pitfalls – Revoking Column Permissions","datePublished":"2014-02-03T16:00:30+00:00","dateModified":"2014-11-13T18:18:46+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/"},"wordCount":567,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Revoking_mismatching_permissions_example.jpg","keywords":["GRANT","Permission","REVOKE","security","security management","SQL Server"],"articleSection":["General","Security","Security Pitfalls"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/","url":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/","name":"Security Pitfalls - Revoking Column Permissions - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Revoking_mismatching_permissions_example.jpg","datePublished":"2014-02-03T16:00:30+00:00","dateModified":"2014-11-13T18:18:46+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"A REVOKE always removes only an exactly alike GRANT. However there is one important exception: The handling of column permissions follows its own rules.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Revoking_mismatching_permissions_example.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/Revoking_mismatching_permissions_example.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1958\/revoking-column-permissions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Security Pitfalls – Revoking Column Permissions"}]},{"@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-vA","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1958","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=1958"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1958\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1958"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1958"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}