2020-02-15

How to make a Table Non-Updatable

It's a common business requirement that some data needed to be unchangeable once it entered into the database, such as payments, bank transfers, bets, etc. Unfortunately, SQL Server has no built in declarative support for immutable columns, but we database administrators and sql developers can make it ourselves by writing triggers which rollback or skip the update operations. In order to tighten it up, we can obscure the body text of the trigger using WITH ENCRYPTION option in the create trigger statement. Furthermore, we can create SQL Server Audit on SCHEMA_OBJECT_CHANGE_GROUP event class, in order to trace any alter/drop/disable operations on the triggers. Let's see a simple demo:

USE TestDB
GO
-- Create a demo Table, with one primary key column, one ordinary column, and one immutable column
CREATE TABLE DemoTable (pk int PRIMARY KEY, col1 varchar(50), immutableCol varchar(50));
GO

-- Create an encrypted trigger to prevent update on the immutable column
CREATE TRIGGER tr_DemoTable_Upd ON DemoTable
WITH ENCRYPTION
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(immutableCol) BEGIN ROLLBACK; RETURN; END
END
GO

-- Create a server audit object
USE master
GO
CREATE SERVER AUDIT Audit_Demo
TO FILE (
FILEPATH = N'D:\temp',
MAXSIZE = 50 MB,
MAX_ROLLOVER_FILES = 1000
)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT Audit_Demo WITH (STATE = ON);
GO
-- Create database audit specification to trace schema changes in the user database
USE TestDB
GO
CREATE DATABASE AUDIT SPECIFICATION DbAuditSpec_SchemaChg
FOR SERVER AUDIT Audit_Demo
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO


Now we have done all the widgets, let's have a test on it.
-- add a testing row
INSERT DemoTable (pk, col1, immutableCol) VALUES (1, 'ABC', 'Immutable value');
GO
SELECT * FROM DemoTable;
GO
-- try updating the immutable column, which should be fail
UPDATE DemoTable SET immutableCol = 'Update it';
GO
SELECT * FROM DemoTable;


Let's try to disable the trigger, then check the audit log to see this action has been logged.
DISABLE TRIGGER tr_DemoTable_Upd ON DemoTable;

Although this approach cannot stop someone with sysadmin right to modify the data, it can deter them from attacking it.

No comments:

Post a Comment