文档结构  
翻译进度:已翻译     翻译赏金:0 元 (?)    ¥ 我要打赏

简介

有时你可能需要限制用户登录到SQL服务器。 这可以通过不同的方式进行,本文讨论了两种主要技术:

  • 使用登录触发器
  • 使用数据库的特定程序来控制登录

二者的差异在于使用登录触发器的用户身份认证通常是由SQL Server做。可以在多种情况下使用程序法,即使从SQL Server端看来连接登陆始终是相同的。当然,这些例子可以通过不同的方式修改来实现所需的逻辑。

第 1 段(可获 1.39 积分)

讨论的主题是

先说重要的事,一些警告

我们将玩登录触发器,所以总会有触发器没有按照预期执行的风险。这种情况下,触发器可能会阻止所有你到SQL Server的登录连接。这种情况下你需要一个专用的管理员连接(DAC)连接SQL Server,去修改或删除触发器。建立一个DAC参见 如何建立专用管理员连接到SQL Server(DAC)

第 2 段(可获 1.95 积分)

另一件事是阻止登录不应混淆到身份验证。在这篇文章中讨论的方法只采取额外的步骤,允许或阻止继续已经成功的登录。如果你想知道更多对于处理身份验证的话,请参阅 在SQL Server中的身份验证。.

第三件事是,在这篇文章中的例子部分依赖于客户端应用程序提供的信息。这也意味着信息可能不可靠,所以技术工作通常用来控制你的应用程序。换句话说,当你想要控制在什么情况下你的应用程序可以或不可以连接到你的数据库,这些例子可能会派上用场。

第 3 段(可获 1.48 积分)

测试程序和测试数据库

为了做一些测试我准备了一个小WPF程序。想法是,你可以启动两个应用程序,并尝试不同的连接在不同情况下的变化。

在我们开始之前,我们还需要数据库和一些用户。这些可以按以下方式创建

-----------------------------------------------------------
-- 创建测试数据库和用户
-----------------------------------------------------------
USE master;

CREATE DATABASE LogonTest

CREATE LOGIN LogOnUser WITH PASSWORD = 'DontForgetThis1'; 
GO;

-- 授予访问查看sys.dm_exec_sessions的权限
GRANT VIEW SERVER STATE TO LogOnUser; 
GO;

CREATE LOGIN NormalUser WITH PASSWORD = 'RememberMe2'; 
GO;

USE LogonTest;
GO;

CREATE USER NormalUser FOR LOGIN NormalUser;
GO;
第 4 段(可获 0.73 积分)

第一个用户,LogOnUser将只是用来执行登录触发器,所以它没有其他功能。另一个用户,NormalUser用来像普通用户那样连接数据库。 这是为什么他们的权限与彼此不同

  • LogOnUser拥有VIEW SERVER STATE权限为了能够查询sys.dm_exec_sessions
  • NormalUser 作为一个用户添加到我们的测试数据库中,以便能够连接到它

使用登录触发器以允许每个用户从应用程序中只能有一个连接

现在让我们尝试第一个变化。如果我们需要允许每个用户从我们的测试程序中只能创建单一的同时连接,触发器可以看起来像这样

-----------------------------------------------------------
-- 创建一个登录触发器,只允许一个连接
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
   @connectioncount int;
BEGIN 
   -- 从应用程序中选择连接的用户的数量
   SELECT @connectioncount = COUNT(*)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process = 1
   AND   lower(a.program_name) = 'login tester'
   AND    a.original_login_name = ORIGINAL_LOGIN();
 
   IF  (@connectioncount > 1) BEGIN
      ROLLBACK;  -- 回滚导致连接失败
   END;
END;
GO

 

第 5 段(可获 1.43 积分)

一些需要注意的事情:这个触发器本身的特别之处在于它创建时使用了ON ALL SERVER …. FOR LOGON。这意味着该触发器是服务器范围的并且用于登录行为。触发器也定义了EXECUTE AS 'LogOnUser'所以在执行时权限由用户LogOnUser决定。

