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 Developers have no rights on production servers
- All objects are owned by "dbo"
- Everything is logged (see Log Your Changes)
- Everything is scripted
- Everything is under version control
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.
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 email.