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.