Security Benefits for SQL Server Temporal Tables - Part 2

By:   |   Updated: 2018-05-29   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Temporal Tables


Problem

You may have heard about SQL Server Temporal Tables by now, but do you know all the benefits of using them? In this tip we look at some of the benefits and uses of SQL Server Temporal Tables.

Solution

In part 1 of this series on benefits of Temporal Tables we discussed recovering data from updates and deletes. We saw how we get can the benefit of easy data audits and easy data recovery. In part 2 we are going to see how built in security of temporal table is useful and how we can further harden the security by customizing the implementation by utilizing existing SQL Server objects such as schemas and filegroups.

Securing your Historical Data with SQL Server Temporal Tables

Benefits: Granular security, Built in optimization, Custom optimization

SQL Server Temporal Tables Built-in Security

There is already some built in security with Temporal Tables out of the box, such as:

  1. History tables cannot be dropped unless system_versioning is turned OFF. Permissions to do this task is a higher-level privilege and is usually available to DBAs only.
  2. No data can be deleted from history tables if system_versioning is ON.
  3. No matter what permissions you have, you cannot modify the schema of the history table directly while system_versioning is ON.
  4. A user having permission to read data from a Temporal Table does not automatically get access to its history table.

We will create a SQL Server Login and a user in a TestTemporal database and grant it SELECT permissions on a temporal table. We will see that it will not automatically grant permission to the user to SELECT from the history table. Permission has to be explicitly given on the history table. This is another mechanism to safeguard audit data.

USE [TestTemporal];
GO
CREATE LOGIN ELLA WITH PASSWORD ='Testing?'
GO 
CREATE USER [ELLA] FOR LOGIN ELLA ;
GO
GRANT SELECT ON [dbo].[Customer] TO [ELLA];
GO
 
execute as user ='ELLA';
 
SELECT suser_name();
 
SELECT * FROM Customer;
 
SELECT * FROM dbo.CustomerHistory;
-- This will generate this error:	
-- The SELECT permission was denied on the object 'CustomerHistory', database 'TestTemporal', schema 'dbo'.

REVERT;
select suser_name()
 
-- Cleanup
DROP USER ELLA;
DROP LOGIN ELLA;

We can provide customization to further secure temporal history tables by leveraging other SQL Server features such as SQL Server Audit. This will keep track of successful and failed attempts to access historical data.

History Table on a Separate Database Schema

Another way to restrict access to historical data is by placing the history table in a separate database schema. That privileged schema is tied to a privileged SQL Server login. Code for how to place the table in separate schema is shown below.

In this example, we are going to assume that we have an existing CustomerHistory table in a separate database schema named ADMIN. Then we will create a Customer table in the default schema DBO and add the ADMIN.CustomerHistory table and make it temporal by turning on system versioning. Many real life existing applications will follow this scenario when they are first trying to implement temporal tables to leverage their existing history log tables.

USE [TestTemporal]
GO
CREATE SCHEMA ADMIN;
GO
CREATE TABLE [ADMIN].[CustomerHistory](
   [CustomerId] [INT] NOT NULL,
   [FirstName] [VARCHAR](30) NOT NULL,
   [LastName] [VARCHAR](30) NOT NULL,
   [Amount_purchased] [DECIMAL](18, 0) NOT NULL,
   [StartDate] [DATETIME2](7) NOT NULL,
   [EndDate] [DATETIME2](7) NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [ix_CustomerHistory] ON [ADMIN].[CustomerHistory]
(
   [EndDate] ASC,
   [StartDate] ASC
) GO
-- Create Customer table in default database schema and make it temporal with ADMIN.customerhistory table.
CREATE TABLE [dbo].[Customer](
   [CustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   [FirstName] [varchar](30) NOT NULL,
   [LastName] [varchar](30) NOT NULL,
   [Amount_purchased] [decimal](18, 0) NOT NULL,
   [StartDate] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
   [EndDate] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
   PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate])
   ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ADMIN.CustomerHistory) 
   -- Schema name 'ADMIN' is mandatory here.
   ) 
GO

Now DBAs can assign tighter security control by restricting access to objects in the ADMIN schema.

tables

One way to assign tighter security using a different database schema is shown below.

-- Create Logins
USE [master]
GO
CREATE LOGIN [DBAAdmin] WITH PASSWORD=N'Testing?'
GO
CREATE LOGIN [DBAJunior] WITH PASSWORD=N'Testing?'
GO
 
