Take the 2-minute tour ×

I've been working a job for 2 years now as a SQL Server DBA for a city.

I was brought in to support an existing DBA, so I've slowly taken everything in and have been working for a while, trying to gain credibility in the organization. It's time to tackle the toughest and most important issue: security.

Our main server is a failover cluster with sql 2005 on it. It holds 166 databases. It serves 550 or so connections with app pooling for most applications, so it easily supports thousands of users. We also serve a public website that gets a fair amount of traffic. Vendor apps, in-house apps, access back-ends - quite a variety with a few critical applications.

Many developers have production access. Changes are being made outside the RFC process and we have no idea what data is being changed by developers. They also enjoy having production access during implementations of systems - which I'd also like to stop.

With so many applications running, any sane DBA would lock the server down.

How would you transition a team of developers from wide open security to a more restrictive environment?

I'm working on an emergency request system which will give them access to their database(s) for a limited time. This would give us auditing of access and ddl changes. Hopefully this will not be used - as it means we did not respond to a page. It will use HTTP, encrypted webconfig, login/pwd on screen(not email), request throttling to 3 per 12 hours. We would also be paged when a login is issued.

Any warnings about this type of system?

The major risk that I can see is that if a user's ntlogin were compromised - so would be their databases. We have that vulnerability right now since they always have access, but would never know if it happened.

edits: Development and staging are robust systems with 8 cores and 8GB memory. Planning on implementing a prd to stg copy mechanism that developers can operate if their db isn't massive and doesn't contain sensitive infos.

Management is supportive. If we can guarantee them access during emergencies, their biggest worry is taken care of. The other problem might be having access to production during implementations - which I'd rather not give. We can practice the move to staging first and test and fixes during the go-live there - with DBA's migrating to prd.

share|improve this question
My first question is - Do you have the blessing from the those who have the power? As you probably know, once people are used to something, to take it away is very difficult, you're now trying to tip the scale back into a sane position from comfort/convenience to security. If developers get upset (which they most certainly will), and complain, will your manager, and his manager all back you up? If not, my first attempt would be to show my manager and his managers exactly how bad-a-situation we're actually in, and make them aware of all the invisible but very real risks that exist. –  Xerxes Sep 22 '09 at 22:57
If you're going to implement your emergency system then look at adding a second authentication type so that you're not exposed to a loss of credentials alone. Building in two factor authentication in to your access portal should not be difficult and any expense is easily justified. –  Chris W Sep 23 '09 at 21:24

7 Answers 7

up vote 1 down vote accepted

I've been working with the same thing in my company. I actually found that most of the people were more receptive to this than I thought, but a few things have been key:

  • Remind them that the primary purpose of this is to prevent accidents. Accidents are something that everyone can relate to. Almost no one thinks they will ever be hacked, and talking about internal hackers puts them on the defensive immediately ("don't you trust me?").
  • Quickly respond to requests when they do run into a block.
  • Be willing to work with them. We found that some of the things I initially locked down were needed for them to do their jobs and wouldn't cause problems if they were opened back up a little.
  • Be consistent. You have to apply these policies universally. When possible, apply them to yourself, also.

I've also been trying to do this in smaller increments to make it less disruptive. For example:

  • All new databases now have restrictive permissions from the start. You don't miss what you've never had.
  • Start by removing sysadmin and other server-level rights, if they currently have them. Most people will agree that they don't need to reconfigure the server, and it removes a lot of security holes. Once they have seen that they can live with that, it sets the stage for more incremental tightening.
  • Tighten down one application's databases at a time. This makes it more manageable for you, plus that enables you to learn over time what rights they need so future tightening goes more smoothly.

Good luck!

share|improve this answer
Thanks Ed - great advice. –  a_dba Sep 23 '09 at 18:52
They were very receptive to your first point when we spoke a few weeks ago - as a developer, I would think it would be a bit of a relief to know that there was no way I could accidentally harm production data. We've had two such accidents with some critical systems this year, so that has prompted it since I'm the person cleaning up the mess. –  a_dba Sep 23 '09 at 18:59

That is a serious undertaking. These people are probably very very set in their ways and it's going to take a lot of support from their superiors to get that changed. Do they already have a test environment? I'm assuming they do, but if they don't that should be step one. Also, make sure that it is up to snuff. If it's old and slow they will cringe at being forced to use it. You need to be prepared to throw them a bone in other areas such as test in order to ease them out of their ways diplomatically.

share|improve this answer

Meet with the lead developers and get their reasons for having production access. You probably know most of them already. Then remove/address their reasons. I'm one of a team of developers that has full production access to servers, and I know I shouldn't have it. I've seen three of my colleagues so far cause significant disruption due to rushed SQL or complacency. "we need to implement changes immediately" - "write a script, I'll review it and run it if it's ok." "We need live data" - "Seriously? Or will last night's backup do?" "I need to test this, see if it works" - "Say hello to your new virtual test server."

We've had three accidents so far, and i'm sure we'll have more as our security lockdown is still some way off.

share|improve this answer
We've done the meeting with the major developers - there was no riot. –  a_dba Sep 23 '09 at 14:34

Start by putting a DDL trigger in place to pick up any schema changes that take place. Just log every DDL command that goes through, looking at who and when.

You may find that things aren't quite as bad as it seems. Work out who is pushing most changes out, and get them on board. After that, you should be able to tie things down better.

Also - put a trace on to pick up what's being run from applications which aren't your proper apps. Look at the ApplicationName and/or HostName fields in the trace. This will help you get a field for ad-hoc queries. Then you should be able to find out who jumps in to hack data often.

