Keep Schema and Ownership Simple

I like to keep things simple because simplicity is easier to manage and less prone to error.  When I’m faced with schema design decisions, I pick the selection with the least complexity that meets my objectives.  Here are some of my thoughts regarding schema and ownership in SQL 2005.

Schema

A schema is basically a container that categorizes database objects and simplifies security administration.  As a namespace, schemas logically organize objects without the need for special object naming rules.  Different objects can have the same name as long as they exist in different schemas because the schema name is essentially an extension of the object name that will “uniqueify” the name within a database. 

Categorizing objects by schema is particularly useful in complex databases with many objects.  There is some subjectivity on exactly how one might draw schema boundaries but the basic concept is the same; group related objects into different schema to provide organization to an otherwise unwieldy schema.  Classifying related objects by schema makes complex databases easier to understand and manage.

Schema also simplifies security administration because permissions can be granted en mass at the schema level.  For example, I can grant EXECUTE permissions on all objects in a schema with a single statement like “GRANT EXECUTE ON SCHEMA::Sales TO SalesRole”.  I can grant CONTROL permissions on a schema to allow privileged users to full control over a specific schema but not others in the same database.

Even with the option to use multiple schemas, I tend to use the built-in dbo schema.  I do this because most of the applications I maintain were developed before SQL 2005 and all objects are already in the dbo schema.  Some of those legacy systems could benefit from multiple schemas but I’ll continue to use dbo for those applications to be consistent until I need to add a group of new objects that are appropriate for a separate schema.  The new SQL 2005 databases I’ve developed thus far have been fairly simple and haven’t warranted using multiple schemas for either classification or security purposes.

Ownership

The owner is important for two main reasons:  1) the owner has powerful CONTROL permissions over all owned objects and 2) the owner determines whether or not the ownership chain is broken between objects.  Schema-contained objects will inherit the schema owner unless explicitly overridden using ALTER AUTHORIZATION.  Personally, I think it best for objects to inherit the schema owner in the vast majority of cases; if an object warrants a different owner than the schema, the object probably belongs in a different schema.

I use the built-in dbo principal for ownership unless I have a reason to do otherwise.  This approach is perfect in environments where only db-owner role members can create objects and schemas are used solely as a namespace rather than a security boundary.  The dbo principal exists in all databases so there is no a need to create a user or role for ownership purposes.  Simple is good.

Different schema owners provide a security boundary between objects in different schema because this breaks the ownership chain.  With a broken chain, explicit permissions on indirectly referenced objects are needed by the end user or the impersonated principal.  Different schema owners ensure that I don’t inadvertently provide access to data in different schema via ownership chaining.

Note that an owner can be any database principal and does not necessarily need to be associated with a login.  I find this feature especially handy in situations where I want to specify an owner other than dbo.  Since I can specify an owner that is not a real person, I won’t need to change ownership if the owner leaves the company or moves on to other roles in the organization. 

It’s probably best to create a principal (role or a user without login) with the same name as the schema for ownership purposes when different owners are desired.  The only case I can think of where it might be appropriate for a real person to own a schema (at least initially) is in a development environment when non-dbo users might create schemas and objects.

posted @ Sunday, February 03, 2008 10:48 AM

Print

Comments on this entry:

# re: Keep Schema and Ownership Simple

Left by Alex Kuznetsov at 2/5/2008 3:03 PM
Gravatar
Dan,

My practice is very similar to yours, but there is one difference: I don't have any objects in dbo schema at all. This forces us to always qualify all the objects with schema names. Does it make sense to you?

# re: Keep Schema and Ownership Simple

Left by Dan Guzman at 2/5/2008 8:13 PM
Gravatar
Yes, this does make sense, Alex. In our shop, we require that all object references be schema-qualified even in the dbo schema. I can see how a user-defined schema can help enfore this standard.

# re: Keep Schema and Ownership Simple

Left by Marjorie at 2/2/2009 3:13 PM
Gravatar
This article and Alex's comments were helpful. We are trying to create an auditable SQL database at our shop (gov't agency). So far I am thinking, stay out of dbo altogether per Alex and separate schemas for data objects (tables, views, etc.) and programming objects (procs, udf, etc.). Different users without logins own each. The application login only has execute on the programming object schema. I'd love your comments on that idea or any reference hints on design best practices if you have any.

# re: Keep Schema and Ownership Simple

Left by Dan Guzman at 2/2/2009 10:01 PM
Gravatar
Hi Majorie.

I see a potential problem with separate schema owners for tables versus procs with an application login that has only proc execute permissions. Since the separate schema owners will break the ownership chain, the app login would also need permissions on the tables accessed by the procedures.

Can you elaborate on what you mean by auditable? Maybe there's another way to address your requirements.

# re: Keep Schema and Ownership Simple

Left by Steve Jones at 9/12/2011 11:43 AM
Gravatar
Interesting thoughts. I like simple as well, and while I can see that schemas could be useful in some places (auditing, staging for ETL, etc), I don't like them as security items.

My reasoning is that sooner or later someone will need separate permissions from the group. In that case, I can use a separate role, as you have noted in granting permissions to the stored procs in a schema. But what about if I add a new object to the schema that should not have access from all existing members. In that case, I can move members to a new role, but it strikes me as retrofitting things out. Especially as I have some people getting rights from the schema, some not. I prefer to just use roles and grant explicit rights for each object to the role.
Comments have been closed on this topic.