Switching Stored Procedure Execution Context in SQL Server using the REVERT clause
Written By: Edwin Sarmiento -- 9/9/2008
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
