Re: using a datasource connection pool resource with username and password supplied by user

2005-10-10 Thread Bob Hall
Jefferey,

Coming into this thread late, but I'm curious.
Why do you want each user to obtain a connection
to the database, effectively logging into the DB,
instead of using a connection pool with a single,
application specific, DB userid/password?

- Bob

--- [EMAIL PROTECTED] wrote:

 Thanks Doug and Chuck,
 
 I suspected as much re. the connection pool.  This
 sort of negates the
 value of it a little (for me anyway).
 
 My original plan was to go with saving the
 connection to the session once
 it was established but I had read somewhere that
 connections are not
 'serializable' and therefore the garbage cleanup in
 tomcat may kill the
 connection unexpectedly?!.?
 
 Has anyone used session tracking to store database
 connections?  If so, has
 anything bad happened?
 
 Doug to answer your question How many users are
 there going to be on the
 system at once and can the
 system handle that many open connections?...
 
 I anticipate that the production version will have
 from 20 - 30 people
 updating information (in different cities ) and
 possibly 50 or so browsing
 the database for information.  The backend database
 will be ORACLE 9i
 running in MS Server 2003 on an IBM server.  In the
 pooled connection
 implementation I allowed for 150 concurrent users. 
 I think oracle running
 on a pretty beefy application server should be able
 to handle it.  The web
 server box will also be MS server 2003 on an older
 style server so I
 suppose the only scary part will be weaknesses (if
 there are any) in Tomcat
 itself.
 
 Anyway, I will implement storing the connection in
 the session with the log
 out killing the connection.
 
 Any comments or gotchas you know about would be
 useful.
 
 Jeffery S. Eaton
 
 
 
 
 


 Opinions contained in this e-mail do not necessarily
 reflect
 the opinions of the Queensland Department of Main
 Roads,
 Queensland Transport or Maritime Safety Queensland,
 or
 endorsed organisations utilising the same
 infrastructure.
 If you have received this electronic mail message in
 error,
 please immediately notify the sender and delete the
 message
 from your computer.


 
 

-
 To unsubscribe, e-mail:
 [EMAIL PROTECTED]
 For additional commands, e-mail:
 [EMAIL PROTECTED]
 
 




__ 
Yahoo! Music Unlimited 
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: using a datasource connection pool resource with username and password supplied by user

2005-10-10 Thread Akoulov, Alexandre [IT]
Hi Jeff,

I will implement storing the connection in
 the session with the log
 out killing the connection.

what happens if the user never logs out? then your Tomcat might end up with 
quite a few open connections that it cannot close and the only way to close 
such connections would be to restart Tomcat. I guess you'll also want to 
implement the connection closure when session times out/invalidated (using 
javax.servlet.http.HttpSessionListener)


Kind regards,

Sasha.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, 10 October 2005 3:48 PM
To: Tomcat Users List
Subject: Re: using a datasource connection pool resource with username
and password supplied by user


Thanks Doug and Chuck,

I suspected as much re. the connection pool.  This sort of negates the
value of it a little (for me anyway).

My original plan was to go with saving the connection to the session once
it was established but I had read somewhere that connections are not
'serializable' and therefore the garbage cleanup in tomcat may kill the
connection unexpectedly?!.?

Has anyone used session tracking to store database connections?  If so, has
anything bad happened?

Doug to answer your question How many users are there going to be on the
system at once and can the
system handle that many open connections?...

I anticipate that the production version will have from 20 - 30 people
updating information (in different cities ) and possibly 50 or so browsing
the database for information.  The backend database will be ORACLE 9i
running in MS Server 2003 on an IBM server.  In the pooled connection
implementation I allowed for 150 concurrent users.  I think oracle running
on a pretty beefy application server should be able to handle it.  The web
server box will also be MS server 2003 on an older style server so I
suppose the only scary part will be weaknesses (if there are any) in Tomcat
itself.

Anyway, I will implement storing the connection in the session with the log
out killing the connection.

Any comments or gotchas you know about would be useful.

Jeffery S. Eaton






Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: using a datasource connection pool resource with username and password supplied by user

2005-10-10 Thread jeffery . s . eaton
Good question Bob,

This system may eventually be implemented for the government department I
work for in Queensland, Australia.  This project is a pilot one which will
involve four separate district offices in four different cities.  The
department has policies on data security which includes authenticating
individual users to a database.  As this is a requirement I can't avoid I
wanted to find a way to implement it even in the proof-of-concept stage.

User authentication can be implemented on the middle tier in Tomcat but I
don't think this will satisfy the dept. requirements.  That being the case,
my plan was to let the database decide if a user can get into the site.  If
they are authenticated to the db then they have access to the site.  One
other advantage of db authentication which will be important in this case
is the separation of database roles.  Users will have access to update only
the tables they are approved to access.

Jeffery S. Eaton




Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: using a datasource connection pool resource with username and password supplied by user

2005-10-10 Thread jeffery . s . eaton
That's right Sasha,

