Test used by SQL Server with execute as with connection pooling

Original: Test for SQL Server's execute as combined with connection pooling

Brief introduction

In SQL Server, the EXECUTE AS keyword allows the current account to execute an SQL statement as another user or login in a specific context, such as a user Zhang San access to the order table, and the user John Doe does not have access to the order table. Then giving the user John Doe access to the table of the order is overdone, because John Doe may only need to access the order table in a very specific context, so you can use EXECUTE as Login Zhang San in a certain context to temporarily access the Order table as Zhang San, guaranteeing more secure permission control.

On the other hand, applications that connect to the database over a network are required to pass the TCP protocol over the transport layer, while the TCP protocol is relatively expensive at the stage of establishing the connection (1. Synchronous Request 2 Synchronous Request +ack 3. Confirm these three phases), so reducing TCP connections can greatly improve performance. Therefore, when the application is connected to the database, and the connection is not cut off on the TCP protocol for a certain amount of idle time, the disconnected operation of the connection is only logically fractured, and when the new request is sent by the application to the client, the connection to the application and the database is reused. This greatly improves the connection performance.

What happens when you use EXECUTE as to switch the security context of a connection on a connection pool we draw conclusions by following several experiments.

Using EXECUTE as to switch security contexts with connection pooling

The connection string used in the experiment is all:

   1: data source=.; Database=test;uid=getmembers;pwd=sa;pooling=true; Connection timeout=30

   2:

   3:

Experiment one: Use dynamic SQL to switch security contexts

The experiment uses two connections, the first connection, the user is getmembers, the security context is switched to the system's maximum privilege login SA, the connection is disconnected while the SA security context, the application side sends SQL code such as code 1:

   1:EXECUTEas' sa '; SELECT  from dbo. higher; "

Code 1. The first statement to connect to the database execution

After you switch the identity to SA, the normal query getmembers does not have the permissions of the Dbo.higher table, and after executing the SQL shown in code 1, the connection closes gracefully. The second connection uses the connection pool to reuse the connections established by the first connection, and executes SQL such as Code 2:

   1:SELECT from higher

Code 2. The SQL used for the second connection

The results of the query as seen in the ASP. NET end are shown in 1.

Figure 1. Two connection information in ASP.

As can be seen from Figure 1, when the connection pool is reused, because the first connection is logged in with the GetMembers login, the security context switches to SA and does not switch back, the second time you log in will be an error, the reported error corresponds in the SQL Server log 2 is shown.

Figure 2. SQL Server Side Error

Conclusion: As a result, when the connection pool is reused, the first connection switches the context of the second connection and then it will be directly error, which is expected to ensure the security, if you want to use this method to combine connection pool, You must use revert to convert the security context back to login when the first connection is complete.

Experiment two: Using EXECUTE as to convert security contexts in stored procedures

or two consecutive connections, the first time in a stored procedure executes the EXECUTE AS conversion context to SA, as shown in code 3:

   1:CREATEPROCEDURE [dbo]. [GetMembers]

   2:

   3:as

   4:

   5:EXECUTEasUSER' sa '

Code 3. Execute execute as in stored procedure

The second connected query executes a very simple SELECT statement, but does not have the corresponding permissions, as shown in result 3:

Figure 3. The second connection is not affected by the first time the context is changed in the stored procedure

This is shown in trace 4 on the database side.

Figure 4: The corresponding trace

As you can see, changing the security context in a stored procedure has no effect on the connection pool, and the security context is only valid in the stored procedure.

Experiment three: The impact of connection pooling on isolation levels

In Experiment 3, change the default isolation level of the connection to a serializable level, as shown in code 4 in the SQL statement.

   1:SETTRANSACTIONisolation level SERIALIZABLE

Code 4: Changing the isolation level of a connection

Subsequent connection queries and returns the isolation level of the current connection, as shown in result 5.

Figure 5. Changing the isolation level causes a change in the connection isolation level in the Reuse connection pool

Conclusion: Using connection pooling to modify the isolation level of session level must be changed back to the default connection, otherwise it may cause subsequent connections to run under an incorrect isolation level.

Experiment Four: The effect of changing the isolation level of connection multiplexing in a stored procedure

Below we change the isolation level in the stored procedure, as shown in code 5:

   1:createPROCEDURE [dbo]. [Testislation]

   2:as

   3:  SET TRANSACTION Isolation  Level READ Uncommitted

   4:

   5:  SELECT  Case

   6:when then' Unspecified '

   7:when then' readuncommitted '

   8:when then' readcommitted '

   9:when then' repeatable '

  Ten:when and then' Serializable '

One   :when and then' Snapshot 'END as

  :from

  :where@ @SPID

Code 5. Change the isolation level in the stored procedure and display the current isolation level

In a subsequent connection, a statement that displays the current session isolation level is called in a non-stored procedure and printed as shown in result 6.

Figure 6.

As can be seen from Figure 6, the third connection changes the isolation level within the stored procedure, and the isolation level of the fourth connection is not affected.

Conclusion: Changing the isolation level within a stored procedure does not affect the use of subsequent connection pools.

Summary

In this article, we have experimented with some of the details of the database in the case of connection pooling, and you can see that there may be a hidden risk of changing the isolation level for connection pooling reuse, in other cases SQL Server can explicitly handle it. Therefore, use connection pooling to modify the isolation level at session level you must either change back to the default connection or modify the isolation level at the statement level instead of the session level.

Test used by SQL Server with execute as with connection pooling