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

Because the database roles (by object, CRUD, etc.) don't generally apply to usernames unless they are database developers. Both AD and the database have roles, but there is the same mismatch between the two. The only halfway defensible mappings would be for administrator roles, but even that is sloppy.

This issue is a result of a common misapprehension fostered by the term "integrated security", which really only ends up meaning "single-login" (i.e. user verification), and maybe creating yet another fleet of roles and groups within AD so that AD administrators can be assigned the responsibility for database security - not usually something they are well-trained at, although I'm sure there are exceptions.

Answer 2

one seems the most sensible for most applications. #2 seems wrong to me. In this case i would have groups and users can fit into a group.

Answer 3

Re 2), application security/permission needs are usually of a much finer granularity than can be provided by database security layers, unless you put most of your application logic into the database. A simple example is that while two users may need to update the orders table, one may be creating their own order and the other may be an admin user editing someone else's order. They both need insert/update privileges on the table. You could implement this restriction via stored procedures, but that is really a workaround - both users still need to update the table so will need an account with those privileges.

I prefer to use the same db account for all end users of an application, and implement application roles and permissions outside of the db.

Obviously for a web app where users can register themselves, 1) is not practical. I have a site with 500,000 or more users - would I want that many db accounts? I don't think so!

I take a minimalist approach (which I am sure many would argue with), where the db user an application runs under has the minimum permissions needed to function, but no more. That means the db user cannot make schema changes, drop tables, restore databases, etc.

A separate account is used during development for schema modification, that has the requisite privileges.

Answer 4

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 5

Also see Is SQL Server/Windows integrated security good for anything?.

Answer 6

I don't know about Oracle, but in SQL Server, you want to pool connections, and the best way to do that is to have a single user connect. Each differant user uses a different connection string, and this can slow things down because existing connections cannot be pooled and re-used for differant users.

If you are doing an N teir application, your security logic should go in the middle teir. You don't want to have to go back to the database every time you want to check and see if someone has permissions to an area. And permisions in most application are usually more specific than can be provided by database security.

Answer 7

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 8

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.

Answer 9

There is something to be said for option 2, if you are in an environment where the application faces off to the Internet and also to internal users who have more privileges.

This way you could run the Internet facing application server(s) under a less privileged (in terms of table access) account on the DB, and the internal users could access a separate server with additional access. The advantage is that even if the Internet facing route gets fully compromised, it still doesn't have the privileges to mess with the tables that the internal users need access to.

Option 1 and 2 would work well if your authentication system supported delegation (e.g. Windows authentication, Kerberos) - because then the application server wouldn't need to hold or store its own credentials, it could simply impersonate the client. This would in fact be considerably better than option 3 (because if the server is compromised, the attacker can only use the privileges of currently connected users, they can't access the whole database). However it's rare that you have the ability to use this feature.

In the usual Internet setting 3 is the only realistic choice.

Edit: none of these options preclude you from doing further access control with business logic in the application tier, so it's not correct to say that this is an advantage of option 3 as some answers have suggested. The application tier still knows who its client is in all cases.