I do expect the users who is reading the article have fair knowledge of Teiid, in terms of what is resource adapter, what is VDB, how to connect to VDB etc. If you are new Teiid, I suggest going through one of the quick start examples to understand various components in Teiid, before diving into this example.

Technical Expertise Level Required: Intermediate

In this example, I will present an example where you can access Twitter data using Teiid, and convert the data into tabular form. Once you have Twitter data in Teiid, you can integrate the data with any other data sources. This is not a new feature in Teiid, as Teiid supports calling any REST based web service using its "web service" resource-adapter and "ws" translator. However, the "web service" resource adapter previously was not capable of participating in OAuth authentication process that is required by Twitter to read and post tweets. The main aim of this article is to show case the new feature that has been added in Teiid 8.10 version to participate in a OAuth authentication using "web service" resource adapter.

Before we can write Twitter interface in Teiid that uses a OAuth authentication, user must first register a client application in Twitter. For that log into your Twitter account, then go to https://apps.twitter.com/ and click "Create New App" and give it name, once you are done you will see your application details like below image. Note that these credentials only define you as the service provider. Your user's credentials to access this app comes in next step

Keep this page handy, now lets configure JBoss EAP with Teiid. The credential flow in JBoss EAP can be defined as below.

OAuth credentials passed to "web service" resource adapter by using a JAAS login module. For this purpose Teiid provides a custom JAAS login module and it also provides an utility to further negotiate for the the Access Token for end user. This utility is available in the "bin" directory of the JBoss EAP installation. It is script by name "teiid-oauth-util.sh" for *nix environments or "teiid-oauth-util.bat" for windows environments. Execute the script and follow the prompts. A sample is shown below

=== OAuth 1.0a Workflow ===


Enter the Consumer Key = muxw4rlZ8GJnvEcB3zLZxIBPo


Enter the Consumer Secret = oThgMitGa0zACpDUjPWFMM7RjxjHHAcolxkaH3PmdCDrfwMmgO


Enter the Request Token URL = https://api.twitter.com/oauth/request_token


Request Token  = xIp8OWzVjmRzctZMOCdQARWaNleeA57J secret = g1uacdwkVnIGUxJ0XQjck8pNKXyMTKsB


Enter the User Authorization URL = https://api.twitter.com/oauth/authorize


Cut & Paste the URL in a web browser, and Authticate
Authorize URL  = https://api.twitter.com/oauth/authorize?oauth_token=xIp8OWzVjmRzctZMOCdQARWaNleeA57J


Enter Token Secret (Auth Code, Pin) from previous step = 9314097


Enter the Access Token URL = https://api.twitter.com/oauth/access_token


Access Token = 15451665-yB69O3Rww8V4C2Y5ArnJHqYpQHrhFAbISoEBxGTYW Secret = EmIpXOeTG9bikaRUCiX26XTPzvt8BH00GPKhtUuHlAhEH


Add the following XML into your standalone-teiid.xml file in security-domains subsystem,
and configure data source security to this domain


<security-domain name="oauth-security">
    <authentication>
        <login-module code="org.teiid.jboss.webservices.OAuth10LoginModule" flag="required" module="org.jboss.teiid.security">
            <module-option name="consumer-key" value="muxw4rlZ8GJnvEcB3zLZxIBPo"/>
            <module-option name="consumer-secret" value="oThgMitGa0zACpDUjPWFMM7RjxjHHAcolxkaH3PmdCDrfwMmgO"/>
            <module-option name="access-key" value="15451665-yB69O3Rww8V4C2Y5ArnJHqYpQHrhFAbISoEBxGTYW"/>
            <module-option name="access-secret" value="EmIpXOeTG9bikaRUCiX26XTPzvt8BH00GPKhtUuHlAhEH"/>
        </login-module>
    </authentication>
</security-domain>

The XML fragment at the end shows configuration for security-domain (login module) in JBoss EAP 6.3. Copy it and edit "standalone-teiid.xml" file in "<jboss-eap>/standalone/configuration" directory and add it in the "urn:jboss:domain:security" subsystem.

