根据用户控制触发器逻辑

• 下载控制 - 1.7 KB

引言

触发器非常适合强制执行各种业务逻辑。例如，如果业务规则定义一个列值在另一个列包含特定数据时不能被更改，触发器可以阻止此类修改。

然而，有时可能需要允许这些修改。通常在这种情况下，触发器会被暂时禁用，执行更改，然后再次启用触发器。棘手之处在于，当触发器被禁用时，它会影响所有用户。如果修改是由管理员或同等权限的用户执行的，那么只为该用户禁用检查会很方便。

本文介绍了一种基于用户允许绕过检查的方法。

场景

首先，我们需要一个表。测试表很简单，只包含几个列。

• KeyColumn，这是主键
• ModifiableColumn，此列可以正常修改
• NonModifiableColumn，此列的值可以在INSERT时设置，但之后不能通过UPDATE修改

-- Create a table with restrictions
CREATE TABLE TableWithRestrictions (
   KeyColumn           INT          IDENTITY(1,1) NOT NULL,
   ModifiableColumn    VARCHAR(100)               NOT NULL,
   NonModifiableColumn INT                        NOT NULL
);
GO 

现在，为了确保最后一列不能被修改，让我们创建一个简单的触发器。

-- Create a trigger to prevent modifications on NonModifiableColumn
CREATE TRIGGER trgTableWithRestrictions
ON TableWithRestrictions
AFTER UPDATE
AS
   DECLARE @IllegalModifications INT;
BEGIN
   -- Check if NonModifiableColumn has been changed
   SET @IllegalModifications = (SELECT COUNT(*)
                                FROM deleted d INNER JOIN inserted i on d.KeyColumn = i.KeyColumn
                                AND  d.NonModifiableColumn <> i.NonModifiableColumn);

   IF (@IllegalModifications > 0) BEGIN
      RAISERROR('NonModifiableColumn cannot be changed', 16, 1) WITH SETERROR;
      ROLLBACK;
   END;
END;
GO 

触发器会在更新时触发，并检查任何更新的行是否在NonModifiableColumn中包含与原始行不同的值。如果存在差异，则会生成错误并回滚事务。

接下来，添加几行。

-- Insert some rows
INSERT INTO TableWithRestrictions (ModifiableColumn, NonModifiableColumn) VALUES ('First', 1);
INSERT INTO TableWithRestrictions (ModifiableColumn, NonModifiableColumn) VALUES ('Second', 2); 

并获取数据。

-- Fetch the data
SELECT * FROM TableWithRestrictions; 

结果应该如下所示：

KeyColumn  ModifiableColumn  NonModifiableColumn 
---------  ----------------  -------------------
1          First             1
2          Second            2 

所以，让我们检查一下触发器是否不允许更新NonModifiableColumn。

-- Try to update the NonModifiableColumn
UPDATE TableWithRestrictions SET NonModifiableColumn = NonModifiableColumn + 1; 

应该会看到一个错误消息，如下所示：

Msg 50000, Level 16, State 1, Procedure trgTableWithRestrictions, Line 14
NonModifiableColumn cannot be changed
Msg 3609, Level 16, State 1, Line 2 
The transaction ended in the trigger. The batch has been aborted. 

创建允许配置

为了允许绕过检查，让我们创建一个小的配置表。该表将包含以下信息：

• TargetSchema，表的架构名称
• TargetTable，表名
• UserName，允许进行修改的用户姓名

所以，表看起来像这样：

-- Create a table for modification permissions
CREATE TABLE AllowedModicication (
   TargetSchema  VARCHAR(128) NOT NULL,
   TargetTable   VARCHAR(128) NOT NULL,
   UserName      VARCHAR(128) NOT NULL
); 

该逻辑将依赖于表和用户都存在于数据库的事实。因此，创建了一个小型触发器来检查表和用户是否都存在于数据库中。触发器如下所示：

-- Create a trigger to ensure that the information is valid
CREATE TRIGGER trgAllowedModicication
ON AllowedModicication
AFTER INSERT, UPDATE
AS
   DECLARE @invalidRows INT;
