Should I have separate SQL accounts for different query types?

Question

I'm getting started on a small internal web application at work, mostly a proof-of-concept, but we want to treat it like a "real" application. I don't have much experience as a DBA, nor does anyone in my group (it's not particularly important to have one, since it's a PoC).

I was wondering though, if this web app was going public, should we have separate accounts to the DB server for different query types? E.g. have one SQL account for SELECT queries, and another for UPDATE/DELETE? I can't convince myself of any particular advantage to doing so, but I've heard of the technique before so there must be some value.

Answer 1

It's useful to have different accounts for different types of tasks (for example, batch jobs vs. web serving) and have limits on connection count and the like on each. This means that if your batch jobs go crazy that it can't take out your web app.

You'd also want different accounts for different permissions. For instance if your admin and user apps were separate they should have their own accounts. This helps to ensure that if your user app was to be compromised, it wouldn't be able to do as much damage to your data.

In these two regards it is useful to have a "readonly" user, but only if your application doesn't do writes.