触发器本身的逻辑很简单;使用相同的登录名通过这个特定的程序发起连接,统计出目前有多少会话连接到SQL Server。如果连接数大于一,那么我们至少是第二次连接。在这种情况下,事务将被回滚,给SQL Server发出我们撤销连接的信号。

连接数据库的代码看起来像这样

/// <summary>
/// Connects to the database using the given host name
/// </summary>
/// <param name="hostname">Host name to use</param>
/// <returns>True, if sucessful</returns>
internal override bool Connect(string hostName) {
   System.Data.SqlClient.SqlConnection connection = null;
   bool ok = true;

   try {
      connection = new System.Data.SqlClient.SqlConnection();
      connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
      connection.Open();
      this.ActiveConnection = connection;
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
      CloseAndDisposeConnection(ref connection);
      ok = false;
   }

   return ok;
}
第 6 段(可获 1.41 积分)

事实上,这并不是用于连接的所有代码,但此时让我们只专注于连接尝试。其余代码可以在下载中找到。如果连接成功,该程序显示活动的连接,如果它失败了,则显示一个消息框。

因此,如果你启动了我们测试程序的两个实例,而且在每个实例中都提供了正确的SQL Server名并用Normal方式连接,Client 1,第一个客户端应该连接成功,第二个程序使用同样的连接方式却运行失败并给出下面的消息:“登录失败…由于触发器执行“

第 7 段(可获 1.34 积分)

影响连接的连接池

好吧,那是一种成功,但如果你继续运行这个程序:

  • 关闭程序1的连接
  • 尝试使用程序2连接

你仍然得到相同的消息,貌似你不是第一个连接到服务器。好吧,事实上不是这样。显然程序1仍然在运行,你最初建立的连接仍然存在,它只是位于连接池中。如果你将结束程序,连接池将被清除。

为了改变上述情形你有 Clear all connection pools –按钮. 一旦程序1中的连接被关闭,按下“清除”按钮,并尝试从程序2连接,它现在应该是成功的。

第 8 段(可获 1.59 积分)

在我们的测试用例中,连接池被一种不希望的方式影响,但它并不总是这样。当你执行以下操作时想一想你的客户端应用程序架构

  • 连接
  • 做一些操作
  • 断开连接
  • 等待用户输入
  • 连接
  • 做一些操作
  • 断开等等. .

在这种情况下,当等待用户输入时另一个会话可能发起连接,导致我们的下一次连接尝试可能会失败。 因为连接保持存活和保留是合理的,不涉及性能方面。

使用登录触发器允许在任何给定时间内只从一个客户端连接

现在让我们做一个小小的修改。 如果我们想要在同一时间仅从单个客户端,允许相同的登陆创建多个连接,让我们修改触发器。

-----------------------------------------------------------
-- 创建一个登录触发器,以便允许一台主机只能创建一个连接
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
   @connectioncount int,
   @host1   nvarchar(128),
   @host2   nvarchar(128),
   @message nvarchar(2000);
BEGIN 
   -- 从不同的主机选择用户连接的数量
   SELECT @connectioncount = COUNT(*),
          @host1           = MIN(a.host_name),
          @host2           = MAX(a.host_name)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process      = 1
   AND   lower(a.program_name)  = 'login tester'
   AND    a.original_login_name = ORIGINAL_LOGIN()
   AND    EXISTS(   SELECT 1
                    FROM  sys.dm_exec_sessions b
                    WHERE b.is_user_process = 1
                    AND   b.program_name = a.program_name
                    AND   b.host_name != a.host_name);

   IF  (@connectioncount > 1) BEGIN
      ROLLBACK; 
   END;
END;
GO
第 9 段(可获 1.69 积分)

