SQL Server must restrict access to system tables, other configuration information, and metadata to DBAs and other authorized users.

From Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide

Part of SRG-APP-000062-DB-000016

Associated with: CCI-000366 CCI-002220

SV-53419r5_rule SQL Server must restrict access to system tables, other configuration information, and metadata to DBAs and other authorized users.

Vulnerability discussion

The principle of Least Privilege must be applied to the ability of users to access system tables, system management information, other configuration information, and metadata. Unauthorized access to this data could result in unauthorized changes to database objects, access controls, or SQL Server configuration. Only database administrators and other authorized users must be allowed such access.To aid in tracking and administering such permissions, individual logins must not be directly granted permissions or built-in server roles. Instead, user-defined server roles must be created, with the permissions and built-in server roles granted to them; the individual logins must be assigned to the appropriate user-defined server roles.The built-in server role "sysadmin" is a partial exception. This cannot be granted to a user-defined role, only to a login account. Most (not necessarily all) database administrators will need to be members of sysadmin. Without this, most DBCC commands and the system stored procedures/functions listed below are unavailable. The users who require such access must be documented and approved. In addition, if the site uses backup-restore software that connects to SQL Server via the Virtual Device Interface (VDI), the account used by that software must have the sysadmin role. (See Microsoft Knowledge Base article 2926557, http://support.microsoft.com/kb/2926557). If this applies, it must be documented and approved.Stored procedures/functions available only to the sysadmin role:fn_yukonsecuritymodelrequiredsp_add_agent_parametersp_add_agent_profilesp_adddatatypesp_adddistributiondbsp_adddistributorsp_addqreader_agentsp_addsubscribersp_addsubscriber_schedulesp_addtabletocontentssp_attachsubscriptionsp_cdc_cleanup_change_tablesp_cdc_disable_dbsp_cdc_disable_tablesp_cdc_drop_jobsp_cdc_enable_dbsp_cdc_enable_tablesp_cdc_restoredbsp_cdc_vupgradesp_certify_removablesp_change_agent_parametersp_change_agent_profilesp_change_subscription_propertiessp_change_users_loginsp_changedistpublishersp_changedistributiondbsp_changedistributor_passwordsp_changedistributor_propertysp_changemergesubscriptionsp_changeqreader_agentsp_changereplicationserverpasswordssp_changesubscriptiondtsinfosp_checkinvalidivarticlesp_copysubscriptionsp_create_removablesp_cycle_errorlogsp_dbcmptlevelsp_dbmmonitoraddmonitoringsp_dbmmonitorchangealertsp_dbmmonitordropalertsp_dbmmonitordropmonitoringsp_dbmmonitorhelpalertsp_dbmmonitorhelpmonitoringsp_dbmmonitorresultssp_dbmmonitorupdatesp_dbremovesp_drop_agent_parametersp_drop_agent_profilesp_dropdatatypemappingsp_dropdistpublishersp_dropdistributiondbsp_dropdistributorsp_dropmergepullsubscriptionsp_droppullsubscriptionsp_dropsubscribersp_dsninfosp_enumdsnsp_flush_commit_table_on_demandsp_generate_agent_parametersp_get_distributorsp_get_Oracle_publisher_metadatasp_getagentparameterlistsp_getdefaultdatatypemappingsp_grant_publication_accesssp_help_agent_defaultsp_help_agent_parametersp_help_agent_profilesp_helpdistpublishersp_helpdistributorsp_helpmergesubscriptionsp_helpqreader_agentsp_helpreplicationdboptionsp_identitycolumnforreplicationsp_IHValidateRowFiltersp_IHXactSetJobsp_link_publicationsp_monitorsp_MSadd_distribution_agentsp_MSadd_logreader_agentsp_MSadd_merge_agentsp_MSadd_snapshot_agentsp_MSadd_subscriber_schedulesp_MSadd_tracer_historysp_MSadd_tracer_tokensp_MScdc_cleanup_jobsp_MScdc_db_ddl_eventsp_MScdc_ddl_eventsp_MSchange_distribution_agent_propertiessp_MSchange_logreader_agent_propertiessp_MSchange_merge_agent_propertiessp_MSchange_snapshot_agent_propertiessp_MSchangedynamicsnapshotjobatdistributorsp_MSchangedynsnaplocationatdistributorsp_MScheck_pull_accesssp_MScleanupmergepublisher_internalsp_MSclear_dynamic_snapshot_locationsp_MScreate_dist_tablessp_MSdbuserprivsp_MSdeletefoldercontentssp_MSdrop_6x_replication_agentsp_MSdrop_merge_agentsp_MSdrop_snapshot_dirssp_MSdropmergedynamicsnapshotjobsp_MSdynamicsnapshotjobexistsatdistributorsp_MSenumallpublicationssp_MSfetchAdjustidentityrangesp_MSfix_6x_taskssp_MSforce_drop_distribution_jobssp_MSget_agent_namessp_MSget_jobstatesp_MSget_oledbinfosp_MSget_publication_from_tasknamesp_MSgetdbversionsp_MSgetmaxsnapshottimestampsp_MShelp_repl_agentsp_MShelp_replication_statussp_MShelp_snapshot_agentsp_MShelpconflictpublicationssp_MShelpdynamicsnapshotjobatdistributorsp_MShelplogreader_agentsp_MShelpsnapshot_agentsp_MShelptranconflictcountssp_MSinit_publication_accesssp_MSreinit_failed_subscriptionssp_MSremoveoffloadparametersp_MSrepl_backup_completesp_MSrepl_backup_startsp_MSrepl_createdatatypemappingssp_MSrepl_dropdatatypemappingssp_MSrepl_enumarticlecolumninfosp_MSrepl_enumpublicationssp_MSrepl_enumpublishertablessp_MSrepl_enumsubscriptionssp_MSrepl_enumtablecolumninfosp_MSrepl_getdistributorinfosp_MSrepl_startup_internalsp_MSreplagentjobexistssp_MSreplcheck_permissionsp_MSreplcheck_pullsp_MSreplcheck_subscribesp_MSreplcheck_subscribe_withddladminsp_MSreplcopyscriptfilesp_MSreplremoveuncdirsp_MSsetalertinfosp_MSSetServerPropertiessp_MSsetupnosyncsubwithlsnatdistsp_MSsetupnosyncsubwithlsnatdist_cleanupsp_MSsetupnosyncsubwithlsnatdist_helpersp_MSstartdistribution_agentsp_MSstartmerge_agentsp_MSstartsnapshot_agentsp_MSstopdistribution_agentsp_MSstopmerge_agentsp_MSstopsnapshot_agentsp_MSupdate_agenttype_defaultsp_oledbinfosp_procoptionsp_removedbreplicationsp_removesrvreplicationsp_replication_agent_checkupsp_replicationdboptionsp_resetstatussp_restoredbreplicationsp_SetAutoSAPasswordAndDisablesp_setdefaultdatatypemappingsp_updatestatssp_validateloginssp_vupgrade_mergeobjectssp_vupgrade_replicationsp_vupgrade_replsecurity_metadataxp_repl_convert_encrypt_sysadmin_wrapper

Check content

Use SQL Server and system documentation to determine privilege assignment of user-defined roles. Determine which user-defined roles grant privileges to system tables and configuration data stored in SQL Server. For each Login: In SQL Server Management Studio, Object Explorer, expand >> Security >> Logins >> Right-click >> Properties >> User >> Securables. If any item in the Explicit Permissions listing, for each highlighted item that exists in the Securables listing, indicates direct permission access, and that permission is anything other than Connect SQL, this is a finding. Navigate from Securables to Server Roles. If any Server Roles are checked from the following list, indicating direct permission access, this is a finding: bulkadmin dbcreator diskadmin processadmin securityadmin serveradmin setupadmin If the sysadmin server role is checked, review system documentation to determine whether this login's need for the sysadmin role is documented and approved. If it is not, this is a finding. If any user-defined server roles with system table or configuration data privileges are checked, review system documentation to determine whether this login's need for the role is documented and approved. If it is not, this is a finding. Navigate from Server Roles to User Mapping. Select in turn each entry where the User column is non-blank. If any Database Roles are checked from the following list, indicating direct permission access, this is a finding: db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin

Fix text

If necessary memberships in the sysadmin role are not documented or not approved, document them and obtain approval. If unnecessary memberships in the sysadmin role are documented, remove them from the documentation. Remove all direct access permissions and unauthorized permissions as required using the below instructions: In SQL Server Management Studio, Object Explorer, expand >> Security >> Logins >> Right-click >> Properties >> User >> Securables. Remove Securables permissions from user account. Navigate from Securables to Server Roles. Remove Server Roles permissions from user account. Navigate from Server Roles to Users Mapping. Remove direct permissions on db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, and db_securityadmin from user account.

Pro Tips

Powered by sagemincer