Intrusions into computer systems are happening all the time now. We need to address this issue as an industry, but it’s important to understand that the way we design databases plays a big role in the impacts that occur during intrusions.
If you don’t accept that you could have an intrusion, you are living in La La Land. (See https://en.wikipedia.org/wiki/Fantasy_prone_personality)
A bug in any one of the frameworks that you use, the code that you write, the protocols that you use, the operating system or hosting services that you use can potentially expose you to an intrusion.
So do we just give up?
No, what you need to ensure is that when an intrusion occurs, the damage or impact is minimized. We do this in all other industries. For example, people working in high locations don’t expect to fall but they (generally) make sure that if they do, while something nasty might happen, it won’t be disastrous.
I routinely see web applications and middleware that can access any part of a database that it wants. The developers love this as it’s easy to do. But it exposes you to major risks. If the application is trampled on, you’ve opened up everything.
I always want to put mitigation in place and to limit the damage.
If your plan is to have your application connect to the database as one user, and you make that user a database owner (db0), or a combination of db_datareader and db_datawriter, or worse, a system administrator; then you don't have a plan.
A better plan is this:
- Create a schema for the application – let’s call it WebApp
- In the WebApp schema, create only the views and procedures that define what you want the application to be able to do (ie: it’s basically a contract between the database and the application)
- Create a new user (from a SQL login or, better-still, a domain service account) for the application to connect through.
- Grant that user EXECUTE and SELECT permission on the WebApp schema (and nothing else)
Then if the application is trampled on, the most that it can do is the list of things that you’ve defined in that schema and nothing else.
We need to start building systems more defensively, and this is reason #82938429 for why I just don’t like most ORMs as they tend to encourage entirely the wrong behavior in this area. (Some let you do it better begrudgingly).