Teiid Translator for resetting Oracle Property

[tanmoypalit]

Hi,

I am trying to build a Translator which will reset the a Oracle Property in existing connection.

Here is piece of code I am using for that:

  OracleConnection oConn = null;

  if (connection instanceof WrappedConnection) {

     try {

                oConn = (OracleConnection)((WrappedConnection)connection).getUnderlyingConnection();

          } catch (SQLException e) {

                   throw new TranslatorException(e, "failed to unwrap connection"); //$NON-NLS-1$

     }

  }

  if (oConn != null ) {

  try {

         Properties OProperties = new Properties();

         OProperties.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER, "xxx");

         oConn.applyConnectionAttributes(OProperties);

         LogManager.logDetail(LogConstants.CTX_CONNECTOR, "set to xxx:"); //$NON-NLS-1$

         } catch (SQLException e) {

              throw new TranslatorException(e);

    }

  }

  return connection;

But the WrappedConnection seems to return the a Physical Connection and could not set the property, which results in the following error:

Error Code:99999 Message:This method is only implemented in logical connections

Any ideas/workaorunds?

Thanks for the help!

Tanmoy

[rareddy]

Tanmoy,

I believe that error is coming from the Oracle driver. Not sure what is a "logical" connection in their scenario?

Ramesh..

[rareddy]

According to this http://docs.oracle.com/cd/B28359_01/java.111/b31224/concache.htm

"The connection cache uses the concept of physical connections and logical connections. Physical connections are the actual connections returned by the database 
and logical connections are containers used by the cache to manipulate physical connections. You can think of logical connections as handles. The caches always return 
logical connections, which implement the same interfaces as physical connections."

I guess, you need to set "ConnectionCachingEnabled" property on the data source to create logical connections, try setting that property on the connection properties in standalone-teiid.xml file

HTH

Ramesh..

[tanmoypalit]

I tried to set the connection property. but it did not work. Still getting the same error.

Added the following in data source configuration:

<connection-property name="ConnectionCachingEnabled">true</connection-property>

Error:

20:16:00,249 WARN  [CONNECTOR] Connector worker process failed for atomic-request=0B2dsi5T4RSe.0.0.0

org.teiid.translator.TranslatorException: Error Code:99999 Message:This method is only implemented in logical connections

        at org.teiid.translator.jdbc.oracle.OracleOciSSOExecutionFactory.getSSOConnection(OracleOciSSOExecutionFactory.java:107)

        at org.teiid.translator.jdbc.oracle.OracleOciSSOExecutionFactory.createResultSetExecution(OracleOciSSOExecutionFactory.ja

        at org.teiid.translator.jdbc.JDBCExecutionFactory.createResultSetExecution(JDBCExecutionFactory.java:56)

        at org.teiid.translator.ExecutionFactory.createExecution(ExecutionFactory.java:251)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:231)

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

        at java.util.concurrent.FutureTask.run(FutureTask.java:138)

        at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:244)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

        at java.lang.Thread.run(Thread.java:662)

Caused by: java.sql.SQLException: This method is only implemented in logical connections

        at oracle.jdbc.driver.PhysicalConnection.applyConnectionAttributes(PhysicalConnection.java:3987)

        at oracle.jdbc.driver.T2CConnection.applyConnectionAttributes(T2CConnection.java:47)

        at org.teiid.translator.jdbc.oracle.OracleOciSSOExecutionFactory.getSSOConnection(OracleOciSSOExecutionFactory.java:104)

        ... 16 more

I know the error is coming from Oracle Driver but it seems that JBoss is wrapping the connection to org.jboss.resource.adapter.jdbc.jdk6.WrappedConnectionJDK6 and underlying connection is returning a physical connection.

-Tanmoy

[rareddy]

Just want to make sure you set the

<connection-property name="ConnectionCachingEnabled">true</connection-property>

property on not using a OracleDriver based connection configuration but on OracleDatasource based configuration?

Ramesh..

[tanmoypalit]

Ok I think I am not doing it right.

I need to create a connection to Oracle using OS authentication (username was passed to the security context). I am using the following instruction from

http://docs.oracle.com/cd/E18283_01/java.112/e16548/clntsec.htm

Check the last part about OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER

JDBC Code Using OS Authentication

Now that you have set up OS authentication to connect to the database, you can use the following JDBC code for connecting to the database:

String url = "jdbc:oracle:thin:@oracleserver.mydomain.com:5521:dbja" Driver driver = new oracle.jdbc.OracleDriver(); DriverManager.registerDriver(driver); Properties props = new Properties(); Connection conn = DriverManager.getConnection( url, props); 

