SQL Server Magazine

Ownership chains are the foundation for a flexible and robust security scheme. The feature has been around since the early days of SQL Server, but its full potential is seldom realized because even some experienced SQL Server DBAs don't understand all aspects of how ownership chains work. Although DBAs commonly use ownership chains as a security tool in a single database, they don't always employ cross-database ownership chains when appropriate. You can use interdatabase ownership chains to reduce your security administration burden and provide a secure environment. You need to understand a few basic concepts and techniques to maximize the power of ownership chains in an easy-to-maintain security model with SQL Server 2000 and 7.0.

Controlling Object Access
The basis for SQL Server database object security is that all database objects have an owner and the owner controls access by granting object permissions to users and roles. When an object depends on another object, such as when a view references a table, an ownership chain is established. The ownership chain remains unbroken as long as the same user owns all of the objects involved.

Ownership chains are the foundation for a flexible and robust security scheme. The feature has been around since the early days of SQL Server, but its full potential is seldom realized because even experienced SQL Server DBAs don't always understand some aspects of how ownership chains work. Although DBAs commonly use ownership chains as a security tool in a single database, they don't always employ cross-database ownership chains when appropriate. You can use inter-database ownership chains to reduce your security administration burden and provide a secure environment. You need to understand a few basic concepts and techniques to maximize the power of ownership chains in an easy-to-maintain security model with SQL Server 2000 and 7.0.

Ownership Chain Concepts
The basis for SQL Server database object security is that all database objects have an owner and the owner controls access by granting object permissions to users and roles. When an object depends on another object, such as when a view references a table, an ownership chain is established. The ownership chain remains unbroken as long as the same user owns all of the objects involved.

A user who has SELECT permission on a view can retrieve data from the view even without permissions on the underlying tables as long as the view and tables have the same owner. An unbroken ownership chain gives object owners more control over access to underlying data because users need permissions only on objects they directly access. The object owner can allow access to data through views, stored procedures, and functions while preventing direct access to the underlying tables. However, object owners need to remember that granting permissions gives users indirect access to the referenced objects.

When referencing and referenced objects have different owners, the ownership chain is broken, so SQL Server checks current user permissions on the referenced objects during query execution. Although an object might reference objects that another user owns in a broken ownership chain, the owner of the referenced objects has to grant permissions on those objects. Thus, object owners retain control over the accessibility of their database objects and data.

The rules governing ownership chains apply only to object permissions, including SELECT, INSERT, UPDATE, DELETE, and EXECUTE permissions on tables, views, procedures, and functions. SQL Server always checks users' permissions to statements such as BACKUP, CREATE, and other Data Definition Language (DDL) statements because these permissions apply to the statement rather than a specific object.

An Unbroken Chain
Ownership chains can help you accomplish security objectives, which include making data available only to authorized users and ensuring data integrity by providing consistent and controlled access to data. You can employ an unbroken ownership chain to limit users to a subset of columns (vertical partitioning), a subset of rows (horizontal partitioning), or both by creating objects such as filtering views, stored procedures, and functions that filter data from underlying tables. Authorized users who don't have direct access to the tables can still access data through the referencing object because the ownership chain is unbroken. However, users are confined to the filtered data subset. Consequently, you can better control access to sensitive data by granting permissions on the objects rather than allowing direct access to tables.

An unbroken ownership chain also provides more control over data modification. You can encapsulate data-manipulation code in stored procedures so that users execute stored procedures to perform data modification rather than updating tables directly. This approach gives users only limited ways to modify data, and essential table data remains consistent and secure. The unbroken ownership chain that you establish eliminates the need for INSERT, UPDATE, and DELETE permissions on the underlying tables. Not only does the stored procedure interface ensure that users modify data in a consistent way, but it gives you more freedom to make schema changes without breaking user or application queries.