How do you guys design your database security for web-applications,
specifically were a connection pool is in use?
Making every end-user a database user is problematic in my environment, but if
that's the only way you see to do it, go ahead and say so.
Short of that, my current "best" approach (which I'm not super happy with) is
to do the following:
1) Make a database account for the application to connect to. The
password to this account is assumed to be well-known/totally compromised.
2) Grant execute on the application packages to the database account (and
grant nothing else!)
3) Make an application context that only the application's login procedure
(in the database) has access to set.
4) Have every public API in the application packages check (first thing)
whether the application context has been set (i.e., whether end-user login
credentials have been presented to the database layer).
So, when the application connects to the database, it supplies the end-user's
credentials, which sets the database context, which tells the APIs that it's OK
for them to do their work. This prevents people from bypassing the security by
connecting to SQL*Plus and issuing API calls.
All that works just fine. What I hate is that, because of the middle-tier
connection pool, the middle tier code has to call the login procedure every
time it gets a connection. And that means, it has to keep the login
credentials around somewhere (temporarily, anyway) because it can hardly
require the end-user to constantly re-enter them. I guess I could maybe create
some sort of "security ticket" to serve as a proxy for the actual
credentials... I haven't gone that far yet.
But, I feel like I'm probably missing at least a few best-practices and/or
powerful features to do all this for me.
Are there any application security experts out there who want to chime in?
Thanks in advance!
Matt
