- thin client (.net desktop application), which connects to our web services,
- web services with business logic and data access layer, running on IIS, authentication through username/password and certificates on ssl/tls,
- our system has to support both oracle and ms sql server for data storage,
- we have to have a detailed audit trail for all operations on the database - select/insert/update/delete including the user (running the thin client).
We are considering two options:
1. One connection: IIS connects to the database always with the same username (application pool identity is a sql user, which is also set in connection string). The database would contain a users table (username and hashed&salted password and some other fields).
- easy administration of users (when you add an user to the thin client for example, then you don't need to add it to the database also).
- if you use sql triggers for auditing the tables, then you cannot get the end user of the application, but the user of the application pool. So if you want to log the end user also, you have to pass it to the database in every query. We could solve this with two additional columns on every table (CreatedBy, ModifiedBy) and then the audit triggers would log them also,
- you cannot use database's built in audit mechanism.
2. Each user own connection: IIS is set to somehow impersonate the thin client user and access the database with those credentials. This also means, that all users have to be sql users.
- we don't need own logic to store users and passwords,
- we can use database's built in audit mechanisms.
- every thin client user has to have corresponding sql user in the database.
- differences between accessing oracle and ms sql databases from web services,
- what about licensing the oracle database if we use only one user to connect to the database (because the cost of the oracle database depends on the number of named users).
What are the best pratices regarding the connection between web services and database - is it better to use one common user (running IIS application) or to use end client users with some kind of impersonation?