Commonwealth of Pennsylvania Pennsylvania state police
PA State Police (PSP)
Database
Contractor Rules of Engagement (ROE)
Database Team
Version 1.1
Date: 04/08/2014
File Name: Database Contractor ROE
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) / Comment10/25/2011 / 1.0 / Sherry Greer / All / Initial Version
04/08/2014 / 1.1 / S. Greer / All / Changed EDC to PSP
Table of Contents
1Introduction
1.1Purpose / Brief Overview
1.2Assumptions
2Development Environment
2.1Development Database Creation
2.2Development SQL Server Permissions
3Test Environment
3.1Test Database Creation
3.2Test Application Changes
3.3Test SQL Server Permissions
4Production Environment
4.1Production Database Creation
4.2Production Application Changes
4.3Production SQL Server Permissions
Introduction
Purpose / Brief Overview
This Rules of Engagement (ROE) document defines the rules of engaging the Database team and what the contractormay do on the database servers without engaging the Database team. A contractor is a company that was hired by the agency to develop a system which includes the database.
The database servers consist of development, testand production environments. The environment dictates the level of engaging the Database team. In order to ensure database standards and policies are being followed, the contractor will follow a structured development life cycle methology of moving the databases and database objects through the development, test and production environments.
Assumptions
This document includes specific information about SQL Server and the database servers utilized for Pennsylvania State Police applications located at the Data Power House and/or EnterpriseDataCenterthat are supported by the PSP Database team. A working knowledge of Development Life Cycle methology is beneficial.
Development Environment
Development Database Creation
Any new databases will require contacting the Database team to create the database. To initially engage the PSP Database team complete the Database Design Requirements QuestionnaireTemplate and supplya physical database model to be reviewed by a Database team member.
If the database was developed at a contractor site (ie. COTS product), the contractor will provide the SQL scripts to create the database or provide a SQL backup file. The Database team member will create the database.
Development SQL Server Permissions
Only the PSP Database team will have system administration permission on the development database servers. The contractor’s developer may be given db_owner permissions to the database to do database administrative functions to the database, if required. Otherwise, any database changes require engaging the Database team. The Database team will ensure that changes to the database conform to the Database standards.
All other permissions will follow the SQL Server Security SOP where the user will have read, write and execute permissions. In addition, the developer will be granted the SHOWPLAN permission, which will allow the developer to view the execution plan for stored procedures. A database role for the database will be setup to allow creating stored procedures, functions and views. All code development should be done in the development environment.
Test Environment
Test Database Creation
Any new database will be promoted from development to the test environment by the PSP Database team.
If the database was developed at a contractorsite (ie. COTS product), the contractor will provide the SQL scripts to create the database or provide a SQL backup file. The PSP Database team member will create the database.
Test Application Changes
Any scripts required for the DBA to run on the test database server should be put in either a descriptive folder on share \\server name\ DBSCRIPTS, Sourcesafe or Team Foundation as directed by the PSP Database team.
Database changes should be promoted from the development server to the test server by the PSP Database team. All code changes will be promoted by the PSP Database team from development.The PSP Database team will ensure that code changes conform to the PSP Database standards.
Test SQL Server Permissions
Only the PSP Database teamwill have system administration permission on the test database servers. The contractor developers will not be allowed to do database administrative functions.
Permissions will follow the SQL Server Security SOP where the developer will have read and write permissions. In addition, theview definitiondatabase permission will be granted. The developer will be removed from the database role that was setup on the development database server whichallowed creating stored procedures, functions and views.
During testing of the application the contractor should engage the PSP Database team to assist in diagnosingdatabase related problems. The contractor may also be granted the following to assist with troubleshooting.
- SQLAgentReaderRole database role allows the user to see all jobs and the history of the jobs
- usp_ReadLog execute permissions to view the logs. The most recent log is returned unless the user supplies a value to return a different log.
Production Environment
Production Database Creation
Any new database will be promoted from test to the production environment by the PSP Database team and requires an MSL document that documents the production database. Items that need documented include user accounts, permissions, database purpose, sizing requirements, and SQL jobs.
Production Application Changes
Any scripts required for the DBA to run on the production database server should be put in either a descriptive folder on share \\server name\ DBSCRIPTS, Sourcesafe or Team Foundation as directed by the PSP Database team. This includes any data fix scripts where data in a particular database needs changed outside of the application. . All code changes will be promoted by the PSP Database team from test.
Database changes should be promoted from the test server to the production server by the PSP Database team. Application builds that require database changes should include PSP Database as the implementer. Any scheduled database changes including data fixes and code changes should be coordinated with the PSP Database team
Production SQL Server Permissions
To insure a safe and secure SQL Server environments each user will be given the minimum rights and permissions to perform their functions. Only the PSP Database team will have system administration permission on the production database servers. The contractor developer will not be allowed to do database administrative functions.
Permissions will follow the SQL Server Security SOP where the developer will have read permission. In addition, the view definition database permission will be granted. The developer will be removed from the database role that was setup on the development database server which allowed creating stored procedures, functions and views..
For any database related problems the contractor should engage the PSP Database team to assist in diagnosing the problems. The contractor may also be granted the following to assist with troubleshooting.
- SQLAgentReaderRole database role allows the user to see all jobs and the history of the jobs
- usp_ReadLog execute permissions to view the logs. The most recent log is returned unless the user supplies a value to return a different log.
Database contractor Rules of Engagement (ROE)Page1 of 5