BEGIN
   -- Check that the table exists
   SET @invalidRows = (SELECT COUNT(*)
                       FROM   inserted i 
                       WHERE  NOT EXISTS (SELECT 1
                                          FROM   INFORMATION_SCHEMA.TABLES t
                                          WHERE  t.TABLE_SCHEMA  = i.TargetSchema
                                          AND    t.TABLE_NAME    = i.TargetTable));

   IF (@invalidRows > 0) BEGIN
      RAISERROR('Table not found in catalog', 16, 1) WITH SETERROR;
      ROLLBACK;
   END;
   
   -- Check that the user exists
   SET @invalidRows = (SELECT COUNT(*)
                       FROM   inserted i 
                       WHERE  NOT EXISTS (SELECT 1
                                         FROM    sysusers u
                                         WHERE   u.name = i.UserName));

   IF (@invalidRows > 0) BEGIN
      RAISERROR('User not found in database', 16, 1) WITH SETERROR;
      ROLLBACK;
   END;
END;
GO 

因此，触发器会从INFORMATION_SCHEMA.TABLES检查表是否存在，并从sysusers检查插入到表中的用户是否是真正的数据库用户。

下一步是修改TableWithRestrictions上的原始触发器，以检查用户是否被允许进行修改。新版本的触发器如下所示：

-- Alter the trigger to check allowed modifications
ALTER TRIGGER trgTableWithRestrictions
ON TableWithRestrictions
AFTER UPDATE
AS
   DECLARE @IllegalModifications INT;
   DECLARE @ByPassAllowed INT;
BEGIN
   SET @ByPassAllowed = (SELECT COUNT(*)
                         FROM   AllowedModicication am
                         WHERE  am.UserName     = USER
                         AND    am.TargetSchema = 'dbo'
                         AND    am.TargetTable  = 'TableWithRestrictions');

   IF (@ByPassAllowed = 0) BEGIN
      -- Check if NonModifiableColumn has been changed
      SET @IllegalModifications = (SELECT COUNT(*)
                                   FROM deleted d INNER JOIN inserted i on d.KeyColumn = i.KeyColumn
                                   AND  d.NonModifiableColumn <> i.NonModifiableColumn);

      IF (@IllegalModifications > 0) BEGIN
         RAISERROR('NonModifiableColumn cannot be changed', 16, 1) WITH SETERROR;
         ROLLBACK;
      END;
   END;
END;
GO 

对NonModifiableColumn的检查仍然相同，但在检查之前，触发器会检查用户和此表是否列在AllowedModification表中。如果找到用户，则会绕过业务规则检查。

使用另一个用户进行测试

为了测试这一点，让我们创建另一个用户。为此，我们需要一个登录名、一个数据库用户定义以及修改TableWithRestrictions的权限。

-- Create a test user
EXEC sp_addlogin @loginame = 'mika', @passwd = 'whoknows';

EXEC sp_adduser @loginame = 'mika';

GRANT SELECT, INSERT, UPDATE, DELETE ON TableWithRestrictions TO mika;
GO 

现在用户已准备就绪，让我们给他绕过业务规则检查的权限。

-- allow the user to make modifications
INSERT INTO AllowedModicication (TargetSchema, TargetTable, UserName) VALUES ('dbo', 'TableWithRestrictions', 'mika');
GO  

现在，为了测试行为，我们需要使用刚刚创建的凭据打开一个新的数据库会话。会话打开后，尝试运行以下语句。

----------------------------------------------
-- This part is executed using the test user credentials
----------------------------------------------
-- Test that the table is found
SELECT * FROM TableWithRestrictions;
GO

-- Try to update the NonModifiableColumn
UPDATE TableWithRestrictions SET NonModifiableColumn = NonModifiableColumn + 1;
GO

-- Fetch the data
SELECT * FROM TableWithRestrictions;
GO
----------------------------------------------
-- End of part, now use the original user who built the tables
---------------------------------------------- 

现在UPDATE已成功执行，从TableWithRestrictions选择的数据如下所示：

KeyColumn  ModifiableColumn  NonModifiableColumn 
---------  ----------------  -------------------
1          First             2
2          Second            3 

如果使用其他凭据执行相同的语句，仍然会生成错误。

进一步拓展