I'd always start with monitoring this type of situation first. Lock it down soon, but start monitoring it now, to get a picture of how big the problem is.

share|improve this answer
+1 for a pragmatic approach –  Nick Kavadias Sep 23 '09 at 1:17
We have the DDL in place. Concerning the trace - how do you filter this down to a reasonable amount of data that doesn't burden the server? We cannot trace on all activity. Application = SQL Management Studio? –  a_dba Sep 23 '09 at 15:29
When you create the trace (use Profiler, and then generate a script for it), put a Column filter in. –  Rob Farley Sep 25 '09 at 2:47

Your biggest worry is going to be applications logging in with inappropriate accounts e.g. dba. You will have to do an audit on your connections before slamming the door on them.

It is very hard to ween developers off production SQL access, so make the access as sudden and sharp as possible. If you have the management support, diplomacy is not the primary thing to worry about. Security and reliability trump all.

If you must be nice, sit all the developers down and tell them what is going to happen and what the processes during an emergency will be. Provide regular backups of the production data to staging. They can play with the data there in safety.

If there is an emergency, you can always restore a backup to staging, they can script the changes they require, test it and send you the script.

I am a developer who had production access to a database. Running an update statment and forgetting the where clause is sooo easy.

If you don't want to be quite that harsh, read only rights to the database should be a good interim step.

share|improve this answer
I agree with the start of you post. That should be your primary concern is that the application users accounts have the correct and most restrictive permissions possible. –  Chris Marisic Sep 23 '09 at 14:23

I have to throw it out there as a developer.

I hate you.

The fact DBA's want to restrict us from doing job is idiotic. We have free rein over the system. So you lock down my account? All I need to do is drop the production connection string in my debug code of visual studio and off I go and can control anything the application can do.

This idea that restricting developers from the production db offers any security is a fools belief.

Now as a developer, what I would recommend is instead of trying to force them out of the db entirely (especially for the cost to the business during deployments) is instead adopt a policy to require all scripts that produce large updates/deletes/inserts to be required to be reviewed by the db team.

As Rob said, log all the DDL going into the db and then should any developers break this rule, report this to management and let them deal with the situtation.

The only thing you need to protect the db from with developers is a poorly written join statement that updates an entire table instead of the 100 records it was supposed to because we suck at writing sql because we can't wait for the day databases don't exist in our lives anymore.

share|improve this answer
Thanks for your alternate, if somewhat narrow view. If the application account is granted exec on stored procedures only, then having the connection string does let you do everything the app can do - only run stored procs. >>"The only thing you need to protect the db from with developers is" I have to disagree here. There are update problems, select problems causing perf. problems, bugs not being fixed/documented, untested code going into production, code being accidentally run on production...and I'm forgetting a few risks I'm sure. I'm sorry you suck at sql, you might want to work on it. –  a_dba Sep 23 '09 at 14:33
The majority of all those reasons you listed are deployment problems, not inherent problems for accessing the production database. And it's fine that I suck at sql I don't need it to do anything other than insert, select update. Any developer that puts logic in the database is doing it wrong. Databases are stupid data stores and must be treated as such. –  Chris Marisic Sep 23 '09 at 15:00
And yes with your point about exec however it still gives me all the ability to use the SPs to insert, update, and select data and do anything the application can. However just like most DBAs trying to implement "security" only allowing exec usage is stupid since it makes it impossible to use tools like NHibernate. IMO 90% of this stuff is all for job preservation, to make developers dependent on the DBAs to accomplish their job. –  Chris Marisic Sep 23 '09 at 15:04
SPs are tested code, which is what we like to have running in production. Good news for you, my friend: docs.jboss.org/hibernate/stable/core/reference/en/html/… - Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows –  a_dba Sep 23 '09 at 15:25
>> "90% of this stuff is all for job preservation, to make developers dependent on the DBAs to accomplish their job." We have 60 developers here, so my job is to keep the system up for all of their applications, make sure there are few accidents and that they are following proper change and documentation procedures. Developers can do their job in development and test - they don't need production update access. If their app needs manual updates to tables, it needs to be fixed. –  a_dba Sep 23 '09 at 15:57

You say you work for a city, but from that I can't really be sure if you are a government employee, a contract company, or even what country you are in.

If you want to make this change (and really, developers shouldn't never have any access to the live environment [I'm a senior developer BTW]) then you need backing from management to get your amateur developers in line (namely, the production DB is for production use, not for you to figure out why your half-ass code doesn't work by running random queries to "see what happens")

I would highly recommend you check if one of these applies to your organization:

SOX (Sarbanes-Oxley Act, applying to publicly traded companies in the USA)
Bill 198 (Province of Ontario, and indirectly the rest of Canada via CSA rules)
CLERP-9 (Australia)
J-SOX (Japan)

All of these do the same thing, ensure that the financials reported to shareholders are as free from fraud as possible. One element is the seperation of responsibilities - in IT this means that developers should never have direct access to read or write to production data. All actions must go through the server administrators, complete with written and signed requests (implimentation forms) which must be kept for auditing purposes.

In all cases management will come to your side very fast - under SOX it is management who are held accountable (jail time!) if there not appropriate "controls" in place.

share|improve this answer
We are a US municipal government, so are not governed by SOX. Some data is covered by HIPAA and PCI - but according to security and external reviews, our current situation is acceptable. >>not for you to figure out why your half-ass code doesn't work by running random queries to "see what happens" They were flabbergasted when we suggested that they shouldn't even have production read access. Not many of them have worked in large scale environments. –  a_dba Sep 23 '09 at 18:57

Your Answer

