Commonwealth of Pennsylvania pennsylvania state police

PA State Police (PSP)

Database

SQL Server Security SOP

Database Team

Version 1.4

Date: 05/22/2014

File Name: Database SQL Server Security SOP

SECURITY WARNING

The information contained herein is proprietary to the Commonwealth of Pennsylvania and must not be disclosed to un-authorized personnel. The recipient of this document, by its retention and use, agrees to protect the information contained herein. Readers are advised that this document may be subject to the terms of a non-disclosure agreement.

DO NOT DISCLOSE ANY OF THIS INFORMATION WITHOUT OBTAINING PERMISSION FROM THE MANAGEMENT RESPONSIBLE FOR THIS DOCUMENT.

Version History

Date / Version / Modified By / Approved By / Section(s) / Comment
03/08/2010 / 1.0 / S. Greer / All / Initial draft
11/24/2010 / 1.1 / S. Greer / 3 / Clarified database owner
09/30/2011 / 1.2 / S. Greer / Header, footer, 1.2 / Replaced Enterprise Server Farm (ESF) with EnterpriseDataCenter (EDC)
04/08/2014 / 1.3 / S. Greer / All / Change EDC to PSP
05/22/2014 / 1.4 / S. Greer / 2.2, 2.3 / Rewrote sections

Table of Contents

1Introduction

1.1Purpose / Brief Overview

1.2Assumptions

2SQL Server Permissions

2.1Application Permissions

2.2User Permissions

2.3Database Roles

3SQL Server Security Checklist

1Introduction

1.1Purpose / Brief Overview

This document defines the guidelines for application permission, user and Role permissions for SQL Server databases.

1.2Assumptions

This document includes specific information about the SQL Serverday-to-day operations performed by the Database team. A working knowledge of SQL Server and databasemanagement is beneficial.

2SQL Server Permissions

2.1Application Permissions

All application ID’s will be given the necessary permission to the Databases, stored procedures, functions and views they need to access. The very most this will ever be is Read, write and execute. They should be given the least amount of permissions as needed. An example of this is if an application needs access to a database for only retrieving data on a specific table then only select permissions to that table would be given.

2.2User Permissions

The user ID’s should be given access to only the databases they need to access. In the development and test environments they will be given read and write permissions. In production it will only be read access. Other than Development the user IDs’ should never be given execute permissions. In development only, the users that will be creating stored procedures, functions and views in a given database will be added to the Role created for that purpose for that database. An example of this is the GAMINGdbo role in the GAMING database.

User Permissions / Production / Staging / Test / Development
Read / Yes / Yes / Yes / Yes
Write / No / No / Yes / Yes
Execute / No / No / No / Yes
Database Role / No / No / No / Yes

2.3Database Roles

The database role(ie. GAMINGdbo) created for the database will have execute permissions on the stored procedures that it owns by default. It should never be given permission to dbo stored procedures. In the development environment only, it will have create permissions on stored procedures, functions and views. In development only the user ID that will be creating stored procedures, functions and views will be added to the role. The application ID should never be added to this role. In all environments except development there should be no one in this role at all.

Role Permissions / Production / Staging / Test / Development
Execute / Yes / Yes / Yes / Yes
Create / No / No / No / Stored procedures
Functions
Views
User ID / No / No / No / Yes
Application ID / No / No / No / No

3SQL Server Security Checklist

SQL Server Security Standards checklist for the DBA

No one should be using the SA account.

No one should have SA permissions.

Change the SA account password from the default.

Only SA should be database owner.

Built in Admin should be removed.

SQL should be running using a Domain account.

All users should have least permissions as possible (reference –Section 2).

Make use of database roles (reference – Section 2).

All new objects should be owned by the role. Views, stored procs and functions.

Developers should log on using their User accounts

Northwind and pubs databases should be removed

Make sure all accounts are being used.

All applications should have a unique SQL id and password created for it

NT AUTHORITY\SYSTEM should have public role only.

No one should be using CMD shell stored procedure.

Are the latest SQL Service Packs installed?

Database SQL Server Security SOPPage1 of 6