3 Replies Latest reply on Mar 25, 2010 3:43 PM by Ramesh Reddy

    Oracle Proxy Session and Teiid

    Andeep Toor Newbie

      We are using Teiid 7.0.0-SNAPSHOT to run a VDB that connects to an Oracle 11g datasource. For this particular project, we need the ability to call an Oracle-specific method on an OracleConnection to open a proxy session. This allows us to have a single connection pool user that we proxy through, making the connection behave as if it were another user. An example of this is:



      OracleConnection oracleConnection = (OracleConnection) connection;

      final Properties props = new Properties();
      props.put(OracleConnection.PROXY_USER_NAME, "Bob");
      oracleConnection.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);


      In this example, our database connection will now behave as if Bob had connected directly. The questions I have are:


      1) Do I need to modify the connection-jdbc extension jar to add this functionality, or is there some other preferred method of adding such functionality?

      2) How can I pass information (like the username "Bob", for example) to the Teiid datasource or connection so that it can be accessed from within JDBC Connector?

      3) Alternatively, is there a way to change what username and password the VDB connects to the Oracle database with? This might be another acceptable way for us to have the connection change to be "Bob".

        • 1. Re: Oracle Proxy Session and Teiid
          Ramesh Reddy Master



          We do support a feature called user specific pools at the connectors. Take a look at http://docs.jboss.org/teiid/6.2/reference/en-US/html/connectors.html#d0e8114


          The above will satisfy (3) from your list. If you like (2) then see section 8.2.4


          Word of caution about 7.0 final release, this way to handle the connector's connection pool to the data source is changing. Teiid will be deployed inside the JBoss AS as runtime and  eiid managed connection pools will be replaced with JBoss managed connection pools. If JBoss AS's data source connection pooling will support identity switching on already created connection, then Teiid will also support it. Other wise that scenario needs to be further investigated. I have not done my investigation yet.


          Hope this helps.



          • 2. Re: Oracle Proxy Session and Teiid
            Andeep Toor Newbie



            Thanks for your reply. I attempted to use the credentials property, as the link you sent me showed, but I received an error:



            java.sql.SQLException: java.lang.IllegalArgumentException
            at org.teiid.jdbc.EmbeddedProfile.parseURL(EmbeddedProfile.java:229)
            at org.teiid.jdbc.EmbeddedProfile.connect(EmbeddedProfile.java:108)
            at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:92)
            at java.sql.DriverManager.getConnection(DriverManager.java:582)
            at java.sql.DriverManager.getConnection(DriverManager.java:185)
            Caused by: java.lang.IllegalArgumentException
            at com.metamatrix.jdbc.util.MMJDBCURL.parseConnectionProperty(MMJDBCURL.java:196)
            at com.metamatrix.jdbc.util.MMJDBCURL.parseConnectionPart(MMJDBCURL.java:166)
            at com.metamatrix.jdbc.util.MMJDBCURL.parseURL(MMJDBCURL.java:134)
            at com.metamatrix.jdbc.util.MMJDBCURL.<init>(MMJDBCURL.java:76)
            at org.teiid.jdbc.EmbeddedProfile.parseURL(EmbeddedProfile.java:194)
            ... 27 more


            The code I am using to connect is




            String credentials = "credentials=(system=mySys,user=foo,password=bar);";

            String url = "jdbc:teiid:FOO@/opt/teiid-7.0.0-SNAPSHOT/deploy.properties;txnAutoWrap=PESSIMISTIC;" + credentials;

            Connection connection = DriverManager.getConnection(url, "admin", "teiid");




            Is there some sort of mismatch between the example for version 6.2 (the version of the doc you sent me) and the version that I am using (7.0.0-SNAPSHOT...really, 7.0.0-M2)? If so, do you have an updated example? Digging into the code, it seems like the MMJDBCURL class throws an exception because it does not like the fact that there is a property (credentials) that has an "=" in its value.


            Again, this would be to satisfy (3) from my original post. For trying to accomplish (2), you pointed out section 8.2.4. Could you explain the following:


            • For JDBC connectors, the extension connection factory class name must be set for user instead of single identity.

              For example the Oracle JDBC connector would use com.metamatrix.connector.jdbc.oracle.OracleUserIdentityConnectionFactory



            Where does the com.metamatrix.connector.jdbc.oracle.OracleUserIdentityConnectionFactory class come from? Where can I find the source for this class? Am I supposed to extend this class? If so, how do I get Teiid to use the new class that I create?

            • 3. Re: Oracle Proxy Session and Teiid
              Ramesh Reddy Master

              Please try


              String credentials = "credentials="+URLEncode.encode("(system=mySys,user=foo,password=bar)");
              String url = "jdbc:teiid:FOO@/opt/teiid-7.0.0-SNAPSHOT/deploy.properties;txnAutoWrap=PESSIMISTIC;" + credentials;
              Connection connection = DriverManager.getConnection(url, "admin", "teiid")


              Docs are not up to date with 7.0 M2, and I do not think they will be until 7.0 final. Yes, 7.0.M2 has its jar as 7.0.0-SNAPSHOT, we do not version them at each milestone yet.


              I am sorry, look like we do not support that feature any more.  However, if you want do this way you can extend the "JDBCConnector" class in the the "/extensions/connector-jdbc-{version}.jar file and override the "createIdentity" and "getConnection" methods. Once you have the updated class, this class needs to jared up and placed in the "extensions" directory and then modify the connector binding properties and update the "ConnectorClass" and "ConnectorClasspath" properties to reflect the changes.


              We will update our docs. Thanks.