In a typical site, there will be a large number of users logged in, who all have sessions in the application (especially with long session timeouts), but not that many are actually active at the same time. If we could disconnect the connections for uses who are logged in but idle, and then reconnect when the users become active, then we'd get by using only as many connections as we expect to have simultaneously active users. That number is likely to be much lower, and a site that has 1000 users actively clicking on things in a, say, 5-minute period should probably run Oracle on Solaris anyway.
Basically we get a most-recently-used cache the size of the number of connections that our database and OS can provide for us, and the way we use it looks pretty much the same as a connection pool: get a connection, use it, and release it. The difference is that instead of blocking in the get until there is a connection for us, we may be reconnecting to the database (possibly after waiting, in case the active set is full and we're thrashing).
In a traditional setup, the web app of course knows how to log in to the database, since it's always using the same username etc. In the setup I'm proposing, only the users themselves know how to log on to the database. The web app could technically store the passwords, but that's madness from a security perspective. Cleartext passwords are to be discarded as soon as possible. Thus, we have to try some other way to log on to the database.
One solution that's pretty much made for this scenario is Kerberos. When a user logs in to the site, authenticate to the Authentication Service, get a Ticket-Granting Ticket, and store that one. Whenever you need to connect to the database on the behalf of the user, use the user's TGT to get a ticket for the database. Should work in theory, but in practice it can be a nightmare both to set up and to get working in Java. It's possible that this would be smoother in Windows, where I imagine you could put the users in Active Directory and be done with the first part, but whether it will work still depends on the Kerberos APIs and how your database drivers use them.
So, if we're passing on Kerberos, we can go for PAM or just roll our own solution. With PAM we could build a module that will let us use one-time passwords, so that we authenticate with the password via PAM on login, get a cookie, and then use that cookie when reconnecting. On logout, the cookie gets invalidated.
For my prototype, I've skipped even that and gone for pre-salted passwords. What I do is that before I send passwords to the database (including on enrollment), I hash the passwords together with a random per-user salt. That salted password is what the database sees when authenticating the users, and never the cleartext one. The salted passwords are then stored in the sessions of the users, and used when reconnecting. Thus, cleartext passwords are never stored, so an attack that would show the contents of session variables of other users would not immediately give a way passwords that the users potentially could be using for other sites.
Now that the prototype implementation can have very large numbers of simultaneous users, the real load testing and performance comparison of using a view filter versus working directly on the tables can commence.
