DBO Schema

I understand there are advantages to creating schemas rather than using the
default dbo schema, but are there any good reasons not to use dbo? Are ther
e
any white papers out there that talk about advantages and disadvantages?
Thanks,
MitchMitch (Mitch@.discussions.microsoft.com) writes:
> I understand there are advantages to creating schemas rather than using
> the default dbo schema, but are there any good reasons not to use dbo?
> Are there any white papers out there that talk about advantages and
> disadvantages?
Let me put it this way: as long as you think one namespace is all you
need, stick with dbo. And there are many situations where one namespace is
OK.
But if the database is big, and there are several teams working
independently, there is a very good idea to have separate schemas to
avoid clashes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.
Using the dbo schema is particularly bad because it is owned by dbo and
you'll have to grant permissions on objects owned by dbo, which, if
compromised, could allow an attacker to execute code with the permissions of
dbo. By not using the dbo schema you are following the principle of minimal
privileges.
If you choose to use the dbo schema instead of different schemas owned by
different principals, then you are avoiding to enforce two significant
security principles.
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.
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:F727A8E0-533B-4625-8175-BF839366F5E3@.microsoft.com...
>I understand there are advantages to creating schemas rather than using the
> default dbo schema, but are there any good reasons not to use dbo? Are
> there
> any white papers out there that talk about advantages and disadvantages?
> Thanks,
> Mitch|||Laurentiu Cristofor [MSFT] (Laurentiu.Cristofor@.nospam.com) 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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In this case, there will be one database user, but there will also be about
700 tables. I was thinking it would be useful to have related tables groupe
d
into different schemas to give a visual relation at first glance.
Also, the user now has db_owner role, but I really don't think it needs to
have more than read/write.
"Erland Sommarskog" wrote:

> Laurentiu Cristofor [MSFT] (Laurentiu.Cristofor@.nospam.com) writes:
> 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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||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@.sommarskog.se> wrote in message
news:Xns98BC14195D92Yazorman@.127.0.0.1...
> Laurentiu Cristofor [MSFT] (Laurentiu.Cristofor@.nospam.com) writes:
> 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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||In this case, there will be about 700 tables. I was thinking it would be
nice to group related tables by schema to quickly see the groupings.
Also, there is only 1 actual database user. It currently has db_owner, but
I believe it only needs read/write privileges.
"Laurentiu Cristofor [MSFT]" wrote:

> 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 so
me
> scenarios, but not in others, hence I recommended breaking it intentionall
y
> 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@.sommarskog.se> wrote in message
> news:Xns98BC14195D92Yazorman@.127.0.0.1...
>
>|||Mitch (Mitch@.discussions.microsoft.com) writes:
> In this case, there will be one database user, but there will also be
> about 700 tables. I was thinking it would be useful to have related
> tables grouped into different schemas to give a visual relation at first
> glance.
So was your question aksed from a security perspective, or from a
modularisation perspective?
Yes, with 700 tables multiple schemas can be a good idea, as it may
make the data model easier to understand and approach.
Also, another advantage with using different schemas, is that it
becomes natural to always use two-part notation. There are situations
where this improves performance.

> Also, the user now has db_owner role, but I really don't think it needs to
> have more than read/write.
I suppose this single database user will be a proxy for a lot of real
users? Yes, this user should definitely not have more rights than
necessary. Ideally, you should use stored procedures and all the
user would need is execution rights on the schemas. If you need to
use dynamic SQL in some places, this is best addressed with signing
these particular procedures with certificate.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx