LearnNowOnline

Blog

SQL Security Fundamentals: Changing the Execution

Excerpt by Don Kiely | June 21, 2013

You don't always want the caller's permissions to be used to validate permissions in a broken ownership chain. Sometimes you want to execute the code as though it was being executed by another user entirely, by using that other user's permissions to validate permissions on all of the accessed objects.

This is called switching the execution context of the code. This lets you take advantage of SQL Server's granular permissions, keeping tight control over the permissions to underlying objects, while giving various users the ability to execute the code.

In SQL Server, when you define any kind of user-defined functions (except inline table-valued functions), stored procedures, and data manipulation triggers, you can use the EXECUTE AS clause as part of the definition of the code to run the code under the security context of the specified user.

Four EXECUTE AS options are available:

• EXECUTE AS CALLER: The default for backward compatibility. The code executes in the context of the caller of the code, who must have permissions both to execute the code and to access underlying objects. The actual behavior depends on whether the ownership chain is broken or unbroken.
• EXECUTE AS= 'username' and EXECUTE AS = 'loginname': The code executes in the context of the specified user or login, so that the user must have permissions on underlying objects. In this case, the caller must either:
  • Have execute permission on the code.
  • Be a sysadmin or db_owner, or have CONTROL SERVER >permissions on the server or the database, or have impersonate permission for the username.>
• EXECUTE AS SELF: A shortcut notation for the current user who is creating the procedure. This is equivalent to EXECUTE AS USER = [myUserName]. The SQL Server catalog stores the actual user ID of the person who writes the code.
• EXECUTE AS OWNER: Another variation of execution under the security context of a specific user, in this case the owner of the code at the time of execution. If the owner changes after the code is created in the database, it is the current owner when the code executes.

This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.