Schema-design for SQL Server: recommendations for Schema design with security in mind
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 “database object schemas,” also described as Namespace.
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 business processes for the ER-diagram and later tabular design, one has to deal with database access processes for the schema design.
Unfortunately, however, I still see databases every week which only seem to know the “dbo” schema.
I admit that the amount of information on this topic is not as extensive as on the regulars “Indexing” and “Performance.” 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 – admittedly long due – blog post I hope to provide a good reference. Because, to relieve all autodidacts, ironically, the well-known “AdventureWorks” 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.
Background Information
Up to SQL Server 2000, users and schemas were independent of each other, and there were only 2 options:
- All developers put all objects into the dbo-schema.
- Objects are in the schema under the developer’s name, e.g.: “Andreas.Shoppingcart”
The second approach was of course entirely impracticable, putting aside one-man-developments. Developers were thus equipped with db_owner(!)-rights and instructed to always specify objects in all DDL commands with dbo.objectname, hence as “Two-Part Name.” If this was forgotten, the developer’s name would suddenly be in front of the objects, and cross-references would in most cases not work. – At least the causer would be obvious 🙂
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.
This is why the Security Team for SQL Server 2005 completely revised the schema concept, with the aim to facilitate the delegation of rights.
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).
Aims and benefits of database schemas
At this point, let me quote a member of the Security Team: “the intent with separating Schema from Users was to increase security – to allow more controlled delegation, etc.
Or, to quote the relevant whitepaper, “SQL Server Best Practices – Implementation of Database Object Schemas:”
“A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects. Database object schemas offer functionality to control and help secure application objects within a database environment…”
So much for the main purpose. Of course, one may also use schemas as organization element. I will even invite you to do that. But please only in second place, once the security borders are set.
The image below illustrates how several database principals can use objects in a common schema:
At first sight, this might seem “neat and tidy.” However, once you look closer and start pondering how to assign authorizations, it looks rather chaotic.
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?
It is certainly clear that db_datareader, db_datawriter and a “self-created” “db_executor” or the like do not serve as a benchmark for this article. – 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.
KISS-Principle: “Keep it simple, stupid“
For views, there is the SELECT permission, in ad-hoc-CRUD queries there is also INSERT, UPDATE and DELETE.
The EXECUTE permission suffices for all procedures.
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.
Here is an example from the official MOC course:
In this sample, the SELECT permission is granted to all objects in the schema “KnowledgeBase.”
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 – 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 – which is not the case here either.
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.
This does not get us very far.
A user can also work on the tables, past the procedures, after establishing a direct connection to the database.
Schema-Design done right
What would it look like if one does it correctly from a security-point-of-view?
It is not that difficult to imagine. In fact, SQL Server knows a thing called “Object Ownership Chaining.” 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 “access-object” (views, procedures, functions) in one schema, and, in another schema, objects (tables) to which one does not want to allow direct access.
In my presentation “Securing SQl Server from Inside-Attacks” at the 2009 PASS Summit in Seattle, I dubbed this principle “schema-ownership-chaining.”
Schema-Ownership-Chaining
In the (slightly modified) slide above, one can see that a user does not have direct access to the tables in the schema “Data” but only via views in the schema “Access” (hence “Access-Schema”). This works because the schemas and the contained objects have the same owner – “dbo” in this case.
For my presentation “SQL Server 2012 Security for Developers” at the 2012 SQLRally Nordic in Copenhagen, I refined this concept a bit more:
Best Practices for Schema-Design
The above graphic illustrates even better that there are no tables in the schema “App1” but only access code in the form of procedures, and, where applicable, views. As a result, one EXECUTE permission is enough for this schema, and whatever the procedures are executing (SELECT, INSERT, UPDATE, DELETE) does not require any further rights – especially not on the tables themselves, here in the schema “Sales.”
A second approach becomes evident here: Considering “processes” or, in this case, applications.
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 “order-concept” 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.
In fact, no one keeps you from creating a “common schema” in which there is the code (procedures and views) that is used by both.
Ultimately, one manages with just a handful of rights in this way and still fulfills the principles of “Least Privilege” and “Separation of Duties.”
Note on Object Owner and broken Ownership Chains
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.
This is how you can prompt the owners of the schemas and tables involved:
A Principal_id of NULL means that the table is owned by the Schema_owner. The schema “Person” is owned by the dbo (principal_id=1)
Altering the owner of the table:
ALTER AUTHORIZATION ON [Person].[Address] TO db_owner
At this point, the ownership chain is broken.
Not only that: also, as a result all schema permissions are reset!
And this is how to reset them to the schema owner – which is better than explicitly assigning the owner to dbo (or another principal):
Result:
AdventureWorks-Schema
Corrected/security-optimized version
Now that the concept is clear, let’s give the AdventureWorks-database a shot.
My suggestion is as follows:
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 “WebShop,” which is meant exemplary for an application that is permitted to call all procedures and use views programmed for this purpose.
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.
Execution-Context and broken ownership chains
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 „EXECUTE AS-Clause.“
The implementation can look like this schema:
Recommendations for Schema-Design
In the following, I will present a few approaches to the decision-making for schema-design.
The ideal measure would be a structure according to process or application (also known as “role” among security experts).
Example:
Process1.Objects, Process2.Objects, Data(1-n)
Further examples for certain scenarios, as also partly implemented in customer projects and shown in seminars by Sarpedon Quality Lab® for years:
Standard OLTP
- Administration (Log-Tables etc.)
- DataPublic
- DataInternalOnly (if DB is used by different Apps, some public, some only for internal staff)
- (Web)App(1)
- (Web)App(2)
- Reporting (prefer own code-only DB!)
Data Processing (Cleansing etc.):
- Import (raw imported data)
- Dev (unfinalized code)
- Data (final, cleaned data)
- Access (Views/Procs for accessing the data)
DataWarehouses (Source for OLAP-Cubes)
- DimData (saves the old-fashion prefix „dim“/“fact“)
- FactData (…)
- vDim (for denormalized, star-schema-Dimension views)
- vFact (for the MeasureGroups)
- … other Housekeeping, Reporting, ETL -Schemas
For merely internal databases it is also possible to use the following approach:
- By Owner:
- DeveloperA.Objects
- DeveloperB.Objects
- By Structure:
- Subproject1.Objects
- Subproject2.Objects
Further hints:
- In place of „dbo,” I recommend setting up an explicit “User without Login” and using it as dedicated schema-owner.
- The following schemas are “backward compatibility schemas” which one can safely delete. They can only be deleted if they are no longer used, which is why there is no risk – and of course, I advise against starting to use them.:
- db_owner
- db_accessadmin
- db_securityadmin
- db_ddladmin
- db_backupoperator
- db_datareader
- db_datawriter
- db_denydatareader
- db_denydatawriter
Call to developers
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.
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 – ideally combined with an access-schema. But anything is better than saving all programs directly in C:\Program Files\ – there, subfolders are also common practice.
Happy Schema-Designing
Andreas
Acknowledgement
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.
Leave a Reply
Want to join the discussion?Feel free to contribute!