Oracle Connections with DataDirect Part 2: Reauthentication

In Part 1, we looked at fixing a problem with the memory usage of connection pools by accelerating the timeout, but that didn’t address some of the root problems.  For one, why does every user have his own connection pool? 

DataDirect pools connections based on unique connection strings (as do most DB providers), but shouldn’t there be a way to use the same connection string for multiple users?  As it turns out, DataDirect and Oracle provide just that mechanism.  DataDirect calls it reauthentication.  This lets you open a DB connection using a common proxy user, set the CurrentUser and CurrentPassword properties on the connection, and then open the connection.  Key here is that all users need the Oracle CONNECT THROUGH permission set.

With this setup, there is only one connection pool for the application, allowing much more efficient reuse of connections across users instead of needing a new connection every time a user logs in.

There is a small performance penalty to be paid with this configuration, however.  Each context switch of a connection from one user to another incurs a small hit, but the overall performance of the application in our case was vastly improved.