Is it a good security practice to separate users for reading and writing to the database?

Question

So, if some parts of the code are subject to SQL injection, at least the user cannot write anything to the database if he uses the front end, which does not have universal write access to everything?

Answer 1

Yes, I would say that it’s good practice to connect users using accounts that allow only the least privileges required to use the site. If your web users should only read data from a database, I would definitely create an account that has read-only access and gets into it through DB.

The more important it would be to protect your web application. You can still be the victim of a devastating SQL Injection attack, even if the user does not write to your database (think of stolen credit card numbers or passwords).

Answer 2

The approach, as a rule, should have different roles, and not the users themselves as such. Regarding SQL injection, I would focus on fixing the problem, rather than mitigating it with the approach you suggest.

Answer 3

Yes, however, there are many design methods that can help control your database interface and surface area.

It should be assumed that the code, as a rule, uses the same login for all its operations in this session (reading and writing). However, if the user is not a recording user, the login used for his session should not have any write permissions.

One good way to reduce the surface area affected by SQL injection is not to let this account update any tables directly in the first place.

When accessing a record through stored procedures, for example, the only injection that can occur performs these procedures with the appropriate parameters.

Answer 4

Yes. In addition to Abe and Cade Roux's good answers, he can help security auditors prioritize and make examination easier after an attack.

This allows you to focus your security checks on code that uses more privileges — you can spend more time auditing code that needs write privileges than code that needs read privileges, and even more time on code that requires privileges for privileged tables.

Another nice property of separation of roles is that it simplifies forensics. If you have role separation and you can define an attack in the database logs, you can narrow down which code could be used - only the code that uses the role associated with the attack in the logs.

Answer 5

In most cases, this is redundant, but most of the time you should use parameterized queries, which in any case are not subject to SQL injection.

Consider using stored procedures and a user account that can only call procedures and not run queries directly.

If you need to use direct queries and for some reason not use parameters, then yes, you should have a user account that can only read from the database when possible.

Answer 6

It seems your idea of ​​SQL injection comes from this stupid Bobby Tables comic.
But in fact, just reading from the database can be more disastrous than writing.

In addition, I have a strong feeling that NO ONE of the good guys who said that “this is good practice” used it in real life. Let's say an interface (in real, not imaginary life) must have write access.

You bark the wrong tree.
If you have some parts of the code that are prone to SQL injection, these are the CORRECT PART. This is the only reasonable solution.