Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
1  
Let's flip this on its head, I'd it ever good practice not to? – Stevetech Mar 26 at 13:01
    
It's certainly standard practice not too in some situations. Many applications have many end users but just a single database user - for example wordpress, drupal, roundcube, redmine. As far as I know the recommended installation for each of these applications has just one user account at the database level, but there can easily be hundreds or thousands of users. – bdsl Mar 26 at 14:18
    
@Stevetech it sounds like your answer to my question is "Yes". can you expand that into a full answer? – bdsl Mar 26 at 14:23

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.

share|improve this answer

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.

enter image description here

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.

share|improve this answer

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?

share|improve this answer
    
I don't think this is directly related to my situation, I was working asking mostly out of curiosity. I work with web applications and MySQL. – bdsl Mar 25 at 16:48
1  
You seem to be saying that all databases should require each human end user to have a unique login, but I know of many server applications that just use one DBMS user for the entire app, and I haven't seen that condemned as less than best practice. – bdsl Mar 25 at 16:51
1  
Nothing wrong with intellectual curiosity - your post appears to have had a good reception. MySQL is probably the worst in the security capacity (as it is in so many other...), but MariaDB does have them and is also Open Source. In reply to comment - what you're talking about is not best practice - it's worst practice. – Vérace Mar 25 at 16:53
1  
@bdsl You mix server with business application and that that is a source of confusion. Business applications include desktop applications. – Paparazzi Mar 25 at 16:59
1  
@bdsl Then stop using the term server applications if you don't mean to exclude desktop application. – Paparazzi Mar 25 at 17:10

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.

share|improve this answer
    
Is it possible to make each user of a Wordpress site connect to the database with different credentials? – bdsl Mar 25 at 21:00
    
@bdsl yes it is. – Neil McGuigan Mar 25 at 21:04
1  
Can you link to any guide on how to do that? I had a quick search and didn't find it. Does it mean that the wordpress login page asks for the credentials needed to log in to the DB? – bdsl Mar 25 at 21:08
    
@bdsl my php is pretty rusty. Here's how you would do it with Spring (Java) and PostgreSQL. blog.databasepatterns.com/2015/03/… . stackoverflow.com/questions/2998597/… – Neil McGuigan Mar 25 at 21:10
1  
Wordpress was just an example of a plugin-based system. It is poorly made from a security standpoint. – Neil McGuigan Mar 25 at 21:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.