{"id":3162,"date":"2016-02-21T16:51:40","date_gmt":"2016-02-21T15:51:40","guid":{"rendered":"http:\/\/andreas-wolter.com\/?p=3162"},"modified":"2017-10-18T11:02:57","modified_gmt":"2017-10-18T10:02:57","slug":"schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind\/","title":{"rendered":"Schema-design for SQL Server: recommendations for Schema design with security in mind"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-b91fe307696d45f96a48de7631ce4d80\">\n#top .av-special-heading.av-av_heading-b91fe307696d45f96a48de7631ce4d80{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-b91fe307696d45f96a48de7631ce4d80 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-b91fe307696d45f96a48de7631ce4d80 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-b91fe307696d45f96a48de7631ce4d80 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >Schema-design for SQL Server: recommendations for Schema design with security in mind<\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Introduction<\/strong><\/p>\n<p>This article picks up a topic I have been teaching time and again in seminars, at conferences and in forums for many years: <strong>Schema-Design<\/strong>. Here, schema does not mean the database schema with its tabular design but rather the \u201cdatabase object schemas,\u201d also described as <em>Namespace<\/em>.<!--more--><\/p>\n<p>Since the release of SQL Server 2005, in fact more than 10 years ago, it is of particular importance to me to train users in correctly using schemas. As a matter of fact, it is not really that difficult. In the same way that a developer\/architect has to deal with <strong>business processes<\/strong> for the ER-diagram and later <strong>tabular design<\/strong>, one has to deal with <strong>database access processes<\/strong> for the <strong>schema design<\/strong>.<\/p>\n<p>Unfortunately, however, I still see databases every week which only seem to know the \u201cdbo\u201d schema.<\/p>\n<p>I admit that the amount of information on this topic is not as extensive as on the regulars \u201cIndexing\u201d and \u201cPerformance.\u201d Hardening Security is an effort perceived as rather annoying, and developers are rarely trained in such areas in order to make the important decisions right at the design stage. With this \u2013 admittedly long due \u2013 blog post I hope to provide a good reference. Because, to relieve all autodidacts, ironically, the well-known \u201cAdventureWorks\u201d database is anything but exemplary when it comes to schema-design. This database is generally supposed to demonstrate the new features of the SQL Server Versions since 2005, but the concepts are not always being developed according to best practices. Particularly as the concept of the Schema-User separation was a relatively new thing back then.<\/p>\n<p><strong>Background Information<\/strong><\/p>\n<p>Up to SQL Server 2000, users and schemas were independent of each other, and there were only 2 options:<\/p>\n<ol>\n<li>All developers put all objects into the dbo-schema.<\/li>\n<li>Objects are in the schema under the developer\u2019s name, e.g.: \u201cAndreas.Shoppingcart\u201d<\/li>\n<\/ol>\n<p>The second approach was of course entirely impracticable, putting aside one-man-developments. Developers were thus equipped with <strong>db_owner<\/strong>(!)-rights and instructed to always specify objects in all DDL commands with dbo.objectname, hence as \u201cTwo-Part Name.\u201d If this was forgotten, the developer\u2019s name would suddenly be in front of the objects, and cross-references would in most cases not work. \u2013 At least the causer would be obvious \ud83d\ude42<\/p>\n<p>The person not paying attention to any of it would face the problem that he could not delete accounts of former developers from the server as there were still objects assigned to them and that were in the end firmly anchored in the application.<\/p>\n<p>This is why the Security Team for SQL Server 2005 completely revised the schema concept, <strong>with the aim to facilitate the delegation of rights<\/strong>.<\/p>\n<p>The dbo-Schema is essentially a relic from the pre-2005 world, which still exists due to backward compatibility reasons, and which is at the same time used as default schema for name resolution (also like before).<\/p>\n<p><strong>Aims and benefits of database schemas<\/strong><\/p>\n<p>At this point, let me quote a member of the Security Team: \u201cthe intent with separating Schema from Users was to increase security \u2013 to allow more controlled delegation, etc.<strong>\u00a0<\/strong><\/p>\n<p>Or, to quote the relevant whitepaper, \u201c<a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/dd283095%28v=sql.100%29.aspx\" target=\"_blank\" rel=\"noopener\">SQL Server Best Practices \u2013 Implementation of Database Object Schemas<\/a>:\u201d<\/p>\n<p>\u201cA schema is a distinct namespace to <strong>facilitate the separation, management, and ownership of database objects<\/strong>. It removed the tight coupling of database objects and owners <strong>to improve the security administration of database objects<\/strong>. Database object schemas offer <strong>functionality to control and help secure application objects within a database<\/strong> environment&#8230;\u201d<\/p>\n<p>So much for the main purpose. Of course, one may also use schemas as <strong>organization element<\/strong>. I will even invite you to do that. But please only in second place, <strong>once the security borders are set<\/strong>.<\/p>\n<p>The image below illustrates how several database principals can use objects in a common schema:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p style=\"text-align: center;\"><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/User-Schema-Separation-MSDN.gif\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3166\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/User-Schema-Separation-MSDN.gif\" alt=\"\" width=\"174\" height=\"105\" \/><\/a><\/p>\n<p style=\"text-align: center;\">Source: <a href=\"https:\/\/technet.microsoft.com\/en-us\/magazine\/2005.05.datasecurity.aspx\" target=\"_blank\" rel=\"noopener\">On The Horizon: Improved Data Security In SQL Server 2005<\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Negative example<\/strong><\/p>\n<p>Let us now look at the initially mentioned schemas in the AdventureWorks database:<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-ObjectTypes.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3168 size-medium\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-ObjectTypes-300x233.png\" alt=\"\" width=\"300\" height=\"233\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-ObjectTypes-300x233.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-ObjectTypes-450x349.png 450w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-ObjectTypes.png 500w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>At first sight, this might seem \u201cneat and tidy.\u201d However, once you look closer and start pondering how to assign authorizations, it looks rather chaotic.<\/p>\n<p>In all schemas, there are tables and either also views or procedures or both. Now if one imagines a frontend application in addition, where is it supposed to obtain permissions?<\/p>\n<p>It is certainly clear that db_datareader, db_datawriter and a \u201cself-created\u201d \u201cdb_executor\u201d or the like do not serve as a benchmark for this article. \u2013 Yet it is by all means a valid approach for smaller databases with few objects, or for databases whose objects are meant to be used truly equally.<\/p>\n<p><strong>KISS-Principle: \u201cKeep it simple, stupid\u201c<\/strong><\/p>\n<p>For views, there is the SELECT permission, in ad-hoc-CRUD queries there is also INSERT, UPDATE and DELETE.<\/p>\n<p>The EXECUTE permission suffices for all procedures.<\/p>\n<p>Permissions can be assigned on a database-schema and object level. And of course, the schema level is the level that would present itself if the intention is to grant authorizations to all objects without restriction, but also if one does not want to grapple with single-object permissions.<\/p>\n<p>Here is an example from the <strong>official MOC course<\/strong>:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-751802791edd487bdc129bb3c5109fae\">\n.avia-image-container.av-av_image-751802791edd487bdc129bb3c5109fae img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-751802791edd487bdc129bb3c5109fae .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-751802791edd487bdc129bb3c5109fae av-styling- avia-align-center  avia-builder-el-5  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3171 avia-img-lazy-loading-not-3171 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/Schema-Sample-MOC-Developing-SQL-Server-Databases-300x115.png\" alt='' title='Schema-Sample-MOC-Developing-SQL-Server-Databases'  height=\"115\" width=\"300\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/Schema-Sample-MOC-Developing-SQL-Server-Databases-300x115.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/Schema-Sample-MOC-Developing-SQL-Server-Databases-495x191.png 495w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/Schema-Sample-MOC-Developing-SQL-Server-Databases-450x172.png 450w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/Schema-Sample-MOC-Developing-SQL-Server-Databases.png 500w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>In this sample, the SELECT permission is granted to all objects in the schema \u201cKnowledgeBase.\u201d<\/p>\n<p>Applied to our AdventureWorks schema design, this would imply that we have to grant SELECT rights to all schemas in which there are views or tables \u2013 with the exception of those where, besides tables, there are merely procedures that actually execute all required operations against the tables contained in the same schema \u2013 which is not the case here either.<\/p>\n<p>At the end of the day, one will have to grant SELECT, INSERT, UPDATE, DELETE rights to all schemas, and, in addition, a couple of EXECUTE permissions to the dbo-, HumanResources-, Production- and Sales-schema.<\/p>\n<p>This does not get us very far.<\/p>\n<p>A user can also work on the tables, past the procedures, after establishing a direct connection to the database.<\/p>\n<p><strong>Schema-Design done right<\/strong><\/p>\n<p>What would it look like if one does it correctly from a security-point-of-view?<\/p>\n<p>It is not that difficult to imagine. In fact, SQL Server knows a thing called \u201c<a href=\"https:\/\/technet.microsoft.com\/de-de\/library\/ms188676(v=sql.105).aspx\" target=\"_blank\" rel=\"noopener\">Object Ownership Chaining<\/a>.\u201d Schemas have an owner and are part of the chain. That means, as long as the schemas involved have the same owner, one can keep \u201caccess-object\u201d (views, procedures, functions) in one schema, and, in another schema, objects (tables) to which one does not want to allow direct access.<\/p>\n<p>In my presentation \u201cSecuring SQl Server from Inside-Attacks\u201d at the 2009 PASS Summit in Seattle, I dubbed <strong>this principle<\/strong> \u201c<strong>schema-ownership-chaining<\/strong>.\u201d<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-a56626b7077e48f30e1f121e150b6f07\">\n.avia-image-container.av-av_image-a56626b7077e48f30e1f121e150b6f07 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-a56626b7077e48f30e1f121e150b6f07 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-a56626b7077e48f30e1f121e150b6f07 av-styling- avia-align-center  avia-builder-el-7  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3175 avia-img-lazy-loading-not-3175 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-Summit-2009.png\" alt='' title='schema-ownership-chaining-PASS-Summit-2009'  height=\"453\" width=\"600\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-Summit-2009.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-Summit-2009-300x227.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-Summit-2009-450x340.png 450w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Schema-Ownership-Chaining<\/strong><\/p>\n<p>In the (slightly modified) slide above, one can see that a user does not have direct access to the tables in the schema \u201cData\u201d but only via views in the schema \u201cAccess\u201d (hence \u201cAccess-Schema\u201d). This works because the schemas and the contained objects have the same owner \u2013 \u201cdbo\u201d in this case.<\/p>\n<p>For my presentation \u201cSQL Server 2012 Security for Developers\u201d at the 2012 SQLRally Nordic in Copenhagen, I refined this concept a bit more:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-402f11c8161f1b0beef4132c095f28e4\">\n.avia-image-container.av-av_image-402f11c8161f1b0beef4132c095f28e4 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-402f11c8161f1b0beef4132c095f28e4 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-402f11c8161f1b0beef4132c095f28e4 av-styling- avia-align-center  avia-builder-el-9  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3177 avia-img-lazy-loading-not-3177 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-SQLRally-2012.png\" alt='' title='schema-ownership-chaining-PASS-SQLRally-2012'  height=\"450\" width=\"600\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-SQLRally-2012.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-SQLRally-2012-300x225.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-chaining-PASS-SQLRally-2012-450x338.png 450w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Best Practices for Schema-Design<\/strong><\/p>\n<p>The above graphic illustrates even better that there are no tables in the schema \u201cApp1\u201d but only access code in the form of procedures, and, where applicable, views. As a result, <strong>one<\/strong> <strong>EXECUTE permission<\/strong> is enough for this schema, and whatever the procedures are executing (SELECT, INSERT, UPDATE, DELETE) does not require any further rights \u2013 especially not on the tables themselves, here in the schema \u201cSales.\u201d<\/p>\n<p>A second approach becomes evident here: Considering \u201c<strong>processes<\/strong>\u201d or, in this case, <strong>applications<\/strong>.<\/p>\n<p>In many databases, an application must not really access all tables. At the latest when several applications are working with one database it becomes apparent that the \u201corder-concept\u201d represents an obstacle. Ideally, then, for each application one schema is created that contains exactly those procedures the former is supposed to use. For ad-hoc-accesses, unfortunately often needed for code generators, it is also possible to do this with views.<\/p>\n<p>In fact, no one keeps you from creating a \u201ccommon schema\u201d in which there is the code (procedures and views) that is used by both.<\/p>\n<p>Ultimately, one manages with just a handful of rights in this way and still fulfills the principles of \u201c<strong>Least Privilege<\/strong>\u201d and \u201c<strong>Separation of Duties<\/strong>.\u201d<\/p>\n<p><strong>Note on Object Owner and broken Ownership Chains<\/strong><\/p>\n<p>Attention: the ownership-chains can be broken on all levels, i.e. schema, procedure, view or table. This also occurs when the owner changes a table, as demonstrated below.<\/p>\n<p>This is how you can prompt the owners of the schemas and tables involved:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-104ac6b3fa3bad4e95d4c1aba4f93b8d\">\n.avia-image-container.av-av_image-104ac6b3fa3bad4e95d4c1aba4f93b8d img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-104ac6b3fa3bad4e95d4c1aba4f93b8d .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-104ac6b3fa3bad4e95d4c1aba4f93b8d av-styling- avia-align-center  avia-builder-el-11  el_after_av_textblock  el_before_av_image '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3184 avia-img-lazy-loading-not-3184 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/select_schema_id_Screenshot.png\" alt='' title='select_schema_id_Screenshot'  height=\"90\" width=\"421\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/select_schema_id_Screenshot.png 421w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/select_schema_id_Screenshot-300x64.png 300w\" sizes=\"(max-width: 421px) 100vw, 421px\" \/><\/div><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-54edb0623cdb612f19adcba5a4277c35\">\n.avia-image-container.av-av_image-54edb0623cdb612f19adcba5a4277c35 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-54edb0623cdb612f19adcba5a4277c35 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-54edb0623cdb612f19adcba5a4277c35 av-styling- avia-align-center  avia-builder-el-12  el_after_av_image  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3180 avia-img-lazy-loading-not-3180 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema_table_owner.png\" alt='' title='schema_table_owner'  height=\"148\" width=\"278\"  itemprop=\"thumbnailUrl\"  \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>A Principal_id of NULL means that the table is owned by the Schema_owner. The schema \u201cPerson\u201d is owned by the dbo (principal_id=1)<\/p>\n<p>Altering the owner of the table:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p style=\"text-align: center;\"><span style=\"color: #0000ff;\">ALTER AUTHORIZATION ON<\/span> [Person].[Address] <span style=\"color: #0000ff;\">TO<\/span> db_owner<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema_table_ownership_broken.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3182 size-full\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema_table_ownership_broken.png\" alt=\"\" width=\"271\" height=\"138\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>At this point, the ownership chain is broken.<\/p>\n<p>Not only that: also, as a result all schema permissions are reset!<\/p>\n<p>And this is how to reset them to the schema owner \u2013 which is better than explicitly assigning the owner to dbo (or another principal):<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p style=\"text-align: center;\"><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/change_owner_to_schema_owner.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-3186\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/change_owner_to_schema_owner-300x22.png\" alt=\"\" width=\"300\" height=\"22\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/change_owner_to_schema_owner-300x22.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/change_owner_to_schema_owner-450x33.png 450w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/change_owner_to_schema_owner.png 469w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>Result:<\/p>\n<p style=\"text-align: center;\"><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema_table_owner.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-3180\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema_table_owner.png\" alt=\"\" width=\"278\" height=\"148\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>AdventureWorks-Schema<\/strong><\/p>\n<p><strong>Corrected\/security-optimized version<\/strong><\/p>\n<p>Now that the concept is clear, let\u2019s give the AdventureWorks-database a shot.<\/p>\n<p>My suggestion is as follows:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-f87ccffd5a3daec5d4d7f40dea7bacf5\">\n.avia-image-container.av-av_image-f87ccffd5a3daec5d4d7f40dea7bacf5 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-f87ccffd5a3daec5d4d7f40dea7bacf5 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-f87ccffd5a3daec5d4d7f40dea7bacf5 av-styling- avia-align-center  avia-builder-el-18  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3188 avia-img-lazy-loading-not-3188 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-redone.png\" alt='' title='AdventureWorks-Schemas-redone'  height=\"354\" width=\"500\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-redone.png 500w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-redone-300x212.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-redone-260x185.png 260w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/AdventureWorks-Schemas-redone-450x319.png 450w\" sizes=\"(max-width: 500px) 100vw, 500px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>The views are not together with tables anymore so that a SELECT right is sure to concern views only. What is new is the schema \u201cWebShop,\u201d which is meant exemplary for an application that is permitted to call all procedures and use views programmed for this purpose.<\/p>\n<p>The dbo-Schema is now empty, and particular objects, e.g. log-tables are in the Admin-Schema. It is also possible to leave them in the dbo-Schema, but it is important to consider that this is always used as a default-schema for name resolution.<\/p>\n<p><strong>Execution-Context and broken ownership chains<\/strong><\/p>\n<p>In some scenarios, a broken ownership-chain can be intentional. In order to still grant particular modules access to data in the target schema without opening the target schema itself with permissions, one can work with the \u201e<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms181362.aspx\" target=\"_blank\" rel=\"noopener\">EXECUTE AS-<\/a>Clause.\u201c<\/p>\n<p>The implementation can look like this schema:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-c08d6120659b01e15d29922ccb91a9cc\">\n.avia-image-container.av-av_image-c08d6120659b01e15d29922ccb91a9cc img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-c08d6120659b01e15d29922ccb91a9cc .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-c08d6120659b01e15d29922ccb91a9cc av-styling- avia-align-center  avia-builder-el-20  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3190 avia-img-lazy-loading-not-3190 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-execution-context-switch-PASS-SQLRally-2012.png\" alt='' title='schema-ownership-execution-context-switch-PASS-SQLRally-2012'  height=\"453\" width=\"600\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-execution-context-switch-PASS-SQLRally-2012.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-execution-context-switch-PASS-SQLRally-2012-300x227.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/schema-ownership-execution-context-switch-PASS-SQLRally-2012-450x340.png 450w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Recommendations for Schema-Design<\/strong><\/p>\n<p>In the following, I will present a few approaches to the decision-making for schema-design.<\/p>\n<p>The ideal measure would be a structure <strong>according to process or application<\/strong> (also known as \u201c<strong>role<\/strong>\u201d among security experts).<\/p>\n<p>Example:<\/p>\n<p>Process1.Objects, Process2.Objects, Data(1-n)<\/p>\n<p><strong>Further examples for certain scenarios<\/strong>, as also partly implemented in customer projects and shown in seminars by <a href=\"http:\/\/www.sarpedonqualitylab.com\/\" target=\"_blank\" rel=\"noopener\">Sarpedon Quality Lab<\/a>\u00ae for years:<\/p>\n<p><strong>Standard OLTP<\/strong><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><ul>\n<li>Administration (Log-Tables etc.)<\/li>\n<li>DataPublic<\/li>\n<li>DataInternalOnly (if DB is used by different Apps, some public, some only for internal staff)<\/li>\n<li>(Web)App(1)<\/li>\n<li>(Web)App(2)<\/li>\n<li>Reporting (prefer own code-only DB!)<\/li>\n<\/ul>\n<p><strong>Data Processing <\/strong>(Cleansing etc.):<\/p>\n<ul>\n<li>Import (raw imported data)<\/li>\n<li>Dev (unfinalized code)<\/li>\n<li>Data (final, cleaned data)<\/li>\n<li>Access (Views\/Procs for accessing the data)<\/li>\n<\/ul>\n<p><strong>DataWarehouses<\/strong> (Source for OLAP-Cubes)<\/p>\n<ul>\n<li>DimData (saves the old-fashion prefix \u201edim\u201c\/\u201cfact\u201c)<\/li>\n<li>FactData (\u2026)<\/li>\n<li>vDim (for denormalized, star-schema-Dimension views)<\/li>\n<li>vFact (for the MeasureGroups)<\/li>\n<li>\u2026 other Housekeeping, Reporting, ETL -Schemas<\/li>\n<\/ul>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>For merely <strong>internal databases<\/strong> it is also possible to use the following approach:<\/p>\n<ul>\n<li>By Owner:\n<ul>\n<li>DeveloperA.Objects<\/li>\n<li>DeveloperB.Objects<\/li>\n<\/ul>\n<\/li>\n<li>By Structure:\n<ul>\n<li>Subproject1.Objects<\/li>\n<li>Subproject2.Objects<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Further hints:<\/strong><\/p>\n<ul>\n<li>In place of \u201edbo,\u201d I recommend setting up an explicit \u201cUser without Login\u201d and using it as dedicated schema-owner.<\/li>\n<li>The following schemas are \u201c<strong>backward compatibility schemas<\/strong>\u201d which one can safely <strong>delete<\/strong>. They can only be deleted if they are no longer used, which is why there is no risk \u2013 and of course, I advise against starting to use them.:<\/li>\n<\/ul>\n<ul>\n<li>db_owner<\/li>\n<li>db_accessadmin<\/li>\n<li>db_securityadmin<\/li>\n<li>db_ddladmin<\/li>\n<li>db_backupoperator<\/li>\n<li>db_datareader<\/li>\n<li>db_datawriter<\/li>\n<li>db_denydatareader<\/li>\n<li>db_denydatawriter<\/li>\n<\/ul>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Here some links for further reading:<\/p>\n<p><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/dd283095(v=sql.100).aspx\" target=\"_blank\" rel=\"noopener\">SQL Server Best Practices \u2013 Implementation of Database Object Schemas<\/a><\/p>\n<p><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/bb669061%28v=vs.110%29.aspx\" target=\"_blank\" rel=\"noopener\">Ownership and User-Schema Separation in SQL Server<\/a><\/p>\n<p><a href=\"https:\/\/technet.microsoft.com\/en-us\/magazine\/2005.05.datasecurity.aspx\" target=\"_blank\" rel=\"noopener\">On The Horizon: Improved Data Security In SQL Server 2005<\/a><\/p>\n<p><a href=\"https:\/\/www.simple-talk.com\/sql\/sql-training\/schema-based-access-control-for-sql-server-databases\/\" target=\"_blank\" rel=\"noopener\">Schema-Based Access Control for SQL Server Databases<\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Call to developers<\/strong><\/p>\n<p>First of all, thank you very much for reading. The first step has thus been taken: to generally engage with the issue of Schema-Design.<\/p>\n<p>It is my hope that this also leads to a rethinking and that I am going to see more well-designed schema-uses. And a practical, logical partitioning for the purpose of order can be part of this \u2013 ideally combined with an access-schema. But anything is better than saving all programs directly in C:\\Program Files\\ \u2013 there, subfolders are also common practice.<\/p>\n<p><br class=\"\u201cclear\u201c\" \/>Happy Schema-Designing<\/p>\n<p>Andreas<\/p>\n<p><br class=\"\u201cclear\u201c\" \/><strong>Acknowledgement<\/strong><\/p>\n<p>Special thanks to Jack Richins and Steven Gott from the Security Team in Redmond for reminding me of some aspects to add and allowing me to quote them in my article.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-27  el_after_av_textblock  el_before_av_hr  first flex_column_div av-zero-column-padding  column-top-margin'     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-29  el_after_av_one_full  el_before_av_social_share '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-30  el_after_av_hr  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind\/&#038;t=Schema-design%20for%20SQL%20Server%3A%20recommendations%20for%20Schema%20design%20with%20security%20in%20mind\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=Schema-design%20for%20SQL%20Server%3A%20recommendations%20for%20Schema%20design%20with%20security%20in%20mind&#038;url=https:\/\/andreas-wolter.com\/en\/?p=3162\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=Schema-design%20for%20SQL%20Server%3A%20recommendations%20for%20Schema%20design%20with%20security%20in%20mind&#038;url=https:\/\/andreas-wolter.com\/en\/schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-31  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"Introduction This article picks up a topic I have been teaching time and again in seminars, at conferences and in forums for many years: Schema-Design. Here, schema does not mean the database schema with its tabular design but rather the \u201cdatabase object schemas,\u201d also described as Namespace.","protected":false},"author":4,"featured_media":3194,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57],"tags":[203,204,205,206],"class_list":["post-3162","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security-en","tag-best-practices","tag-delegation-of-duties","tag-least-privilege","tag-sql-security"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=3162"}],"version-history":[{"count":10,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3162\/revisions"}],"predecessor-version":[{"id":5597,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3162\/revisions\/5597"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/3194"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=3162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=3162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=3162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}