SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Bad Admins: Non-Production Servers

This is part of a series of tips on how bad/rogue admins can get access to the data in your SQL Servers.

We all know we shouldn't do it: don't put production data in non-production. The main reason is because we don't treat non-production like production. If we did, we'd call it production. The types of things we typically see:

  • More folks have administrative access to the servers or to the SQL Servers.
  • More folks have access to the data in non-production than in production, even if it's not administrative level access.
  • Non-production servers tend to be monitored less for security events.
  • Non-production servers tend to get more leeway even if security events are monitored because, well, they are non-production servers.

And here is where the fallacy has crept in. We are classifying the server and not the data. If it's production data, it needs to be handled with production-level controls. But in most shops this isn't the case. Production data gets restored to non-production and then gets handled with non-production controls. This is the perfect situation for a rogue admin intent on stealing said data. He or she gets the data desired and doesn't have to face production-level controls and scrutiny to get it. As a matter of fact, because of an issue that's cropped up, the admin may be asked to check out things with privileged access in non-production, making his or her theft all the easier.

There's really only two solutions here:

  • Don't restore/move/replicate/otherwise transfer production data to a non-production server.
  • If you must have production data on a non-production server, treat it with the same level of protection as a production server.

Short of doing these two things, that rogue admin is going to have a field day.


K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Steve Jones on 29 July 2011

Completely agree. We often do need to move production data to another instance for testing or for research of an issue, but we need to provide the same level of controls.

It's a pain, but we have to start dealing with this better, which will reduce the amount of data loss from laptop thefts, unauthorized people querying data, etc.

Posted by Jakub Dvorak on 29 July 2011

I'd add one point - my personal oppinion is that if any manipulation with any form of production data (backups, exports, etc.) should occur, proper anonymization processes should be deployed.

Posted by pat.hall on 1 August 2011

My suggestion is that "production" server is a bit simplistic.

You need to classify the data sensitivity and the downtime sensitivity separately.

"Production" server usually means both sensitive data and must stay up.

"Nonproduction" server usually means it going down doesn't immediately impact the end users, but doesn't really relate to the sensitivity of data.

I suggest: Production uptime, Confidential data vs. Nonproduction uptime, Nonconfidential data, etc; classify the two sets separately.

I'd also note that for some performance analysis of tricky issues, having a full copy of the data is critical to track down/duplicate the issue.  Sometimes, having an exact copy is important to replicate particular behaviors on oddball data.

"Proper anonymization" makes sense... but with many real life databases, it varies between "much harder than it sounds" and "nigh impossible without near-infinite time and/or destroying some required data", depending on how, say, comment fields were used.

Posted by Vlad-207446 on 1 August 2011

unfortunatly some times a "Proper anonymization" will remove the couse of an issue you are trying to replicate

I have seen issue where a customer information were causing the issue as it was improperly entered (I know, I know UI needs to prevent that but it was allowing it because customer requested that way...) but all non-production enviroments (DEV/QA/UQA/TEST) could not replicate it

as all the TestData was entered correctly by devs and all prodData  was anonymised by specual process that effectively destroied bad data :-P in process.

Leave a Comment

Please register or log in to leave a comment.