What are the advantages to each approach for mapping application end users to database users?

Question

There seems to be three common approaches for mapping an application end user to a database user.

1. One to One Mapping: Each Application user (bob, nancy and fred) also get a corresponding database user account (bob nancy and fred).
2. N to M mapping: Each application user is mapped to a database user that represents their role. bob and nancy are mapped to the 'clerk' database user while fred is mapped to the 'manager' database user.
3. N to 1 mapping: Each application user is mapped to a single database user (app_user) and identity is only managed at the application tier.

It seems that #3 is the most common in web application development. Why is there not a greater emphasis on the other two options?

Oracle encourages techniques like #2 using its proxy authentication features for the following reason:

Limited trust model-controlling the users on whose behalf middle tiers can connect, and the roles the middle tiers can assume for the user

Scalability-by supporting lightweight user sessions and eliminating the overhead of re-authenticating clients

Accountability, by preserving the identity of the real user through to the database, and enabling auditing of actions taken on behalf of the real user

Oracle's Proxy Authentication documentation

Answer 1

In addition to the simpler administration, there are performance advantages of option 3 on web-servers; this allows pooling of connections - i.e. a low number of physical database connections can be re-used continuously to service a large number of app users. This is known as the "trusted subsystem" model - i.e. your app-server validates external callers, but then the app-server itself is used as the identity for calling downwards. The biggest issue here is that for audit etc you need to keep telling the db who made the current change (things like USER_NAME(), SUSER_SNAME() cease to be useful) - and of course, this is relatively easy to spoof.

If the web-server used security per user, this wouldn't be possible - and so you'd essentially have to disable connection pooling. The act of establishing a connection is (relatively) expensive, so this would have a significant impact on performance. You wouldn't want to keep the (per-user) connection around between requests, as this would lead to a huge pool and a lot of open connections (also expensive).

The "per role" option site between them - but it is rare that roles are truly mutually exclusive, which makes this hard to implement.

With client apps that talk directly to the database, option 1 is the simplest to maintain, since you don't need to distribute any special account details to the client. The pooling likewise isn't an issue, since the client's machine is only acting as 1 user.

Answer 2

I'd like to add that method #1 requires the code that creates users of the application to run under a DB account that can mess with privileges. To me, this is an unnecessary risk.

Answer 3

Some of the reasons why N to 1 mapping is so much widely used could be possibly because of,

1. In a conventional software development database is considered as a mere repository and not beyond.

2. Programmers approach database as a black box and access privileges are considered as one time activity.

3. Programmers are happy to debug the code and resolve problems rather than worrying about security role definitions and maintenance at the database level.

4. In a typical application where there is a "User / Role maintenance" screen provided to admin user, it is easy to have tables like USER, SECURITY_ROLE, USER_SECURITY_ROLE etc. to maintain the application user information rather than creating user, security entries in database itself.

5. In case of not a well defined roles in business model (Dual roles, customizable access privileges etc.) it is easy to implement security at application level rather than at database.