逻辑几乎与以前是一样的。我只是修改了查询,以查找来自不同主机名的连接。如果找到多于一个的连接,停止登录尝试。

现在客户端需要调整。正如一开始所说的我们依靠的是客户端连接时发送的信息。在测试程序中你可以选择你想用客户端1还是客户端2发起连接。这两个客户端的名称几乎是相同的,他们在开始时只有一个不同的客户端号。

但要保持示例尽可能接近真实世界的情况,该程序还使用客户端计算机的MAC地址来标识自己。使用下面代码获取MAC地址

/// <summary>
/// 返回一个网卡的MAC地址用于客户端识别
/// </summary>
/// <returns></returns>
internal static string MacAddress() {
   var macQuery = 
     from netInterface in System.Net.NetworkInformation.NetworkInterface.GetAllNetworkInterfaces()
     where netInterface.OperationalStatus == System.Net.NetworkInformation.OperationalStatus.Up
     && netInterface.NetworkInterfaceType != System.Net.NetworkInformation.NetworkInterfaceType.Loopback
     && netInterface.NetworkInterfaceType != System.Net.NetworkInformation.NetworkInterfaceType.Tunnel
     orderby netInterface.Id descending
     select netInterface;

   return macQuery.FirstOrDefault().GetPhysicalAddress().ToString();
}

 

第 10 段(可获 1.58 积分)

这个想法是程序用于操作的地址不是一个回环地址。在实际情况中,你希望在查询中添加更多的检查条件,例如,地址是有意义的,而不是一个空字符串。

现在,如果你使用客户端1的名称同时从两个正在运行的测试程序发起连接,这应该是成功的。然而,如果你使用客户端1的名称从程序1连接,并使用客户端2的名称从程序2连接,后者应该像先前一样连接失败。

因此,我们不能同时从两个具有不同名称的客户端连接。再次,如果您关闭连接,并尝试从另一个连接,要记得清除连接池。

第 11 段(可获 1.55 积分)

从登录触发器与客户端通信

好吧,我们可以限制登录,但客户端总是得到相同的消息,实际上并没有解释为什么连接被拒绝。 在某些情况下,能够告知客户端原因的话这将是极好的。 然而,利用传统的机制,例如RAISERROR 或者PRINT是不能实现的。 来自MSDN

登录触发器在登录身份验证阶段完成后被激发,但在用户会话实际建立之前。因此,触发器内部发出的到达用户的所有消息,如错误消息和PRINT语句消息,都将转储到SQL Server错误日志。

第 12 段(可获 1.36 积分)

那太遗憾了,让我们不得不想想别的方式。一个策略是我们有一个共享的文件夹,它可以由SQL Server和应用程序同时访问。如果我们在这个文件夹中创建一个小的消息文件,我们就可以在文件中写入原因让客户端获取。

本文中使用的CLR存储过程的例子是简化的,如果你感兴趣,你可以阅读更多关于从SQL Server中写入一个文件从数据库中写入一个文件. 它还解释了创建和注册存储过程所需的步骤。

在这个例子中使用的CLR存储过程看起来像这样

public class Logger {
   /// <summary>
   /// 将一行数据写入指定的文件中的存储过程
   /// </summary>
   /// <param name="fileName">Full name of file to write into</param>
   /// <param name="line">Line to write</param>
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void WriteLine(string fileName, string line) {
      System.IO.FileStream logStream;
      System.IO.StreamWriter logFile;

      using (logStream = new System.IO.FileStream(fileName, 
                                                  System.IO.FileMode.Append, 
                                                  System.IO.FileAccess.Write, 
                                                  System.IO.FileShare.ReadWrite)) {
         using (logFile = new System.IO.StreamWriter(logStream)) {
            logFile.WriteLine(line);
            logFile.Flush();
            logFile.Close();
         }
      }
   }
}

代码很简单,将文本行写入提供的文件中。

logger项目编译后,我们可以像下面这样将存储过程注册到SQL Server中去

