There appears to become three common processes for mapping a credit card applicatoin consumer to some database user.
- Face to face Mapping: Each Application user (bob, nancy and fred) will also get a corresponding database user account (bob nancy and fred).
- N to M mapping: Each application user is planned to some database user that signifies their role. bob and nancy are planned towards the 'clerk' database user while fred is planned towards the 'manager' database user.
- N to at least one mapping: Each application user is planned to some single database user (application_user) and identity is just handled in the application tier.
It appears that #3 is easily the most common in web database integration. Exactly why is there not really a greater emphasis alternatively two options?
Oracle encourages techniques like #2 having its proxy authentication features for an additional reason:
Limited trust model-manipulating the customers on whose account middle tiers can connect, and also the roles the center tiers can assume for that user
Scalability-by supporting lightweight user periods and getting rid of the overhead of re-authenticating clients
Accountability, by protecting the identity from the real user right through to the database, and enabling auditing of actions taken with respect to the actual user
Additionally towards the simpler administration, you will find performance benefits of option 3 on web-servers this enables pooling of connections - i.e. a minimal quantity of physical database connections could be re-used continuously to service a lot of application customers. This is whats called the "reliable subsystem" model - i.e. your application-server validates exterior phone callers, however the application-server is used because the identity for calling downwards. The greatest problem here's that for audit etc you have to keep telling the db who made the present change (such things as
SUSER_SNAME() stop being helpful) - not to mention, this really is relatively simple to spoof.
When the web-server used security per user, this would not be possible - and thus you'd basically need to disable connection pooling. The action of creating an association is (relatively) costly, which means this might have a substantial effect on performance. You wouldn't like to help keep the (per-user) connection around between demands, because this would result in a huge pool and lots of open connections (also costly).
The "per role" option site together - but it's rare that roles are truly mutually exclusive, making this tough to implement.
With client applications that talk straight to the database, option 1 may be the easiest to keep, since you don't have to distribute any special account particulars towards the client. The pooling likewise is not an problem, because the client's machine is just serving as 1 user.
Re 2), application security/permission needs are often of the much finer granularity than could be supplied by database security layers, unless of course you place much of your application logic in to the database. An easy example is the fact that while two customers might need to update the orders table, one might be creating their very own order and also the other might be an admin user editing another person's order. Both of them need place/update rights up for grabs. You can implement this restriction via saved methods, but that's a real workaround - both customers still have to update the table same goes with need a free account with individuals rights.
I favor to make use of exactly the same db take into account all clients of the application, and implement application roles and permissions outdoors from the db.
Clearly for any web application where customers can register themselves, 1) isn't practical. I've got a site with 500,000 or even more customers - would i would like that lots of db accounts? I do not think so!
I have a minimalist approach (which I am certain many would argue with), in which the db user a credit card applicatoin runs under has got the minimum permissions required to function, but forget about. Which means the db user cannot make schema changes, drop tables, restore databases, etc.
Another account can be used throughout development for schema modification, which has the requisite rights.