Modifying execution context in SQL Server 2005

by Tim Chapman | Feb 12, 2007 7:19:00 PM

Tags: Advertising & Promotion, Databases, Enterprise software, Tim Chapman, Execution context, Microsoft SQL Server, permission, execution context, Microsoft SQL Server 2005, SQL Server Newsletter

Takeaway: Tim Chapman outlines how you can use the EXECUTE AS clause to change execution context in SQL Server 2005. He then shows how you can use this clause to impersonate the owner of a table in a schema.

Execution context is the identity for which SQL Server permits actions to occur by a user. For example, typically when you log in to SQL Server, the login account is checked for certain permissions. Some of these permissions include the ability to log in to the server, the ability for the account to access the database, and the ability to perform actions in that database.

SQL Server 2005 contains the EXECUTE AS clause, which allows you to switch the execution context for batches and procedures so different permissions other than the person calling the procedure or batch are used.

Ownership chains

Before I dive into how to change execution context in SQL Server 2005, it is important that you understand how ownership chains work.

When a user executes a stored procedure (assuming the user has execute permissions on the stored procedure), SQL Server checks the owner(Schema) of the procedure and compares it to the owner of any objects the procedure accesses. If the procedure and any called objects have the same owner, permissions on the referenced object(s) are not evaluated. So, if user Tim is given execute permissions to a procedure named usp_ProcedureChain owned by dbo, as long as dbo owns any other procedures that usp_ProcedureChain calls, an error will not occur when Tim executes the procedure.

Context switching

In SQL Server 2000, you could use the SETUSER command to impersonate the user context for a SQL User Account. Unfortunately, this command could only be used by a sysadmin or db_owner and could not be used for Windows accounts.

In SQL Server 2005, the EXECUTE AS clause can be used instead of SETUSER to change the execution context of a stored procedure, trigger, batch, or function by specifying a user name or login name in the definition of the code. When the context is changed to another user or login, SQL Server will verify the permissions against that login or user. To specify the EXECUTE AS clause when you create or modify a procedure or function, you must have IMPERSONATE permissions on the specified principal and also permissions to create the object.

Example

To get an idea of how changing the execution context of a stored procedure can be useful, I'll present an example. I will look at how you can use EXECUTE AS to impersonate the owner of a table in a schema and insert into that table by someone who does not have explicit permissions to do so.

The first statement in the example, the REVERT command, is used to traverse one step back up the execution context chain. (I am running it first so that you can rerun the example in its entirety without worrying about cleaning up any objects.)

REVERT

GO

The seven statements in Listing A are cleanup statements that check to see if the objects I use later in the example already exist; if they do, I will remove them.

The following script creates two server logins and two database user accounts to log into. Notice the CHECK_EXPIRATION and CHECK_POLICY statements, which are new to SQL Server 2005. These statements tell SQL Server not to enforce the password expiration policy on this user account and not to check any type of password policy on this account. These are very useful options for enforcing security policies within SQL accounts.

CREATE LOGIN [BaseUser] WITH PASSWORD=N'baseuser', 

DEFAULT_DATABASE=[TRS],

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE USER [BaseUser] FOR LOGIN [BaseUser]

GO

CREATE LOGIN [TableOwner] WITH PASSWORD=N'tableowner', 

DEFAULT_DATABASE=[TRS], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE USER TableOwner FOR LOGIN TableOwner 

GO

In SQL Server 2005, a schema is no longer the same thing as a database user; it is an entirely different namespace for which objects are contained. The user-schema separation is nice because it makes ownership of objects transferrable and much easier to manage than in SQL Server 2000. The following statement creates the schema that I will use to work in:

CREATE SCHEMA [TableOwnerSchema] AUTHORIZATION [TableOwner]

GO

Now I enable logins so they can be used:

ALTER LOGIN [TableOwner] ENABLE

ALTER LOGIN [BaseUser] ENABLE

GO



GRANT CREATE TABLE TO TableOwner

GO

First, I make use of the EXECUTE AS command. I am setting the current execution context to be that of TableOwner. After running this command, all permission evaluations will be run for that of TableOwner; the previous sysadmin privledges will not apply.

EXECUTE AS USER = 'TableOwner'

GO

Run this statement to show that the current execution context is that of TableOwner:

SELECT SESSION_USER

GO

This script will create a table in the TableOwnerSchema schema named MyTable. TableOwner is allowed to execute this statement because I granted the user CREATE TABLE permissions earlier.

CREATE TABLE TableOwnerSchema.MyTable

(
      Field1 INT

)

GO

When I run this REVERT statement, I traverse one step back in the execution context chain. In SQL Server 2005, execution context can be nested, so if you have been running under several different users for the same connection, you may need to run this statement several times to get back to your original login context.

REVERT

GO

SELECT SESSION_USER

GO

Now I'll run a quick select statement from our new table to make sure it exists:

SELECT * FROM TableOwnerSchema.MyTable

GO

The following script creates a procedure that will insert into the new TableOwnerSchema.MyTable table. Notice that I use the WITH EXECUTE AS 'TableOwner' statement in the procedure definition. This means that when the procedure is executed, it will always run under the context of TableOwner.

CREATE PROCEDURE usp_InsertMyTable

WITH EXECUTE AS 'TableOwner'

AS 
BEGIN
            INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8)

END

GO

I can grant execute permissions on this stored procedure to a user account. In this case, I use the previously created account called BaseUser.

GRANT EXEC ON usp_InsertMyTable TO BaseUser
GO

In the following snippet, I switch the execution context to BaseUser and try to run the stored procedure:

EXECUTE AS USER = 'BaseUser'

GO

EXEC usp_InsertMyTable

GO

I was able to insert a record into the TableSchema.MyTable table because the execution context of TableOwner in the procedure allowed me to do so. BaseOwner does not have explicit permissions to insert into that table, so any attempt to do so by that user will result in an error. To illustrate this, run the following script, which will alter our procedure so that it runs under the execution context of the caller of the procedure.

REVERT

GO

ALTER PROCEDURE usp_InsertMyTable

AS 
BEGIN
            INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8)

END

GO

EXECUTE AS USER = 'BaseUser'

GO

EXEC usp_InsertMyTable

GO

REVERT

Developers or DBAs will find it very useful to be able to switch the permission context in which procedures execute. This can be especially handy when you're dealing with a TRUNCATE TABLE statement, which does not have assignable permissions. You could switch the context in your procedure to a user who has permission to truncate the target table, and then revert back to the previous permission set when you are finished.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

DO YOU WANT JANITORIAL TO UPLOAD TO YOU TUBE? BALTHOR | 02/12/07