-----------------------------------------------------------
-- 添加CLR存储过程
-----------------------------------------------------------
ALTER DATABASE LogonTest SET TRUSTWORTHY ON;
GO

sp_configure 'show advanced options', 1; 
GO 

RECONFIGURE; 
GO 

sp_configure 'clr enabled', 1; 
GO 

RECONFIGURE; 
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'WriteLine')
DROP PROCEDURE WriteLine
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Logger')
DROP ASSEMBLY Logger
GO

CREATE ASSEMBLY Logger 
FROM ' ……\LoginTestExe\Logger\bin\Debug\Logger.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE PROCEDURE WriteLine (@fileName nvarchar(2000), @line nvarchar(2000)) 
AS EXTERNAL NAME Logger.Logger.WriteLine;
GO
第 13 段(可获 1.78 积分)

因为我们将使用logon触发器程序,我们需要做以下几件事

  • 添加数据库用户LogOnUser
  • 给用户LogOnUser赋予执行存储过程的权限。

例子如下:

-----------------------------------------------------------
-- Grant privileges to LogOnUser
-----------------------------------------------------------
USE LogonTest;
GO

CREATE USER LogonUser FOR LOGIN LogonUser;
GO

GRANT EXECUTE ON LogonTest..WriteLine TO LogonUser;
GO

现在我们可以对logon触发器做个小调整。如下所示:

-----------------------------------------------------------
-- Create a logon trigger to allow a connection from
-- one host only
-- Write information about rejection reason to a file
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
   @connectioncount int,
   @host1         nvarchar(128),
   @host2         nvarchar(128),
   @message         nvarchar(2000);
BEGIN 
   -- Select the amount of connections for the user from different hosts
   SELECT   @connectioncount = COUNT(*),
         @host1 = MIN(a.host_name),
         @host2 = MAX(a.host_name)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process = 1
   AND   lower(a.program_name) = 'login tester'
   AND    a.original_login_name = ORIGINAL_LOGIN()
   AND    EXISTS(   SELECT 1
               FROM   sys.dm_exec_sessions b
               WHERE   b.is_user_process = 1
               AND      b.program_name = a.program_name
               AND      b.host_name != a.host_name);

   IF  (@connectioncount > 1) BEGIN
      SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126) 
                     + ': ' + @host1 +  ': Simultaneous connection from ' + @host2;
      PRINT @message;
      EXEC LogonTest..WriteLine @fileName='C:\TEMP\ConnectionRejectionInfo.txt', @line=@message

      SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126) 
                     + ': ' + @host2 +  ': Simultaneous connection from ' + @host1;
      PRINT @message;
      EXEC LogonTest..WriteLine @fileName='C:\TEMP\ConnectionRejectionInfo.txt', @line=@message

      ROLLBACK; 
   END;
END;
GO
第 14 段(可获 0.61 积分)

与之前的代码基本上是一样的,但补充两点:

  • 由于使用了PRINT命令,为什么连接被拒绝的消息被写入SQL Server 日志
  • 同样的消息被写入一个C:\TEMP 文件夹下名为 ConnectionRejectionInfo.txt 的文件

从每个客户端的角度来看任意一个客户端存在连接的信息都被写入文件中,因此,无论哪一个客户端试图建立连接,针对它的一条消息就可以在文件中找到。另一种方法可以依赖后者的连接总是失败的事实,但是如果拒绝连接的逻辑是不同的,这个方法就不适用了。

第 15 段(可获 1.38 积分)

