• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

JNDI Datasource - Oracle Proxy Authentication

I am using a Java application running inside Tomcat to integrate Oracle Application Express (Apex) with Jasper Reports.
It is required to create a JNDI datasource in /apache-tomcat-7.0.35/conf/Catalina/localhost/JasperReportsIntegration.xml

It works good if I provide normal schema name/password for the Oracle server. However I want to use Oracle Proxy Authentication for some reason. When I define Username as MYPOWERUSER[MYNORMALUSER] (as required for Proxy Authentication) I see following error in catalina.out:

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-01017: invalid username/password; logon denied

Open in new window


However this method of authentication works without a problem with SQLPlus e.g. If I write:

sqlplus MYPWERUSER[MYNORMALUSER]/MYPASSWORD@myoracleserver.com:1521/XE

Open in new window



 
             <Resource name="jdbc/MYDATASOURCE" auth="Container" type="javax.sql.DataSource"
              driverClassName="oracle.jdbc.OracleDriver"
              maxActive="20" maxIdle="10" maxWait="-1"
              initialSize="4" minIdle="3" validationQuery="select user from dual"
              testWhileIdle="true" testOnBorrow="true" timeBetweenEvictionRunsMillis="300000"
              numTestsPerEvictionRun="100" minEvictableIdleTimeMillis="10000"

              url="jdbc:oracle:thin:@myoracleserver.com:1521:XE"
              username="MYPWERUSER[MYNORMALUSER]"
              password="MYPASSWORD"
              />

Open in new window



Tomcat/Jasper works fine if I make JNDI without Proxy authentication as following:


 
            <Resource name="jdbc/MYDATASOURCE" auth="Container" type="javax.sql.DataSource"
              driverClassName="oracle.jdbc.OracleDriver"
              maxActive="20" maxIdle="10" maxWait="-1"
              initialSize="4" minIdle="3" validationQuery="select user from dual"
              testWhileIdle="true" testOnBorrow="true" timeBetweenEvictionRunsMillis="300000"
              numTestsPerEvictionRun="100" minEvictableIdleTimeMillis="10000"

              url="jdbc:oracle:thin:@myoracleserver.com:1521:XE"
              username="MYNORMALUSER"
              password="MYNORMALUSERPASSWORD"
              />

Open in new window



I have already granted required privileges such as:

ALTER USER MYNORMALUSER GRANT CONNECT THROUGH MYPWERUSER;

Open in new window


This integration kit was developed by some other company namely Opal Consulting: http://opal-consulting.de/apex/f?p=20090928:…



I would be thankful if someone can help me with creating JNDI datasource with Proxy Authentication.
0
sysautomation
Asked:
sysautomation
1 Solution
 
gheistCommented:
The grant of alter user should be other way around.
0
 
sysautomationAuthor Commented:
> ALTER USER MYNORMALUSER GRANT CONNECT THROUGH MYPWERUSER;

Why would SQL Plus work if the above isn't correct?

Also see http://oracle-base.com/articles/misc/proxy-users-and-connect-through.php
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Basically, you can't... at least not like the way you are trying to do it. The reason that MYPWERUSER[MYNORMALUSER] works in SQLPlus and not elsewhere is because that is a special SQLPlus syntax. The JDBC driver doesn't understand it.

So, what can you do about it? Well, the JDBC driver *does* support proxy authentication but only in code (from what I can tell). Refer: http://docs.oracle.com/cd/B28359_01/java.111/b31224/proxya.htm     And I haven't use Jasper too much but I am pretty sure that there wouldn't be a hook for you to make the right calls to transform a normal connection into your proxied session. The only (remote) possibility is that you can write your own JNDI factory to retrieve a normal DataSource and then wrap it so that it sets connections up as proxied connections and then return the wrapped DataSource to Jasper.

It seems that it has been done before for tcServer (http://static.springsource.com/projects/tc-server/6.0/admin/cadmresourcelink.html) but a quick look for the source code for that OracleProxyDataSourceFactory class didn't turn anything up. Maybe you can find it, or at least find the binary and use it as is in Tomcat, otherwise it might not be to difficult to go about writing your own.

Sorry, that the news isn't better! :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now