SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is the Schema Still Needed?


Is the Schema Still Needed?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63793 Visits: 19116
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/istheschemastillneeded.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
AjarnMark
AjarnMark
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 55
Outlived its usefulness? Heck, I'd be happy if somebody told me what its usefulness ever was. I have never understood the desire to have multiple objects with the same base name in the same database. What is or ever was the benefit?



ccadman
ccadman
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 8
Only being able to speak from personal experience, that is what I will speak from. It is not a feature that I have ever used and the only reason to know about it is so to be sure that an oversight doesn't allow it to cause problems.



Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1884 Visits: 445
Up until about two months ago, I thought it was totally silly and had very little use... But, one, I hope "clever" <g>, of them did spring to mind.

I've been writing a doc management system which is to have very tight security, etc, etc. The customers can write their own custom reports which can be based upon anything in the system. In order to ensure that someone cannot run and report and see something they should not normally see, all users of the system have their own SQL login (which my app will create/destroy/modify as required). Each user will have their own set of views (all with the same name, only distinguished by the schema) which map pretty much map directly to each normal table in the database, except for a discrimination on each view's where clause to exclude rows that view's owner cannot see.

Thus, when a report is written, just the ordinary view names are used (no schemas) - then when an ordinary pleb logs in, they cannot see the additional records that the super user who wrote the report can see. No changes required to the report at all!!

I've done a bit of testing on the above and it seems to work quite well.... Anyone else think of a "good" reason for them??? Particularly when MS bring forth row level security in Yukon (apparently?), the above use will diminish too (does anyone else do things this way or have I missed something)

My 2c.. Cheers,
Ian



ckempste
ckempste
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1409 Visits: 1
Hi Steve

Good point to bring up actually.

This sort of concept comes from the oracle world, but were see a clearer distinction between "user" and "owner" of objects. Here in oracle an instance = a single db with 1 or more "schemas", where schemas can be similar to a single sql server instance with a bunch of user databases. This is purely thinking aloud though and there are other issues to consider of course..

In SQL Server best practice stats "all objects owned by dbo"... so other "schema" users are irrelevant. I tend to disagree, esp during development where people can play around with their own objects and we cut them over to dbo and wrapper the security model around them to formalise the structure/framework of the user objects in the db.

There are some other cases where I like to break the dbo rule, and have a different schema owner for my audit tables.. eg:

dbo.course
dbaudit.course_audit

why? I can still wrapper up all my security privs etc, but at the same time its easily to single out my audit tables and keep them alphabetically listed. Of course, alphabetic listing is not the only reason... Smile

The BIG problem with non-dbo schemas tends to be db import/export via DTS and its inability to match up obj names between schema owners.

Cheers

Ck


Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
mmollerihug
mmollerihug
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
if you go back to the fundamental justifications for having databases (right back to the days hierarchical Dbs for instance) the whole thrust was about data management approaches that would break down isolated "islands of information" that bedevilled the corporate landscapes and caused no end of inconsistencies and confusions.
The schema concept being challenged here came to the relational world in Oracle's products - it was a pretty silly idea then and frankly it still is - because it tends to reinforce the "islands" type thinking. But hey, I won't just bash Oracle for it - if there was a stupid idea lying around then for sure M$ would just have to adopt it too now wouldn't they?



dwestmore
dwestmore
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 1
I vote thumbs down to schemas, for me they're just a problem I could do without (ever seen what happens if someone is the owner of a DB but is not a sysadmin?).
They also add a minimum of 4 useless characters (including the dot) to names, and given that SQL is full of stupid windows that can't be resized (e.g. many wizards) this another nuisance aspect....



David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7793 Visits: 3290
I liked the idea of developing and testing with your own "schema" before cutting across to a dbo implementation.

It is not a facility that I commonly use and I don't know many other people who do. The problem is that if you work in a shop where no-one else uses a facility then who do you learn what the correct use of that facility is?

There are two uses for this facility that spring to mind.
1. Holding personalised settings for an app a'la windows profiles.
2. A centrally managed app where the data and/or other objects have to be isolated according to the user.

These days the sort of isolation and security that is carried out at the application level rather than the database level.

In the case of point 2 its a case of "an" answer rather than "the" answer.

LinkedIn Profile

Newbie on www.simple-talk.com
Philip Kelley
Philip Kelley
SSC Eights!
SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)SSC Eights! (861 reputation)

Group: General Forum Members
Points: 861 Visits: 232
I'd vote a general thumbs-down as well--but they do have their uses.

The primary negative point is Confusion. Schemas are a pretty abstract concept (the closest analogy I can come up with is "parallel dimensions applied to database tables"), and how can you clearly and simply explain schemas to the people who may be required to use them--especially if they're not even clear on why a table is (you'll note I didn't say "what").

Managing them is also confusing. How many schemas can there be in a database? Can you readily and ably manage them and their contents as a coherent set? I don't know anything about this subject, as once I realized what they were I made sure they weren't going to be used.

Lastly, there's the (at least to me) very confusing and harshly underdocumented subject of optimization. In all code (stored procedure or otherwise), "best practice" is you're supposed to qualify all objects with their full ownership chain (i.e., "SELECT * from myDB.dbo.myTable", not "USE myDB" | "SELECT # from myTable"). If so, what's the point of setting context (USE mdDB)? What happens if you have multiple database instances with different names? Or is that just for stored procedures (and which is better, "EXECUTE myDB.dbo.myProc", "EXECUTE dbo.myProc", or "EXECUTE myProc"? I know this underlies optimizing procedure cache hits and utilization, but I've never gotten the long or short of it.)

I do like the idea of setting up "your own set of objects" (your own pocket universe) for development purposes... but again, how easy is it to explain this to the developers? How easy is it to set everything to .dbo when it's time to roll it to a QC environment? How many problems and glitches will arise solely from the fact that everyone was working in their own schema? Me, I just create and destroy a separate copy of the database whenever I need to do something like this... but I'm the DBA and can get away with it. Others are not so lucky.

Now, if it were possible to define or configure a distinct set of schemas (as opposed to one for everyone who can log on with no questions asked), and permit or deny individuals access to them, that might have some benefit. (It's only a thought--I can see this quickly spiraling out of control.)

This should be an interesting thread. Enough two centses add up to dollars...

Philip



jkbrake
jkbrake
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 1
I agree with the article.

Regarding the ownership issue, we provide a separate copy of a database for each developer who needs one to run and test their changes on their own machine. Changes to the production databases are controlled by the DBA group so that the only schema is dbo.

User preferences are done by attaching user identification columns to the appropriate tables where necessary. Users are not allowed to create objects in the database.

I agree with the KISS principle as well.

James Brake
DBA
Associate Electric Cooperative, Inc.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search