I will have to ensure connections are closed down automatically if the user
doesn't log out, but at this stage I will note your comment for further
research.

Thanks,

Jeffery S. Eaton




Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: using a datasource connection pool resource with username and password supplied by user

2005-10-10 Thread Bob Hall
Jefferey,

Are the uses going to be allowed to execute ad hoc
queries?  If yes, I can see why you would choose to
take the take the direct DB authentication route.

If not, then a a JDBCRealm could be used and specific
role(s) assigned to each user that would govern what
they could do in the webapp.  The isUserInRole()
method in HttpServletRequest can be used to protect
parts of the application as needed.

Info on JDBCRealms:
http://www.jajakarta.org/tomcat/tomcat5.0/en/docs/tomcat-docs/realm-howto.html

- Bob

--- [EMAIL PROTECTED] wrote:

 Good question Bob,
 
 This system may eventually be implemented for the
 government department I
 work for in Queensland, Australia.  This project is
 a pilot one which will
 involve four separate district offices in four
 different cities.  The
 department has policies on data security which
 includes authenticating
 individual users to a database.  As this is a
 requirement I can't avoid I
 wanted to find a way to implement it even in the
 proof-of-concept stage.
 
 User authentication can be implemented on the middle
 tier in Tomcat but I
 don't think this will satisfy the dept.
 requirements.  That being the case,
 my plan was to let the database decide if a user can
 get into the site.  If
 they are authenticated to the db then they have
 access to the site.  One
 other advantage of db authentication which will be
 important in this case
 is the separation of database roles.  Users will
 have access to update only
 the tables they are approved to access.
 
 Jeffery S. Eaton
 
 
 


 Opinions contained in this e-mail do not necessarily
 reflect
 the opinions of the Queensland Department of Main
 Roads,
 Queensland Transport or Maritime Safety Queensland,
 or
 endorsed organisations utilising the same
 infrastructure.
 If you have received this electronic mail message in
 error,
 please immediately notify the sender and delete the
 message
 from your computer.


 
 

-
 To unsubscribe, e-mail:
 [EMAIL PROTECTED]
 For additional commands, e-mail:
 [EMAIL PROTECTED]
 
 





__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: using a datasource connection pool resource with username and password supplied by user

2005-10-10 Thread jeffery . s . eaton
Thanks for the suggestion Bob,

I think what you are saying about realms is valid and most likely the
easiest way to enforce security.  It would be my choice if it wasn't a
corporate standards issue.  I will read up on the link you sent and see if
I can get away with it in terms of meeting with policy.

Thank you all for your input.

Jeffery S. Eaton




Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



[OT] Re: using a datasource connection pool resource with username and password supplied by user

2005-10-10 Thread Bob Hall
If the DB login requirement is removed you might
want to take a look at Apache Turbine, it supports
finer-grained access to a web app.

http://jakarta.apache.org/turbine/index.html

- Bob


--- [EMAIL PROTECTED] wrote:

 Thanks for the suggestion Bob,
 
 I think what you are saying about realms is valid
 and most likely the
 easiest way to enforce security.  It would be my
 choice if it wasn't a
 corporate standards issue.  I will read up on the
 link you sent and see if
 I can get away with it in terms of meeting with
 policy.
 
 Thank you all for your input.
 
 Jeffery S. Eaton
 
 
 


 Opinions contained in this e-mail do not necessarily
 reflect
 the opinions of the Queensland Department of Main
 Roads,
 Queensland Transport or Maritime Safety Queensland,
 or
 endorsed organisations utilising the same
 infrastructure.
 If you have received this electronic mail message in
 error,
 please immediately notify the sender and delete the
 message
 from your computer.


 
 

-
 To unsubscribe, e-mail:
 [EMAIL PROTECTED]
 For additional commands, e-mail:
 [EMAIL PROTECTED]
 
 




__ 
Yahoo! Music Unlimited 
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



using a datasource connection pool resource with username and password supplied by user

2005-10-09 Thread jeffery . s . eaton

I have set up a connection pool using the following set up:
___-

?xml version=1.0 encoding=iso-8859-1?
!--
Context configuration file for the Road Safety Audit Management System
Web App
--


Context path=/RSAMS docBase=RSAMS debug=1 reloadable=true
crossContext=true
Logger className=org.apache.catalina.logger.FileLogger
prefix=localhost_rsams_log. suffix=.txt timestamp=true/

   !--the RSAMS resource for connection pooling --
   Resource name=jdbc/rsams_oracle auth=Container
type=javax.sql.DataSource
   maxActive=150 maxIdle=30 maxWait=3
   driverClassName=oracle.jdbc.driver.OracleDriver
   url=jdbc:oracle:thin:@rockrsa:1521:rsams
removeAbandoned=true
 removeAbandonedTimeout=600 username=jseaton
password=ring2405/

/Context
_

to access this pool I use the following code:

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup(java:/comp/env);
DataSource ds = (DataSource)envContext.lookup(jdbc/rsams_oracle);
conn = ds.getConnection();
.

