Skip to content

Revoke Model Database Access to Non-dbo Users

Description

From the Microsoft SQL Server documentation:

The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.

For more details, see model Database

Rationale

If an attacker could modify the model database, these changes would take effect not only on new databases, but also the tempdb. A change in the configuration of the tempdb could be used to undermine the security of any other databases on the system.

The only user granted access to the model database should be the dbo user.

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 Model database is only accessible by 'dbo'
 */
function validate(databaseSettings) {
    const success =
            isEmptyArray(databaseSettings.users) ||
            isEmptyArray(databaseSettings.users
                .filter(user => 
                    user.sqlserver &&
                    user.sqlserver.parentDatabase &&
                    user.sqlserver.name !== 'dbo' && 
                    !user.sqlserver.isDisabled &&
                    user.sqlserver.parentDatabase.toLowerCase() === 'model' &&
                    user.sqlserver.source === 'sys.database_principals'))

    return {
        success,
    }
}

validate(databaseSettings)