Is it a good security practice to have separated read and write users for a database?

Question

So if some parts of the code are prone to sql injection, at least the user can't write anything to the database if he happens to be using the front end which does not have universal write access to everything?

Answer 1

The approach is generally to have different roles, not really users per se. As far as SQL injection attacks, I would concentrate on fixing the problem outright instead of mitigating it through this approach you propose.

Answer 2

Yes, I would say it's good practice to have users connect using accounts that only allow the least privileges they need to use the site. If your web users should only be reading data from the database then I would definitely create an account that only has read access and have them hit the DB through that.

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

Answer 3

Yes, however there are a lot of design techniques which can help control your database interface and surface area.

One must assume that the code will generally use the same login for all its operations in a given session (reads and writes). However, if a user is not a writing user, the login used for his session should certainly not have any write rights.

One good way to reduce your surface area exposed to SQL injection is not to have that account be able to update any tables directly in the first place.

With write access through stored procs, for example, the only injection which can happen is executing those procedures with appropriate parameters.