It all works fine:

But what I really want to do is to get a database user and password from
the user and (after validating it) write this to a session cookie.
Then when the user interacts with the database (which is all the time) the
username and password will be extracted
from the cookie and used in the following way:

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup(java:/comp/env);
DataSource ds = (DataSource)envContext.lookup(jdbc/rsams_oracle);
conn = ds.getConnection(username,password);
..

The problem is I have not been able to find a way to get this to work.

I have tried removing the user and password from the connection pool
resource (doesn't work)
I have tried this code in the calling - conn =
ds.doGetConnection(username,password) - Doesn't work.

I thought javax.sql.datasource which is the type of the resource supported
the getConnection(username, password) method but it doesn't seem to work
when
using a resource set up as above.

If anyone can point me in the right direction I will be most grateful.  I
have trawled the forums with no success as everyone seems to be
happy with the username and password being set in the context resource.


Any help I can get would be most appreciated.

Regards,


Jeffery S. Eaton




Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: using a datasource connection pool resource with username and password supplied by user

2005-10-09 Thread Caldarale, Charles R
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] 
 Subject: using a datasource connection pool resource with 
 username and password supplied by user
 
 But what I really want to do is to get a database user and 
 password from the user and (after validating it) write this 
 to a session cookie.  Then when the user interacts with the 
 database (which is all the time) the username and password 
 will be extracted from the cookie and used in the following
 way:

I don't think you can do this with a connection pool.  The connections
in the pool are opened once only, and persist for the life of the
application, not the session.  Any user (session) can end up using any
of the connections, which is why you have to configure the credentials
with the ressource.  I think if you want to authenticate on each
session, you'll have to dispense with the pool and suffer the overhead
of opening a DB connection each time.

Perhaps someone else has a better idea...

 - Chuck


THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY
MATERIAL and is thus for use only by the intended recipient. If you
received this in error, please contact the sender and delete the e-mail
and its attachments from all computers.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: using a datasource connection pool resource with username and password supplied by user

2005-10-09 Thread Parsons Technical Services
Chuck is right in that it can't be done with the standard pool that Tomcat 
has. Due simply to the fact that the pool is established before anyone has 
made a call to the server.


But what you could do is to create a connection for that user when they 
authenticate and hold on to the connection for the session. Two limitations 
are the time on the connection and the potential number of open connections. 
If you control the time the connection is held alive for the session and 
return it after some period of time to prevent time out on the database side 
or tying up a connector forever. In essence you will be creating a one 
connection pool for each session. Second is the total number of connections. 
How many users are there going to be on the system at once and can the 
system handle that many open connections?


If you come up with a different/better solution let us know.

Doug


- Original Message - 
From: Caldarale, Charles R [EMAIL PROTECTED]

To: Tomcat Users List tomcat-user@jakarta.apache.org
Sent: Sunday, October 09, 2005 11:26 PM
Subject: RE: using a datasource connection pool resource with username and 
password supplied by user




From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Subject: using a datasource connection pool resource with
username and password supplied by user

But what I really want to do is to get a database user and
password from the user and (after validating it) write this
to a session cookie.  Then when the user interacts with the
database (which is all the time) the username and password
will be extracted from the cookie and used in the following
way:


I don't think you can do this with a connection pool.  The connections
in the pool are opened once only, and persist for the life of the
application, not the session.  Any user (session) can end up using any
of the connections, which is why you have to configure the credentials
with the ressource.  I think if you want to authenticate on each
session, you'll have to dispense with the pool and suffer the overhead
of opening a DB connection each time.

Perhaps someone else has a better idea...

- Chuck


THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY
MATERIAL and is thus for use only by the intended recipient. If you
received this in error, please contact the sender and delete the e-mail
and its attachments from all computers.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: using a datasource connection pool resource with username and password supplied by user

2005-10-09 Thread jeffery . s . eaton
Thanks Doug and Chuck,

I suspected as much re. the connection pool.  This sort of negates the
value of it a little (for me anyway).

My original plan was to go with saving the connection to the session once
it was established but I had read somewhere that connections are not
'serializable' and therefore the garbage cleanup in tomcat may kill the
connection unexpectedly?!.?

Has anyone used session tracking to store database connections?  If so, has
anything bad happened?

Doug to answer your question How many users are there going to be on the
system at once and can the
system handle that many open connections?...

I anticipate that the production version will have from 20 - 30 people
updating information (in different cities ) and possibly 50 or so browsing
the database for information.  The backend database will be ORACLE 9i
running in MS Server 2003 on an IBM server.  In the pooled connection
implementation I allowed for 150 concurrent users.  I think oracle running
on a pretty beefy application server should be able to handle it.  The web
server box will also be MS server 2003 on an older style server so I
suppose the only scary part will be weaknesses (if there are any) in Tomcat
itself.

Anyway, I will implement storing the connection in the session with the log
out killing the connection.

Any comments or gotchas you know about would be useful.

Jeffery S. Eaton






Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]