Is having different users for different types of queries a good practice?

Question

I am using MySQL and PHP for a project I am working. I have created separate users for carrying out different functions (one for running select queries, one for running update queries, etc.) to provide an extra layer of security. This way, I figure if someone does manage to carry out an injection attack (which is unlikely since I am using prepared statements), whatever they are able to do will be limited to the type of query that was originally meant to be run. Is this a good practice or is it not worth the trouble?

Answer 1

I personally don't think it's worth the bother, since it's trickier to code, test and deploy. Make sure your software is immune to SQL injection instead.

Answer 2

Aside from the extended logic, you will also have different connections and essential overhead in that area.

IMHO it's wise to not do all your queries in a webapp with the root user and if the data is so hot, then make sure the designated user has no DROP, DELETE etc. priviledges. You could implement soft-delete if it's necessary in your application.

Last but not least, make sure to sanitize all GPC and make sure to properly quote/escape files in your queries. Using prepared statements can be one thing, but in the end it can be as simple as using mysql_real_escape_string() or whatever quoting-methods your DBAL/ORM offer.