Solved

JNDI Datasource - Oracle Proxy Authentication

Posted on 2015-01-13
3
528 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
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 35

Accepted Solution

by:
mccarl earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

863 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

19 Experts available now in Live!

Get 1:1 Help Now