Solved

JNDI Datasource - Oracle Proxy Authentication

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

691 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