Recently, I've been discussing security in SQL Server 2000. In my February column, "Crossing the Line: Ownership Chains," 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," 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.
EXECUTE AS SELF. EXECUTE AS SELF means "execute as the current user who's creating or altering the module." EXECUTE AS SELF is the same as EXECUTE AS USER = user_name, where the specified user is the person creating or altering the routine. The catalog stores the user's user ID (UID) rather than the value SELF.
Note that the user that SELF refers to doesn't necessarily own the object. Objects don't really have owners in SQL Server 2005, but you can think of schema owners as also owning all objects in their schemas. Users can have permissions to create objects in schemas that they don't own. For example, Susan can have ALTER SCHEMA permission on a schema that Joe owns. If Susan creates a table in Joe's schema, Joe is the owner and Susan is the creator; any procedure Susan created to EXECUTE AS SELF will execute in the context of Susan.
Which Option to Choose
This new feature might take some time to figure out, so here are some guidelines about which EXECUTE AS option to use in which situation. Use EXECUTE AS CALLER in the following cases:
- when you want the statements in the routine to execute in the context of the calling user
- when you want SQL Server to check permissions for the statements in the routine against the calling user and rely only on ownership chaining to bypass permission checks on underlying objects
- when you reference only objects in schemas that have the same owner and can therefore rely on ownership chaining to provide access to dependent objects
Use EXECUTE AS USER = user_name in the following situations:
- when you want the statements in the routine to execute in the context of a specified user
- when you can't rely on ownership chaining to hide the underlying schema (e.g., when the routine accesses objects that have different owners), and you want to avoid granting permissions on referenced objects
- when you want to create a custom permission set
In many cases, you might want to grant a permission that usually isn't grantable, such as permission to TRUNCATE TABLE. Even in SQL Server 2005, you can't grant this permission. But the database owner can write a stored procedure that truncates the table. If the database owner and the table owner are the same, you have an unbroken ownership chain. The benefits of ownership chaining apply only to Data Manipulation Language (DML) statements (SELECT, INSERT, UPDATE, and DELETE), so a user who has permission to run the procedure will still get a permission violation for the statement that truncates the table. But if you use the EXECUTE AS clause to create the procedure, the user running the procedure can assume another user's identity when running the procedure, and the TRUNCATE TABLE statement will succeed.
You'd use EXECUTE AS SELF when you have an application that creates routines for users calling into it and you want those routines to be created in the execution context of those users. In this scenario, you don't know at design time what the calling username will be.
User-Schema Separation
Another big change in the security model in SQL Server 2005 simplifies the relationship between users and objects and lets you add and drop users without needing to worry about objects the users own. For ANSI compliance, SQL Server 2005 distinguishes between users and schemas.
The ANSI SQL-92 standard defines a schema as a collection of database objects that one user owns and that form one namespace (a set of objects that can't have duplicate names). For example, two tables can have the same name only if they're in separate schemas; no two tables in a schema can have the same name. You can think of a schema as a container of objects. (Note that with database tools, schema refers to the catalog information that describes objects in a schema or database. In Analysis Services, a schema describes multidimensional objects such as cubes and dimensions.)
SQL Server 2005 breaks the linking of users to schemas; either primary or secondary principals can own schemas. The term principal here means an entity that can access securable objects. A primary principal represents a single user (such as a SQL Server or Windows login); a secondary principal represents multiple users (such as a role or a Windows group). Another change in SQL Server 2005 is that objects don't have owners. Whoever owns a schema also "owns" the objects it contains. Note that schemas contain objects, not users.
Every new database you create in SQL Server 2005 includes several schemas. Corresponding to the dbo, INFORMATION_SCHEMA, and guest "users" in SQL Server 2000, each SQL Server 2005 database has schemas with these names. In addition, every SQL Server 2005 database has a schema called sys, which gives users access to all the system tables and views. Finally, every predefined database role from SQL Server 2000 corresponds to a schema of the same name in SQL Server 2005.
You can assign new users a default schema that might not exist when you create the user. A user's default schema is used for name resolution during object creation or object reference. As I mentioned in "Object Ownership and Security," if you didn't specify a default schema for a user, the user's default schema is the dbo schema. SQL Server 2005 will always check the sys schema first for object access, regardless of the user's default schema. For example, if user Sue runs the query SELECT * FROM table1, and the default schema for Sue is SueSchema, the name resolution would follow these steps:
- Look for sys.table1
- Look for SueSchema.table1
- Look for dbo.table1
This lookup mechanism applies to all statements, even ALTER and DROP. Note that when a sysadmin creates an object that has a one-part name, the schema is always dbo. However, a sysadmin can explicitly specify an alternate schema to create an object in.
Backward Compatibility
SQL Server 2000 doesn't separate usernames and schema—it doesn't even contain the concept of schema. Thus, SQL Server 2000 stamps any object created by a user with the username in place of a schema. In SQL Server 2005, to maintain backward compatibility, Microsoft rewrote the sp_grantdbaccess and sp_adduser procedures to use the new Data Definition Language (DDL) statements I discuss in the sidebar "New DDL."
In SQL Server 2005, sp_grantdbaccess creates a user and a schema that has the same name and ID as the user. This schema is the user's default schema, and the user is the schema owner. For example, if someone executes the procedure
EXEC sp_grantdbaccess Susan, sue, db_datareader
SQL Server 2005 translates it internally into the new DDL, as Listing 1 shows. In addition, SQL Server 2005 internally rewrites sp_revokedbaccess sue as
DROP SCHEMA sue DROP USER sue
Note that SQL Server 2005 drops the schema only if it has the same name and ID as the user.
Remember that if you run CREATE USER instead of sp_grantdbaccess and you don't specify a default schema, the default schema is dbo. SQL Server 2005 won't create a new schema with the same name as the user.
Get Ready Now
Even if you don't plan to upgrade immediately to SQL Server 2005 when it's released, I recommend you start thinking of users and schemas as separate things. In SQL Server 2000, schemas always have the same name as users, but you can keep them straight by remembering that you grant permissions to users, whereas schemas contain (and qualify) objects.
The separation of users and schemas will ease administration in several ways. The biggest benefit is that managing users, particularly dropping users, will be much easier. In addition, you can set multiple users as owners of a schema by using secondary principals (e.g., groups, roles). And multiple users can have the same default schema for common name resolution.
The ability to specify a user as the executor for a particular module gives you more control over object access and is a big improvement over the cross-database ownership chaining control that Microsoft introduced in SQL Server 2000 Service Pack 3 (SP3). Cross-database ownership is an all-or-nothing solution: Ownership chaining applies across databases, or it doesn't. With the additional control in SQL Server 2005, a module developer can decide which modules users can execute and which ones they can't.
These enhancements are only the tip of the Yukon security-feature iceberg. Stay tuned for more new security features, and as we get closer to SQL Server 2005's release, I'll dig into details about using the features.
