Re: DBO Schema
- From: "Laurentiu Cristofor [MSFT]" <Laurentiu.Cristofor@xxxxxxxxxx>
- Date: Thu, 18 Jan 2007 12:46:42 -0800
It's definitely not the necessary approach in all cases - you can have
scenarios where this may not be needed. But without a specific scenario
being the subject of discussion, I made the safest geneal recommendation.
I see ownership chaining as a sword with two edges - it is desirable in some
scenarios, but not in others, hence I recommended breaking it intentionally
accross schemas.
We always have to make tradeoffs between security, manageability, and
performance. Because the questions are posted on the security forum, I
emphasize security in my answers ;)
In the end, the most important thing is to be aware of what tradeoffs are
made in a design. There is no substitute for understanding what you're
building.
Thanks
--
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns98BC14195D92Yazorman@xxxxxxxxxxxx
Laurentiu Cristofor [MSFT] (Laurentiu.Cristofor@xxxxxxxxxx) writes:
The question you should ask instead should be: do you have any good
reasons for using the dbo schema? If not, then you should not use it.
Using different schemas owned by different principals helps you separate
the objects contained in them and provides better security for those
objects, because if someone compromises one schema, he'll have a harder
time getting at the data in other schemas. So, schemas can be used for
defense in depth.
I would question that this a useful approach in all cases. For the
system I work with, using schemas would make very much sense, since
we have divided the systems into subsystem, so using schemas could
give each subsystem its own namespace. If we ever take that route
remains to see, but if we ever do it is also clear that all schemas
would be owned by dbo. Anything else would only cause problems with
broken ownership chains and descreased security. Today we grant all
users SELECT on all tables since we some dynamic SQL here and there.
But some of customers do not really like that, and we should abandon
it. But if subsystem schemas would have different owners, ownership
chaining would break (outer subsystems frequently refer to inner
subsystems), which would requires users to also have INSERT, UPDATE
and DELETE privs which is completely unacceptable. Or we would have
to entangle in a web of certificates and procedure signing.
There may of course be situations where it may make sense to have
different owners of objects in a database, but I will have to admit
that I can't really envision such a scenario.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: DBO Schema
- From: Mitch
- Re: DBO Schema
- References:
- Re: DBO Schema
- From: Laurentiu Cristofor [MSFT]
- Re: DBO Schema
- From: Erland Sommarskog
- Re: DBO Schema
- Prev by Date: Re: SQL 2000 & server side trace
- Next by Date: Re: DBO Schema
- Previous by thread: Re: DBO Schema
- Next by thread: Re: DBO Schema
- Index(es):
Relevant Pages
|
|
