Should I have separate SQL accounts for different types of queries?

Question

I am starting to work with a small internal web application, basically a proof of concept, but we want to consider it as a “real” application. I do not have much experience as a database administrator, and no one in my group (this is not particularly important to have, as it is PoC).

I was wondering, though, if this web application becomes publicly available, should we have separate accounts on the database server for different types of queries? For instance. to have one SQL account for SELECT queries and another for UPDATE / DELETE? I can’t convince myself of any special advantages for this, but I’ve heard about technology before, so there must be some value.

Answer 1

It is useful to have different accounts for different types of tasks (for example, batch jobs against web services) and have restrictions on the number of connections, etc. on each. This means that if your batch jobs go crazy, that he cannot take out your web application.

You also need different accounts for different permissions. For example, if your administrative and user applications were separate, they should have their own accounts. This helps ensure that if your user application is to be compromised, it will not be able to do as much damage to your data.

In these two respects, it is useful to have a read-only user, but only if your application is not recording.

Answer 2

You can limit the type of requests to the main account that is used when anonymous users access the site. However, I do not think you need a different user for each request in this subset.

What do you want to focus on which databases / tables are available to each user, and not a particular type of query.

Answer 3

there were both types of accounts used by the application, I would suggest that the practice you are talking about is an attempt to prevent SQL injections . this is probably not necessary if you are sure to sanitize your entrances. remember bobby tables !

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

Answer 4

Yes. See Least Privilege Principle .

In information security, computer science and other fields, the principle of least privilege, also known as the principle of minimum privilege or simply minimal privilege, requires that in a particular level of abstraction of computing environment, each module (for example, a process, user or program based on the layer that we consider) should have access only to such information, and resources that are necessary for its legitimate 1 [2] When applied to the users, the terms and the lowest user access and an account with the least privileges (LUA) are also used, referring to the concept that all users at any time to work with minimal privileges possible, and to launch applications with the highest possible privileges.

There are many technologies that help a company adopt this principle. Many of them belong to the same category as technologies aimed at maintaining the end-user identification at each level and answers to the question:

"Who is the" real "user?

At a minimum, you should be aware of the consequences and risks of deciding to ignore the Least Privilege Principle and use a single user account for a common database for all interactions between your mid-tier server / application server and the database. There are methods to stay productive as database application developers and still provide robust security features in your application.

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

• Kerberos ticket or X.509 certificate (SSL).
• Proxy Authentication Allows you to continue merging connections, but the proxy server as different roles for each session.

There are other benefits to incorporating the principle of least privilege besides security. In many databases, a read-only connection may work better because it does not need to know and / or participate in transactions.

Answer 5

You do not need separate accounts for request types. Typically, a database connection uses a database user who has nothing to do with a user accessing a web application.

Answer 6

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

This will prevent the need to add new users to the database security manager.

The only time I saw separate SQL database accounts was to separate the functionality of the application, and even then they are service accounts. i.e.

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

You can then launch your web application as ReportService or TransactionService based on its needs.

By combining these two concepts together (user access in code, function-discrimination for services), you can effectively test that your user access control mechanisms work. Otherwise, you need to configure the user in the database, and then see what behavior you get from the database.