Database Design SQL Server Security SOP



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, |Replaced Enterprise Server Farm (ESF) with Enterprise |

| | | |1.2 |Data Center (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

1 Introduction 4

1.1 PURPOSE / BRIEF OVERVIEW 4

1.2 ASSUMPTIONS 4

2 SQL SERVER PERMISSIONS 5

2.1 APPLICATION PERMISSIONS 5

2.2 USER PERMISSIONS 5

2.3 DATABASE ROLES 5

3 SQL SERVER SECURITY CHECKLIST 6

INTRODUCTION

1 Purpose / Brief Overview

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

2 Assumptions

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

SQL Server Permissions

1 Application 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 User 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 |

3 Database 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 |

SQL 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?

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download