Temporal Table Security
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
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 settings from the current table are not automatically applied to the history table. Auditing needs to be enabled explicitly for history table. Once enabled, 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
Feature | 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
Feature | 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.
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
Feedback
Submit and view feedback for