Switching Stored Procedure Execution Context in SQL Server using the REVERT clause

Problem

In the Granting permission with the EXECUTE AS command in SQL Server 2005 tip, you have seen how you can grant granular permissions using the EXECUTE AS clause. Context switching within a programmable object such as a stored procedure or function is important especially if the user calling the stored procedure does not have enough privileges to run a code block. But to make it more granular, we allow switching execution context only when needed and revert back to the original execution context of the caller. How do we do it?

Solution

The EXECUTE AS clause in SQL Server 2005 has given us the options to control the security context for code module execution. Extending that concept, we use the REVERT clause to switch the execution context back to the caller of the last EXECUTE AS statement. This enables us to allow users to impersonate highly privileged accounts only when needed and revert back to the original execution context with limited privileges. Take for example a stored procedure that does some changes in the database based on some business logic. Before running the code block to change the data, you need to generate a database backup that can be used to rollback the change whenever necessary. As we do not want to give more privilege to the user that would run the stored procedure, we will use the EXECUTE AS clause to impersonate a more privileged account to do the database backup and REVERT clause to switch back to the original execution context of the caller. Let’s look at a sample script to demonstrate how that works.

We’ll first add logins to SQL Server. One will be an ordinary user with very minimal privileges and another would be a member of the sysadmin role

Next, we will add those logins as users in the AdventureWorks database

Then, let’s create a stored procedure that encapsulates the logic we presented earlier. For demonstration purposes, we will only do a SELECT command so we only need to grant SELECT permissions on the SQLUser1 user.

Now, we proceed to grant only the appropriate permissions to the database user, SQLUser1. Note that SQLUser1 does not have privileges to do a database backup as the user is not a member of the dbo nor the db_backupoperator role. But since he needs to do a database backup from within the stored procedure, he needs to switch context to a more privileged user, SQLDBA. We, then, grant him IMPERSONATE permissions to impersonate SQLDBA

Testing the stored procedure

Let’s login to SQL Server using SQLUser1 and run a BACKUP DATABASE command.

Notice that SQLUser1 does not have explicit permission to run a BACKUP DATABASE command. Running the stored procedure dbo.DisplayContextwithRevert would allow SQLUser1 to run the BACKUP DATABASE command with elevated privileges as SQLDBA and revert back to being SQLUser1. The highlighted CURRENT_USER variable values illustrate the execution context while running the different code blocks from inside the stored procedure

Next Steps

• It is a good security practice to grant database users only the permissions that they need. Should they need to switch execution context to have elevated privileges for a specific command, remember to revert back the privileges once the command has been executed.
• Check out REVERT clause in SQL Server Books Online.
• Check out the SQL Server 2005 Security Best Practices whitepaper.
• Check out the first tip in the series – Granting permission with the EXECUTE AS command in SQL Server 2005.
• Check out the 20+ security tips on MSSQLTips.com.