Skip to content

Drop 'Orphaned Users'

Description

In a Microsoft SQL Server database, a user must have both a logon at system level, and a user which is mapped to that logon at database level. Users that are not mapped to a logon can leave stranded objects, and complicate an evaluation of database access.

Rationale

A user which exists at database level, but does not have a system logon could be enabled by an attacker, and used to disguise their activities. Removing users which are no longer relevant also makes auditing permissions more accurate.

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 there are no orphaned users 
 */
function validate(databaseSettings) {
    if(!databaseSettings.users) databaseSettings.users = []
    const dbUsers = databaseSettings.users.filter(user => 
        user.sqlserver &&
        user.sqlserver.authenticationTypeDesc === 'INSTANCE' &&
        user.sqlserver.source === 'sys.database_principals')
    const serverUsers = databaseSettings.users.filter(user => 
        user.sqlserver &&
        user.sqlserver.source === 'sys.server_principals')
    const orphanedUsers = dbUsers.filter(dbUser => 
        !serverUsers.find(serverUser => serverUser.sqlserver.sid === dbUser.sqlserver.sid))

    const success = isEmptyArray(orphanedUsers)
    return {
        success,
    }
}

validate(databaseSettings)