?
Solved

JNDI Datasource - Oracle Proxy Authentication

Posted on 2015-01-13
3
Medium Priority
?
615 Views
Last Modified: 2015-01-18
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
Comment
Question by:sysautomation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 62

Expert Comment

by:gheist
ID: 40549815
The grant of alter user should be other way around.
0
 

Author Comment

by:sysautomation
ID: 40550558
> 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
 
LVL 36

Accepted Solution

by:
mccarl earned 1500 total points
ID: 40550569
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question