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).
Sir Greg,
I like simplicity of this solution. Does it work also with WPF apps ?
Thanks
Absolutely does.
You know, the only part of SQL that I absolutely haven't been across is security. It always seems to be an afterthought in conferences, in training and in books I've read. Performance, and design are covered, but never security. That or I haven't been paying attention.
I'm really going to have to start developing more of a plan to look after these issues.
Hey Greg,
Apart from obvious stuff like central management, password strength policy etc, what's the rationale behind recommending domain accounts instead of SQL server logins?
I understand that Kerberos does not exchange the credentials. However, I assume that certain mainstream providers (e.g. SqlClient) encrypt the sql login creds in transit?
This is an interesting problem at the moment for applications running in linux containers that are not domain joined. I'm really keen to see if there are obscure traps you might fall into if you use SQL logins instead.
You can now apply organizational password policy, etc. to SQL Logins in recent versions.
One key issue is simply where those passwords are stored though. I'd rather them be configured as service credentials at the OS level, rather than stored anywhere separate that the app can get to.
Regards,
Greg
Thanks Greg. Makes sense now.
General advise I find in MSDN is a bit confusing for fully automated infrastructure setup where SQL logins for applications are auto generated and stored in a key management service (e.g. Vault).
And yes, Linux-based SQL Server servers make that trickier for Wintel apps. I expect that to change to OAuth based access via Azure AD (AAD) over time.