SQL Server ->> Revert運算式

標籤：

Revert運算式從SQL Server 2005就有。它的作用是用於切換當前過程的執行內容返回上一個EXECUTE AS 語句發生之前的安全上下文。Revert可以在預存程序、ad-hoc環境下、使用者定義函數中使用。Revert是需要和EXECUTE AS配合使用。

 

這裡引用一個MSDN的例子

-- Create two temporary principals.CREATE LOGIN login1 WITH PASSWORD = ‘J345#$)thb‘;CREATE LOGIN login2 WITH PASSWORD = ‘Uor80$23b‘;GOCREATE USER user1 FOR LOGIN login1;CREATE USER user2 FOR LOGIN login2;GO-- Give IMPERSONATE permissions on user2 to user1-- so that user1 can successfully set the execution context to user2.GRANT IMPERSONATE ON USER:: user2 TO user1;GO-- Display current execution context.SELECT SUSER_NAME(), USER_NAME();-- Set the execution context to login1. EXECUTE AS LOGIN = ‘login1‘;-- Verify that the execution context is now login1.SELECT SUSER_NAME(), USER_NAME();-- Login1 sets the execution context to login2.EXECUTE AS USER = ‘user2‘;-- Display current execution context.SELECT SUSER_NAME(), USER_NAME();-- The execution context stack now has three principals: the originating caller, login1, and login2.-- The following REVERT statements will reset the execution context to the previous context.REVERT;-- Display the current execution context.SELECT SUSER_NAME(), USER_NAME();REVERT;-- Display the current execution context.SELECT SUSER_NAME(), USER_NAME();-- Remove the temporary principals.DROP LOGIN login1;DROP LOGIN login2;DROP USER user1;DROP USER user2;GO    

 

REVERT還支援一個WITH COOKIE = @varbinary_variable選項。這個東西的作用主要是為了保證在啟動串連池（connection pool）的情況下，當前會話的上下文不被下一個重用會話的人切換。這個東西就像一個密碼一樣的東西，你儲存了密碼，就只有你自己知道密碼，才能去解碼。

 

--Create temporary principal CREATE LOGIN login1 WITH PASSWORD = ‘[email protected]$$w0rdO1‘; GO CREATE USER user1 FOR LOGIN login1; GODECLARE @cookie varbinary(100); --variable to store the cookie --switch execution context, generate cookie and assign it to variable EXECUTE AS USER = ‘user1‘ WITH COOKIE INTO @cookie;select @cookieselect CURRENT_USEREXECUTE AS USER = ‘user2‘;-- Use the cookie in the REVERT statement. SELECT CURRENT_USER AS UserName;  DECLARE @cookie varbinary(100); -- Set the cookie value to the one from the SELECT @cookie statement. SET @cookie = 0x21873959E804DD435976EA5D25B7352431A98B4F144C76F6B1502C5AA3C20F30105842EEA9C361B3DA03B2DBD36E0E070100;REVERT WITH COOKIE = @cookie; -- Verify the context switch reverted. SELECT CURRENT_USER AS UserName;  GO

 

 

 

 

參考：

REVERT (Transact-SQL)

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

 

SQL Server ->> Revert運算式