Control trigger logic based on user

• Download control - 1.7 KB

Introduction

Triggers are great to enforce different kind of business logic. For example, if the business rules define that a column value cannot be changed if another column contains certain data, triggers can prevent these kind of modifications. 

However, sometimes it may be necessary to allow such modifications. Typically in this situation the trigger is temporarily disabled, changes are carried out and the trigger is enabled again. The tricky part is that while the trigger is disabled it affects all users. If the modification is done by an admin or equivalent it would be handy to disable the checks only for that person.

This article describes one way to create a user based allowance to bypass checks. 

The scenario 

First we need a table. The test table is simple and contains only few columns:

• KeyColumn, this is the primary key
• ModifiableColumn, this column can be modified normally
• NonModifiableColumn, the value of this column can be set in INSERT, but later the value cannot be modified via 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 

Now to ensure that the last column cannot be modified let's create a simple trigger

-- 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 

The trigger fires upon update and just checks if any of the updated rows contain a different value than the original row in NonModifiableColumn. If there are differences, an error is generated and the transaction is rolled back. 

Next, add a few rows 

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

and  fetch the data

-- Fetch the data
SELECT * FROM TableWithRestrictions; 

The results should look like

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

So let's check that the trigger doesn't allow to update the NonModifiableColumn.

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

What should be seen is an error message like:

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. 

Create allowance configuration

In order to allow bypassing a check, lets create a small configuration table. The table will contain the following information:

• TargetSchema, name of the schema of the table
• TargetTable, the table name
• UserName, name of the user allowed to make modifications 

So the table looks like this

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

The logic is going to rely on the fact that both the table and the user exist in the database. Because of this a small trigger is created to check that both the table and the user exist in the database. The trigger looks like the following

-- 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 

So the trigger checks from INFORMATION_SCHEMA.TABLES that the table is present and from sysusers that the user inserted to the table is a real database user.  

Next step is to alter the original trigger on TableWithRestrictions to check if the user is allowed to make modifications. The new trigger version looks like this

-- 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 

The check for NonModifiableColumn is still the same but before it is checked, the trigger checks if the user and this table is listed in AllowedModification table. If the user is found, the business rule check is bypassed. 

Test with another user

In order to test this, let's create another user. For this we need a login, a database user definition and privileges to modify the 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 

Now that the user is ready, let's give him privileges to bypass the business rule check 

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

Now to test the behavior we need to open a new session to the database using the just created credentials. When the session is opened, try running the following statements.

----------------------------------------------
-- 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
---------------------------------------------- 

Now the UPDATE was carried out successfully and the data selected from TableWithRestrictions looks like 

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

And if the same statements are executed with some other credentials, an error still generates. 

Taking this a bit further 

While we're at it, lets not stop here. We can now control who can bypass the checks and for the rest the checks apply. But in the beginning I said that occasionally there may be need to bypass the checks. In order for the solution so far to work nicely, the user should be removed from AllowedModicication so that he cannot bypass the checks all the time. That's not handy.

So lets expand the configuration table a little bit. First remove all the rows

-- Truncate the permission table
TRUNCATE TABLE AllowedModicication; 

And add few additional columns

• ValidityStart, defines the starting moment the user can bypass checks
• ValidityEnd, after this moment the user cannot bypass the checks anymore
• IsValid, this is a calculated column; Is the bypass permission currently valid
• Comment, free description,  for example why the permission was granted 

-- 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); 

So if no value is defined to ValidityStart, the permission is granted immediately and it lasts for one hour by default.

Another enhancement is to create a small function to actually do the check. The function looks like 

-- 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 

Now the original trigger on TableWithRestrictions can be simplified as

-- 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 

So let's make a small test and insert a new permission

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

And to see the permission details

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

The results looks something like

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 

So now I have one hour to make modifications and that permission will expire automatically without any further operations.  

Final words 

So, hopefully this article explained a few ideas how logic in triggers can be controlled based on the database user. References you may find useful: 

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

History

• 20^th May, 2012: Initial version