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

Security

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?

Best Answer

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.

Related Question