I don't want to authenticate against the DB in our applications.
Will you offer your killer arguments against this approach?


If I cannot convince my team to do middle-tier authentication
with generic connection pooling, do you have any advice
on how to elegantly handle web application use of an
individually authenticated java.sql.Connection? How/where
should I store a Connection between application requests?
Is it reasonable to create a singleton pool map that holds
the Connection indexed by a session cookie?

My opinion is that you should not have a middle tier at all, if you're not going to use it. It starts with the user authentication, but where does it end? Do you have lots of stored procedures? Why not make a thin client to the database itself, like in the old days?

I don't see a proper way to handle DB based authentication with Hibernate, it's way out of the scope. You cannot hold connections between requests, at least if your database is not configured for this kind of client connections (many long connections instead of few short-lived). This kind of system requires a different setup, wrt memory profile and load.

How would you handle this in your middle tier without Hibernate?

Oh, *now* I finally understand what you want!

And it is VERY, very evil.

As Christian has alluded, this is NOT the Right Way to architect web applications. It is completely un-J2EE. Don't take my word for it, go read some books about enterprise/web architecture.

But, nevertheless, you could do it with Hibernate, using SessionFactory.openSession(Connection). Hibernate is not the problem here. The problem is that you are trying to do a Bad Thing.

I agree however...

Our data is our product more or less. It cannot be corrupted.
All data inserts and updates must be trackable to the user
that initiated them. It is difficult to convince our team that
started from a very data-centric perspective to relinquish
any logging control to the application layer. They want the
benefits of a multi-tiered architecture with a web tier but they
do not seem to understand why each user cannot have a
managed connection with a limited lifetime that corresponds
to an HTTPSession (not actually stored in an HTTPSession so
it won't be serialized
but grabbable with the session id from a manager or something).
While this places all kinds of architectural limitations on
authentication and connection management because the
connection and authentication are so intimately tied, I don't
see how I can tell them this isn't a viable approach for apps.
with a small user base in a protected network environment.

BUT, I want to!!! Unfortunately, I can't argue for
scalability because it is unlikely we'll ever tax the number of
available connections. As for security, security requirements
are somewhat nebulous and not the highest priority. As for
time, authenticating against the DB is the fastest at least in
the short run because we don't have to implement application
level tracking of user changes to the DB, etc. So, what's the
killer issue against authenticating to the DB?

There is no killer issue. It's actually OK if you implement application and business logic in the database and access it with a thin client (like a text console or Oracle Forms application or ...). Its not good if you want to have a middle tier and web based clients, though.

I think you are looking the wrong way: Stay true to your database vendor (this means: pay him more money) and use whatever he proposes as a client scenario for your valuable and unchangeable data interface.

If what your'e worried about is auditing, why not have the middleware do it for you (i.e: log4j or the jdk loggin api?)

It seems to me as if you don't haev much of a choice if you already have stored procedures in the DB anyways, so I don't see much of a point in going through all the hassle of using hibernate.