Changing the Execution Context
One of the interesting problems with stored procedures in SQL Server 2000 and earlier was that the code executed with the security context of the owner of the procedure. Developers regularly rely on the fact that all the resources used by a stored proceduretables, views, etc.are owned by the owner of the stored procedure that is accessing the resources. This is called an unbroken ownership chain
. The reason that an unbroken ownership chain is convenient is that SQL Server will verify only that the caller of the stored procedure has execute permission on the procedure. With an unbroken ownership chain, it doesn't bother checking permissions on the underlying objects, since it assumes that it is okay for the owner of the procedure to give access to other objects it owns.
If the ownership chain is brokenthe procedure accesses objects not owned by the procedure's ownerthen SQL Server has to verify that the caller has permissions on all the underlying objects. This is why executing dynamic SQL within a stored procedure requires that the caller have permissions on the objects accessed by the dynamic SQL. This is why Microsoft has long recommended that all tables and objects in a database be owned by the special dbo
user. Since all objects are owned by that single user, you always have an unbroken ownership chain.
But this scheme places some rather stringent limitations on the security design of an application, preventing granular permissions to protect various objects throughout the database. If dbo owns everything, you can't easily grant true ownership to different users or groups who ultimately have responsibility for the objects. You also can't take full advantage of ownership chains to protect resources. You also can't make use of fine-grained permission schemes and control the security context of a procedure; the permissions of the owner is the only security context that really matters. These issues caused developers and admins to take other, messier measures to protect objects in the database. (By the way, this discussion applies to user-defined functionsexcept inline table-valued functionsand triggers as well as stored procedures. To keep the discussion simple I'll just refer to stored procedures.)
|Never again will you have to grant membership in a role with many far-ranging privileges to convey only a narrow permission needed by a user.|
SQL Server 2005 introduces the concept of execution contexts
to overcome these limitations. The idea is that the creator of the procedurewho may or may not be the ultimate ownercan specify the security context of the procedure. The context can be that of a login, role, or database user so you can create users in a database that may or may not map to a server login but which have a fine-grained set of permissions on various objects. The only real limitation is that the creator of the procedure must have permission to impersonate the principal specified as the execution context.
Here's how it works. Say that you have a database table VendorTable
that contains some information about vendors, such as an ID, vendor name, state, and phone number. User TestUser
permission on the underlying table, but ProcOwner
has impersonation rights on TestUser
. The following code executes with ProcOwner
logged into Management Studio, SQL Server 2005's development environment.
CREATE PROC GetVendorByState
WITH EXECUTE AS 'TestUser'
EXEC ('SELECT * FROM VendorTable WHERE state =
'''+ @state +'''')
Obviously there is no real need to use dynamic SQL in this example, but it makes painfully clear that the ownership chain is broken, and SQL Server will have to check permissions on the underlying table. Once this code executes, ProcOwner
can assign execute permission to whomever it wants, and the procedure will run not under the security context of the caller but of TestUser
will have to have SELECT
permission on the VendorTable
The code above executes as a specific user, which is but one of four ways you can use the EXECUTE AS
- EXECUTE AS CALLER means that the security context will be that of the caller of the procedure or function. In this case, there must be an unbroken ownership chain or else the caller must have the required permissions on all objects used. This is the default execution context for backwards compatibility to match how SQL Server 2000 and earlier works.
- EXECUTE AS 'user' is the form used in the sample code above. The permissions of 'user' are used to verify that all objects are accessible. The creator of the procedure or function must have IMPERSONATE permissions on the specified user, although you never need this permission to impersonate yourself.
- EXECUTE AS SELF is just a shorthand notation for the creator of the procedure. So if user ProcOwner creates a procedure with this form of execution context, the security context of ProcOwner will be used to check permissions. Note that the actual user name is associated with the procedure, not some abstract SELF user. If ownership changes and you want the new owner's security context to be used, you'll have to alter the procedure after changing ownership.
- EXECUTE AS OWNER uses the security context of the current owner of the procedure at time of execution. This is probably a better option than EXECUTE AS SELF for the reasons explained in the previous bullet point. This option is appropriate when you want to change owners and use the current owner's permissions, without changing the code.
I suspect that a best practice will eventually emerge that you should use either EXECUTE AS 'user'
or EXECUTE AS OWNER
, but time will tell if such a recommendation emerges.
Changing the execution context of code modules is a great feature for implementing least privilege practices in database applications, letting you create users that have exactly the permissions to do a specific job and no more. But like most such features that provide almost unlimited flexibility, it is easy to get carried away and obfuscate the database's security design so much as to make it impossible to maintain. Use this feature with careful thought and design, and you'll significantly improve database security.