Securing the Development Environment

Its quite common for development teams to have fairly loose standardsor no standards at allfor management of the development environment. The development environment and servers should be managed professionally, although usually not to the same standards as the test and production systems. The data on the development servers is often sensitive, as its drawn from the production source systems. You certainly should secure the hardware and operating system as weve just described, within reason. You should have a policy against, or strict procedures for, granting access to development servers to anyone outside the development portion of the organization.

To ease deployment, make the development machines security environment similar to the production systems. On the other hand, you dont want to lock down the systems so tightly that the developers will have problems getting their work done.

A common approach is to manage shared development resources fairly well, yet allow developers to create private databases. Institute a change control process for the shared resources, like the relational data warehouse data model. Once other team members are depending on a data model, allow changes only weekly, and require 24 hours advance notice.

Once youve instituted change control on the shared resources, youll see private databases popping up. Thats because some team members, in a sensitive part of their development cycle, really need an unchanging database, or they need to change it more frequently. Changes to the shared database can, at times, be incredibly annoying. Because the SQL Server database software is easy to install on a desktop machine, its hard to prevent private databases from cropping up. If you cant, or dont want to, forbid private databases, make it easy for your team members to secure them. Develop a policy for private databases, including system security proceduresusually the same procedures that you implement for your shared development resources. Better yet, write a lockdown scriptprobably a combination of a document script and a batch fileto perform basic lockdown .

Developers should use read-only access to the source transaction systems. This is especially true for the DBAs and ETL developers, who may be creating and deleting database objects in the relational data warehouse database. Its not impossible to imagine they could be careless and inadvertently execute a destructive statement against a transaction system.

Note 

If youve ever accidentally created a table in the Master database, you know what were talking about.

Its safest to use only the minimum privileges necessary to get the job done. In the case of DW/BI system development, that should mean read-only access to the source databases. In a large corporation, this is unlikely to be an issue: Of course the DW/BI team will not have write privileges into a transaction system. In a small company where people wear many hats, its not uncommon for a DW/BI developer to have high privileges on a production system.