Let Oracle Know the Real User of ADO.Net application When Using Connection Pool

When using connection pooling, all connections are made with a fixed user name. In the database, it is difficult to tell who is the real user that is updating the database. While I can pass the user name to every stored procedures, it will be extremely tedious when multi-level of stored procedure call and still won't work for triggers.

The solution to this problem is to set the user name in some sort of database session state storage. For Oracle database, that is the CLIENT_IDENTIFIER variable in USERENV of the SYS_CONTEXT. You can set this variable by calling a build in package procedure dbms_session.SET_IDENTIFIER and retrieve it with SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER').

ODP.Net provide a convenient property, OracleConnection.ClientId, for this. It further reset ClientId automatically before the connection is returned back to the pool.

More information about this topic can be found here.

This same technique can be used with any database that provide some kind of database session storage, and in many databases, the temporary table can serve the same purpose.

For project that are required to run on different databases. A custom stored procedure can be used for this purpose and implementation can be vary. Actually, even with the case of Oracle, we end up used a package because

1. We can set more information then just CLIENT_IDENTIFIER. For example, the name of application, the client machine name and etc.
2. We caches those information in a package variable for fast access, package variable is 100 times faster then SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'), which is about the same speed as the build in function USER (we cache USER as well in package variable).

In my next post, I'll discuss how to set the ClientId when Spring.Net ADO support is used to manage the connections.