Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database Connection and Session

 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am developing a web based system and I cannt use Connection Pool in it. Because every user who will access the system has some database role and connection must have to authenticate. So that Connection object will not be generic but it will be specific to the user who is using it. So is it possible that I'll create a Connection and put it into that user's Session and use it. But problem is that if that user will close his browser, how can i close Database connection???/ Shall i use HttpSessionListner or does it work?

Thanx
 
Senthil B Kumar
Ranch Hand
Posts: 160
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if the number of database roles are fixed, why cant you try creating a custom connection pooling utility which will pool connections of each roll.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanx a lot for your suggestion, and ya i thought about that, but although it is fixed at the moment but it could be changed and obviously I dont want too much dependency. Because Its in hand of Database administrator and he can change role whenever he want.
And still there are approx 15 roles. And i dont think so that it would be a gud idea to create 15 seperate pools.
Please comment about HttpSessionListener issue.

Thanx,
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Firstly: please reduce you usage of comtractions (thanx, cannt, gud, ya etc). They make posts harder to understand and make it harder for us to help you and harder for others to follow up.

Thanks, Dave.

Back to your question: The session listener sound like it will work, but I'd try to avoid putting the connection on the session. Having a connection broker which serves specific Connections rather than just pooling should be easy to implement and doesn't limit the distribution of you application. ie if you find you suddenly need multiple servers you'd have to rewrite your session management, or you could push the broker to an EJB tier and life would continue with a little indirection.

Just my thoughts, but then I'll argue all day against putting unnecessary data on the session
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you please elaborate and explain how to use that Connection broker in this scenario. Does that mean I have to write seperate broker for each type of connection. And by the way I totally agree with you About Sessions.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is one major problem after putting Connection in Session is, if user will close the browser, Session will not be closed thus Connection will remain unclosed.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's what the session listener is for - eventually the session will timeout on the server, and when that happens the connection will be closed. Not prefect but atleast it gets closed.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahsan,
I strongly caution you against putting a connection object in the session. Connections are expensive resources and you would have them open much longer than necessary. If you have enough users, this would slow down your system.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if i will not put Connection into Session then what shall i do? Create Connection Pool for each type of connection ( i.e for seperate db role )
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, you have a class which follow the following contract:

When asked for a Connection, the user must provide a session ID and the broker provides a connection for this session and caches it internally.
When the session expires (as notified by a listener), the connection is closed and removed from the internal cache.

This assumes the user is only allowed a single connection per session and single session per user. You can (and should) consider managing pooling per session and synchronisation of resources.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But what will happen if user will close his browser ? SessionListener cannt do anything in that case, and it'll not raise any event. And it'll let Database Connection unclosed.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the user closes their browser the session will still timeout, eventually. Then the listener will be informed and the connection can be closed. It won't happen immediately, but atleast it will happen.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In order to write a Session Listener, after writing Listener class, is it necessary to add <listener> info in web.xml.
And Do i need to add filters as well in my application because Iv read somewhere that the <listener> element must directly follow the <filter> and <filter-mapping> elements and directly precede the <servlet> element.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ahsan Jamil:
In order to write a Session Listener, after writing Listener class, is it necessary to add <listener> info in web.xml.
And Do i need to add filters as well in my application because Iv read somewhere that the <listener> element must directly follow the <filter> and <filter-mapping> elements and directly precede the <servlet> element.


Yes you have to add the listener element in your web.xml.
And no its not necessary to use filter.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David O'Meara:
When asked for a Connection, the user must provide a session ID and the broker provides a connection for this session and caches it internally.
When the session expires (as notified by a listener), the connection is closed and removed from the internal cache.


David,
As Jeanne said, "Connections are expensive resources and you would have them open much longer than necessary. If you have enough users, this would slow down your system".

I think you are suggesting somewhat like the same thing, not in the session but internally for a particular session. Plus closing the connection on session time-out, it is the same as well. No matter you cache the connection into session or internally for a session, you are doing it on the server. Which may result in performance lack.

Correct me if wrong, please.
thanks.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, It will definitely be a Performance issue, and thats what im trying to find? SOLUTION OF THAT ISSUE?
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not implementing role just in your app? Because if you do it via DB then you have to implement it in your app too.

