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

You don't need separate accounts for query types. Normally, the connection to the database uses a database user which has nothing to do with the user accessing the web application.

Answer 2

You can limit the type of queries the main account that is used when anonymous users access the site has access to. However, I don't think you need a different user per query within that subset.

What you want to focus on is what databases/tables are accessible by each user, rather than the specific type of query.

Answer 3

were both these account types used by the application, i would imagine that the practice you are referring to is an attempt to ward off sql injection attacks. it probably isnt necessary if you make sure to sanitize your inputs. remember bobby tables!

another reason for read-only accounts would be to allow admin users to run reports directly on the db on system activity and debugging production problems.

Answer 4

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.

Answer 5

In a "public" application, a good practice is to use a service account to access the database (run queries, exec stored procedures), and compute user access control at the code level.

This prevents you from needing to add new users into the database's security manager.

The only time I've even seen separate SQL database accounts being used is to separate application functionality, and even then, they are service accounts. i.e.

• Grant select to ReportService
• Grant select, update, delete to TransactionService

You can then run your web application as the ReportService or TransactionService, based on its needs.

By adding these two concepts together (user access in code, function-discrimination for services) you can effectively unit-test that your user access control mechanisms are working. Otherwise, you need to setup a user in the database, then see what kind of behavior you get from the database.

Answer 6

Yes. See Principle of least privilege.

In information security, computer science, and other fields, the principle of least privilege, also known as the principle of minimal privilege or just least privilege, requires that in a particular abstraction layer of a computing environment, every module (such as a process, a user or a program on the basis of the layer we are considering) must be able to access only such information and resources that are necessary to its legitimate purpose.[1][2] When applied to users, the terms least user access or least-privileged user account (LUA) are also used, referring to the concept that all users at all times should run with as few privileges as possible, and also launch applications with as few privileges as possible.

There are a lot of technologies which help a company embrace this principle. Many fall under the same category as technologies focused on preserving the end user identity across each tier and answering the question:

'Who is the "real" User'?

You should at minimum be aware of the consequences and risks the decision to ignore the Principle of Least Privilege and using a single shared database user account for all interactions between your mid-tier/application server and database. There are techniques to remain productive as a database application developer and still provide robust security features in your application.

Examples of Technologies in this space include but are not limited to:

1. Kerberos ticket or an X.509 certificate (SSL).
2. Proxy Authentication - Allows you to continue to pool connections but proxy as different roles for each session.

There are other benefits to embracing the principle of least privilege besides security. In many databases a read only connection can perform better because it doesn't need to be aware of and/or participate in transactions.