UCF STIG Viewer Logo

Permissions on system tables should be restricted to authorized accounts.


Overview

Finding ID Version Rule ID IA Controls Severity
V-2458 DM1749-SQLServer9 SV-23790r2_rule ECLP-1 Medium
Description
Microsoft SQL Server defaults to allow all users to view the majority of the system tables. The system tables contain information such as login IDs, permissions, objects and even the text of all stored procedures. In a secure environment, any direct access granted to these tables by users bypasses security controls defined within the associated system procedures and views. The bypass of these controls can lead to unauthorized viewing of sensitive data.
STIG Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide 2015-06-16

Details

Check Text ( C-22796r2_chk )
From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT u.name AS [Principal], t.name AS [Table], p.permission_name AS [Permission], p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
JOIN sys.tables t ON p.major_id = t.object_id
WHERE p.state_desc <> 'DENY'
AND t.is_ms_shipped = 1
ORDER BY u.name, t.name

Review the list of permissions assigned to system tables.

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

Note: By default, SELECT permission is granted to the PUBLIC role for system tables in all databases. Even though permission is set by default, it is still a Finding.
Fix Text (F-24604r1_fix)
Revoke permissions granted to system tables where supported by the DBMS vendor.

From the query prompt:

USE [database name]
REVOKE [permission] ON [object name] FROM [user name]

Document permission grants in the System Security Plan and authorize with the IAO.