The preceding code assumes that it is executed by p_floyd on the client machine. The JDBC drivers retrieve the OS user name from the user.name system property that is set by the JVM. As a result, the following thin driver-specific error no longer exists:

ORA-17443=Null user or password not supported in THIN driver 

Note:

By default, the JDBC driver retrieves the OS user name from the user.name system property, which is set by the JVM. If the JDBC driver is unable to retrieve this system property or if you want to override the value of this system property, then you can use the OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER connection property.

Is it possible to apply this property to an active connection in Teiid Translator?

-Tanmoy

[rareddy]

Not without extending the Oracle Translator or writing a Delegate Translator.

[shawkins]

I'm a little late to this, but it seems like the Oracle mechanism you are using is only for the initial connection.  It also looks like the applyConnectionAttributes is only for when you are using OracleDataSource connections.  Do you have a reference that applyConnectionAttributes will trigger a reauthentication?

Generally JBoss connection pooling is capable of reauthentication via plugins, but I not sure how it is configured.  The built-in MySQL plugin will use a changeUser call, while the Oracle version uses a proxy user - ironjacamar-oracle-reauth/src/main/java/org/jboss/jca/adapters/jdbc/extensions/oracle/OracleReauthPlugin.java at master …

So someone related to JBoss JCA and Ironjacamar may have seen a request similar to this and could help you through it.  As long as there is a mechanism, as Ramesh says it can always be done in Teiid through some straight-forward extensions as well.

You should also be able to simplify things like:

if (connection instanceof WrappedConnection) {

     try {

                oConn = (OracleConnection)((WrappedConnection)connection).getUnderlyingConnection();

          } catch (SQLException e) {

                   throw new TranslatorException(e, "failed to unwrap connection"); //$NON-NLS-1$

     }

  }

Without using the JBoss specific code to just:

oConn = connection.unwrap(OracleConnection.class);

[tanmoypalit]

Hi Steve/Ramesh,

Oracle proxy already works for me in the Translator. But the difference is that in case of proxy, a new session is created within the active connection but in case of OS authentication the user needs to be re-authenticated.

I am still not sure whether this is possible or not. according to the following post it may not be possible:

http://stackoverflow.com/questions/15483356/connection-cannot-be-cast-to-oracle-jdbc-oracleconnection (reply 2)

Also I was wondering in Teiid how pool connection re-authenticates in case of CallerIdentityLoginModule security authentication as it is also re-authenticating an existing pool connection with the username/password from the context?

We are in the process of migrating our servers from Teiid 7.7 to Teiid 8.3 (or 8.4)?. So I am currently on 7.7 but that will change soon.

Following is the method I added to the new translator which I am using to reset the connection:

public Connection getSSOConnection(Connection connection, ExecutionContext executionContext) throws TranslatorException {

  OracleConnection oConn = null;

  if (connection instanceof WrappedConnection) {

  try {

  oConn = (OracleConnection)((WrappedConnection)connection).getUnderlyingConnection();

  } catch (SQLException e) {

  throw new TranslatorException(e, "failed to unwrap connection"); //$NON-NLS-1$

  }

  }

  if (connection instanceof OracleConnection) {

  oConn = (OracleConnection)connection;

  }

  String SSOUser = getUserName(executionContext.getSubject(), defaltSSOUser());

  if (oConn != null && SSOUser != null) {

  try {

  Properties SSOProperties = new Properties();

  SSOProperties.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER, SSOUser);

  oConn.applyConnectionAttributes(SSOProperties);

  LogManager.logDetail(LogConstants.CTX_CONNECTOR, "Switched to SSO User:" + SSOUser); //$NON-NLS-1$

  } catch (SQLException e) {

  throw new TranslatorException(e);

  }

  }

  return connection;

  }

private static String getUserName(Subject subject, String default) {

  Set<java.security.Principal> users = subject.getPrincipals(java.security.Principal.class);

  if ((users != null) && (users.size() > 0)) {

  for (java.security.Principal user : users) {

  if (user.getName() != null) {

  return user.getName();

  }

  }

  }

  return default;

  }

But I get the logical connection error.

My ds.xml is like the following:

<connection-property name="ConnectionCachingEnabled">true</connection-property>

<security-domain>db-security2</security-domain>

<driver-class>oracle.jdbc.OracleDriver</driver-class>

<connection-url>jdbc:oracle:oci:@xxx.xxx.xxx.xxx:1648:MYDB</connection-url>

[rareddy]

