By: Edwin Sarmiento | Comments (2) | Related: > Stored Procedures
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
USE master
GO
--Add Windows logins to SQL Server
IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'SQLSRV90\SQLUser1')
CREATE LOGIN [SQLSRV90\SQLUser1]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks
IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'SQLSRV90\SQLDBA')
CREATE LOGIN [SQLSRV90\SQLDBA]
FROM WINDOWS
WITH DEFAULT_DATABASE = AdventureWorks
Next, we will add those logins as users in the AdventureWorks database
USE AdventureWorks
--Add the new logins to the AdventureWorks database
CREATE USER SQLUser1 FOR LOGIN [SQLSRV90\SQLUser1]
CREATE USER SQLDBA FOR LOGIN [SQLSRV90\SQLDBA]
--Add SQLDBA Windows account to the db_owner role
EXEC sp_addrolemember 'db_owner', 'SQLDBA'
GO
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.
--Create procedure that executes a SELECT with a BACKUP DATABASE command
CREATE PROCEDURE dbo.DisplayContextwithRevert
WITH EXECUTE AS CALLER
AS
--The user will only be granted permission to do this section of the code
SELECT * FROM Person.Contact
--We will just display the execution context of the user executing this section of the code for demonstration
SELECT CURRENT_USER AS UserName;
--We will switch execution context to a more privileged user to do this portion of the code
EXECUTE AS USER='SQLDBA';
BACKUP DATABASE AdventureWorks TO DISK='C:\AdventureWorks.BAK' WITH INIT, STATS=10;
--We will just display the execution context of the user executing this section of the code
SELECT CURRENT_USER AS UserName;
--We will revert to the execution context of the original caller to limit the privileges back
REVERT;
SELECT * FROM Person.Contact
SELECT CURRENT_USER AS UserName;
GO
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
-- Grant user permissions
GRANT EXECUTE ON dbo.DisplayContextwithRevert TO SQLUser1
GRANT SELECT ON Person.Contact TO SQLUser1
GO
-- Grant the IMPERSONATE permission on the SQLUser1 user so it can switch execution context to SQLDBA
GRANT IMPERSONATE ON USER:: SQLDBA TO SQLUser1
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.
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
