Object permission assignments should be authorized.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-2457	DM1715-SQLServer9	SV-23785r2_rule	ECLP-1	Medium

Description
Securely designed applications require only that database application user accounts have permissions to access and manipulate only the application data assigned to them in accordance with the their job function. Restrictions may be further restricted by granting data access to users only through execution of database procedures. Excess privileges can lead to unauthorized data access and can compromise data integrity.

Description

Securely designed applications require only that database application user accounts have permissions to access and manipulate only the application data assigned to them in accordance with the their job function. Restrictions may be further restricted by granting data access to users only through execution of database procedures. Excess privileges can lead to unauthorized data access and can compromise data integrity.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-22833r2_chk )
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE name NOT IN ('tempdb', 'ReportServerTempDB') AND state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal]', o.name AS [Object], p.permission_name AS [Type] FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p WHERE o.object_id = p.major_id AND p.grantee_principal_id = u.principal_id AND p.state IN ('G', 'W') AND (p.type NOT IN ('DL', 'EX', 'IN', 'SL', 'UP') OR u.name IN ('public', 'guest')) ORDER BY u.name, o.name, p.permission_name Review results listed. If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding. Note: Some permissions assigned to PUBLIC within the master database may require the 'Allow modifications to be made directly to the system catalogs' database setting be temporarily enabled.

Check Text ( C-22833r2_chk )

From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE name NOT IN ('tempdb', 'ReportServerTempDB')
AND state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT u.name AS [Principal]', o.name AS [Object], p.permission_name AS [Type]
FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.state IN ('G', 'W')
AND (p.type NOT IN ('DL', 'EX', 'IN', 'SL', 'UP')
OR u.name IN ('public', 'guest'))
ORDER BY u.name, o.name, p.permission_name

Review results listed.

If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.

Note: Some permissions assigned to PUBLIC within the master database may require the 'Allow modifications to be made directly to the system catalogs' database setting be
temporarily enabled.

Fix Text (F-20259r1_fix)
Revoke unauthorized permissions assigned to application user roles where supported by the DBMS vendor. From the query prompt: USE [database name] REVOKE [permission] ON [object] FROM [group name] Document assigned role permissions in the System Security Plan and authorize with the IAO.