Re: DBO Schema
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 17 Jan 2007 15:07:25 -0800
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: Laurentiu Cristofor [MSFT]
- Re: DBO Schema
- References:
- Re: DBO Schema
- From: Laurentiu Cristofor [MSFT]
- Re: DBO Schema
- Prev by Date: SQL 2000 & server side trace
- Next by Date: Re: SQL 2000 & server side trace
- Previous by thread: Re: DBO Schema
- Next by thread: Re: DBO Schema
- Index(es):
Relevant Pages
|
|
