Secure your temporal table history | sqlsunday.com https://sqlsunday.com/2023/01/19/secure-temporal-tab...
Secure your temporal
table history
2023-01-19 / DANIEL HUTMACHER
You may have already discovered a relatively new feature in SQL
Server called system-versioned temporal tables
(https://learn.microsoft.com/en-us/sql/relational-databases/tables
/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”.
1 CREATE SCHEMA App;
2
3 CREATE TABLE App.Customers (
4 Company_ID int IDENTITY(1, 1) NOT NULL,
5 CompanyName nvarchar(250) NOT NULL,
6 Email varchar(250) NOT NULL,
7 Valid_From datetime2(7) GENERATED ALWAYS AS
8 Valid_To datetime2(7) GENERATED ALWAYS AS
9 CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID),
10 PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
11 ) 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:
9 ---
10 PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
11 ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=App.Customers_history))
1 of 4 8/9/23, 08:39
Secure your temporal table history | sqlsunday.com https://sqlsunday.com/2023/01/19/secure-temporal-tab...
Put history tables in their own
schema
But you can even create the history table in a different schema:
1 CREATE SCHEMA History;
2
3 CREATE TABLE App.Customers (
4 Company_ID int IDENTITY(1, 1) NOT NULL,
5 CompanyName nvarchar(250) NOT NULL,
6 Email varchar(250) NOT NULL,
7 Valid_From datetime2(7) GENERATED ALWAYS AS
8 Valid_To datetime2(7) GENERATED ALWAYS AS
9 CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID),
10 PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
11 ) 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:
1 CREATE USER LowPrivilegeUser WITHOUT LOGIN;
2 GRANT SELECT, INSERT, UPDATE, DELETE ON App.Customers
Now, let’s impersonate this user to see what we can do.
1 EXECUTE AS USER='LowPrivilegeUser';
2
3 --- Create a customer record
4 INSERT INTO App.Customers (CompanyName, Email) VALUES
5
6 --- Update a customer record
7 UPDATE App.Customers SET Email='sales@contoso.com' WHERE
8
9 --- Delete the customer record
10 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:
1 --- Can we view the history?
2 SELECT * FROM History.Customers;
2 of 4 8/9/23, 08:39
Secure your temporal table history | sqlsunday.com https://sqlsunday.com/2023/01/19/secure-temporal-tab...
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
1 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.
HISTORY ,
PERMISSIONS ,
TEMPORAL TABLE ,
VERSIONING
3 thoughts on “Secure your temporal
table history”
1. Wilfred van Dijk
2023-01-19 AT 11:27
I think this is solved in SQL2022 by implementing ledger
tables; even DBA’ers can’t delete data from this table type (but
it looks data will be forever in this audit table)
REPLY
3 of 4 8/9/23, 08:39
Secure your temporal table history | sqlsunday.com https://sqlsunday.com/2023/01/19/secure-temporal-tab...
◦ Daniel Hutmacher
2023-01-19 AT 11:32
I think ledger tables are pretty cool, but they solve for
slightly different requirements – plain history is not the
same as a proper ledger.
REPLY
2. Pingback: Securing Temporal Tables – Curated SQL
4 of 4 8/9/23, 08:39
