STIGQter: STIG Summary: MS SQL Server 2016 Database Security Technical Implementation Guide Version: 2 Release: 1 Benchmark Date: 23 Oct 2020:

SQL Server must protect against a user falsely repudiating by use of system-versioned tables (Temporal Tables).

DISA Rule

SV-213903r508025_rule

Vulnerability Number

V-213903

Group Title

SRG-APP-000080-DB-000063

Rule Version

SQL6-D0-000500

Severity

CAT III

CCI(s)

• CCI-000166 - The information system protects against an individual (or process acting on behalf of an individual) falsely denying having performed organization-defined actions to be covered by non-repudiation.

Weight

10

Fix Recommendation

Alter sensitive tables to utilize system versioning.

Alter non-temporal table to define periods for system versioning .

ALTER TABLE InsurancePolicy
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(),
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999');

ALTER TABLE InsurancePolicy SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.InsurancePolicyHistory));

https://docs.microsoft.com/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2016#system_versionin

Check Contents

Check the server documentation to determine if collecting and keeping historical versions of a table is required.

If collecting and keeping historical versions of a table is NOT required, this is not a finding.

Find all of the temporal tables in the database using the following query:

SELECT SCHEMA_NAME(T.schema_id) AS schema_name, T.name AS table_name, T.temporal_type_desc, SCHEMA_NAME(H.schema_id) + '.' + H.name AS history_table
FROM sys.tables T
JOIN sys.tables H ON T.history_table_id = H.object_id
WHERE T.temporal_type != 0
ORDER BY schema_name, table_name

Using the system documentation, determine which tables are required to be temporal tables.

If any tables listed in the documentation are not in the list created by running the above statement, this is a finding.

Ensure a field exists documenting the login and/or user who last modified the record.

If this does not exist, this is a finding.

Vulnerability Number

V-213903

Documentable

False

Rule Version

SQL6-D0-000500

Severity Override Guidance

Check the server documentation to determine if collecting and keeping historical versions of a table is required.

If collecting and keeping historical versions of a table is NOT required, this is not a finding.

Find all of the temporal tables in the database using the following query:

SELECT SCHEMA_NAME(T.schema_id) AS schema_name, T.name AS table_name, T.temporal_type_desc, SCHEMA_NAME(H.schema_id) + '.' + H.name AS history_table
FROM sys.tables T
JOIN sys.tables H ON T.history_table_id = H.object_id
WHERE T.temporal_type != 0
ORDER BY schema_name, table_name

Using the system documentation, determine which tables are required to be temporal tables.

If any tables listed in the documentation are not in the list created by running the above statement, this is a finding.

Ensure a field exists documenting the login and/or user who last modified the record.

If this does not exist, this is a finding.

Check Content Reference

M

Target Key

3992

Comments