Database (User) Connection per Http Session (User), how?
Database (User) Connection per Http Session (User), how?
February 19, 2012 - 18:34
Hi All,
I have a mandatory design constraint that a database connection be the same
user as the http session user (for EJB 3.0 entities). AFAIK I can't use a
glassfish configured datasource, can anyone help explain how I can safely,
securely and reliably provide one database connection per http session to
the container/manager (within the appropriate session/user context)?
Thanks in advance!
--AH
Re: Database (User) Connection per Http Session (User), how?
February 21, 2012 - 17:48
Hi Nick,
Thanks so much for your answer, I'll keep hoping I can find an alternative
solution but I feel I have exhausted all known options.
We have limited users, which is lucky. It would also be appropriate to run
very short session times so that connections expire sooner.
Hopefully I can get the current user via CDI (currently using seam solder)
http://docs.jboss.org/seam/3/3.1.0.Final/reference/en-US/html/injectable...
Agreed, connection pools are not applicable here.
Authentication will also be a challenge as you point out. Authentication is
really "can you make a database connection?". I have more to think about
here.
THANKS HEAPS!
On Wed, Feb 22, 2012 at 2:09 AM, wrote:
> This may be possible, but it opens up all sorts of problems, particularly
> if
> you have more than a handful of users. Are you sure there isn't another way
> to fulfill your requirements?
>
> You should be able to use a glassfish datasource but you'd need to provide
> your own custom DataSource implementation that creates a connection for the
> current user. You'll need some way of passing the current user from the EJB
> to the DataSource (ThreadLocal?).
>
> Connection pooling won't help as you'll have a single connection per user.
> You probably won't want to keep the connections open/cached for the whole
> session if you have more than a few users, so you'll end up creating a new
> connection for every query.
>
> The next issue is the users' password which you'll need to create the DB
> connection. Are you using container security? If so I don't think there's
> an
> easy way to get it. The only thing I can think of is to write a custom
> authentication module (JSR-196) that stores the password in the user
> session,
> then you can get it to the DataSource from there, but you'll need to be
> very
> careful that you're not opening up any security holes.
>
> I'd say find a way to avoid having to do this if you can.
>
> Nick
>
>
>
>
> --
>
> [Message sent by forum member 'nickwi']
>
> View Post: http://forums.java.net/node/**883638
>
>
>
Re: Database (User) Connection per Http Session (User), how?
February 21, 2012 - 05:24
I would be very interested in this as well. The only strategy I have come up with so far is to programatically provision a connection pool and then tear it down at the end of their session.
This may be possible, but it opens up all sorts of problems, particularly if you have more than a handful of users. Are you sure there isn't another way to fulfill your requirements?
You should be able to use a glassfish datasource but you'd need to provide your own custom DataSource implementation that creates a connection for the current user. You'll need some way of passing the current user from the EJB to the DataSource (ThreadLocal?).
Connection pooling won't help as you'll have a single connection per user. You probably won't want to keep the connections open/cached for the whole session if you have more than a few users, so you'll end up creating a new connection for every query.
The next issue is the users' password which you'll need to create the DB connection. Are you using container security? If so I don't think there's an easy way to get it. The only thing I can think of is to write a custom authentication module (JSR-196) that stores the password in the user session, then you can get it to the DataSource from there, but you'll need to be very careful that you're not opening up any security holes.
I'd say find a way to avoid having to do this if you can.
Nick