When using EJB and making use of connection pools. How can changes to the database be audited per user?

Subrahmanyam Allamaraju

This is quite not the case with EJBs. When you create a connection pool, the J2EE server is the client for the database. Since a J2EE environment is a shared environment, the practice is setup a very small number of connection pools, and recycle the same pool of connections across several invocations (across several "users").

The notion of a database user does not therefore exist. For instance, you may have 1000 concurrent HTTP sessions, but only 10% of them may be using connections actively. In such cases if you try to allocate connections on user bases, you'll have too many idle connections, ultimately paying performance penalty.

To answer your question, there is no direct and simple way of determining who changed what. A reasonable approach is to constrain the methods on various beans based on user roles, and log/audit certain business events (not database events).

An alternate approach follows(thanks to Siva Visveswaran In order to do this we basically need to distinguish between the database user (used for connection therefore pooled) and the application user (not used to connect to db but perhpas part of an access control group that may vey well mirror your pools).

For this we typically don't rely on DBMS logging but instead perform application level logging using LAST_UPDATED_USER and LAST_UPDATE _TIMESTAMP columns on the table. Okay so how do we populate the LAST_UPDATED_USER with the real user who's updating the information? We can obtain it from the Session/Entity context in the EJB.