{"id":2251,"date":"2014-03-16T11:00:45","date_gmt":"2014-03-16T15:00:45","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2251"},"modified":"2014-11-13T13:11:01","modified_gmt":"2014-11-13T18:11:01","slug":"ownership-chaining-quandary","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/","title":{"rendered":"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nIn The Ownership Chain<\/a> we looked at how SQL Server skips permission checking, based on the ownership chain of the securables involved. In that same post I said that ownership chaining is a good thing in most circumstances. However, as with most powerful technologies, there is the potential for abuse.\n<\/p>\n

\nIn this post, I would like to show you one example of how ownership chaining can be misused to gain unauthorized access to a securable.\n<\/p>\n

Example of Unauthorized access using Ownership Chaining<\/h3>\n

\nLet us look at the example of the Junior DBA. Junior is supposed to do most of the maintenance tasks in our database. That includes the execution of upgrade scripts and hot fixes from time to time. However, management decided that Junior should not be able to see or change data in the table that holds the salary information for all employees. How would you implement that?\n<\/p>\n

\nFirst, we need a JuniorDba user:\n<\/p>\n

\n[sql]\nCREATE LOGIN JuniorDba WITH PASSWORD='********',CHECK_POLICY=OFF;
\nCREATE USER JuniorDba FROM LOGIN JuniorDba;
\n[\/sql]\n<\/div>\n

\nThe database schema looks like this:\n<\/p>\n

\n[sql]\nCREATE SCHEMA HumanResources;
\nGO
\nCREATE TABLE HumanResources.salary(employee_id INT, amount NUMERIC(14,4));
\nINSERT INTO HumanResources.salary VALUES(42,1000000);
\n[\/sql]\n<\/div>\n

\nWell, there are other objects in the database and probably also other columns in that salary<\/span> table, but for our example this suffices.\n<\/p>\n

\nNow, the requirement was for Junior to be able to execute maintenance tasks as well as install upgrades and hot fixes. That means, we should grant CONTROL<\/span> on the database. We also want to prevent that Junior snoops on the salary, so we should deny SELECT<\/span> on that table.\n<\/p>\n

\n[sql]\nGRANT CONTROL TO JuniorDba;
\nDENY SELECT ON OBJECT::HumanResources.salary TO JuniorDba;
\n[\/sql]\n<\/div>\n

\nIn real life you would also deny other privileges on this table, but for this example denying SELECT<\/span> is enough to demonstrate the problem.\n<\/p>\n

\nNow Junior should not be able to select from the salary<\/span> table anymore. Let us confirm:\n<\/p>\n

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

\nGreat, that works. However, Junior is clever and immediately identifies the rather huge gap. Junior can create a procedure like this one:\n<\/p>\n

\n[sql]\nCREATE PROCEDURE dbo.snoop AS SELECT * FROM HumanResources.salary;
\n[\/sql]\n<\/div>\n

\nBy default, procedures and tables are owned by the owner of their schema. If the HumanResources<\/span> schema was created by a sysadmin<\/span> or a member of the db_owner<\/span> database role, it is owned by dbo, the same as the dbo<\/span> schema. That means, that now both the procedure and the table have the same owner and therefore ownership chaining is in effect:\n<\/p>\n

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

\nWith that simple procedure, Junior was able to get a complete picture of the company's salary situation. The CONTROL<\/span> privilege on the database combined with ownership chaining allowed Junior to circumvent an explicit DENY<\/span>. This type of problem is not unique to the CONTROL<\/span> permission. Other combinations of privileges can lead to the same result.\n<\/p>\n

\nTo prevent this kind of attack, a lot of moving parts have to be taken into account. You could just change the owner of that table to a separate database principal. However, now other procedures cannot rely on ownership chaining anymore either. That means that the legitimate users now need direct access to that table, a situation you probably want to avoid too. You could try to separate the entire salary related functionality into its own schema and block that schema off from Junior. However, then Junior might not be able to execute upgrade scripts anymore.\n<\/p>\n

\nIf this quandary needs to be solved within the database, you might have to revert to signing all modules (that need to access the salary table) with a certificate. A procedure can be scripted with its signature. That means that Junior could still install a procedure that has been signed by a more senior team member. However, Junior would not be able to alter that procedure for adverse intents, as that would invalidate the signature.\n<\/p>\n

\nModule signing is a very clean solution; however, it comes with a high maintenance overhead. A simpler solution would be to encrypt the salary information within the application. That way it would not matter if Junior could see the stored (and encrypted) information.\n<\/p>\n

Summary<\/h3>\n

\nWhile Ownership Chaining is a good thing in most situations, it can be misused to gain unauthorized access to a securable in other situations. This can create a quandary that is not easy to solve.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Ownership chaining is a powerful tool for permission management. However, in some situations it can be misused to gain unauthorized access to a securable. Read on to see this in action.<\/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,50,38,58,15],"class_list":["post-2251","post","type-post","status-publish","format-standard","hentry","category-general","category-security","category-security-pitfalls","tag-deny","tag-permission","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nThe Ownership Chaining Quandary or How to Circumvent an Explicit DENY - sqlity.net<\/title>\n<meta name=\"description\" content=\"Ownership chaining is a powerful tool for permission management. But, read on to see how in some situations it can be misused to gain unauthorized access.\" \/>\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\/2251\/ownership-chaining-quandary\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Ownership chaining is a powerful tool for permission management. But, read on to see how in some situations it can be misused to gain unauthorized access.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/\" \/>\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-16T15:00:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:11:01+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/select_permission_denied.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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY\",\"datePublished\":\"2014-03-16T15:00:45+00:00\",\"dateModified\":\"2014-11-13T18:11:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/\"},\"wordCount\":762,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/select_permission_denied.jpg\",\"keywords\":[\"DENY\",\"Permission\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\",\"Security Pitfalls\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/\",\"name\":\"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/select_permission_denied.jpg\",\"datePublished\":\"2014-03-16T15:00:45+00:00\",\"dateModified\":\"2014-11-13T18:11:01+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Ownership chaining is a powerful tool for permission management. But, read on to see how in some situations it can be misused to gain unauthorized access.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/select_permission_denied.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/select_permission_denied.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2251\\\/ownership-chaining-quandary\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Ownership Chaining Quandary or How to Circumvent an Explicit 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":"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY - sqlity.net","description":"Ownership chaining is a powerful tool for permission management. But, read on to see how in some situations it can be misused to gain unauthorized access.","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\/2251\/ownership-chaining-quandary\/","og_locale":"en_US","og_type":"article","og_title":"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY - sqlity.net","og_description":"Ownership chaining is a powerful tool for permission management. But, read on to see how in some situations it can be misused to gain unauthorized access.","og_url":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-16T15:00:45+00:00","article_modified_time":"2014-11-13T18:11:01+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/select_permission_denied.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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY","datePublished":"2014-03-16T15:00:45+00:00","dateModified":"2014-11-13T18:11:01+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/"},"wordCount":762,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/select_permission_denied.jpg","keywords":["DENY","Permission","security","security management","SQL Server"],"articleSection":["General","Security","Security Pitfalls"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/","url":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/","name":"The Ownership Chaining Quandary or How to Circumvent an Explicit DENY - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/select_permission_denied.jpg","datePublished":"2014-03-16T15:00:45+00:00","dateModified":"2014-11-13T18:11:01+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Ownership chaining is a powerful tool for permission management. But, read on to see how in some situations it can be misused to gain unauthorized access.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/select_permission_denied.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/select_permission_denied.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2251\/ownership-chaining-quandary\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The Ownership Chaining Quandary or How to Circumvent an Explicit 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-Aj","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2251","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=2251"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2251\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}