-- Create Users
USE [TestTemporal]
GO
CREATE USER [DBAAdmin] FOR LOGIN [DBAAdmin]
CREATE USER [DBAJunior] FOR LOGIN [DBAJunior]
GO
 
-- Make both users database owner
ALTER ROLE [db_owner] ADD MEMBER [DBAAdmin]
ALTER ROLE [db_owner] ADD MEMBER [DBAJunior]
GO
 
-- Make DBAAdmin owner of schema ADMIN
ALTER AUTHORIZATION ON SCHEMA::[ADMIN] TO [DBAAdmin]
GO
 
-- Deny Select permission on ADMIN schema for DBAJunior
DENY SELECT ON SCHEMA::[ADMIN] TO [DBAJunior]
GO
 
-- Test the permissions
 
-- DBAJunior can select from dbo.Customer table but not from ADMIN.CustomerHistory table
EXECUTE AS USER ='DBAJunior'
SELECT USER_NAME()
GO
SELECT * FROM [dbo].Customer
SELECT * FROM [ADMIN].CustomerHistory
GO 
 
REVERT
SELECT USER_NAME()
GO
			
-- Error Message: The SELECT permission was denied on the object 'CustomerHistory', database 'TestTemporal', schema 'ADMIN'.
 
-- DBAAdmin can select from both tables
EXECUTE AS USER ='DBAAdmin'
SELECT USER_NAME()
GO
 
SELECT * FROM [dbo].Customer
SELECT * FROM [ADMIN].CustomerHistory
GO 
 
REVERT
SELECT USER_NAME()
GO

History Table on a Separate Database File Group

Placing a history table on a filegroup other than the temporal table will improve the query performance of your application. Another use of a separate filegroup is that you can implement table partitioning for the history table for data retention or for index maintenance. You can implement filegroup backups and in the case of very large history tables this becomes a very important consideration.

The following code shows you how to place the CustomerHistory table on a filegroup called FG_History in the database TestTemporal.

USE master
GO
DROP DATABASE IF EXISTS TestTemporal;
GO
 
-- Creating an additional filegroup name History in TestTemporal database
CREATE DATABASE [TestTemporal]
ON  
PRIMARY       ( NAME = N'TestTemporal', FILENAME = N'C:\Data\TestTemporal.mdf'), 
 FILEGROUP [FG_History] ( NAME = N'History', FILENAME = N'C:\Data\History.ndf')
 LOG ON       ( NAME = N'TestTemporal_log', FILENAME = N'C:\Log\TestTemporal_log.ldf')
GO
 
-- Creating History table on FG_History filegroup with data compression.
USE TestTemporal
GO
CREATE TABLE dbo.[CustomerHistory](
   [CustomerId] [INT] NOT NULL,
   [FirstName] [VARCHAR](30) NOT NULL,
   [LastName] [VARCHAR](30) NOT NULL,
   [Amount_purchased] [DECIMAL](18, 0) NOT NULL,
   [StartDate] [DATETIME2](7) NOT NULL,
   [EndDate] [DATETIME2](7) NOT NULL
) 
ON [FG_History] WITH (DATA_COMPRESSION = PAGE)
GO
 
-- Create Customer table in default database schema and make it temporal with dbo.CustomerHistory table.
CREATE TABLE [dbo].[Customer](
   [CustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   [FirstName] [varchar](30) NOT NULL,
   [LastName] [varchar](30) NOT NULL,
   [Amount_purchased] [decimal](18, 0) NOT NULL,
   [StartDate] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
   [EndDate] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
   PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate])
   )WITH     (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory)   ) 
GO

In the screenshot below we see the properties of the CustomerHistory table. This table is page compressed and is on a non-default filegroup FG_History. The filegroup containing the history table can be placed on more redundant hardware with extra security than the filegroup containing the temporal tables.

storage

Summary

In this tip we covered various ways in which temporal history tables can be secured from unwanted data access. This tip also showed the benefits of using a temporal table and it’s built in security. We also saw how temporal tables can provide for granular security and an easy mechanism of implementing more enhanced and custom security methods.

Next Steps
  • Read Part 1 of this series here.
  • Learn more about Filegroups here.
  • Review this Temporal Table security article from Microsoft.
  • Learn more about other data security technologies here.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ameena Lalani Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-05-29

Comments For This Article

















get free sql tips
agree to terms