THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Thoughts on schemas and schema proliferation

In SQL Server 2005 Microsoft introduced user-schema separation and since then I have seen the use of schemas increase; whereas before I would typically see databases where all objects were in the [dbo] schema I now see databases that have multiple schemas, a database I saw recently had 31 (thirty one) of them.

I can’t help but wonder whether this is a good thing or not – clearly 31 is an extreme case but I question whether multiple schemas create more problems than they solve? I have been involved in many discussions that go something like this:

Developer #1> “I have a new function to add to the database and I’m not sure which schema to put it in”
Developer #2> “What does it do?”
Developer #1> “It provides data to a report in Reporting Services”
Developer #2> “Ok, so put it in the [reports] schema”
Developer #1> “Well I could, but the data will only be used by our Financial reporting folks so shouldn’t I put it in the [financial] schema?”
Developer #2> “Maybe, yes”
Developer #1> “Mind you, the data is supposed to be used for regulatory reporting to the FSA, should I put it in [regulatory]?”
Developer #2> “Err….”

You get the idea!!! The more schemas that exist in your database then the more chance that their supposed usages will overlap.

I’m left wondering whether the use of schemas is actually necessary. I don’t  really see them as an aid to security because I generally believe that principles should be assigned permissions on objects as-needed on a case-by-case basis (and I have a stock SQL query that deciphers them all for me) so why bother using them at all? I can envisage a use where a database is used to house objects pertaining to many different business functions (which, in itself, is an ambiguous term) and in that circumstance perhaps a schema per business function would be appropriate; hence of late I have been loosely following this edict:

If some objects in a database could be moved en masse to another database without the need to remove any foreign key constraints then those objects could legitimately exist in a dedicated schema.

I am interested to know what other people’s thoughts are on this. If you would like to share then please do so in the comments.

@Jamiet

Published Monday, December 27, 2010 10:07 PM by jamiet
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dave Wentzel said:

Your last paragraph nails it.  Oracle has had schemas since the '80's, so I look to how Oracle people use them as guidance.  In the Oracle world a schema more closely maps to a SQL Server database.  An instance and a database are the same thing (other than when using the advanced HADR stuff).  So, it's common to backup/restore at the schema level, not the database/instance level.  You can restore a backed up schema to a new schema then create a new login using the new schema as the default.  By merely logging out/in you can connect to say, prod and test, without a new connection string.  Even so, this is rarely done for new dev work.  In the old days we often saw one big Oracle instance/db with a bunch of smaller, self-contained schemas mapping to individual applications with separate security, which is what you describe.  

December 27, 2010 5:03 PM
 

Alexander Kuznetsov said:

I would not make blanket statements. In my current system I have thousands of objects, but just a few distinct sets of permissions, so schemas really simplify the management of my permissions a lot -  only a few objects don't fit the common patterns.

In some other cases there are almost as many distinct sets of permissions as the objects themselves, so there is little point in having schemas...

December 27, 2010 6:09 PM
 

Bob Beauchemin said:

Well...one premise of a schema is that, if you assign privs at the schema level, then DROP-CREATE == ALTER. You can't assign privs at an object level if the object doesn't exist yet. You can with schemas because the priv applies to future objects in the schema as well as current objects.

And I'm not even going into schema owner == object owner. Everything being owned by DBO is a little like everything in file system being owned by administrator.

And, Dave, AFAIK Oracle has never had real schemas. Quote, from the Oracle BOL (10g, maybe it changed in 11?) "Note: This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions." http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6014.htm

Just like SQL Server pre-2005.

Cheers,

Bob

December 27, 2010 8:55 PM
 

jamiet said:

Bob,

Agreed about [dbo]. Even if I were only using one schema it wouldn't be [dbo].

I never really considered the advantage that schemas bring where they allow you to grant privs to non-existent objects. Of course - that could also be considered to be a disadvantage - do you want privs getting assigned automatically? Perhaps, tho perhaps not. Its a good point that you make anyway. That actually reminds me of a debate a few months ago between Brent Ozar & Buck Woody - http://www.brentozar.com/archive/2010/05/why-use-schemas/

Thanks for the clarification on Oracle. I know your other half is an Oracle DBA so you write with some authority here :)

JT

December 28, 2010 5:43 AM
 

WIDBA said:

Schemas are really nice when you have multiple applications writing code at the same database, from permissions management to a nice separation of application code.  The pain of having to deal with multiple roles and users makes it worthwhile.  The developers write code to their schema and the permissions are done.  Using schemas for tables is a bit more problematic, but has its purposes when you want to "hide" tables from users effectively.  (a table that houses sensitive data can be put in another schema that no person with Select on DBO could see, for example)

It really is a big "it depends".

December 28, 2010 9:05 AM
 

Dave Wentzel said:

Bob, I don't understand your comment regarding Oracle and schemas working like pre-SQL 2005.  I didn't think CREATE SCHEMA was valid SQL in SQL Server 2000.  Regardless, the CREATE SCHEMA syntax is more or less identical from Oracle to SQL to the ANSI standard.  Oracle's limitation is that CREATE SCHEMA doesn't actually create the schema as you mentioned, it merely assigns the attributes.  I'm not sure how Oracle never supported real schemas if the definition of a schema is a named collection of objects.  Following that logically then a database is really not much different than a schema anyway, which is how Oracle and MySQL handle it.  In fact CREATE SCHEMA is a synonym for CREATE DATABASE in MySQL.  

December 28, 2010 1:38 PM
 

Mike H. said:

Another chief difficulty is getting consistency and buy in from the designers/architects. I worked on a project that split off user created tables from the primary file group to a set if user created file groups. People either didn't know or care to determine what file group a table should be in. Now, unless the DBA rides herd, new tables often get created in the default file group because the file group was not explicitly identified.

December 28, 2010 1:45 PM
 

Bob Beauchemin said:

Hi Dave,

Actually there was a CREATE SCHEMA statement in SQL Server 2000 (I had to look it up once and was surprised, see http://msdn.microsoft.com/en-us/library/aa258256(SQL.80).aspx). But it didn't create a schema either, schemas were really == user (like they are in Oracle, if they haven't changed since the BOL I cited; it was an Oracle DBA/Dev myself once and had to look it up to ensure it hadn't changed).

It's not really about the syntax as such as it is about the fact that schemas are not necessarily tied to users in SQL 2005 and above. That means that you could create a role (say payroll) assign a schema to the role and move users in and out of the role with impunity. One of the problems that schema solves AFAIK, is that database objects aren't tied to individual users, so objects don't have to move ownership when the users move or leave. And also, if you architect your schemas correctly, you can use them, for the most part, as a privilege boundary, as I mentioned above. Oracle folks do often use users/schemas that do not correspond to a real user, as we did in SQL Server.

I think schemas in Oracle are more like databases in SQL Server/DB2/Sybase. And databases (at least in SQL Server) still have a single (login) owner in SQL Server. So unless you use Windows Group logins (which has its own set of issues) the problem moves up a level. And databases have additional issues when moving them between instances (collation, etc) in database products that support multiple user databases.

December 28, 2010 2:29 PM
 

Peter said:

We have a number of self contained mini-promo websites and the majority of them need a table to store user registration info. Most of the time this is called "users". All these websites were desgined without any schema in mind, so what we do rather than create a separate database for each is to have a single promo database and then create a schema for each site and a user with the default schema set to match.

December 29, 2010 4:32 AM
 

Kristian Wedberg said:

I always deliberately design & use schemas since they do so many (read 3) wonderful things:

- Group objects in alphabetical lists and UIs (e.g. Visual Studio)

- Provide namespaces making related objects easier to move

- Grouped permissions

Your rule of thumb sounds great to decide the size & content of the schemas...

/Kristian

January 4, 2011 8:01 AM
 

DAVE said:

I take each db as it comes.

Where I do uses schemas is in my data warehouse build to help me identify what tables are part of the configuration/run procedure i.e. an ssis parameter table and those that are actual data tables

I do however keep it to a minimum!

January 4, 2011 8:40 AM
 

M Williams said:

My primary work involves

1) loading data from multiple data sources into a staging database

2) conforming and cleansing the data in the staging database

3) delivering the data to multiple destinations

because of this I have fould schemas to be very usefull in my staging database.  This feature allows me to separate my tables according to their source and function.  Mind you, I do not use schemas necessarily to separate business functions but to separate data source tables, staging tables, and delivery tables.

It has been most helpful.

January 10, 2011 3:46 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement