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

SQL Server must enforce discretionary access control policies, as defined by the data owner, over defined subjects and objects.

DISA Rule

SV-213921r508025_rule

Vulnerability Number

V-213921

Group Title

SRG-APP-000328-DB-000301

Rule Version

SQL6-D0-002800

Severity

CAT III

CCI(s)

Weight

10

Fix Recommendation

To correct object ownership:

ALTER AUTHORIZATION ON <Securable> TO <Principal>

To revoke any unauthorized permissions:

REVOKE [Permission] ON <Securable> TO <Principal>

Check Contents

Review system documentation to determine requirements for object ownership and authorization delegation.

Use the following query to discover database object ownership:

Schemas not owned by the schema or dbo:

SELECT name AS schema_name, USER_NAME(principal_id) AS schema_owner
FROM sys.schemas
WHERE schema_id != principal_id
AND principal_id != 1

Objects owned by an individual principal:

SELECT object_id, name AS securable,
USER_NAME(principal_id) AS object_owner,
type_desc
FROM sys.objects
WHERE is_ms_shipped = 0 AND principal_id IS NOT NULL
ORDER BY type_desc, securable, object_owner

Use the following query to discover database users who have been delegated the right to assign additional permissions:

SELECT U.type_desc, U.name AS grantee,
DP.class_desc AS securable_type,
CASE DP.class
WHEN 0 THEN DB_NAME()
WHEN 1 THEN OBJECT_NAME(DP.major_id)
WHEN 3 THEN SCHEMA_NAME(DP.major_id)
ELSE CAST(DP.major_id AS nvarchar)
END AS securable,
permission_name, state_desc
FROM sys.database_permissions DP
JOIN sys.database_principals U ON DP.grantee_principal_id = U.principal_id
WHERE DP.state = 'W'
ORDER BY grantee, securable_type, securable

If any of these rights are not documented and authorized, this is a finding.

Vulnerability Number

V-213921

Documentable

False

Rule Version

SQL6-D0-002800

Severity Override Guidance

Review system documentation to determine requirements for object ownership and authorization delegation.

Use the following query to discover database object ownership:

Schemas not owned by the schema or dbo:

SELECT name AS schema_name, USER_NAME(principal_id) AS schema_owner
FROM sys.schemas
WHERE schema_id != principal_id
AND principal_id != 1

Objects owned by an individual principal:

SELECT object_id, name AS securable,
USER_NAME(principal_id) AS object_owner,
type_desc
FROM sys.objects
WHERE is_ms_shipped = 0 AND principal_id IS NOT NULL
ORDER BY type_desc, securable, object_owner

Use the following query to discover database users who have been delegated the right to assign additional permissions:

SELECT U.type_desc, U.name AS grantee,
DP.class_desc AS securable_type,
CASE DP.class
WHEN 0 THEN DB_NAME()
WHEN 1 THEN OBJECT_NAME(DP.major_id)
WHEN 3 THEN SCHEMA_NAME(DP.major_id)
ELSE CAST(DP.major_id AS nvarchar)
END AS securable,
permission_name, state_desc
FROM sys.database_permissions DP
JOIN sys.database_principals U ON DP.grantee_principal_id = U.principal_id
WHERE DP.state = 'W'
ORDER BY grantee, securable_type, securable

If any of these rights are not documented and authorized, this is a finding.

Check Content Reference

M

Target Key

3992

Comments