842
Part VI: Securing Your SQL Server
Ownership chains are great for developing tight security in which users execute stored pro-
cedures but aren’t granted direct permission to any tables.If the ownership chain is broken, meaning that there’s a different owner between an object
and the next lower object, then SQL Server checks the user’s permission for every object
accessed.The EXECUTE AS clause, added in SQL Server 2005, adds flexibility to control effective
permissions under which SQL Server objects are accessed in queries, stored procedures,
functions, and triggers. While EXECUTE AS provides additional control capabilities, it
breaks ownership chaining because the user executing the query, stored procedure, or
function impersonates the specified user in the EXECUTE AS clause.When the chain is broken, the following happens:■ The ownership chain from dbo.A to dbo.B to dbo.Person is unbroken, so dbo.A can
call dbo.B and access dbo.Person as dbo.
■ The ownership chain from dbo.A to Sue.C to Jose.Purchase is broken because dif-
ferent owners are present. Therefore, dbo.A calls Sue.C using Jose's permissions,
and Sue.C accesses Jose.Purchase using Jose's permissions.■ (^) The ownership chain from dbo.A through dbo.B to Jose.Person is also broken, so
dbo.A calls dbo.B using dbo’s permissions, but dbo.B must access Jose.Purchase
using Jose's permissions.
■ It is possible for dbo, Sue, and Jose to all have the same owner. In that case, the
ownership chain works.
Object Ownership
An important aspect of SQL Server’s security model involves object ownership. Every
object is contained by a schema. The default schema is dbo — not to be confused with
the dbo role.Ownership becomes critical when permission is granted to a user to run a stored procedure
when the user doesn’t have permission to the underlying tables. If the ownership chain
from the tables to the stored procedure is consistent, then the user can access the stored
procedure and the stored procedure can access the tables as its owner. However, if the own-
ership chain is broken, meaning there’s a different owner somewhere between the stored
procedure and the table, the user must have rights to the stored procedure, the underlying
tables, and every other object in between.There is a fi ne point in the details: A schema is owned. And because a schema is owned,
anything contained by it has the same owner.c33.indd 842c33.indd 842 7/31/2012 10:01:24 AM7/31/2012 10:01:24 AM
http://www.it-ebooks.info