Recently, I've been discussing security in SQL Server 2000. In my February column, "Crossing the Line: Ownership Chains" (InstantDoc ID 40963), I talked about the limitations of ownership chaining and the additional security concerns inherent in cross-database ownership chaining. In my April column, "Object Ownership and Security" (InstantDoc ID 41773), I talked about the confusions and limitations surrounding SQL Server 2000's model, which doesn't separate the concepts of user and schema. This month, let's look at some security enhancements in SQL Server 2005, formerly code-named Yukon, that address these security limitations.
Microsoft made security a major focus of SQL Server 2005, addressing and improving many aspects of security that were weak or incomplete in previous releases. The most important change is that SQL Server 2005 is secure right out of the box, even if you install it with all the default setup options. Security terminology in SQL Server 2005 is also different. In addition to the term schema, which I mentioned last month, I discuss in this article the security concepts of authentication and authorization. These new concepts provide the foundation for SQL Server 2005's security model.
You can think of a schema as a container in which a developer creates database objects. When referencing objects in SQL Server 2005, you refer to the schema that contains the object, and not to the object owner. Authentication is a process by which SQL Server validates and establishes the identity of an individual attempting to access a resource. Authorization is the process by which SQL Server decides whether to allow a given identity access to a resource.
Execution Context of Programmable Modules
Ownership chaining is an aspect of authorization by which SQL Server automatically authorizes one user (e.g., user1) to access objects another user (e.g., user2) owns if user1 has permission to execute a programmable module (stored procedure or function) that user2 owns. However, aside from the mechanism of ownership chaining, SQL Server 2000 has no technology for controlling user authentication during access of dependent objects. A user always executes a module as himself or herself and can access dependent objects only if one of the following two conditions is true:
- The dependent objects have the same owner as the module owner.
- The user executing the module has explicit permissions to access the objects.
SQL Server 2005 gives you the ability to define the execution context of stored procedures and user-defined functions (UDFs)—except inline table-valued functions—by using the EXECUTE AS clause at the top of the module definition header. This ability can give an application developer greater control over authentication by letting users perform actions within a module as if they were authenticated as a different user.
EXECUTE AS can be useful when your module uses dynamic SQL. SQL Server 2000 always checks permissions for each dynamic SQL statement you execute, ignoring condition number 1. (Ownership chaining never applies.) If the caller of the procedure doesn't have permission on the objects referenced in the dynamic SQL, the execution fails. However, in SQL Server 2005, if a user created the procedure containing the dynamic SQL to run in the context of a user who has permission on the referenced objects, execution succeeds. In the Beta 2 release of SQL Server 2005, the EXECUTE AS clause gives three possible options for authorization: CALLER (default), USER = user_name, and SELF.
EXECUTE AS CALLER. EXECUTE AS CALLER, the default behavior, is backward-compatible with SQL Server 2000. When a programmer specifies EXECUTE AS CALLER, the statements inside the module execute in the context of the routine's caller. Therefore, the user executing the routine must have appropriate permissions not only on the routine but also on any database objects that the routine references. Permissions on the referenced objects might be granted to the caller explicitly or might be implied because of the ownership chain. In the latter case, SQL Server 2005 evaluates permissions on the referenced objects by examining the ownership chain between calling objects and referenced objects, just as SQL Server 2000 does. However, keep in mind that in SQL Server 2005, objects don't have owners. Schemas have owners and contain objects.
EXECUTE AS USER = user_name. When you specify EXECUTE AS USER = user_name, the routine executes in the context of the user specified as user_name. When the routine executes, SQL Server first verifies that the user executing the routine has EXECUTE permission on the routine; then, it verifies permissions for statements within the routine against user_name. You can't arbitrarily choose a username for people to use when they execute your module. To specify AS some particular name, you must have special permissions (such as IMPERSONATE) or be a member of a special role (e.g., sysadmin, db_owner).
Suppose user Susan creates a stored procedure in her default schema (which she owns) that references a table in a schema (JoeSchema) that she doesn't own. (Susan must have permission to create a procedure in JoeSchema if she doesn't own it.) However, Mary has SELECT permissions on the table in JoeSchema. Susan specifies EXECUTE AS USER = Mary in the CREATE PROCEDURE statement, as follows:
CREATE PROCEDURE AccessMyTable
WITH EXECUTE AS USER = Mary
AS SELECT * FROM JoeSchema.MyTable
Then, Susan grants EXECUTE permissions on the stored procedure to user Scott. When Scott executes the stored procedure, SQL Server verifies that he has permission to execute the stored procedure; however, it checks Mary's permissions on the referenced table. In this scenario, even though Scott doesn't have SELECT permissions on the table directly, he can access the data through the procedure because the procedure runs in Mary's context, and Mary has access to the table.
Prev. page
[1]
2
3
next page 