Otherwise, right now my thought is with kolkata prince.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
another solution (a variation on kolkata's above) is to have a general Connection pool, but decorate it with roles.

What this means is that when users request a Connnection from your decorator, you get a connection from the internal pool, run SQL against it to allow the given role, then return the Connection. When the Connection is returned to the wrapper, you remove that role and return the Connection to the underlying pool.

What's the special SQL? No idea. I've done it like this a long time ago, but your DBAs should be able to help.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David O'Meara:
another solution (a variation on kolkata's above) is to have a general Connection pool, but decorate it with roles.


Agreed. But your DB roles and Application roles would be two different things. This way you can't map your DB roles in your app. Though, dont know if you really want to map those.
[ February 18, 2005: Message edited by: Adeel Ansari ]
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If i'll creat ea pool like that, then if user A of role X will demand a connection, he will get connection of that specific role but when user B of role Y will demand same connection from pool, that pooled connection cant serve because it has created on the basis of role X.

And I cannt force DBA to change role because its a legacy system.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ahsan Jamil:
If i'll creat ea pool like that, then if user A of role X will demand a connection, he will get connection of that specific role but when user B of role Y will demand same connection from pool, that pooled connection cant serve because it has created on the basis of role X.

And I cannt force DBA to change role because its a legacy system.


No dont change the role buddy. Well, if user B needs role X and assigned role is Y, it means he/she need some privileges in role X. So just revoke that role Y and assign him/her role X. And if he/she needs both the roles several times then assign both the roles X and Y to user B. But then keep in mind you have to make it many to many relationship between users and role.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One more solution. Associate two or more users to the same person.
Are you getting me??
 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ahsan Jamil:
But what will happen if user will close his browser ? SessionListener cannt do anything in that case, and it'll not raise any event. And it'll let Database Connection unclosed.


If you have a static frame in your application, you can keep track of window unload event and close the DB connection if the window gets unloaded.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Adeel Ansari:


No dont change the role buddy. Well, if user B needs role X and assigned role is Y, it means he/she need some privileges in role X. So just revoke that role Y and assign him/her role X. And if he/she needs both the roles several times then assign both the roles X and Y to user B. But then keep in mind you have to make it many to many relationship between users and role.


ADEEL ! You are not getting my point. Basically Every Connection at the time of creation needs to know about Userid,Password,Role(Optional) of Database User who is going to connect with Database.

if im a user A with role DATAADMIN and create a connection and pool it. And then a user B with role DATAUSER tries to get connection from that pool and will get already created that connection (by user A) B can use this connection but can use all rights of DATAADMIN. Its not possible to change Role of connection on runtime(as far as i know). So i want to know of this solution how can i sort this out. Someone told create connection for userA and put in session and use this connection for each request from user A. and so on. but it'll not be a good idea as far as performanc is concerned.
thanx
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How many users will be logged into the application simultaneously? The advantage of a connection pool is that your needs scale based on simultaneous transactions, not users. Timedout users will affect this; make sure to count them in your estimte.

With this number you can determine if there will be problems. How many open connections are you allowed? DBAs tend to be fussy about hundreds of open connections. You may have to pay more money than the business wants to spend.

Are rights granted strictly to roles? Is it possible to provide your own user login and role mapping tables and use the thread-pool-per-role idea above? You'd create a user per role with system-controlled passwords that your application would use to perform user actions. Your tables would be queried to determine which pool to use for that user and store its identifier in the user's session.

If you have a large number of roles (dynamic should be easy enough to handle), the pools won't be maximized, but you can configure them individually with information stored in your role table. It could get messy if you use multiple roles per user as you'd need to create a separate role account for each unique combination of roles you may assign.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahsan,

No I am not saying to assign a new role or revoke an existing role at runtime. I was saying that if user B needs role which is not assigned to him/her. Then he/she needs to make the request to admin that please I need this kinda privileges. But now its something else, because I misunderstood your point.

Infact, I haven't got you still. How can User B, with role DATAUSER, gets the connection as DATAADMIN. At the time of authentication you are checking username, password and the role. So, just give him/her the connection from that role-specific pool. Moreover, for authentication use a general connection.

Are you getting me?
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David Harkness:
Your tables would be queried to determine which pool to use for that user and store its identifier in the user's session.


Yeah. Or when you are authenticating user, queried out his/her ROLE_NAME and bind that roleName with the user's session. Obviously, when one role per user.

In case of many roles per user. You can ask at the time of login, "with which role you wanna login". Like we do in Oracle or other Databases

conn as sysdba, or
conn as sysoper
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Adeel Ansari:
Ahsan,

No I am not saying to assign a new role or revoke an existing role at runtime. I was saying that if user B needs role which is not assigned to him/her. Then he/she needs to make the request to admin that please I need this kinda privileges. But now its something else, because I misunderstood your point.

Infact, I haven't got you still. How can User B, with role DATAUSER, gets the connection as DATAADMIN. At the time of authentication you are checking username, password and the role. So, just give him/her the connection from that role-specific pool. Moreover, for authentication use a general connection.

Are you getting me?


Yes i got it, but I'll have approx 15-20 roles wich means iv 2 write so many role-specific pools? does it make sense?

And yes we will have a seperate database for authentication of user, We have user,role and user-role(resolving many-many) table their.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ahsan Jamil:
Yes i got it, but I'll have approx 15-20 roles wich means iv 2 write so many role-specific pools? does it make sense?


Ok. Now it again became the original question .

Yes if you really need connection pooling.
Or
Provide general connections, using one pool, and implement the role stuff on UI components. Although you need to do it either way.
 
Ahsan Jamil
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes! Connection Pooling is definitely required because of load on system, but still maintaining role based auth is priority
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ahsan Jamil:
Yes! Connection Pooling is definitely required because of load on system, but still maintaining role based auth is priority
Then it sounds to me like your choices are limited. You need to create a separate pool for each role or roll your own pool that tracks the role for each connection and makes them available based on role.

If you must have one connection per user (instead of multiple shared per role), you're not pooling anymore. Pooling implies sharing resources among requestors in order to lower your resource requirements. No sharing, no pooling.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic