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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- database design sql server security sop
- server guide home
- introduction to sql azure johns hopkins university
- sql views lecture
- getting the information you need from cdw sql starter
- washington state university
- introduction claros data management hach
- introduction to sql azure rice university
- accessing and manipulating sql server data
Related searches
- database design tutorial for beginners
- sql server create database syntax
- create database sql server script
- access database design best practices
- sql server sql syntax
- database design best practices microsoft
- access database design pdf
- access database design basics
- access database design examples
- access database design tutorial
- ms access database design tutorial
- how to take sql server database backup