Control trigger logic based on user
This article describes a mechanism how trigger actions bypassed based on database user information
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 keyModifiableColumn, this column can be modified normallyNonModifiableColumn, the value of this column can be set inINSERT, but later the value cannot be modified viaUPDATE
-- 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 tableTargetTable, the table nameUserName, 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 checksValidityEnd, after this moment the user cannot bypass the checks anymoreIsValid, this is a calculated column; Is the bypass permission currently validComment, 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
- 20th May, 2012: Initial version