SQL Server ->> EXECUTE AS LOGIN/USER和Revert表达式

EXECUTE AS LOGIN/USER和Revert表达式都是从SQL Server 2005就有。Revert的作用是用于切换当前过程的执行上下文返回上一个EXECUTE AS 语句发生之前的安全上下文。Revert可以在存储过程、ad-hoc环境下、用户定义函数中使用。Revert是和 EXECUTE AS LOGIN/USER配合起来使用的。

 

这里需要先讲一个执行上下文（Execution Context）的概念。当一个用户开启一个会话到SQL Server之后，整个会话的权限检查都是基于登陆用户的身份。

EXECUTE AS LOGIN/USER可以借以某个登陆用户和数据库用户的身份去执行某些操作，直到下一个REVERT语句发生才切换回caller的身份。当为LOGIN的时候，复制的用户身份是server层面的，所以是对于整个server层面的所有数据库而言。而当为user的时候，并不会去复制login层面的权限。但是这里MSDN说的不对，这里有一段msdn在EXECUTE AS (Transact-SQL)中的话：

While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers.

 

它的意思是一旦只要执行上下文切换成功且没有用REVERT切换回caller的上下文，任何对当前数据库范围外的其他数据库资源的访问都会失败，包括了USE <database>语句，分布式查询和带有3个以上标识符的对象引用。这里说的不对而且解释的也不清楚。不是说所有的都失败。失败的原因是当我们以某个数据库用户的身份切换后，对于其他数据库的资源访问都将以guest用户身份进行，包括USE <database>语句。而我们都知道除了tempdb，msdb和master三个db，用户数据库和model数据库的guest用户是被禁用的。所以当我们试图去切换到其他数据库的安全上下文的时候就根本找不到一个用户身份。但是，我们是可以成功执行USE <database>语句去切换到tempdb，msdb和master三个db。

 

这里引用一个MSDN的例子

-- Create two temporary principals.
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';
GO
CREATE 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 = 'P@$$w0rdO1'; 
GO 
CREATE USER user1 FOR LOGIN login1; 
GO

DECLARE @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 @cookie

select CURRENT_USER

EXECUTE 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

 

上面对动态语句同样适用

EXECUTE AS USER = 'user1';

select CURRENT_USER

EXECUTE AS USER = 'user2';

SELECT CURRENT_USER AS UserName;  

EXEC('SELECT CURRENT_USER AS UserName;') 

SELECT CURRENT_USER AS UserName;  

REVERT

 

 对于EXECUTE AS USER其实就没有太多好讲的是了，倒是对于EXECUTE AS LOGIN还有一点我好奇的，就是多个数据库我可以存在对多个数据库的安全上下文（因为我可以在不同的数据库下再执行不同的EXECUTE AS LOGIN/USER语句去切换到其他用户的安全上下文），那是不是可以随便切换呢？答案当然是否定的。这里上下文也有和事务一样的特性，就是你可以在某个库下任意嵌套多层的上下文切换。当你用USE <database>语句切换到tempdb，msdb，master库并切换上下文的时候是可以的，只是如果你在tempdb中的上下文切换还没有结束（REVERT）就切换回原来的数据库就不行了，这就如同事务一样，你在事务1开启了事务2，事务2你不rollback或者commit怎么跳回到上一层的事务。但是你可以继续切换到msdb下（另一个或者说第三个数据库下）这是没问题的。

 

总结一下：

安全上下文切换确实是个好玩的东西，也是有一定的学问在里面的。

1）EXECUTE AS LOGIN/USER各自复制的安全上下文特性不一样；

2）在一个数据库下，可以多次得嵌套上下文切换，当然我相信不会有人去这么干，这样的复杂代码设计应该不会有人去这么干吧。

3）对于EXECUTE AS USER，实际是可以再使用USE <database>的，只是变成了guest身份；

4）在不结束某个库的上下文切换前是无法跳转回原来的数据库，这点和事务的原理类似，不管当前的login是否在先前的数据库中涉及了上下文切换；

 

参考：

EXECUTE AS (Transact-SQL)

REVERT (Transact-SQL)

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