当前位置：Gxlcms > 数据库问题 > SQL Server ->> Revert表达式

SQL Server ->> Revert表达式

时间：2021-07-01 10:21:17 帮助过：71人阅读

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）的情况下，当前会话的上下文不被下一个重用会话的人切换。这个东西就像一个密码一样的东西，你保存了密码，就只有你自己知道密码，才能去解码。

<span style="color: #008080;">--</span><span style="color: #008080;">Create temporary principal </span>
<span style="color: #0000ff;">CREATE</span> LOGIN login1 <span style="color: #0000ff;">WITH</span> PASSWORD <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P@$$w0rdO1</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">; 
</span><span style="color: #0000ff;">GO</span> 
<span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> user1 <span style="color: #0000ff;">FOR</span><span style="color: #000000;"> LOGIN login1; 
</span><span style="color: #0000ff;">GO</span>
<span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@cookie</span> <span style="color: #0000ff;">varbinary</span>(<span style="color: #800000; font-weight: bold;">100</span>); <span style="color: #008080;">--</span><span style="color: #008080;">variable to store the cookie </span><span style="color: #008080;">
--</span><span style="color: #008080;">switch execution context, generate cookie and assign it to variable </span>
<span style="color: #0000ff;">EXECUTE</span> <span style="color: #0000ff;">AS</span> <span style="color: #ff00ff;">USER</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user1</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">WITH</span> COOKIE <span style="color: #0000ff;">INTO</span> <span style="color: #008000;">@cookie</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">select</span> <span style="color: #008000;">@cookie</span>
<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">CURRENT_USER</span>
<span style="color: #0000ff;">EXECUTE</span> <span style="color: #0000ff;">AS</span> <span style="color: #ff00ff;">USER</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user2</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
</span><span style="color: #008080;">--</span><span style="color: #008080;"> Use the cookie in the REVERT statement. </span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">CURRENT_USER</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> UserName;  
</span><span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@cookie</span> <span style="color: #0000ff;">varbinary</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">); 
</span><span style="color: #008080;">--</span><span style="color: #008080;"> Set the cookie value to the one from the SELECT @cookie statement. </span>
<span style="color: #0000ff;">SET</span> <span style="color: #008000;">@cookie</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0x21873959E804DD435976EA5D25B7352431A98B4F144C76F6B1502C5AA3C20F30105842EEA9C361B3DA03B2DBD36E0E070100</span><span style="color: #000000;">;
REVERT </span><span style="color: #0000ff;">WITH</span> COOKIE <span style="color: #808080;">=</span> <span style="color: #008000;">@cookie</span><span style="color: #000000;">; 
</span><span style="color: #008080;">--</span><span style="color: #008080;"> Verify the context switch reverted. </span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">CURRENT_USER</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> UserName;  
</span><span style="color: #0000ff;">GO</span>

参考：

REVERT (Transact-SQL)

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

SQL Server ->> Revert表达式

标签：

SQL Server ->> Revert表达式

人气教程排行