Skip to content

Ensure only the default permissions specified by Microsoft are granted to the public server role

Description

publicis a special fixed server role containing all logins. Unlike other fixed server roles, permissions can be changed for the public role. In keeping with the principle of least privileges, the public server role should not be used to grant permissions at the server scope as these would be inherited by all users.

Rationale

Every SQL Server login belongs to the public role and cannot be removed from this role. Therefore, any permissions granted to this role will be available to all logins unless they have been explicitly denied to specific logins or user-defined server roles.

Applies To

  • Databases

Tags

This rule is applied when the following tags are present:

Tag With Value
secureclouddb/provider aws
secureclouddb/service rds
secureclouddb/engine sqlserver

Default Rule

const { isEmptyArray } = module
/**
 * @param {Object} databaseSettings - database settings object
 * @returns {boolean} true if only the default permissions specified by Microsoft are granted to the public server role
 */
function validate(databaseSettings) {
    const success = isEmptyArray(databaseSettings.users) ||
        isEmptyArray(
            databaseSettings.users.filter(user =>
                user.sqlserver &&
                user.sqlserver.name === 'public' &&
                user.sqlserver.source === 'sys.server_principals' &&
                !isEmptyArray(
                    user.sqlserver.permissions.filter(permission =>
                        permission.stateDesc.startsWith("GRANT") &&
                        !(permission.stateDesc === 'GRANT' && permission.permissionName === 'VIEW ANY DATABASE' && permission.classDesc === 'SERVER') &&
                        !(permission.stateDesc === 'GRANT' && permission.permissionName === 'CONNECT' && permission.classDesc === 'ENDPOINT' && permission.majorId === 2) &&
                        !(permission.stateDesc === 'GRANT' && permission.permissionName === 'CONNECT' && permission.classDesc === 'ENDPOINT' && permission.majorId === 3) &&
                        !(permission.stateDesc === 'GRANT' && permission.permissionName === 'CONNECT' && permission.classDesc === 'ENDPOINT' && permission.majorId === 4) &&
                        !(permission.stateDesc === 'GRANT' && permission.permissionName === 'CONNECT' && permission.classDesc === 'ENDPOINT' && permission.majorId === 5)))))

    return {
        success,
    }
}

validate(databaseSettings)