Solved

JNDI Datasource - Oracle Proxy Authentication

Posted on 2015-01-13
3
518 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
3 Comments
 
LVL 61

Expert Comment

by:gheist
Comment Utility
The grant of alter user should be other way around.
0
 

Author Comment

by:sysautomation
Comment Utility
> 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 35

Accepted Solution

by:
mccarl earned 500 total points
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now