You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.

CREATE SCHEMA App;

CREATE TABLE App.Customers (
    Company_ID      int IDENTITY(1, 1) NOT NULL,
    CompanyName     nvarchar(250) NOT NULL,
	Email           varchar(250) NOT NULL,
	Valid_From      datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	Valid_To        datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID),
	PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
) WITH (SYSTEM_VERSIONING=ON);

What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.

But how to you version the contents of a table, while hiding things like deleted records from prying eyes?

In the example above, we didn’t specify the name of the history table, so SQL Server picked one for us, App.MSSQL_TemporalHistoryFor_18099105, where the number at the end is the object_id of our base table. If you’re like me, you may want to pick a slightly more readable table name, but specifying one in the CREATE TABLE statement:

---
	PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=App.Customers_history));

Put history tables in their own schema

But you can even create the history table in a different schema:

CREATE SCHEMA History;

CREATE TABLE App.Customers (
    Company_ID      int IDENTITY(1, 1) NOT NULL,
    CompanyName     nvarchar(250) NOT NULL,
	Email           varchar(250) NOT NULL,
	Valid_From      datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	Valid_To        datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID),
	PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=History.Customers));

I tend to use database schemas to assign permissions, rather than messing with object-level permissions. With just a little planning, you can build simple, yet very effective, access controls using schemas. In our case, we could limit access to the History schema to only a handful of principals, while allowing the application to access the App schema as usual.

Testing the permissions

Let’s create a minimally privileged user and assign some schema permissions on the App schema:

CREATE USER LowPrivilegeUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON App.Customers TO LowPrivilegeUser;

Now, let’s impersonate this user to see what we can do.

EXECUTE AS USER='LowPrivilegeUser';

--- Create a customer record
INSERT INTO App.Customers (CompanyName, Email) VALUES ('Contoso', '[email protected]');

--- Update a customer record
UPDATE App.Customers SET Email='[email protected]' WHERE Email='[email protected]';

--- Delete the customer record
DELETE FROM App.Customers WHERE CompanyName='Contoso';

But if we try to work with the history table as the low-privilege user, we get a permission error:

--- Can we view the history?
SELECT * FROM History.Customers;

Msg 229, Level 14, State 5, Line 39
The SELECT permission was denied on the object 'Customers', database 'Test', schema 'History'.

Finally, we’ll revert back to the user we logged in as

REVERT;

Data retention vs. privacy

Let’s be honest. We all hang on to data more than we probably need to. The new gold standard, whether you are subject to new privacy laws or not, is to design software with “privacy by default” in mind, meaning you only keep data you absolutely need in order to meet your business objective. When you do need to keep old data, for whatever reason (auditing, analysis, etc), you want to reduce the surface area of this data as much as you can.

A data breach does not always mean somebody walking out of the data center with all your backups or files – it could just be a bug in the application API being exploited by a script kiddie, or even a user with a grudge. This is why the principle of “least required permissions” is so important.

The pattern we’ve looked at here is a low-effort pattern to implement a principle of least permissions for historical data.