为了处理程序方面的情况,让我们看看用于连接的代码

      /// <summary>
      /// Connects to the database using the given host name.
      /// 如果连接失败从共享文件中获取消息
      /// </summary>
      /// <param name="hostname">Host name to use</param>
      /// <returns>True, if sucessful</returns>
      internal override bool Connect(string hostName) {
         System.Data.SqlClient.SqlConnection connection = null;
         bool ok = true;
         string[] logMessages;
         System.Collections.Generic.List<LogMessage> messageList;
         LogMessage myMessage;

         try {
            connection = new System.Data.SqlClient.SqlConnection();
            connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
            connection.Open();
            this.ActiveConnection = connection;
         } catch (System.Exception exception) {
            logMessages = System.IO.File.ReadAllLines(@"C:\TEMP\ConnectionRejectionInfo.txt");
            messageList = new System.Collections.Generic.List<LogMessage>(logMessages.Length);
            foreach (string line in logMessages) {
               messageList.Add(new LogMessage(line));
            }
            var msgQuery = from msg in messageList
                           where msg.MessageTime != null
                           && msg.Host == hostName
                           orderby msg.MessageTime descending
                           select msg;
            myMessage = msgQuery.FirstOrDefault();
            System.Windows.MessageBox.Show(myMessage != null ? myMessage.Message : exception.Message, 
                                           ConnectionBase.Title);
            CloseAndDisposeConnection(ref connection);
            ok = false;
         }

         return ok;
      }
第 16 段(可获 0.23 积分)

如果连接失败,在共享文件中的内容将被读取并使用LogMessage类解析。之后,基于客户端名称和最新时间戳的正确的消息将被找到。向用户显示此消息。

让我们来测试:如果你第一次使用客户端1从程序1连接,然后使用客户端2建立一个带获取消息功能的普通连接,你应该收到一条类似于以下内容太的消息

错误信息也可以在SQL Server日志中找到

 

请记住,要使它工作你需要有一个C:\TEMP文件夹,并有权访问它或修改程序集和触发器以使用一些其他文件。

第 17 段(可获 1.41 积分)

使用一个数据库特定的登录程序

使用登录触发器并不总是可行的或可能的。例如,考虑一个存在多个不同数据库的合并的SQL Server实例。你可能没有(也不应该有)权限访问服务器的大部分功能,即使你可以,也不能保证创建一个登录触发器不会影响使用其他数据库的程序。

一个简单的解决方案是将登录触发器中的逻辑移到一个存储过程,并总是在连接到数据库时,调用该存储过程。

首先,让我们删除登录触发器,以便它不影响进一步的测试

-- 删除登录触发器
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO

现在用于存储过程的T-SQL代码可以像下面这样

-----------------------------------------------------------
-- 创建一个存储过程来检查存在其他主机的连接。如果发现了,则引发一个错误
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'CheckConnection')
DROP PROCEDURE CheckConnection
GO

CREATE PROCEDURE CheckConnection
WITH EXECUTE AS 'LogOnUser'
AS
DECLARE
   @connectioncount INT,
   @host            VARCHAR(128),
   @user            VARCHAR(128),
   @msg             VARCHAR(128);
BEGIN
   -- Select the amount of connections for the user from different hosts
   SELECT @connectioncount = COUNT(*)
   FROM sys.dm_exec_sessions a
   WHERE a.is_user_process = 1
   AND   lower(a.program_name) = 'login tester'
   AND   a.original_login_name = ORIGINAL_LOGIN()
   AND   EXISTS(  SELECT 1
                  FROM  sys.dm_exec_sessions b
                  WHERE b.is_user_process = 1
                  AND   b.program_name = a.program_name
                  AND   b.host_name != a.host_name);

   IF (@connectioncount > 1) BEGIN
      SELECT TOP 1
             @user = a.login_name,
             @host = a.host_name
      FROM sys.dm_exec_sessions a
      WHERE a.is_user_process = 1
      AND   lower(a.program_name) = 'login tester'
      AND   a.original_login_name = ORIGINAL_LOGIN()
      ORDER BY a.login_time;
       
      SET @msg = 'User ' + @user + ' already connected from ' + @host;
      RAISERROR(@msg, 15, 1);
     RETURN;
   END;
END;
GO

