When using EJB and making use of connection pools. How can changes to the database be audited per user?
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.
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.