Keeping Your Developers Under Control
Introduction
I love my children. They bring some of the greatest joy into my life as I watch
them learn, develop new skills, and break the rules my wife and I set for them. Yes,
breaking rules (one of kids favorite pasttimes) is often frustrating and annoying at the
time, but funny later. Much to their annoyance as well, we have to stop them and enforce
rules to be sure that they learn to respect them and some semblance of order is maintained
in our household.
Not that I dislike developers, or think they are like my kids, or even that I even think they are out of control.
It's just that anyone will wreck havoc on a stable database environment (or any other one for that matter) if
there are not rules they have to follow. And not only are the rules needed, but they need to be
enforced. There are a number of tools available within SQL Server to setup and enforce rules
which can help ensure a stable database environment. This article will discuss the security
setup that I use to ensure that I can maintain order in my company with regard to the
SQL Servers. The second part to this series will focus on techniques to enforce rules that
are setup in any environment.
What Are The Rules?
Over the years, working by myself and with all sizes of teams from two to thirty, I have
decided there are some rules that are needed to ensure that development can proceed efficiently
and effectively in a database environment. Here are a few that I follow to try and prevent problems
from occuring.
- "sa" and "dbo" rights are limited to DBAs and Change Control Personnel
- All objects are owned by "dbo"
- Everything is logged (see Log Your Changes)
- Everything is scripted
- Everything is under version control
Developers have no rights on production servers
These are simple and effective, though sometimes annoying rules. I will discuss each briefly, though
over time I will update and expand this article (or devote an article to each) to explain each of these
in more detail with reasoning, give justification, and techniques for enforcement.
Rights
Everyone wants rights. This country was founded because our forefathers wanted more rights
than they had. Well, in my SQL environment, it's a dictatorship. No developer has the need for
"sa" or "dbo" rights. There is nothing involved in development that requires these rights. Well,
let me qualify this slightly. Unless the developer is specifically working on administrative tools, then
they may require additional rights to test their code, but in all the development projects I have
worked on within a non-system database, they do not need "dbo" or "sa" rights.
Why not? Because this is the easiest way to maintain control over the changes that occur on a development
server. If developers can change db settings or server settings, there are two possible
side effects. One, the development server becomes different than the production server and
the changes may not be compatible with the production server. This is a waste of development
time and none of us needs delivery dates to slip more than they already do.
Two, the DBA needs to
be aware of changes that are made so they can be migrated to the production server easily with a
minimum of downtime. How many times has any of you moved code from a test to a live environment and
something did not work? Then "Oh yeah, I forgot about that (DLL, procedure, setting, etc.)?"
was uttered by the developer working on the change? It has happened to me more times than I
care to admit (even caused by me more times than I care to remember). It has happened much, much
less since I started to lock down rights on all SQL Servers, development and production.
A couple jobs ago, I walked in the first day and found that all developers (6), and the junior
DBA (1) had sa rights on three development, two QA, and two production SQL Servers. Development proceeded
on the development boxes and then each developer moved his or her changes to the QA box. When
something did not work as expected, the developer made the change on QA. Then the DBA moved the
changes to production and when bugs were found, the developer or DBA made the changes on the production
server. Care to guess how much downtime was experienced? More than management wanted, and it consistantly
occurred during every release of new software for days afterward. You can imagine why the
previous DBA left and how I got a job. Not a fun situation to start work in.
How did I correct this? First thing, change the "sa" password and keep it to
myself and the other DBA. On ALL servers. Next, create a "development" group (role) in
the development databases and move all developers into this group. They were granted
rights to "create procedures" and "create views" only. In some cases I have granted
"create table" rights, but rarely. I prefer to discuss schema changes with the developer
prior to implementation to ensure things are normalized, not duplicated, etc. This did
two things. One, it controls which changes can be made without the DBA's knowledger while
allowing developers to get work done. Two, it ensures the DBA is informed of changes prior to
implementation. Objects created by the developers cannot be executed by anyone other than the
owner, so they have to deliver these to the DBA for recompilation as "dbo" before they can be
tested (you do have someone else test the code, right?).
Production Server Rights
None. Simple and leave it at that. Developers should not be making any changes to
a production environment without testing. Since no one should deliver code that is not
tested by someone else, let the developer develope on a test server, give the code to a DBA
or someone responsible for managing change, and let that person make the change on
production.
Who Owns What?
On the production servers, keep it simple. Let all objects be owned by the dbo,
set appropriate permissions for groups (roles) and you will minimize the security
problems that occur. If different people own different objects, then migrations, maintenance,
and control quickly spiral out of control. Do yourself a favor and let dbo own everything.
Everything Is Logged
All changes have to be logged in the production environment as a minumum. See Log
Your Changes
Everything Is Scripted
This one deserves a long article to itself, but here are the highlights. The GUI is nice
and makes changes easy. However, it probably causes more DBAs to utter the dreaded
"Oh yeah, I forgot about that (DLL, procedure, setting, etc.)?" than anything else. We are
all human, and with your log (you DO have a log, right?) you can recreate these changes, but
at a minimum, you have to do everything twice (once to develop and once to migrate). It
takes only slightly longer to script things, and there are some features in the SQL 2000
tools to make this easier. If you have a script, then duplicating your work on the
production (or ten production) server(s) is easier.
Everything Is Under Version Control
Everyone has a version control system (VCS) (right?, please?). Do you let your kids play with
matches? Without a version control system, that's what developers are doing. Of course, with a
VCS and not using it, the same thing happens, but that's a different issue. Like the scripting rule,
this one deserves a longer treatment, but here are some quickies.
One, while rare you will "roll back" to a previous version, just like you "rarely" restore your
database from a backup, a version control system is just good sound development practice. It also
provides that same backup system that pressing "Save" in Word often does. It saves you when
your machine crashes (though in four months of Win2000 Professional, I have zero crashes).
In many of today's larger, distributed shops, this also provides a central point of code location
that ensures that different members of a team do not "step on" or overwrite another's hard work. This
is perhaps one of the easiest methods of keeping some sort of order in your development shop. If you do not
have a "VCS" system, you can always make a series of folders on a network drive and copy code there
every night, but this does not keep developers from overwriting someone else's changes. You need some
strong administrative policies if you choose this route.
Conclusion
I apologize for the short treatment of some of these topics, but, well, this thing is
long enough. I will spend some time on each of these areas with more examples and reasoning
in other articles and eventually this should be a good guideline for setting up an environment.
Perhaps even a good set of white papers you can use to justify yourself to management.
As always, I welcome feedback, comments, and suggestions. This is a topic that is often
asked about and discussed and if anyone has better ideas or disagrees, please let me know with an