GRANT EXECUTE ON CheckConnection TO NormalUser;
GO
第 18 段(可获 1.46 积分)

逻辑上基本和以前一样,但现在我们可以使用RAISERROR,因为我们执行存储过程的上下文成功建立了连接。调用端现在看起来像这样

 internal override bool Connect(string hostName) {
    System.Data.SqlClient.SqlConnection connection = null;
    bool ok = true;

    try {
       connection = new System.Data.SqlClient.SqlConnection();
       connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
       connection.Open();
       using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand()) {
          command.Connection = connection;
          command.CommandText = "CheckConnection";
          command.CommandType = System.Data.CommandType.StoredProcedure;
          command.ExecuteNonQuery();
       }
       this.ActiveConnection = connection;
    } catch (System.Exception exception) {
       System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
       CloseAndDisposeConnection(ref connection);
       ok = false;
    }

    return ok;
}
第 19 段(可获 0.43 积分)

成功连接后,该过程会被调用。如果成功,继续该程序;否则显示消息说明为什么连接失败。

使用无需服务器侧系统视图的数据库特定登录过程

如果考虑一个更受限制的情况,我们必须接受不能使用服务器服务的事实,我们必须有替代方法。这可能是在统一环境中的情况:你没有获取系统视图的权限,只能使用专有数据库中的信息。

第 20 段(可获 1.21 积分)

以前,我们可以通过检查sys拥有的视图查看存在的登录,但是如果我们不允许这样做,我们必须自己维护登录信息。 问题是,如果我们创建一个表,当我们进入数据库时插入一行数据,当程序结束时删除这行数据,我们可能很快会遇到问题。 想想程序异常终止的情况。 在这种情况下,它不能清除登录表中的信息,当尝试重新连接时,我们已经有一个“活动”的连接,基本上没有办法知道信息是否准确。

第 21 段(可获 1.4 积分)

一般来说,我建议避免读取未提交的数据,但在这种特殊情况下,SQL Server从正在进行的事务中读取信息的能力带来很大便利。 这通过在SELECT语句中使用 WITH (NOLOCK)提示来实现。 正如所说的那样,在正常情况下,这可能会导致不想要的结果,所以如果你有更多的兴趣,请阅读当使用NOLOCK提示时应该考虑什么

因此,我们的想法是,我们首先登录到SQL Server,然后我们从预留表中读取连接预留。 如果没有冲突的连接,我们使用客户端上启动的事务将新的预留写入表中。 此后,客户端继续正常操作,但是只要程序运行,事务就保持打开。 当程序结束时,事务被回滚,并且持有事务的连接被关闭。

第 22 段(可获 1.83 积分)

为什么这么复杂? 原因是,通过使用事务,我们确保如果程序崩溃,事务由SQL Server自动回滚,并且我们在预留表中没有残留信息。

保存预留信息的表结构可能看起来像下面这样

-----------------------------------------------------------
-- Reservation table
-----------------------------------------------------------
CREATE TABLE ConnectReservation (
   ID          INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
   Host        VARCHAR(128) NOT NULL,
   Program     VARCHAR(128) NOT NULL,
   UserName    VARCHAR(128) NOT NULL,
   ConnectTime DATETIME     NOT NULL,
   ProcessId   SMALLINT     NOT NULL
);
GO
第 23 段(可获 0.59 积分)

现在的存储过程就变成下面这样子:

-----------------------------------------------------------
-- Procedure
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ReserveConnection')
DROP PROCEDURE ReserveConnection
GO

CREATE PROCEDURE ReserveConnection AS
DECLARE
   @host VARCHAR(128),
   @user VARCHAR(128),
   @msg VARCHAR(128);
