Security Through Ownership Chains

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.

Ownership Chain Links

SQL Server identifies object owners by their security ID (SID)—not by username—in ownership chains. Because the SID is the link in the ownership chain, you can think of objects as owned by SQL Server logins rather than database users.

The subtle difference between username and SID isn't a concern with intradatabase ownership chains because each object owner (database user) is mapped to one login. As a result, all objects that a user owns in a single database are always associated with the same SID. However, when multiple databases are involved, you need to consider that users who have the same name in different databases can be associated with different logins; when the different logins show up in the ownership chain, the chain is broken. Also, a login might be mapped to different usernames in different databases. Such a situation doesn't break the ownership chain even though the object owner names are different.

The code in Listing 1 shows the importance of SIDs in inter-database ownership chains. The script creates two databases, Database1 and Database2, which are owned by logins DatabaseOwner1 and DatabaseOwner2, respectively. View [Database2].[dbo].[MyView] references table [Database1].[dbo].[MyTable], and to the public role, I've granted SELECT permissions on the view but not on the table. The code then attempts to select from the view under the guest user security context.

The ownership chain at first appears unbroken because the table and view owners are both DBO. However, the DBO users are mapped to different logins. Consequently, the object owner SIDs are different, and the interdatabase ownership chain is broken. The result is that the first SELECT statement fails with a permission error because the user doesn't have SELECT permission on the table.

When database ownership changes so that the same login (DatabaseOwner1) owns both databases, the SELECT statement succeeds even though the user doesn't have permission to access the table directly. The ownership chain is unbroken in this case because the DBO user in each database maps to the same login. SQL Server Books Online (BOL) isn't clear about the importance of database ownership in interdatabase ownership chains. For a clearer, more detailed explanation of how SQL Server determines whether to check permissions, see the Microsoft article "INF: Object Ownership Chain Checking Across Databases Depends on Database Ownership."

Note that I've included the SETUSER command in the script to illustrate the behavior of cross-database ownership chains. SETUSER lets a DBA test security through a single database connection, but the command is included in SQL Server 2000 and 7.0 for backward compatibility only; you shouldn't use SETUSER in production code because the command's behavior might change in future SQL Server releases or service packs. You can achieve the same result without SETUSER by executing the SELECT statements when you're logged on as any non-sa user other than DatabaseOwner1. You can't use the DatabaseOwner1 login to test security because it has full permissions on all Database1 objects. Similarly, the sa login is the DBO in all databases and has full permissions on all objects.

For an interdatabase ownership chain to be unbroken, object owners in the different databases must map to the same login. You can execute the sp_helpuser system stored procedure in each database to get a report that shows login and user mapping.

You can use the sp_changedbowner system stored procedure to change database ownership so that all DBO-owned objects in the databases involved in the chain map to the same login SID. When you use sp_changedbowner, you might get the ambiguous error 15110, The proposed new database owner is already a user in the database, if SQL Server detects a mismatch between the DBO SID recorded in the sysdatabases and sysusers system tables. Such a mismatch might occur if someone other than the original owner restored the database. The SID column in sysdatabases shows the user who restored the database, but the DBO SID in the sysusers table still contains the original owner.

You can easily correct a SID mismatch. The script that Listing 2 shows temporarily changes database ownership to a non-conflicting login (TempOwner), which synchronizes the DBO in the sysdatabases and sysusers tables. You can then change the DBO to the desired login (sa).

Interdatabase Considerations

SQL Server logins must have access to all the databases they use, even when they access objects indirectly, as in the case of a view referencing a table in another database. However, rather than add individual users to each database, you might want to employ the guest user in situations in which users don't need direct access to database objects. This kind of setup simplifies security administration because all logins can share the guest user context. You can add the guest user to user databases by using the sp_grantdbaccess system stored procedure. Note that SQL Server automatically adds the guest user to the master, msdb, and tempdb system databases.

You can use your knowledge of interdatabase ownership chains to control the use of master database objects, especially extended stored procedures and OLE Automation (sp_OA*) stored procedures. These procedures can provide users with helpful application functionality but can pose a considerable security risk if used inappropriately. For example, the xp_cmdshell extended stored procedure is both powerful and dangerous. It can execute virtually any program or OS command through a single T-SQL statement and provides a simple and efficient method for applications to invoke command-line utilities such as DTSRUN or bulk copy program (bcp) directly on the database server. However, unless implemented carefully, xp_cmdshell can let unauthorized users execute commands.

