At some point during SQL Server’s history, its designers must have confronted the following problem: how to give someone permission to see parts of a table without giving him any permission on the table? Slices of a table are easily defined using views, so the problem becomes one of giving SELECT permission on a view without granting a SELECT permission on the underlying table as well. Normally, the SELECT permission on the view alone is not sufficient, because the system would check permissions once when you select from the view and a second time when the view makes reference to the underlying table, so the second check would fail if the user has no permission on the table. The solution provided for this problem in SQL Server is ownership chaining. Ownership chaining, or OC for short, will bypass the permission check that would be done when the view is referencing the table, if the owner of the view is the same as the owner of the table. Intuitively, this makes sense as the intention of the owner in granting SELECT on the view must have been made so that the user would be able to actually select from that view. This mechanism has then been extended beyond this simple example involving a view and a table – the same would hold, for example, if a procedure would use a view that would refer to a table and the caller only had EXECUTE permission on the procedure and no permissions on the view or table. This idea has proliferated so that it resulted in the extension of OC to work across databases – the so-called cross-database ownership chaining (CDOC). CDOC is a topic that goes beyond what I want to cover in this post, so I’ll only mention that these days it is disabled by default and it’s a good idea to leave it like that.
So, OC provides a simple way to provide restricted access to objects and simplifies permission management. This sounds like a lot of good stuff. However, to achieve this, OC is bypassing permission checks completely, which means, of course, that it can bypass denies as well. I can deny someone access to a table and I might think that this prevents him from accessing that table, ever – I would be wrong however, as he might still access the table through OC. Ok, you’ll say, but then I’m paying the price because I must have granted him access to a view or procedure that I own and which accesses the table. This is true, but is only true in an ideal world where people follow good practices and are aware of all the consequences of their actions – sadly, this is not our world. The bad aspect of OC is the fact that it weakens the semantics of a deny. A deny should have meant that in no way could a user perform the action that he is denied while the deny is in effect. OC represents an exception to this rule and exceptions to security principles are the root of evil.
All good security principles are often ignored in practice. One principle I often see ignored in the SQL Server world is the principle of executing with minimum privileges. It’s often the case that people create objects as sysadmins and when they do that, who will be the owner of the object? dbo, of course. Soon enough, most objects are owned by dbo – see where I’m going with this? Having all objects be owned by dbo means OC chaining is rampant in the database. It’s easy now for unintended OC to grant wrong access, especially in view of multiple users mapping to dbo. Because OC cannot be turned off (I wish there would be a database setting to do that), you must always be mindful of OC when you are designing your database and deciding the ownership of entities in it.
Let’s look at schemas now. Similar to how databases can be used to separate applications in a server, schemas provide a further level of separation within a database. In SQL 2000, schemas were equated to users – schemas didn’t really exist as distinct entities, but it was assumed that each user had a corresponding schema. This allowed referring to objects using a user name in place of a schema, as when saying: db1.alice.proc. In SQL Server 2005, schemas became actual objects acting as containers of other basic entities, like procedures, views, tables, etc. To keep backward compatibility with existing use, when creating a user with old procedures such as sp_adduser, both a user and a schema named after it are created and the schema will be owned by that user; when using the new DDL, however, CREATE USER would only create the user and if a schema is desired as well, it would have to be explicitly created using CREATE SCHEMA. The owner of a schema will be the default owner of all objects within that schema (in this case, the principal_id column of the sys.objects row for such objects will be marked as NULL). It is possible, however, to change the ownership of individual objects in a schema to other principals than the schema owner (in this case the principal_id column would indicate the effective owner). You can read more about schemas in this BOL article.
The reason I grouped these topics together is because both are related to object ownership. OC permits a bypassing of permission checks in references made by one object to another and schemas provide a way to specify an owner for a large collection of objects. For schemas to be effective at separating access to objects, because of OC, they should be owned by different principals. It would be a mistake to allow all schemas in the database to be owned by dbo, for an extreme example.
Today, the problem for which OC constitued a solution can be resolved in other ways. With the introduction of module signing in SQL Server 2005, it is now possible to associate permissions with a module, which allows more granular access control than OC could provide. But OC is always on by default and must be kept in mind at all times to prevent undesired side-effects from its existence. To mitigate such undesired side-effect, keep OC in mind when deciding the ownership of entities in your database. In particular, do not setup your database such that all objects are owned by dbo. Keep in mind that SQL Server 2005 allows you now to create users that are database scoped – the loginless users created via CREATE USER…WITHOUT LOGIN – these types of users are perfect for breaking ownership chains in a database.