BEGIN
   -- Check if a connection from the host already exists
   SELECT @host = cr.Host,
          @user = cr.UserName
   FROM   ConnectReservation cr WITH (NOLOCK)
   WHERE  cr.UserName = ORIGINAL_LOGIN()
   AND    cr.Host     != HOST_NAME();

   IF (@host IS NOT NULL) BEGIN
      SET @msg = 'User ' + @user + ' already connected from ' + @host;
      RAISERROR(@msg, 15, 1);
      RETURN;
   END;

   INSERT INTO ConnectReservation (Host, Program, UserName, ConnectTime, ProcessId)
      VALUES (HOST_NAME(), APP_NAME(), ORIGINAL_LOGIN(), SYSDATETIME(), @@SPID);
END;
GO
第 24 段(可获 0.1 积分)

正如你可以看到的存储过程执行了一个简单的select语句来检查来自其他主机的连接是否已经存在。 如前所述,关键是使用WITH (NOLOCK)提示。 如果存在该行,则会引发错误,否则将插入预留信息。

如果我们看看客户端,它与以前有点不一样

      private StandardConnection ActiveConnection;
      private System.Data.SqlClient.SqlConnection ConnectReservation;
      private System.Data.SqlClient.SqlTransaction ReservationTransaction { get; set; }

      /// <summary>
      /// Connects to the database using the given host name
      /// </summary>
      /// <param name="hostname">Host name to use</param>
      /// <returns>True, if sucessful</returns>
      internal override bool Connect(string hostName) {
         bool ok = true;

         try {
            this.ConnectReservation = new System.Data.SqlClient.SqlConnection();
            this.ConnectReservation.ConnectionString = ConnectionBase.GetConnectionString(hostName);
            if (!ReserveConnection(this.ConnectReservation)) {
               this.ConnectReservation.Dispose();
               return false;
            }
            this.ActiveConnection = new StandardConnection();
            if (!this.ActiveConnection.Connect(hostName)) {
               ConnectionBase.CloseAndDisposeConnection(ref this.ConnectReservation);
               ok = false;
            }
         } catch (System.Exception exception) {
            System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
            ok = false;
         }

         return ok;
      }

      /// <summary>
      /// Open and reserves a connection to the SQL Server. Existing connections 
      /// are checked and if not found, a new connect reservation is made
      /// </summary>
      /// <param name="connection">Connection to use. This should be kept alive and 
      ///    transaction active for desired duration</param>
      /// <returns>True if reservation is successful</returns>
      private bool ReserveConnection(System.Data.SqlClient.SqlConnection connection) {
         bool ok = true;

         try {
            connection.Open();
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand()) {
               command.Connection = connection;
               this.ReservationTransaction = connection.BeginTransaction();
               command.Transaction = this.ReservationTransaction;
               command.CommandText = "ReserveConnection";
               command.CommandType = System.Data.CommandType.StoredProcedure;
               command.ExecuteNonQuery();
               // Leave the transaction open
            }
         } catch (System.Exception exception) {
            System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
            ConnectionBase.CloseAndDisposeConnection(ref connection);
            ok = false;
         }
         return ok;
      }

      /// <summary>
      /// Close current connections
      /// </summary>
      override internal bool CloseConnection() {
         if (this.ActiveConnection != null) {
            this.ActiveConnection.CloseConnection();
         }

         if (this.ReservationTransaction != null) {
            this.ReservationTransaction.Rollback();
            this.ReservationTransaction = null;
         }
         CloseAndDisposeConnection(ref this.ConnectReservation);
        
         return true;
      }
第 25 段(可获 0.75 积分)

当连接时完成了以下操作:

  • 事务开始
  • 调用ReserveConnection存储过程
  • 如果成功,则存储包含活动事务的连接
  • 将创建要执行数据库操作的实际连接

在这种情况下,使用与保持事务打开的连接的单独连接进行实际操作至关重要。 否则,我们将在程序执行的某个时刻提交或回滚预留的事务。

使用具有事务性预留的程序验证用户