既然我们已经做到这里，不妨继续深入。我们现在可以控制谁可以绕过检查，而其他人则受到检查的约束。但一开始我说过，偶尔可能需要绕过检查。为了使到目前为止的解决方案能够很好地工作，应该将用户从AllowedModicication中移除，这样他们就不能一直绕过检查。这并不方便。

因此，让我们稍微扩展一下配置表。首先，移除所有行。

-- Truncate the permission table
TRUNCATE TABLE AllowedModicication; 

并添加几个附加列。

• ValidityStart，定义用户可以绕过检查的起始时间
• ValidityEnd，在此时间之后，用户将无法绕过检查
• IsValid，这是一个计算列：绕过权限当前是否有效
• Comment，自由描述，例如授予权限的原因

-- Add validity information to the table
ALTER TABLE AllowedModicication ADD ValidityStart DATETIME DEFAULT GETDATE() NOT NULL;
ALTER TABLE AllowedModicication ADD ValidityEnd DATETIME DEFAULT DATEADD(hour, 1, GETDATE()) NOT NULL;
ALTER TABLE AllowedModicication ADD IsValid AS IIF(GETDATE() BETWEEN ValidityStart AND ValidityEnd, 1, 0); 

因此，如果未为ValidityStart定义值，则权限将立即授予，并且默认情况下持续一个小时。

另一项增强功能是创建一个小的函数来实际执行检查。该函数如下所示：

-- Create a function to check if restrictions are bypassed
CREATE  FUNCTION BypassAllowed(
   @UserName     VARCHAR(128),
   @TargetSchema VARCHAR(128),
   @TargetTable  VARCHAR(128)) RETURNS INT
AS
BEGIN
   DECLARE @BypassAllowed INT;
   SET @BypassAllowed = (SELECT COUNT(*)
                         FROM   AllowedModicication am
             WHERE  am.UserName     = @UserName
             AND    am.TargetSchema = @TargetSchema
             AND    am.TargetTable  = @TargetTable
             AND    am.IsValid      = 1);
   RETURN @BypassAllowed;
END;
GO 

现在，TableWithRestrictions上的原始触发器可以简化为：

-- Alter the trigger to check allowed modifications
ALTER TRIGGER trgTableWithRestrictions
ON TableWithRestrictions
AFTER UPDATE
AS
   DECLARE @IllegalModifications INT;
BEGIN
   IF (0 = dbo.BypassAllowed( USER, 'dbo', 'TableWithRestrictions')) BEGIN
      -- Check if NonModifiableColumn has been changed
      SET @IllegalModifications = (SELECT COUNT(*)
                                   FROM deleted d INNER JOIN inserted i on d.KeyColumn = i.KeyColumn
           AND  d.NonModifiableColumn <> i.NonModifiableColumn);
      IF (@IllegalModifications > 0) BEGIN
         RAISERROR('NonModifiableColumn cannot be changed', 16, 1) WITH SETERROR;
      ROLLBACK;
      END;
   END;
END;
GO 

所以，让我们做一个小测试并插入一个新的权限。

-- allow the user to make modifications
INSERT INTO AllowedModicication (TargetSchema, TargetTable, UserName, Comment) 
VALUES ('dbo', 'TableWithRestrictions', 'mika', 'Writing a CodeProject article'); 

要查看权限详细信息。

-- Check the data in allowed modifications
SELECT TargetTable, UserName, ValidityStart, ValidityEnd, IsValid, Comment FROM AllowedModicication; 

结果看起来像这样：

TargetTable            UserName  ValidityStart            ValidityEnd              IsValid  Comment
---------------------  --------  -----------------------  -----------------------  -------  -------
TableWithRestrictions  mika      2012-05-20 23:55:13.930  2012-05-21 00:55:13.930  1        Writing a 
                                                                                            CodeProject 
                                                                                            article 

所以，现在我有一个小时的时间进行修改，并且该权限将自动过期，无需任何进一步的操作。

结语

因此，希望本文介绍了一些基于数据库用户控制触发器中逻辑的方法。您可能会发现以下参考资料有用：

• CREATE TABLE
• ALTER TABLE 
• CREATE TRIGGER 
• CREATE FUNCTION 
• RAISERROR 
• sp_addlogin 
• sp_adduser 
• INFORMATION_SCHEMA.TABLES 
• sysusers 

历史

• 2012年5月20日：初始版本