Worthwhile from security standpoint to limit database server user for ASP.NET website to only EXECUTE on stored procedures?

Question

I know that obviously we must avoid sql injection attacks through user input validation and parametrized queries. There's already a firewall in place on the database server to limit remote connections to be accepted only from the web server.

Would it also add value from a security standpoint to limit the actual database user account that the ASP.NET website uses to the EXECUTE permission only on the stored procedures that they need? All database interaction would take place using these stored procedures.

This seems to me that even in a scenario where an attacker figures out a way to access the database connection, the attack is limited to only executing predefined queries and no open ended queries?

Answer 1

There are two main (security) reasons to do this, above and beyond just using parameterized queries:

• Parameter type enforcement
• Least privilege.

The principle of Least Privilege requires you to allow any entity (user or application) access only to whatever it needs to do the defined task. If you don't restrict the webapp only to the SPs, the application could potentially execute any arbitrary query.
Note that this is relevant in two situations: preventing an attacker, that managed to find a vulnerability in your application (either SQL injection, or any other vuln that could allow him to execute code), from running malicious SQL queries; and, much lesser risk, developers that are looking for insecure, unapproved shortcuts (or even malicious developers).
Granting only EXECUTE privileges on the required SPs, will stop the application from running any query that was not predefined.

Wrt enforcing parameter types, while it is possible to implement this in other ways, this brings the type enforcement to the database, but before it hits the db server. I.e. using the types that are actually defined on the database, and without accidentally skipping a parameter.

---

Note that in order to do this properly, and avoid some common mistakes, you want to:

1. define a specific user account for the ASP.NET application
2. assign the account to a custom DB role
3. remove the account from all other roles, such as dbo.
4. grant EXECUTE privileges to the custom DB role you created
5. remove all other privileges on the SPs, tables, and other DB objects. This includes default "public" roles, and so forth.
6. ensure that the custom DB role has no other privileges.

Answer 2

Absolutely.

But then you need to be careful about the stored procedures. If one could allow arbitrary queries to be passed to it then you are still stuck in the same position.

Answer 3

There is a nasty downside here - we once had these sorts of rules, but not all database requests fit neatly in stored procedures. So what happened was many of the developers were hand-building SQL inside the stored procedures and executing it there. This led to a lot of ugly code and nasty potential holes as sp_executsql, at least at that time, would run with sa level permissions once it was inside a stored proc and sanitizing input inside SQL procedures is a lot uglier than sanitizing input in a modern application language.

We started running with allowing CRUD operations on tables and enforcing parameterization in the application layer after this came to light.

Answer 4

The least privilege principle comes to mind here, now to be a bit anecdotal. In PostgreSQL (yes I realize this is a SQL Server question, but it could be something to look out for) it is possible for a non-privileged user to write a function that overloads (same function name + parameters) that can be a malicious query. When a privileged user runs this query they may think that they are running the default add function when in reality they are using the malicious one. As a developer I know you are creating a maintenance nightmare, because there will always be a "one-off" query that needs to be written. So here is a question, when you create a new user account if that is a pre-defined query, you have not afforded yourself any extra protection. One could argue that you have in fact made it worse as now you allow an attacker to monitor your system indefinitely, unless you constantly monitor log files for new users. Also, data changes so the UPDATE statement is your new worst enemy as it allows someone to put javascript directly into a record that gets returned, because and I am speaking from experience, everyone trusts that the data in the database is already secured.