Return-Path: <EPugh@upstate.com>
Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Delivered-To: mailing list torque-user@db.apache.org
Received: (qmail 17506 invoked from network); 25 Mar 2003 00:50:28 -0000
Received: from unknown (HELO hqexch01.upstate.com) (205.160.101.145)
  by daedalus.apache.org with SMTP; 25 Mar 2003 00:50:28 -0000
Received: by hqexch01.upstate.com with Internet Mail Service (5.5.2653.19)
	id <HMR46JRH>; Mon, 24 Mar 2003 18:51:16 -0500
Message-ID: <4C47F66C98B28B4F903120350DEF05B06CB0E4@hqexch01.upstate.com>
From: EPugh@upstate.com
To: torque-user@db.apache.org
Subject: RE: Getting access to the db connections
Date: Mon, 24 Mar 2003 18:51:16 -0500
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2653.19)
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C2F260.42383FC0"
X-Spam-Rating: daedalus.apache.org 1.6.2 0/1000/N

------_=_NextPart_001_01C2F260.42383FC0
Content-Type: text/plain;
	charset="iso-8859-1"

Humm...

I know you can provide a sql statement to "test" the connection.  So,
theoretically, you could exec a stroed proc.. via something like:  exec
MySproc

Here is my setup:

Torque.props:
torque.database.default.adapter=mssql
torque.dsfactory.default.factory=org.apache.torque.dsfactory.JndiDataSourceF
actory
torque.dsfactory.default.jndi.path=java:comp/env/jdbc/fortius

Web.xml:
    <resource-ref>
      <description>
        Resource reference to a factory for java.sql.Connection
        instances that may be used for talking to a particular
        database that is configured in the server.xml file.
      </description>
      <res-ref-name>
        jdbc/fortius
      </res-ref-name>
      <res-type>
        org.apache.torque.pool.TorqueClassicDataSource
      </res-type>
      <res-auth>
        Container
      </res-auth>
    </resource-ref>  

Tomcat server.conf:
<GlobalNamingResources>
  <Resource name="jdbc/fortius" scope="Shareable"
type="javax.sql.DataSource"/>
    <ResourceParams name="jdbc/fortius">
      <parameter>
        <name>validationQuery</name>
        <value>SELECT 1</value>
      </parameter>
      <parameter>
        <name>url</name>
 
<value>jdbc:microsoft:sqlserver://cuzco:1433;DatabaseName=fortius;SelectMeth
od=cursor</value>
      </parameter>
      <parameter>
        <name>password</name>
        <value>boo!</value>
      </parameter>
      <parameter>
        <name>maxActive</name>
        <value>8</value>
      </parameter>
      <parameter>
        <name>maxWait</name>
        <value>5000</value>
      </parameter>
      <parameter>
        <name>driverClassName</name>
        <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
      </parameter>
      <parameter>
        <name>username</name>
        <value>SilverUserLogin</value>
      </parameter>
      <parameter>
        <name>maxIdle</name>
        <value>2</value>
      </parameter>
    </ResourceParams>
</GlobalNamingResources>


-----Original Message-----
From: Steve Lukshides [mailto:lukshide@us.ibm.com]
Sent: Monday, March 24, 2003 6:48 PM
To: Turbine Torque Users List
Subject: RE: Getting access to the db connections







Eric, thanks for your input.  We're actually using WebSphere.  I assume
Torque can use WebSphere's connection pool, but there is pretty scant
documentation on Torque in general and none that I've found that is
WebSphere specific.  When you say that the container sets up the pool, does
Tomcat have the ability to call a stored procedure for each connection
established?

Steve




|---------+---------------------------->
|         |           EPugh@upstate.com|
|         |                            |
|         |           03/24/2003 06:23 |
|         |           PM               |
|         |           Please respond to|
|         |           "Turbine Torque  |
|         |           Users List"      |
|         |                            |
|---------+---------------------------->
 
>---------------------------------------------------------------------------
----------------------------------------------------------|
  |
|
  |       To:       torque-user@db.apache.org
|
  |       cc:
|
  |       Subject:  RE: Getting access to the db connections
|
  |
|
 
>---------------------------------------------------------------------------
----------------------------------------------------------|




One thing you could use in connection pooling defined by the Tomcat
container (if that is what you are using).  Then it means the container
setsup the pool, and your app doens't have a username/password, just a
reference to the pool setup by the container.

Then it would be up to the container to provide proper security of the
username and password.

What you have sounds pretty icky and resource intensive...  Security by
obfuscation is typically not real secure!

Eric

-----Original Message-----
From: Steve Lukshides [mailto:lukshide@us.ibm.com]
Sent: Monday, March 24, 2003 6:25 PM
To: torque-user@db.apache.org
Subject: Getting access to the db connections






Hi All,

Our Oracle DB Admin insists that we run a stored procedure for each
connection made to the database.  I'm not an Oracle DBA so forgive me if
this explanation is less than clear.  As a security measure he expects
every connection made to the database to call a stored procedure to set the
application's role.  The idea is to prevent someone from logging on to the
DB through SQL Plus with the user name and password that Torque uses and
gain rights to use the database.  If someone were to discover the
username/password that Torque uses and then logs on through SQL Plus they
would not gain any rights until the SP is executed, which they are not
likely to know and will be difficult to discover.

So my question is, how can I get at each connection that Torque
establishes, preferably at the time it is established, and use it to
execute the SP on?  Is this practical?  Is there a better way to do this?

Thanks,
Steve Lukshides

Sr. I/T Specialist, IBM Global Services
1475 Phoenixville Pike, West Chester, PA 19380
Phone: 610-989-0340
Voice Mail: 610-578-2385  IBM Tie Line: 873-2385
Email: lukshide@us.ibm.com


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org




---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

------_=_NextPart_001_01C2F260.42383FC0--