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.