{"id":2291,"date":"2014-03-26T18:03:45","date_gmt":"2014-03-26T22:03:45","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2291"},"modified":"2014-11-13T15:23:19","modified_gmt":"2014-11-13T20:23:19","slug":"presentation-materials-permission-management-12-pitfalls-misconceptions","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/","title":{"rendered":"Presentation Materials: Permission Management – 12 Pitfalls & Misconceptions"},"content":{"rendered":"
\n

Abstract<\/h3>\n

\nSQL Server comes with a powerful built-in permission management system. However, as most powerful tools, this one too can be difficult to use and get right.\n<\/p>\n

\nThis is a two-part session. During the first part, we will go through a review of SQL Server security terms and concepts, particularly the GRANT, REVOKE and DENY statements. For the second part, the audience selects topics that we will cover in detail. You will be able to choose from 12 different security pitfalls and misconceptions. All these pitfalls\/misconceptions have one thing in common: If you are not aware of them, you might GRANT or DENY more permissions than you intended.\n<\/p>\n

Prerequisites<\/h3>\n

\nTo get the most out of this 200 level session, you should feel comfortable writing simple queries and creating basic tables and procedures.\n<\/p>\n

Materials<\/h3>\n

\nThe following is a list of posts about the Pitfalls & Misconceptions:\n<\/p>\n

\n

\n
\n

ALL PRIVILEGES \u2013 The Misleading Permissions Shortcut<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Is Granting on All Columns Really the Same as Granting on the Table Itself?<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

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

Debunking Symmetric Key Recreate-Ability<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n
\n
\n

Security Pitfalls – Revoking Column Permissions<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

REVOKE CASCADE does not work on CONTROL permission<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

DENY CASCADE does not CASCADE the DENY<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Security Pitfalls – Column Level GRANT overrides Table Level DENY<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

The Unexpected Side Effect of DENY CONTROL<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

The Unexpected Security Implications for Role Owners<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

CONTROL SERVER vs. sysadmin – Differences when Creating a Schema<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

An implicit user creation has failed.<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

The Ownership Chaining Quandary or How to Circumvent an Explicit DENY<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

OPEN SYMMETRIC KEY – Avoid the NULL Ciphertext Trap<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Your responsibilities as the DBA include the management of access permissions. To be able to live up to the expectation that you will be able to secure the data, you must know the 12 Pitfalls & Misconceptions that we will talk about in this session.<\/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":[11],"tags":[],"class_list":["post-2291","post","type-post","status-publish","format-standard","hentry","category-presentations"],"acf":[],"yoast_head":"\nPresentation Materials: Permission Management - 12 Pitfalls & Misconceptions - sqlity.net<\/title>\n<meta name=\"description\" content=\"Your responsibilities as the DBA include the management of access permissions. To be able to live up to the expectation that you will be able to secure the data, you must know the 12 Pitfalls & Misconceptions that we will talk about in this session.\" \/>\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\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Presentation Materials: Permission Management - 12 Pitfalls & Misconceptions - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Your responsibilities as the DBA include the management of access permissions. To be able to live up to the expectation that you will be able to secure the data, you must know the 12 Pitfalls & Misconceptions that we will talk about in this session.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/\" \/>\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-03-26T22:03:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T20:23:19+00:00\" \/>\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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Presentation Materials: Permission Management – 12 Pitfalls & Misconceptions\",\"datePublished\":\"2014-03-26T22:03:45+00:00\",\"dateModified\":\"2014-11-13T20:23:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/\"},\"wordCount\":213,\"commentCount\":5,\"articleSection\":[\"Presentations\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/\",\"name\":\"Presentation Materials: Permission Management - 12 Pitfalls & Misconceptions - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-03-26T22:03:45+00:00\",\"dateModified\":\"2014-11-13T20:23:19+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Your responsibilities as the DBA include the management of access permissions. To be able to live up to the expectation that you will be able to secure the data, you must know the 12 Pitfalls & Misconceptions that we will talk about in this session.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2291\\\/presentation-materials-permission-management-12-pitfalls-misconceptions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Presentation Materials: Permission Management – 12 Pitfalls & Misconceptions\"}]},{\"@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":"Presentation Materials: Permission Management - 12 Pitfalls & Misconceptions - sqlity.net","description":"Your responsibilities as the DBA include the management of access permissions. To be able to live up to the expectation that you will be able to secure the data, you must know the 12 Pitfalls & Misconceptions that we will talk about in this session.","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\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/","og_locale":"en_US","og_type":"article","og_title":"Presentation Materials: Permission Management - 12 Pitfalls & Misconceptions - sqlity.net","og_description":"Your responsibilities as the DBA include the management of access permissions. To be able to live up to the expectation that you will be able to secure the data, you must know the 12 Pitfalls & Misconceptions that we will talk about in this session.","og_url":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-26T22:03:45+00:00","article_modified_time":"2014-11-13T20:23:19+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Presentation Materials: Permission Management – 12 Pitfalls & Misconceptions","datePublished":"2014-03-26T22:03:45+00:00","dateModified":"2014-11-13T20:23:19+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/"},"wordCount":213,"commentCount":5,"articleSection":["Presentations"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/","url":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/","name":"Presentation Materials: Permission Management - 12 Pitfalls & Misconceptions - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-03-26T22:03:45+00:00","dateModified":"2014-11-13T20:23:19+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Your responsibilities as the DBA include the management of access permissions. To be able to live up to the expectation that you will be able to secure the data, you must know the 12 Pitfalls & Misconceptions that we will talk about in this session.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2291\/presentation-materials-permission-management-12-pitfalls-misconceptions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Presentation Materials: Permission Management – 12 Pitfalls & Misconceptions"}]},{"@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-AX","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2291","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=2291"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2291\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2291"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2291"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2291"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}