-
1. Re: Teiid Translator for resetting Oracle Property
rareddy Jan 31, 2014 12:31 PM (in response to tanmoypalit)Tanmoy,
I believe that error is coming from the Oracle driver. Not sure what is a "logical" connection in their scenario?
Ramesh..
-
2. Re: Teiid Translator for resetting Oracle Property
rareddy Jan 31, 2014 12:39 PM (in response to tanmoypalit)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..
-
3. Re: Teiid Translator for resetting Oracle Property
tanmoypalit Jan 31, 2014 3:22 PM (in response to rareddy)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
-
4. Re: Teiid Translator for resetting Oracle Property
rareddy Jan 31, 2014 4:14 PM (in response to tanmoypalit)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..
-
5. Re: Re: Teiid Translator for resetting Oracle Property
tanmoypalit Jan 31, 2014 4:24 PM (in response to 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 theuser.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 theuser.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 theOracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER
connection property.Is it possible to apply this property to an active connection in Teiid Translator?
-Tanmoy
-
6. Re: Re: Teiid Translator for resetting Oracle Property
rareddy Jan 31, 2014 4:29 PM (in response to tanmoypalit)Not without extending the Oracle Translator or writing a Delegate Translator.
-
7. Re: Re: Teiid Translator for resetting Oracle Property
shawkins Jan 31, 2014 7:46 PM (in response to rareddy)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);
-
8. Re: Teiid Translator for resetting Oracle Property
tanmoypalit Feb 3, 2014 9:29 AM (in response to shawkins)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:
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>
-
9. Re: Teiid Translator for resetting Oracle Property
rareddy Feb 3, 2014 10:12 AM (in response to tanmoypalit)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..
-
10. Re: Teiid Translator for resetting Oracle Property
tanmoypalit Feb 3, 2014 11:14 AM (in response to rareddy)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
-
11. Re: Teiid Translator for resetting Oracle Property
rareddy Feb 3, 2014 11:38 AM (in response to tanmoypalit)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..