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.
Prev. page
1
[2]
3
next page 
