Home » Blog » Prevent unauthorized REVERTing after EXECUTE AS

Prevent unauthorized REVERTing after EXECUTE AS

2014-01-10 - General, Security

Introduction

Yesterday we talked about the EXECUTE AS and the REVERT statements. To execute the REVERT statement, no special permissions are required. That means, anybody getting hold of a connection with a switched security context can switch back to the original security context at any time.

In some situations that might not be desirable. For example if you, following the Principle of Least Privilege used EXECUTE AS to reduce the active permissions on a given connection. For those situations there is a way to prevent the REVERT action.

Preventing REVERT

There are actually two ways to prevent the execution of REVERT. The first is to specify WITH NO REVERT when executing EXECUTE AS. Let's look at an example:

[sql] CREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE USER TestUser1 FOR LOGIN TestLogin1;
GO
EXECUTE AS LOGIN='TestLogin1' WITH NO REVERT;
GO
SELECT 'login' AS token_type,principal_id,name FROM sys.login_token AS LT
UNION ALL
SELECT 'user' AS token_type,principal_id,name FROM sys.user_token AS UT;
GO
REVERT;
GO
SELECT 'login' AS token_type,principal_id,name FROM sys.login_token AS LT
UNION ALL
SELECT 'user' AS token_type,principal_id,name FROM sys.user_token AS UT;
[/sql]

These statements first create a login and an associated user. Then the security context is switched using EXECUTE AS WITH NO REVERT. Using the token DMVs we can see that the switch was successful. Afterwards a REVERT is attempted. However, executing the REVERT statement here fails with error 15196 and the token DMVs confirm that the security context was not switched back:

EXECUTE AS WITH COOKIE

In some situations you want to prevent others from switching the security context back, but you want to be able to go back yourself. For that case SQL Server offers EXECUTE AS with a cookie. For an example let's connect to SQL Server with TestLogin1 and then use the EXECUTE AS WITH COOKIE statement to switch the security context to the login TestLogin2:

[sql] DECLARE @cookie VARBINARY(8000);
EXECUTE AS LOGIN='TestLogin2' WITH COOKIE INTO @cookie;
SELECT @cookie AS cookie;
[/sql]

The EXECUTE AS WITH COOKIE statement returns a VARBINARY(8000) value through the provided output variable:

The returned value is actually a VRABINARY(100) value, but the documentation explicitly mentions that the length might change anytime and that you should therefore always provide a variable of data type VARBINARY(8000) for the cookie.

Any attempt to revert without this exact cookie value will now fail:

To successfully revert you need to use the REVERT WITH COOKIE statement and provide the same value that was returned by the EXECUTE AS statement:

[sql] REVERT WITH COOKIE = 0x094DACF9798499803059E31A83A97F4096C9D69F6335001C66F88F3775891711328E6A8E2BDA1EBF77CA0FA1F961E2A40100;
[/sql]

Summary

EXECUTE AS WITH NO REVERT and EXECUTE AS WITH COOKIE provide ways to change the security context of a connection while preventing unauthorized reverting to the original security context. This can come in handy in situations where you want to use EXECUTE AS to deliberately reduce the active set of permissions.