Temporal Table Security

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

To understand security as it applies to temporal tables, it is important to understand the security principals that apply to temporal tables. After you understand these security principles, you are ready to dive into the security around the CREATE TABLE, ALTER TABLE, and SELECT statements.

Security Principles

The following table describes the security principles that apply to temporal tables:

Principle Description
Enabling/disabling system-versioning requires highest privileges on affected objects Enabling and disabling SYSTEM_VERSIONING requires CONTROL permission on both the current and the history table
History data cannot be modified directly When SYSTEM_VERSIONING is ON users cannot alter history data regardless of their actual permissions on current or the history table. This includes both data and schema modifications.
Querying history data requires SELECT permission on the history table Merely because a user has SELECT permission on the current table does not mean that they have SELECT permission on the history table.
Audit surfaces operations affecting history table in specific ways: Auditing on history table regularly captures all direct attempts to access the data (regardless if they were successful or not).

SELECT with temporal query extension shows that history table was affected with that operation.

CREATE/ALTER temporal table expose information that permission check happens on history table as well. Audit file will contain additional record for history table.

DML operations on current table surface that history table was affected but additional_info provides necessary context (DML was result of system_versioning).

Performing Schema Operations

When SYSTEM_VERSIONING is set to ON, schema modification operations are limited.

Disallowed ALTER schema operations

Operation Current Table History Table
DROP TABLE Disallowed Disallowed
ALTER TABLE…SWITCH PARTITION SWITCH IN only (see Partitioning with Temporal Tables) SWITCH OUT only (see Partitioning with Temporal Tables)
ALTER TABLE…DROP PERIOD Disallowed -
ALTER TABLE…ADD PERIOD - Disallowed

Allowed ALTER TABLE operations

Operation Current History
ALTER TABLE…REBUILD Allowed (independently) Allowed (independently)
CREATE INDEX Allowed (independently) Allowed (independently)
CREATE STATISTICS Allowed (independently) Allowed (independently)

Security of the CREATE Temporal TABLE Statement

Create New History Table Reuse Existing History Table
Permission Required CREATE TABLE permission in the database

ALTER permission on the schemas into which the current and history tables are being created
CREATE TABLE permission in the database

ALTER permission on the schema in which the current table will be created.

CONTROL permission on the history table specified as part of the CREATE TABLE statement creating the temporal table
Audit Audit shows that users attempted to create two objects. Operation may fail due to lack of permissions to create a table in the database or due to lack of permissions to alter schemas for either table. Audit shows that temporal table was created. Operation may fail due to lack of permission to create a table in the database, due to lack of permissions to alter the schema for the temporal table, or to lack of permissions on the history table.

Security of the ALTER Temporal TABLE SET (SYSTEM_VERSIONING ON/OFF) Statement

Create New History Table Reuse Existing History Table
Permission Required CONTROL permission in the database

CREATE TABLE permission in the database

ALTER permission on the schemas into which the history table is being created
CONTROL permission on the original table which is altered

CONTROL permission on the history table specified as part of the ALTER TABLE statement
Audit Audit shows that the temporal table was altered and the history table was created at the same time. Operation may fail due to lack of permissions to create a table in the database, due to lack of permissions to alter schema for history table, or due to lack of permission to modify temporal table. Audit shows that temporal table was altered, but operation required access to history table. Operation may fail due to lack of permissions on the history table or lack of permissions on the current table.

Security of SELECT Statement

SELECT permission is unchanged for SELECT statements that do not affect the history table. For SELECT statements that affect the history table, SELECT permission is required on both the current table and the history table.

Did this Article Help You? We’re Listening

What information are you looking for, and did you find it? We’re listening to your feedback to improve the content. Please submit your comments to sqlfeedback@microsoft.com

See Also

Temporal Tables
Getting Started with System-Versioned Temporal Tables
Temporal Table System Consistency Checks
Partitioning with Temporal Tables
Temporal Table Considerations and Limitations
Manage Retention of Historical Data in System-Versioned Temporal Tables
System-Versioned Temporal Tables with Memory-Optimized Tables
Temporal Table Metadata Views and Functions