Oracle Proxy Authentication and BIRT

Hi all. 
 
I'm a new user of BIRT application, actually I've started learning it and discovering mysteries around it. 
 
I have one task which I hope could be resolved within BIRT. 
I'd like to use sth which is called Oracle Proxy Authentication. This could be described briefly as following: 
 
we have connection pool on application server (let's say Tomcat) established on one user - let's call him Big Application user. But we want to run our application using different user. How to do this? 
 
Oracle 10 offers solution: Proxy Users. You can use Oracle?s proxy users to connect to Oracle through one Big Application User and still pretend to be someone else. The big advantage: select USER in the database works as expected. Auditing still works, grants and roles still work. 
 
This requires some steps to be done on Oracle Database side but this is clear for me. Below you can find Java snippet which is used for creating session using Proxy Authentication: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
private void proxyConnection() throws Exception {
Properties properties = new Properties();
properties.put("PROXY_USER_NAME", "pu_user_1");
OracleConnection conn = (OracleConnection)DriverManager.getConnection(
"jdbc:oracle:thin:pu_pool_user/pu_pool_user@localhost:1521:ora1012",
properties);
conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, properties);
printUserInfo(conn);
conn.close();
}
</pre>
Here pu_pool_user is the Big Application user for which connection pool is created. pu_user_1 is Proxy User who will run BIRT report basing on his database privileges, roles etc. Here printUserInfo should return information for this proxy user. 
 
I heard about setting sth in application context but I don't know how to do that exactly. Maybe Scripted Source will be better for that? Please tell me what could be the best solution here. 
 
Thanks in advance 
Marcin

Find more posts tagged with

Comments

JasonW

Marcin

Take a look at this blog post:
http://birtworld.blogspot.com/2008/11/birt-connection-pooling-continued-again.html
This post shows setting a connection when calling birt from a java program. If you are using the viewer you could set it in script in the beforeOpen script of the data source like:

importPackage(Packages.my.connection.package);
var conn = new MyConnectionClassGenerator();
reportContext.getAppContext().put("OdaJDBCDriverPassInConnection", conn);

This method requires you create some external java class to return the connection object.

Jason

smarcin

Hi again

I've some troubles when trying to import package with my own class which provides connection into JavaScript:

The following items have errors:

ReportDesign (id = 1):
+ There are errors evaluating script "importPackage( Packages.MyConnection );
var conn = MyConnectionClass.getConnection("proxy_user");
reportContext.getAppContext().put("OdaJDBCDriverPassInConnection", conn);":
{1}.
...
Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred: Error evaluating Javascript expression. Script engine error: ReferenceError: "MyConnectionClass" is not defined. (#2)
Script source: , line: 0, text:
__bm_beforeOpen(). See next exception for more information.
Error evaluating Javascript expression. Script engine error: ReferenceError: "MyConnectionClass" is not defined. (#2)

So it looks like BIRT is not able to see my class. Here's fragment of JavaScript:

importPackage( Packages.MyConnection );
var conn = MyConnectionClass.getConnection("proxy_user");
reportContext.getAppContext().put("OdaJDBCDriverPassInConnection", conn);

getConnection is a static method within MyConnectionClass. It returns object Connection. I have made a jar from my class and copied into:
$ECLIPSE_INSTAL/plugins/org.eclipse.birt.report.viewer_2.6.1.v20100913/birt/scriptlib
doesn't work
so next I've copied into:
$ECLIPSE_INSTAL/plugins/org.eclipse.birt.report.viewer_2.6.1.v20100913/birt/WEB-INF/lib
the same
I've also tried copying raw .class file into
$ECLIPSE_INSTAL/plugins/org.eclipse.birt.report.viewer_2.6.1.v20100913/birt/WEB-INF/classes
but this did not change it.

Please help.

CBR

What's the package in which you can find the MyConnection class?
importPackage must be used with a package name. What it does in your code is to import package MyConnection.

CBR

In addition you should read this blog post: http://birtworld.blogspot.com/2009/12/birt-designer-classpath-changes.html
It shows how to add a Java project to the Report projects classpath.

smarcin

Hi again. 
 
I fixed somehow this error while generating report. However now my connection is not override when report is deployed to Tomcat (all is good within Eclipse). I've put my jar into /tomcat/webapps/birt/scriptlib. I've also checked if BIRT_VIEWER_SCRIPTLIB_DIR parameter is not override but according to below snippet all is set to default. 
<pre class='_prettyXprint _lang-auto _linenums:0'>


<context-param>
<param-name>BIRT_VIEWER_SCRIPTLIB_DIR</param-name>
<param-value></param-value>
</context-param>

</pre>
 
Here's fragment of MyConnection package with MyConnectionClass defined. I've added into MyConnection.jar JDBC driver for Oracle so maybe this is a problem. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
package MyConnection;

import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.OracleConnection;

public class MyConnectionClass {

public static Connection getConnection(String proxyUser) {
Properties properties = new Properties();
properties.put("PROXY_USER_NAME", proxyUser);
OracleConnection conn = null;
try {
conn = (OracleConnection)
DriverManager.getConnection("jdbc:oracle:thin:pu_pool_user/****@****:1521:****", properties);
conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, properties);
//printUserInfo(conn);
//conn.close();

} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
// TODO: handle exception
}
return conn;
}
}
</pre>
 
And below snippet of beforeOpen event handler: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage( Packages.MyConnection );
var conn = new Packages.MyConnection.MyConnectionClass.getConnection("READ");
reportContext.getAppContext().put("OdaJDBCDriverPassInConnection", conn);
</pre>
 
To summarize: my connection works fine while checking via Eclipse (Report Design perspective), but is somehow omitted while deploying report to Tomcat.

smarcin

Plese help! My deadline is coming.

JasonW

Where did you put the oracle driver?
Do you get anything in the log file?

Jason

smarcin

Hi JasonW

I've got my Java Project. Below is a directory hierarchy for this project:

[.settings]
|--org.eclipse.jdt.core
[bin]
|--[MyConnection]
|----MyConnectionClass.class
[src]
|--ojdbc6_g.jar
|--[MyConnection]
|----MyConnectionClass.class
|----MyConnectionClass.java
.project
.classpath
oracle_proxy.rptconfig
oracle_proxy.rptdesign

So I've put ojdbc6_g.jar inside of src (it's also added into build path) and made a simple JAR based on the src - just like here: http://www.vogella.de/articles/EclipseBIRT/article.html#birtdeploying_tomcat

The funny thing about whole report is that it's no matter my JDBC driver is inside of JAR which I put to SCRIPTLIB, I need to put it also into standard path for JDBC drivers: WEB-INF/platform/plugins/ and so on.

Regarding logs - I have only empty files inside of tomcat/webapps/birt/logs but I've never turn logging on so maybe that's the reason.

Many thanks in advance.

smarcin

OK, forget about this. Finally after 2 or maybe 3 weeks fighting against BIRT, Tomcat, Eclipse finally it works as it should. I've defined resource on Tomcat and used connection pool within MyConnectionClass.