Re: DBO Best Practices
From: Kimberly L. Tripp (Kimberly@nospam.sqlskills.com)
Date: 10/24/02
- Next message: Tim: "Giving truncate rights to certain users"
- Previous message: Kimberly L. Tripp: "Re: EXEC master..xp_cmdshell Prevention"
- In reply to: Bill Hollinshead [MS]: "Re: DBO Best Practices"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Kimberly L. Tripp" <Kimberly@nospam.sqlskills.com> Date: Wed, 23 Oct 2002 22:40:35 -0700
Bill's message is EXCELLENT! I just thought I'd add one more note along the
lines of always owner qualifying objects... I tend to recommend this even on
create. I tend to think of the fact that there is someone who is THE dbo and
someone (or a group of people) who are just "a" dbo.
THE dbo is the person who creates a database OR
the person who was given database ownership through sp_changedbowner OR
anyone who is a system administrator that uses that database
A dbo is anyone who is a member of the db_owner role
The difference between the two is that objects created by THE dbo are
automatically named dbo.
Objects created by A dbo are automatically named with the creator's username
as the object owner.
When developers are lazy (as Bill mentions) optimization plans are not
reused and when ownership chains are broken permissions get very complex.
Best habits are to be very careful with all object ownership issues and
ALWAYS owner qualify EVERYTHING.
CREATE TABLE dbo.table
EXEC dbo.proc
SELECT a.col, b.col
FROM dbo.table as a
JOIN dbo.table as b
Good naming conventions, always owner qualifying objects, etc. can really
help your performance, readability and optimization plan reuse.
hth,
kt
""Bill Hollinshead [MS]"" <billhol@online.microsoft.com> wrote in message
news:8WKfyhqeCHA.1340@cpmsftngxa08...
> Hi Tim,
>
> Somewhat of an aside: If you let DBO own databases or objects then regular
> users (those who are not sysadmins, db_owners, etc) will not be able to
> alter those same objects - which is why
> http://msdn.microsoft.com/library/en-us/vdtsql/dvmsgstx_userisnotdbo.asp
> can be seen. On the other hand, using DBO as the object owner does
> unfortunately allow developers to write 'lazy' or 'sloppy' <g> code that
> doesn't explicitly reference the object's owner (because SQL Server will
> look for an object owned by the current user and if not found will
> automatically look the object owned by DBO). This lazy practice is sloppy
> because such T-SQL's execution plan, even though cached, cannot be reused
> by other users. This doesn't mean DBO shouldn't own the objects, it just
> means that is best to always reference an object's owner.
>
> Please note that the DBO user is identified by the SQL Server process
> (i.e., the SQL Server application) - see
> http://msdn.microsoft.com/library/en-us/adminsql/ad_security_9qyh.asp.
Thus
> the permissions of a DBO (within SQL Server's process space) relate to the
> objects that SQL Server provides its logins (databases, tables, views,
> etc.). Even so, the role membership of a user also affects how SQL Server
> allows a external process to be spawned - for example, SQL Server
considers
> the role membership of a SQL Server user if that user attempts to run
> xp_cmdshell - see the "Important" note under the Permissions section of
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp.
>
> In contrast to user permissions, the SQL Server startup account (for the
> SQL Server process) controls SQL Server's ability to work with NT's
objects
> (files, hives, log on as a service, etc.). The startup account defines the
> security context of the SQL Server process, and this definition is well
> before the SQL Server process accepts logins (and thus well before SQL
> Server identifies any particular SQL Server login as a user or a member of
> a role, and thus before a DBO has been identified). Because a DBO user can
> spawn a process under the security context of the SQL Server startup
> account, it usually is best to allow the SQL Server process have the
> minimum permissions needed for SQL Server to function. See
> http://msdn.microsoft.com/library/en-us/instsql/in_overview_6k1f.asp.
>
> And because a DBO user can gain the security context of the SQL Server
> startup account, it is important to 1) avoid letting clients login as a
> member of the sysadmin role and thus become a DBO user (unless
specifically
> desired), and 2) avoid giving the SQL Server startup account more
> permissions than needed. Otherwise, those who are DBO (and hopefully there
> will be very few that are allowed to be identified as such) and run
> xp_cmdshell will gain the SQL Server startup account's security context
> (i.e., permissions).
>
> Far more information about security is available at
> http://www.microsoft.com/sql/techinfo/administration/2000/security.asp,
> especially within the Security White Paper.
>
> Note that it does not matter whether the same NT account both starts the
> SQL Server service and is identified (once SQL Server starts) to be a
> member of SQL Server's sysadmin role (i.e., identified as a DBO). These
two
> accounts are very separate, one is a NT account and the other is a SQL
> Server login. SQL Server will not be aware that they are the same account
> even when SQL Server and NT are configured in such a manner <g>.
>
>
> Thanks,
>
> Bill Hollinshead
> Microsoft, SQL Server
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
- Next message: Tim: "Giving truncate rights to certain users"
- Previous message: Kimberly L. Tripp: "Re: EXEC master..xp_cmdshell Prevention"
- In reply to: Bill Hollinshead [MS]: "Re: DBO Best Practices"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
