Security Through Ownership Chains

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, InstantDoc ID 20534.) 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:

• Add users to the fixed database roles (e.g., db_owner, db_ddladmin) rather than making users DBOs. Fixed database role membership lets users perform administrative functions while maintaining an unbroken ownership chain to other databases.
• Grant object permissions only to database roles, and control object access through user role membership. Although this practice isn't directly related to ownership chains, roles reduce the effort necessary to manage security.
• Add users to databases only when they require direct object access. When users need only indirect access, add the guest user to the database. You don't need to grant object permissions to the guest user when you have an unbroken ownership chain and indirect object access.
• Avoid dynamic SQL in stored procedures. On statements that run dynamically through EXECUTE or sp_executesql, SQL Server checks permissions in the security context of the user who executed the procedure instead of the procedure owner. As a result, users must have permissions on all objects that are directly referenced by dynamically executed statements. For example, the statement

EXECUTE sp_executesql N'SELECT * FROM MyTable'

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.