using a datasource connection pool resource with username and password supplied by user
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
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]
Re: using a datasource connection pool resource with username and password supplied by user
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
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
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]