By default, only members of the sysadmin fixed server role can execute xp_cmdshell. A non-sysadmin user who has EXECUTE permission on xp_cmdshell can execute ad hoc OS commands and programs. Non-sysadmin users are limited only by the xp_cmdshell OS security context, which is the local SQLAgentCmdExec account in SQL Server 7.0, and by the configurable SQL Server Agent proxy account in SQL Server 2000. Members of the sysadmin fixed server role run under the security context of the SQL Server service account.

A common misconception is that non-sysadmin users need to have direct EXECUTE permissions on xp_cmdshell to use the procedure. This is not the case, thanks to interdatabase ownership chains. You can disallow ad hoc commands while still letting applications exploit xp_cmdshell functionality by creating a DBO-owned user stored procedure in an sa-owned user database that executes xp_cmdshell. The ownership chain isn't broken because the owner of both procedures (the DBO) maps to the same login (sa). Users need to have EXECUTE permissions only on the user procedure, and they can access the master database as the guest user.

This indirect access to xp_cmdshell provides an additional layer of security because users are confined to using only the code in the stored procedure. However, you need to construct the xp_cmdshell command within the stored procedure in a way that prevents users from executing commands other than the ones you intend them to execute.

For example, an application could use a stored procedure to export data from a table into a text file by executing the bcp command-line utility through xp_cmdshell. The sample procedure usp_UnsecureBCPCommand, which Listing 3 shows, executes the passed parameter directly with no restrictions. Consequently, users who have EXECUTE permissions on usp_UnsecureBCPCommand have the same power as users who have direct EXECUTE permissions on xp_cmdshell—they can execute any ad hoc command. You can plug this security hole by using Listing 4's usp_SecureBCPCommand procedure, which doesn't accept ad hoc commands. Users are confined to exporting files to a predetermined location. Similarly, you can control access to other master database objects such as xp_sendmail and the sp_OA* procedures.

Some security risks are inherent with sa database ownership of user databases. Users who have permissions to create a DBO-owned procedure in an sa-owned user database can create and execute a procedure that executes xp_cmdshell without restrictions. Consequently, you should scrutinize the membership of the db_owner and ddl_admin fixed database roles in a production environment in which sa owns a user database.

Application-Role Considerations

Application roles are also useful for limiting ad hoc database access. (For an introduction to application roles, see Andy Baron and Mary Chipman's article "Mastering Application Roles," June 2001.) A limitation of application roles is that an application role is recognized only within a single database, so users can access other databases as the guest user only after the role is activated. Multi-database applications that use application roles must grant object permissions to the guest user in other databases, either directly or through role membership. However, you often want to avoid granting object permissions to the guest user because when you do so, you effectively give all SQL Server logins permissions on the object.

You can use indirect object permissions to augment application-role security in multidatabase applications. When you create referencing objects in the application-role database (e.g., a view that selects from a table in another database), application-role users can access objects in other databases even when they don't have direct permissions. The referenced database simply needs to contain the guest user, and the ownership chain needs to be unbroken. Interdatabase access by application-role users is identical to interdatabase access by non-application­role users, except that the users must access other databases through the guest user security context.

Best Practices

In most production SQL Server implementations, the DBA commonly creates all objects with DBO as the owner. DBO ownership of all objects guarantees that intradatabase ownership chains are never broken. Furthermore, making sa the owner of all databases ensures that interdatabase ownership chains also are never broken. Standardized ownership simplifies administration and makes unbroken ownership chains automatic—you need to grant permissions only on objects that users and applications use directly. Several other best practices complement this strategy:

EXECUTE sp_executesql N'SELECT * FROM MyTable'

requires that the user have SELECT permission on MyTable even when the same user owns the procedure and table.

An unbroken ownership chain is an important and powerful weapon in a DBA's arsenal. The feature provides the means for a flexible application security architecture and maximizes performance. With prudent planning and practices, an unbroken ownership chain becomes an essential device in SQL Server security deployment.