SQL Server的Execute As与连接池结合使用的测试

  在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

实验一：使用动态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

实验二：在存储过程中使用Execute As转换安全上下文

    还是两次连续的连接，第一次在存储过程中执行Execute As转换上下文为SA，代码如代码3所示：

代码3.在存储过程中执行Execute As

 

 

实验三：连接池对隔离级别的影响

    在实验3中对连接的默认隔离级别更改，更改为可序列化级别，SQL语句如代码4所示。

 1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

代码4.改变连接的隔离级别

 

  

实验四：在存储过程中改变隔离级别的连接复用的影响

    下面我们在存储过程中改变隔离级别，代码如代码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

   

代码5.在存储过程中更改隔离级别，并显示当前的隔离级别。

 

小结

    本文对在使用连接池情况下数据库中的一些细节场景进行了实验，可以看到对于连接池复用来说，改变隔离级别可能会存在隐性的风险，其他情况SQL Server都能够显式处理。因此使用连接池对修改Session级别的隔离级别用完必须改回默认连接，或者在语句级别修改隔离等级而不是Session级别。