我们还没有碰到的事是用户认证。 在所有示例中,我们使用SQL Server的实际登录名进行用户标识。 登录可以是Windows身份验证或SQL Server身份验证,但仍然由数据库服务器验证。 如果情况是针对不同用户使用相同的登录,并且该用户实际上是被程序标识的,那该怎么办。

第 26 段(可获 1.9 积分)

在本文中,我不会深入关于正确的由程序完成的用户身份验证的细节,我认为它是正确完成的。 认证步骤将在连接预留信息之前,因此您可能会有一个小而好的存储过程来检查用户是否已知,并且散列密码与存储在数据库中的散列值相匹配。

连接预留信息的修改是非常小的。我们将用户名传递给存储过程,而不是使用系统函数 ORIGINAL_LOGIN()

-----------------------------------------------------------
-- 创建一个存储过程,以根据预留表数据和程序标识的用户检查来自其他主机的现有连接
-- 如果找到,则引发一个错误
-----------------------------------------------------------
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ReserveConnectionWithUser')
DROP PROCEDURE ReserveConnectionWithUser
GO

CREATE PROCEDURE ReserveConnectionWithUser @user varchar(128) AS
BEGIN
   SELECT @host = cr.Host
   FROM   ConnectReservation cr WITH (NOLOCK)
   WHERE  cr.UserName = @user
   AND     cr.Host     != HOST_NAME();

   IF (@host IS NOT NULL) BEGIN
      SET @msg = 'User ' + @user + ' already connected from ' + @host;
      RAISERROR(@msg, 15, 1);
      RETURN;
   END;

   INSERT INTO ConnectReservation (Host, Program, UserName, ConnectTime, ProcessId)
      VALUES (HOST_NAME(), APP_NAME(), @user, SYSDATETIME(), @@SPID);
END;
GO
第 27 段(可获 1.06 积分)

在上述变化中,检查和使用的预留信息都是使用提供的用户名。

同样在程序中的改变很小,我们只是将用户名作为参数传递给存储过程

      /// <summary>
      /// Open and reserves a connection to the SQL Server. Existing connections are checked 
      /// and if not found, a new connect reservation is made
      /// </summary>
      /// <param name="connection">Connection to use. This should be kept alive and 
      /// transaction active for desired duration</param>
      /// <param name="userName">User name to make the reservation for</param>
      /// <returns>True if reservation is successful</returns>
      private bool ReserveConnectionWithUser(System.Data.SqlClient.SqlConnection connection, 
                                             string userName) {
         bool ok = true;

         try {
            connection.Open();
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand()) {
               command.Connection = connection;
               this.ReservationTransaction = connection.BeginTransaction();
               command.Transaction = this.ReservationTransaction;
               command.CommandText = "ReserveConnectionWithUser";
               command.CommandType = System.Data.CommandType.StoredProcedure;
               command.Parameters.Add(new System.Data.SqlClient.SqlParameter() {
                  ParameterName = "user",
                  DbType = System.Data.DbType.String,
                  Direction = System.Data.ParameterDirection.Input,
                  Value = userName
               });
               command.ExecuteNonQuery();
               // Leave the transaction open
            }
         } catch (System.Exception exception) {
            System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
            ConnectionBase.CloseAndDisposeConnection(ref connection);
            ok = false;
         }
         return ok;
      }
第 28 段(可获 0.45 积分)

结语

当我写这篇文章时度过了非常有趣的时光,我希望你会从这些想法中受益。 我知道我没有包括所有可能的变化,例如未提到的一种变化是使用程序认证用户与数据库存储过程,但在允许使用系统范围视图的情况下。 然而,正如上一章所述,改动非常小,因为我不想让文章变得太长就没写。

当然,如果您有更多的想法或问题,请随时留下评论。 但是在任何情况下,都要享受编码带来的乐趣!

链接和参考

第 29 段(可获 1.63 积分)

文章评论

toypipi
为什么这篇文章的第20段没有管理员处理呢??? :sob: :sob: :sob: