UCF STIG Viewer Logo

Object permission assignments should be authorized.


Finding ID Version Rule ID IA Controls Severity
V-2457 DM1715-SQLServer9 SV-23785r2_rule ECLP-1 Medium
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.
Microsoft SQL Server 2005 Database Security Technical Implementation Guide 2015-06-16


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.