Steve is saying that in 8.x version of the Teiid with JBoss AS 7.x, there is plugin way to re-authenticate. But last time I checked they only had for MySQL and what I remember did not meet your needs.

Second, what we are saying in-order to get "logical" connection the driver class should be like

<driver-class>oracle.jdbc.OracleDatasource</driver-class>

But I do not think above will work as is, try configuring a XA datasource for Oracle. An example -ds.xml file you can find in the docs/examples/jca. Hopefully, XA data source class is extended from OracleDatasource

Ramesh..

[tanmoypalit]

I tried XA Data Source (no changes to above translator code)

<datasources>

    <xa-datasource>

        <jndi-name>PL_XA</jndi-name>

        <track-connection-by-tx>true</track-connection-by-tx>

        <isSameRM-override-value>false</isSameRM-override-value>

        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>

        <xa-datasource-property name="URL">jdbc:oracle:oci:@xxx.xxx.xxx.xx:1648:MYDB</xa-datasource-property>

        <xa-datasource-property name="User">user</xa-datasource-property>

        <xa-datasource-property name="Password">pass</xa-datasource-property>

        <exception-sorter-class-name>

            org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter

        </exception-sorter-class-name>

        <no-tx-separate-pools/>

    </xa-datasource>

    <mbean code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter"

           name="jboss.jca:service=OracleXAExceptionFormatter">

        <depends optional-attribute-name="TransactionManagerService">

            jboss:service=TransactionManager

        </depends>

    </mbean>

</datasources>

but got the following error:

16:05:36,589 ERROR [CONNECTOR] Connector worker process failed for atomic-request=WBPNbFhKRTH+.0.0.0

java.lang.NullPointerException

        at oracle.jdbc.driver.LogicalConnection.applyConnectionAttributes(LogicalConnection.java:384)

        at org.teiid.translator.jdbc.oracle.OracleOciSSOExecutionFactory.getSSOConnection(OracleOciSSOExecutionFactory.java:104)

        at org.teiid.translator.jdbc.oracle.OracleOciSSOExecutionFactory.createResultSetExecution(OracleOciSSOExecutionFactory.java:68)

        at org.teiid.translator.jdbc.JDBCExecutionFactory.createResultSetExecution(JDBCExecutionFactory.java:56)

        at org.teiid.translator.ExecutionFactory.createExecution(ExecutionFactory.java:251)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:231)

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

        at java.util.concurrent.FutureTask.run(FutureTask.java:138)

        at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:244)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

        at java.lang.Thread.run(Thread.java:662)

16:05:36,589 ERROR [PROCESSOR] Unexpected exception for request WBPNbFhKRTH+.0

java.lang.NullPointerException

        at oracle.jdbc.driver.LogicalConnection.applyConnectionAttributes(LogicalConnection.java:384)

        at org.teiid.translator.jdbc.oracle.OracleOciSSOExecutionFactory.getSSOConnection(OracleOciSSOExecutionFactory.java:104)

        at org.teiid.translator.jdbc.oracle.OracleOciSSOExecutionFactory.createResultSetExecution(OracleOciSSOExecutionFactory.java:68)

        at org.teiid.translator.jdbc.JDBCExecutionFactory.createResultSetExecution(JDBCExecutionFactory.java:56)

        at org.teiid.translator.ExecutionFactory.createExecution(ExecutionFactory.java:251)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:231)

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

        at java.util.concurrent.FutureTask.run(FutureTask.java:138)

        at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:244)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

        at java.lang.Thread.run(Thread.java:662)

I also tried to set <xa-datasource-property name="ConnectionCachingEnabled">true</xa-datasource-property> in ds.xml but got the following error:

2014-02-03 15:49:34,713 WARN  [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] (Worker1_QueryProcessorQueue1) Throwable while attempting to get a new connection: null

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Can not use getXAConnection() when connection caching is enabled)

  at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:474)

  at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:418)

  at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:639)

  at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:273)

  at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:689)

  at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)

  at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:424)

  at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)

  at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941)

  at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)

  at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:228)

  at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:56)

  at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:184)

  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:207)

  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)

  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

  at java.util.concurrent.FutureTask.run(FutureTask.java:138)

  at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:244)

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)

  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

  at java.lang.Thread.run(Thread.java:662)

Caused by: java.sql.SQLException: Can not use getXAConnection() when connection caching is enabled

  at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:150)

  at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:99)

  at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:458)

  ... 25 more

[rareddy]

I do not see any other way configure "OracleDataSource" in JBoss EAP 5.1. You can ask in IronJacmor forum. In EAP 6.1, I know you can configure it.

Ramesh..