SQL Server->EXECUTE AS LOGIN/USERおよびRevert式

8340 ワード

EXECUTE AS LOGIN/USERとRevert式はSQL Server 2005からある.Revertの役割は、現在のプロセスの実行コンテキストを切り替えて、前のEXECUTE AS文が発生する前のセキュリティコンテキストを返すことです.Revertは、ストアド・プロシージャ、ad-hoc環境、ユーザ定義関数で使用できます.RevertはEXECUTE AS LOGIN/USERと合わせて使用されています.

ここではまず実行コンテキスト(Execution Context)の概念を述べる必要がある.ユーザーがSQL Serverにセッションを開くと、セッション全体のパーミッションチェックはログインユーザーのIDに基づいて行われます.
EXECUTE AS LOGIN/USERは、あるログインユーザとデータベースユーザとして、次のREVERT文が発生するまでcallerのアイデンティティに切り替えることができる.LOGINの場合、レプリケーションされたユーザーIDはserverレベルであるため、serverレベル全体のすべてのデータベースにとって使用されます.userの場合、loginレベルの権限はコピーされません.しかし、ここでMSDNの言うことは間違っています.ここにはEXECUTE AS(Transact-SQL)の中のmsdnの言葉があります.
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文、分散クエリ、および3つ以上の識別子を持つオブジェクト参照を含む、現在のデータベース範囲外の他のデータベースリソースへのアクセスが失敗することを意味します.ここの言うことは間違っていて、説明もはっきりしていません.すべてが失敗したわけではない.失敗した理由は、あるデータベース・ユーザーとして切り替えた後、USE文を含む他のデータベースへのリソース・アクセスがguestユーザーとして行われるためです.tempdb,msdb,masterの3つのdbを除いて,ユーザデータベースとmodelデータベースのguestユーザは無効であることが知られている.そのため、他のデータベースのセキュリティコンテキストに切り替えようとすると、ユーザーIDが見つかりません.しかし,我々はUSE文を成功裏に実行してtempdb,msdb,masterの3つの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文でtempdb,msdb,masterライブラリに切り替えてコンテキストを切り替える場合は可能ですが、tempdbでのコンテキスト切り替えが完了していない場合(REVERT)は元のデータベースに戻すことはできません.これはトランザクションのようにトランザクション1でトランザクション2を開きます.トランザクション2 rollbackまたはcommitがどのように上位レベルのトランザクションにジャンプしないか.しかし、msdbの下(別のデータベースまたは3番目のデータベースの下)に切り替え続けることは問題ありません.

まとめてみます.
安全コンテキストの切り替えは確かに面白いものであり、一定の学問がある.
1)EXECUTE AS LOGIN/USERそれぞれのコピーの安全コンテキスト特性が異なる;
2)1つのデータベースの下で、何度もネストされたコンテキストを切り替えることができます.もちろん、そんなことをする人はいないと信じています.このような複雑なコード設計では、そんなことをする人はいないでしょう.
3)EXECUTE AS USERについては,実際にUSEを再利用することができ,guestアイデンティティになっただけである.
4)あるライブラリのコンテキスト切替を終了しない前に元のデータベースに戻ることができないことは、現在のloginが以前のデータベースでコンテキスト切替に関連しているかどうかにかかわらず、トランザクションの原理と同様である.

参照先:
EXECUTE AS (Transact-SQL)
REVERT (Transact-SQL)
Switching Stored Procedure Execution Context in SQL Server using the REVERT clause

php数学関数

Teams業務効率化まとめ