Object permissions should not be assigned to PUBLIC or GUEST.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15172	DM6196-SQLServer9	SV-23871r2_rule	ECLP-1	Medium

Description
The guest account is available to users that do not have authorized accounts on the database. The PUBLIC role is granted to all users of the database regardless of assigned job function. Assignment of object privileges to unauthorized users can compromise data integrity and/or confidentiality.

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

Details

Check Text ( C-22798r2_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], o.name AS [Object], 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.all_objects o ON p.major_id = o.object_id WHERE p.state_desc <> 'DENY' AND u.name IN ('guest', 'public') ORDER BY u.name, o.name, p.permission_name If any results listed are unauthorized, this is a Finding. 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 be enabled.

Check Text ( C-22798r2_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], o.name AS [Object], 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.all_objects o ON p.major_id = o.object_id
WHERE p.state_desc <> 'DENY'
AND u.name IN ('guest', 'public')
ORDER BY u.name, o.name, p.permission_name

If any results listed are unauthorized, this is a Finding.

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 be enabled.

Fix Text (F-19722r1_fix)
Revoke any unauthorized object privileges assigned to PUBLIC or GUEST where supported by the DBMS vendor. Document all remaining object privileges assigned to PUBLIC in the System Security Plan and authorize with the IAO. From the query prompt: SELECT name FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [database name] REVOKE [privilege] ON [object name] FROM '[public or guest]' Repeat for each object privilege assigned to public or guest: From the query prompt: USE [database name] REVOKE [permission] ON [schema name].[object name] TO PUBLIC To determine correct schema name for the object, use: SELECT SCHEMA_NAME(schema_id) FROM [master].sys.all_objects WHERE name = '[object name]'

Fix Text (F-19722r1_fix)

Revoke any unauthorized object privileges assigned to PUBLIC or GUEST where supported by the DBMS vendor.

Document all remaining object privileges assigned to PUBLIC in the System Security Plan and authorize with the IAO.

From the query prompt:

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

Repeat for each database:

From the query prompt:

USE [database name]
REVOKE [privilege] ON [object name] FROM '[public or guest]'

Repeat for each object privilege assigned to public or guest:

From the query prompt:

USE [database name]
REVOKE [permission] ON [schema name].[object name] TO PUBLIC

To determine correct schema name for the object, use:

SELECT SCHEMA_NAME(schema_id)
FROM [master].sys.all_objects
WHERE name = '[object name]'