Is it ever good practice to use a distinct database account for each user of an application?

Question

The applications I'm used to are server based and use one database account for many users, with the application code controlling what the user can do, or single-user.

Are there any successful complex business applications where each person needs their own database account, and the database server is relied on to enforce policy rules about what each user should be allowed and not allowed to do?

I'm thinking about applications where multiple people contribute to information in one database, and can access information stored by others - e.g. colleagues in an organisation who all need to access customer records.

Also is there a name for this type of set up?

Answer 1

If you need really tight control enforced at the data level. For example extensive auditing. Auditing is not much good if several users share the same account. If you have some users that have a need to access the data database directly.

If security is that tight you typically don't even expose the database directly. You have a service and the client must get data from the service.

In a web application the database (e.g. port 1433) is typically not exposed directly so you have a level of security. Even if the web application accesses the database directly the users still does not have direct access to the database.

If the login and password is in the client application then it can be hacked. If a domain you can use integrated security.

At the database you can have pretty fine controls. But row level control is a bit of work. A database is not a good tool for business rules. Business rules and detailed security are typically enforced at the application level.

You can have a mixed mode where there are some stored procedures used by admins and you want to track which admin. And you may give read only access to users that generate report directly.

Answer 2

The challenge here is that your database access is managed by an abstraction. Instead of users connecting as themselves, they essentially take on the identity of some generalized application role. You not only lose visibility of the individual connecting, but you also lose granularity of defining different types of access for all your individual users.

The main reason for using this approach is simplicity. Many applications are designed so that the user of the application will have no knowledge of the database. They really don't need it, especially if the application manages its own internal security. Most individual users will never connect to the database directly, so defining an explicit login for them is not necessary.

The only time you should consider letting the database manage your security is if you have users that will connect directly to your database. This means they are going around your application and it can no longer enforce security on its own. The advantage here is you can be more granular on defining your security. The disadvantage is the higher overhead for managing users and their permissions.

If you do feel the need for this sort of access, you want to use Role Based Access Control. You should define roles based on what kind of permissions are needed within your database and then group individual users under those roles. This gives you better auditing and control over your security model, which can quickly spiral out of control when managing direct access.

There is a hybrid approach to this. If you want your security to be partially manged by the database, you can create multiple application users, each defined by their role and grant access explicitly to those users based on the role they fulfill. This means you can leverage the database engine for some of your security model, but you still have to have some management within the application. It increases the complexity of the application user model, but gives you finer granularity on the different logins in use.

Answer 3

You should start enforcing security at the most granular level as soon as possible. Roles help in this regard - giving people access to a number of tables at once isn't a nightmare.

The single account for everybody is a great source of questions here and elsewhere - "record x was deleted, how do I find out who did it?" - the answer is you can't - without individual accounts and auditing.

By "auditing" I mean that while it's all very well having an account for everybody, that doesn't mean that you have true security. If, say, a record in the HR table is deleted, all you can tell is that somebody with access to the HR table did that - that's x number of people.

You need triggers in your system which log actions to be able to track down to the individual level who performed action X (unless you have an RDBMS such as Oracle where this can be made automatic).

In any case, you should always make your security as granular as possible as soon as possible - give people access to tables only on a "need to know" basis. And, always include timestamps for actions to tables - people frequently give their ids to others - if you can say, "Jimmy, you were the only one in the office at 17:49..." - again, it's not ironclad, just another arrow in your quiver.

Maybe if you gave us your RDBMS, you could obtain advice more specific/relevant to your situation?

Answer 4

Yes, it is. Connecting to a database from an application as a single powerful user is a violation of the principle of least privilege. This is the root cause of most SQL Injection attacks.

This is usually done due to ignorance, for the sake of simplicity, or sometimes for performance.

Databases are often long-lived and used by multiple applications at the same time, and over time. You can save resources by centralizing access control in the database instead of over multiple applications.

You'd want to pick a db server that supports Row Security, Column Security, and Impersonation / Proxy Authentication (which supports real db users + connection pooling)

It woulda also be more secure for "plugin" based applications, like Wordpress, where it's hard to avoid SQL injections due to unskilled plugin authors. Each plugin gets db login, instead of the application as a whole.