Why is security important in a dev environment?
6August 23, 2022 by Kenneth Fisher
I was asked a rather interesting question during an interview recently. It went something like this:
Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?
And in general my response is “You give it to them. It’s too late to stop them at that point.”
If you are waiting until the move to production to restrict permissions you are waiting far too late. This needs to start in dev. As an example we had a team who the service account for their new application to the db_ddladmin role. No big deal in dev right? We didn’t even catch it in test. They get to model and all of a sudden their truncates no longer work. Truncate requires the alter permission on the table. That’s included with db_ddladmin but is a somewhat unusual permission to grant someone outside of it. I mean you don’t normally want your application able to add or remove columns right? Fortunately that was a relatively easy one to fix. The one where they had the application creating and dropping tables all over the place (for some reason they didn’t want to use temp tables) was a little more complicated.
It comes down to this. If you want your application to exist with a given set of security standards when you get to production, then you need to make sure it’s written and tested with those permissions. I.e. your dev and test environments. If you were wondering, this is why, among other reasons, that I don’t give sysadmin, or db_owner even in a development environment.
SQL Studies 
You refer the matter to the relevant product-owner, team responsible for data-security and your boss and tell them that version of the product being rolled onto the productive environment requires permissions to the database it shouldn’t have.
How should you proceed?
Certainly, in the end it’s the business that has to make decisions. Usually anyway. But in my experience it’s pretty rare for them to scrap a production deployment and go back to dev unless there is some type of certification or audit issue. And I can’t tell you how often I’ve heard “Put it in place now, we’ll fix it later.” Want to guess how often they actually fix it? 🙂
While again, you are right, it’s not usually our place to make final decisions, it’s still a LOT easier to get it right from the outset.
I agree completely.
I want officialdom to know that a potentially dangerous mistake is being made at this late stage, one that was/is preventable, one which I am flagging now. If a disaster does happen and an investigation is carried out, I want it to be seen that I acted responsibly.
Depending on who the various bodies are that are being reported to are and depending on previous histories with lax security, this flagging may trigger action, but probably not.
For me, the best realistic consequence from this flagging is that I (or one of the DBAs) get to be involved with the dev team and product owners from the very beginning in order to help prevent such a scenario from occurring.
[…] Kenneth Fisher shares an opinion: […]
Sorry, what’s SQL server Id?
When you create a login in SQL Server one of your options is to create a SQL Server authenticated Id. It’s the one where you assign a password to it. SQL Server stores the password (in a hash) and then when you connect SQL Server is what authenticates it rather than say Windows or Active Directory. It’s considered less secure.