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 prohibit user installation of logic modules (stored procedures, functions, triggers, views, etc.) without explicit privileged status.

DISA Rule

SV-213923r508025_rule

Vulnerability Number

V-213923

Group Title

SRG-APP-000378-DB-000365

Rule Version

SQL6-D0-003000

Severity

CAT II

CCI(s)

Weight

10

Fix Recommendation

Document and obtain approval for any non-administrative users who require the ability to create, alter, or replace logic modules.

Revoke the ALTER permission from unauthorized users and roles:
REVOKE ALTER ON [<Object Name>] TO [<Principal Name>]

Check Contents

If the SQL Server instance supports only software development, experimentation and/or developer-level testing (that is, excluding production systems, integration testing, stress testing, and user acceptance testing), this is not a finding.

Obtain a listing of users and roles who are authorized to create, alter, or replace logic modules from the server documentation.

In each user database, execute the following query:

SELECT P.type_desc AS principal_type, P.name AS principal_name,
O.type_desc,
CASE class
WHEN 0 THEN DB_NAME()
WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id)
WHEN 3 THEN SCHEMA_NAME(major_id)
ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')'
END AS securable_name, DP.state_desc, DP.permission_name
FROM sys.database_permissions DP
JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id
LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U')
WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53)

SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name
FROM sys.database_principals R
JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id
JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id
WHERE R.name IN ('db ddladmin','db_owner')
AND M.name != 'dbo'

If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding.

If any user or role membership is not authorized, this is a finding.

Vulnerability Number

V-213923

Documentable

False

Rule Version

SQL6-D0-003000

Severity Override Guidance

If the SQL Server instance supports only software development, experimentation and/or developer-level testing (that is, excluding production systems, integration testing, stress testing, and user acceptance testing), this is not a finding.

Obtain a listing of users and roles who are authorized to create, alter, or replace logic modules from the server documentation.

In each user database, execute the following query:

SELECT P.type_desc AS principal_type, P.name AS principal_name,
O.type_desc,
CASE class
WHEN 0 THEN DB_NAME()
WHEN 1 THEN OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id)
WHEN 3 THEN SCHEMA_NAME(major_id)
ELSE class_desc + '(' + CAST(major_id AS nvarchar) + ')'
END AS securable_name, DP.state_desc, DP.permission_name
FROM sys.database_permissions DP
JOIN sys.database_principals P ON DP.grantee_principal_id = P.principal_id
LEFT OUTER JOIN sys.all_objects O ON O.object_id = DP.major_id AND O.type IN ('TR','TA','P','X','RF','PC','IF','FN','TF','U')
WHERE DP.type IN ('AL','ALTG') AND DP.class IN (0, 1, 53)

SELECT R.name AS role_name, M.type_desc AS principal_type, M.name AS principal_name
FROM sys.database_principals R
JOIN sys.database_role_members DRM ON R.principal_id = DRM.role_principal_id
JOIN sys.database_principals M ON DRM.member_principal_id = M.principal_id
WHERE R.name IN ('db ddladmin','db_owner')
AND M.name != 'dbo'

If any users or role permissions returned are not authorized to modify the specified object or type, this is a finding.

If any user or role membership is not authorized, this is a finding.

Check Content Reference

M

Target Key

3992

Comments