[SOLVED] Moving from a direct db connection per user to using a web-service with pooled connections

Moving from a direct db connection per user to using a web-service with pooled connections

I'm working on moving a V.Rich Client from talking direct to the DB with one login per user to talking direct with a WCF service layer.

In the new model I'm like the users to authenticate with the middle tier and for the middle tier to us just one account to connect to the database.

My problem comes in that the database's triggers and store routines use the USER() function on the database to perform auditing. How can I replace this functionality when only one DB account is being used?

Hopefully I've explained the problem but any questions fire away. In case you're wondering the platform is .Net, WCF and Mysql

Thanks,

Tom.

Solution

The way to replace this is to add an extra parameter to your sprocs, and pass in the user that the application knows. Because you're pooling your connections, the DB library has no idea who the true end user is ... it only knows the middleware's user. So you have to proxy the value you want to audit with into the sprocs.