Re: DBO Schema

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


.

Relevant Pages

  • Re: DBO Schema
    ... Because the questions are posted on the security forum, ... Using different schemas owned by different principals helps you separate ... give each subsystem its own namespace. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: DBO Schema
    ... reasons for using the dbo schema? ... Using different schemas owned by different principals helps you separate ... give each subsystem its own namespace. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Granting Permissions to Roles in SQL Server 2005
    ... Schema.ObjectName, or just ObjectName in all of our code. ... No one forces you to use schemas. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Granting Permissions to Roles in SQL Server 2005
    ... Schema.ObjectName, or just ObjectName in all of our code. ... No one forces you to use schemas. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (microsoft.public.sqlserver.security)
  • Re: BizTalk 2006 VS 2005 Project templates
    ... expect Visual Studio to work without freezing like this. ... SQL Server 2k5 sp1 ... BizTalk Server 2006 Ent Ed. ... freezing is caused by not setting a Root Reference in schemas. ...
    (microsoft.public.biztalk.general)
Loading