SQL Server的Execute As與連接池結合使用的測試 - CareySon－IT工程師數位筆記本

文章出處

簡介

在SQL Server中，Execute As關鍵字允許當前賬戶在特定上下文中以另一個用戶或登錄名的身份執行SQL語句，比如用戶張三有權限訪問訂單表，用戶李四并沒有權限訪問訂單表，那么給予用戶李四訪問訂單的表的權限就有些過頭了，因為李四可能只有在很特定的上下文環境中才需要訪問訂單表，因此可以在特定上下文中使用Execute As Login 張三，暫時以張三的身份訪問訂單表，從而保證更安全的權限控制。

另一方面，應用程序通過網絡與數據庫連接是需要在傳輸層通過TCP協議，而TCP協議在建立連接的階段的成本會比較高（1.同步請求 2同步請求+Ack 3.確認這三個階段），因此減少TCP連接可以很大程度上提升性能。因此當應用程序與數據庫建立連接后，在一定空閑時間內不在TCP協議上切斷連接，而是保持連接，連接的斷開操作僅僅是邏輯上斷開，當新的請求由應用程序發送到客戶端時，復用之前建立在應用程序與數據庫上的連接，從而極大的提升了連接性能。

當在連接池上使用Execute As切換連接的安全上下文時則可能產生的情況我們通過下述幾種實驗來得出結論。

在使用連接池的情況下使用Execute As切換安全上下文

試驗中所用的連接字符串全部為：

        1: data source=.;database=test;uid=GetMembers;pwd=sa;pooling=true;Connection Timeout=30

       2:  

       3:

實驗一：使用動態SQL，切換安全上下文

該實驗分別使用兩個連接，第一次連接中，用戶為GetMembers，將安全上下文切換為系統最大權限登錄名SA，連接斷開時保持SA安全上下文，應用程序端發送的SQL代碼如代碼1：

       1: EXECUTE AS LOGIN = 'sa';SELECT * FROM dbo.Higher;"

代碼1.第一次連接數據庫執行的語句

在將身份切換為SA后，正常查詢GetMembers沒有的dbo.Higher表的權限，執行完代碼1所示的SQL后，連接正常關閉。第二次連接使用連接池復用第一次連接所建立的連接，執行的SQL如代碼2：

       1: SELECT * FROM Higher

代碼2.第二次連接使用的SQL

在Asp.net端看到的查詢結果如圖1所示。

圖1.兩次連接在Asp.net中的信息

由圖1可以看出，當復用連接池時，由于第一次連接以GetMembers登錄名登錄，安全上下文切換到SA并沒有切換回來，第二次再次登錄時就會報錯，報的錯對應在SQL Server日志里如圖2所示。

圖2.SQL Server端報錯

結論：由此看出，當連接池復用時，第一次連接切換了上下文第二次連接復用時就會直接報錯，這也是期待的結果，從而保證了安全性，如果希望采用這種方式結合連接池，則必須在第一次連接完使用Revert將安全上下文轉換回登錄時的安全上下文。

實驗二：在存儲過程中使用Execute As轉換安全上下文

還是兩次連續的連接，第一次在存儲過程中執行Execute As轉換上下文為SA，代碼如代碼3所示：

       1: CREATE PROCEDURE [dbo].[GetMembers]

       2:  

       3: AS

       4:  

       5: EXECUTE AS USER = 'sa'

代碼3.在存儲過程中執行Execute As

第二次連接進來的查詢執行一個非常簡單的Select語句，但沒有對應權限，執行結果如圖3所示：

圖3.第二次連接不會受第一次在存儲過程中改變上下文的影響

在數據庫端對應的跟蹤如圖4所示。

圖4.對應的跟蹤

因此可以看出，在存儲過程中改變安全上下文對連接池無影響，安全上下文僅僅在存儲過程中有效。

實驗三：連接池對隔離級別的影響

在實驗3中對連接的默認隔離級別更改，更改為可序列化級別，SQL語句如代碼4所示。

       1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

代碼4.改變連接的隔離級別

隨后的連接查詢并返回當前連接的隔離級別，結果如圖5所示。

圖5.改變隔離級別導致復用連接池中的連接隔離級別改變

結論：使用連接池對修改Session級別的隔離級別用完必須改回默認連接，否則可能導致后續連接在不正確的隔離級別下運行。

實驗四：在存儲過程中改變隔離級別的連接復用的影響

下面我們在存儲過程中改變隔離級別，代碼如代碼5所示：

       1: create PROCEDURE [dbo].[TestIslation]

       2: AS

       3:  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

       4:  

       5:  SELECT CASE transaction_isolation_level 

       6: WHEN 0 THEN 'Unspecified' 

       7: WHEN 1 THEN 'ReadUncommitted' 

       8: WHEN 2 THEN 'ReadCommitted' 

       9: WHEN 3 THEN 'Repeatable' 

      10: WHEN 4 THEN 'Serializable' 

      11: WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 

      12: FROM sys.dm_exec_sessions 

      13: where session_id = @@SPID