Title

DDL permissions should be granted only to authorized accounts. (Cat II impact)

Discussion

Data Definition Language (DDL) commands include CREATE, ALTER, and DROP object actions. These actions cause changes to the structure, definition and configuration of the DBMS as well as to the objects themselves that can affect any or all operations of the database. Such privileged actions, when not restricted to authorized persons and activities, can lead to a compromise of data and DBMS availability.

Check Content

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 LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8) LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3 WHERE p.state_desc <> 'DENY' AND p.state IN ('G', 'W') AND (p.type LIKE 'CR%' OR p.type LIKE 'AL%') ORDER BY u.name, o.name, p.permission_name Compare the results listed to those documented in the System Security Plan. If any accounts listed are: 1. Application users 2. Application user roles 3. Application administrator roles 4. PUBLIC role 5. GUEST user this is a Finding. If any application developer accounts are listed with DDL privileges to production databases, this is a Finding. If results are not documented in the System Security Plan or authorized by the IAO, this is a Finding.

Fix Text

Revoke DDL permissions from unauthorized accounts with the REVOKE command: From the query prompt: USE [database name] REVOKE [permission] FROM [user name] Document required DDL permission grants in the System Security Plan and authorize with the IAO.

Additional Identifiers

Rule ID: SV-23804r2_rule

Vulnerability ID: V-2463

Group Title:

Expert Comments

CCIs tied to check.

Controls tied to check. These are derived from the CCIs shown above.