Now we need to create a "web service" resource adapter that can make use of this security-domain. For that edit "standalone-teiid.xml" again and add following XML under "urn:jboss:domain:resource-adapters" subsystem.

<resource-adapter id="webservice2">
    <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>
    <transaction-support>NoTransaction</transaction-support>
    <connection-definitions>
        <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/twitterDS" enabled="true" use-java-context="true" pool-name="teiid-ws-ds">
            <config-property name="SecurityType">
                OAuth
            </config-property>
            <security>
                <security-domain>oauth-security</security-domain>
            </security>
        </connection-definition>
    </connection-definitions>
</resource-adapter>

The security element defined inside the resource adapter will tie the OAuth credential passing from LoginModule to resource adapter. Now, we need to define VDB that uses resource adapter defined above to issue SQL queries.  A sample Dynamic VDB given below, deploy it to a running JBoss EAP/Teiid server with above configuration.

twitter-vdb.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="twitter" version="1">
    <description>Shows how to call Web Services</description>
    <model name="twitter">
        <source name="twitter" translator-name="rest" connection-jndi-name="java:/twitterDS"/>
    </model>
    <translator name="rest" type="ws">
        <property name="DefaultBinding" value="HTTP"/>
        <property name="DefaultServiceMode" value="MESSAGE"/>
    </translator>
</vdb>

Make sure the VDB is deployed in "ACTIVE" state. Then using any JDBC SQL client like SquirreL to connect to VDB. You can then issue any Twitter REST API calls. I will show an example usage to issue a call to "user_timeline" REST API call. In the below query invokeHTTP call is making the REST API call and returns a JSON document, that document is converted to XML using JSONTOXML function, then XMLTABLE is used to extract the content in tabular form.

select tweet.* from
    (call twitter.invokeHTTP(endpoint=>'https://api.twitter.com/1.1/statuses/user_timeline.json', action=>'GET')) w, 
    XMLTABLE('/myxml/myxml' passing JSONTOXML('myxml', w.result) columns
    created_on string PATH 'created_at',
    source string PATH 'source',
    text string PATH 'text') tweet;

you will see data in tabular form like

This concludes what I wanted show case, Teiid is used to make a REST API calls to web services that are secured through OAuth. Teiid supports both OAuth 1.0A and OAuth 2.0 protocols.

But, is above true "delegation" of user credential who is accessing the Teiid?

Unfortunately NO. The above was not at all a delegation of the credentials of the logged in user to Teiid. The above represents a static credentials of a user that is being used in OAuth Authentication. The next question arises is how can Teiid be configured in "true" delegation fashion, where the logged in user's credentials were used to negotiate a Access Token to Twitter?

For that, the OAuthLoginModule provided by the Teiid must be extended, where in the code the developer will have access to the logged in user's "Subject" object.  Based on "Subject" user can automate the steps defined in the "teiid-oauth-util.sh" utility (see teiid/OAuthUtil.java at master · teiid/teiid · GitHub) by removing any human interaction in between to copy the URL to web browser and get the access code programatically. Then replace previous LoginModule with this extended one in the security-domain configuration defined above. The reason Teiid does not provide this automation is due to fact that, each website is different in terms of their login requirements and in provision of authentication code. So, it is near impossible to guess what the flow would be generically. Yes, it would have been easy to provide a Login Module just for Twitter, but the support we are going after in Teiid is generic support for all the OAuth based services. Since customers and users when they are using this feature in their enterprise, they know exact specific resource that is secured through OAuth, it will be simple exercise to develop an extension like this.

Further Work

• As I was eluding in the previous paragraph, one could write a LoginModule for Twitter, Google, Facebook alike.
• For more inclined advance users, it would be very easy to write a "twitter translator" or "Face Book Translator", which can be SQL wrappers (tables, stored procedures) of all the REST API that web site exposes, so that above complicated use of invokeHTTP and XMLTable can be contained behind a view or procedure definition and give your uses a simple SQL based access to the data behind the web service. That would make a fun project!

HTH, let me know if you have any question in Teiid forums

Ramesh..