Kerberos Authentication to Oracle from Teiid

Version 1

    In this article I will provide step by step "how to" instructions to use Kerberos authentication from Teiid. It is expected that the user has knowledge about Kerberos authentication and have gathered all the necessary configuration and keytabs before starting this exercise. There are multiple sections to this article, as listed below

     

    1) Log into Oracle using the Kerberos authentication.

    2) Log into Teiid using JDBC driver and authenticate using Kerberos to Teiid server.

    3) Delegate the JDBC Kerberos user to the Oracle.

    3) Configure the OData access to use the Kerberos authentication, then delegate the same token to Teiid, which in turn delegates the same token to Oracle.

     

    Note that each of the above build on top of each other in terms of configuration, so do not skip to the section you are just interested in.

     

    For the purposes this article, my Oracle instance is already configured to accept Kerberos based authentication, and has been verified independently of Teiid that the Kerberos authentication works.

     

    Log into Oracle using the Kerberos authentication

     

    First we need to create a module for Oracle driver. In the "modules/system/layer/dv/com/oracle/main" create a file called module.xml with following contents

    <?xml version="1.0" encoding="UTF-8"?>
    <module xmlns="urn:jboss:module:1.0" name="com.oracle">
        <resources>
            <resource-root path="ojdbc6_with_krb_fix.jar"/>
        </resources>
        <dependencies>
            <module name="javax.api"/>
            <module name="javax.resource.api"/>
            <module name="sun.jdk"/>
            <system export="true">
                <paths>
                    <path name="sun/security/krb5/internal"/>
                </paths>
            </system>
        </dependencies>
    </module>
    

    and make sure you copy the JDBC jar file in the same directory.

     

    Note: The JDBC Driver from Oracle previous 11.0.2 had bug with kerberos authentication, so you need to get latest one (alas I could not make work with Oracle 12c ojdbc7.jar either)

     

    Edit the standalone.xml, could be standalone-teiid.xml depending upon how you installed Teiid, and add the following

     

    In DataSources subsystem, create connection configuration as

     

    <datasource jndi-name="java:/Oracle12_krb" pool-name="Oracle12_krb" enabled="true" spy="true">
        <connection-url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev151.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=qaora12)))</connection-url>
        <connection-property name="oracle.net.authentication_services">
            (KERBEROS5)
        </connection-property>
        <driver>oracle</driver>
        <pool>
            <prefill>false</prefill>
            <allow-multiple-users>false</allow-multiple-users>
        </pool>
        <security>
            <security-domain>direct-krb</security-domain>
        </security>
    </datasource>
    <driver name="oracle" module="com.oracle">
         <driver-class>oracle.jdbc.OracleDriver</driver-class>
    </driver>
    

     

    The <security-domain> element defines the JAAS security domain to be used with the Oracle connection. Now lets configure the security domain specific details. Edit the standalone.xml and add

     

    <system-properties>
       <property name="java.security.krb5.conf" value="${jboss.home.dir}/krb5oracle.conf"/>
       <property name="java.security.krb5.debug" value="true"/>
    </system-properties>
    

     

    My krb5oracle.conf file looks like

    [libdefaults]
      debug = true
      dns_lookup_realm = false
      rdns = false
      forwardable = true
      ticket_lifetime = 24h
      renew_lifetime = 7d
      max_renewable_life = 7d
      default_realm = EXAMPLE.COM
    
    [realms]
     EXAMPLE.COM = {
       kdc = kerberos-test.example.com:88
       default_domain = EXAMPLE.COM
      }
    
    [domain_realm]
      .example.com = EXAMPLE.COM
      example.com = EXAMPLE.COM
    [login]
      krb4_convert = true
      krb4_get_tickets = false
    
    [pam]
      debug = true
      ticket_lifetime = 36000
      renew_lifetime = 36000
      krb4_convert = false
    

     

    copy this file into where Teiid/WildFly is installed, in the home directory. Continue editing the standalone.xml and add security-domain in "security" subsystem

    <security-domain name="direct-krb">
        <authentication>
            <login-module code="Kerberos" flag="required" module="org.jboss.security.negotiation">
                <module-option name="storeKey" value="true"/>
                <module-option name="useKeyTab" value="true"/>
                <module-option name="keyTab" value="${jboss.home.dir}/KRBUSR05"/>
                <module-option name="principal" value="KRBUSR05@EXAMPLE.COM"/>
                <module-option name="doNotPrompt" value="true"/>
                <module-option name="debug" value="true"/>
                <module-option name="refreshKrb5Config" value="false"/>
                <module-option name="isInitiator" value="true"/>
                <module-option name="addGSSCredential" value="true"/>
                <module-option name="delegationCredential" value="USE"/>
            </login-module>
        </authentication>
    </security-domain>
    

     

    Copy the Key Tab file for user "KRBUSR05 " into the Teiid/WildFly home directory. Now save the file, and start the Teiid server. Deploy a test VDB like

    oracle-vdb.xml

    <vdb name="oracle" version="1">
      <model name="test">
        <source name="local" translator-name="oracle" connection-jndi-name="java:/Oracle12_krb"/>
           <metadata type="DDL"><![CDATA[
             CREATE FOREIGN TABLE dual (
                "user" string PRIMARY KEY
             );
           ]]> </metadata>
      </model>
    </vdb>
    

     

    Now using your favorite JDBC client, connect to the "oracle" vdb and issue "select * from test.dual", you will see "KRBUSR05" as your user. If you are NOT looking into SSO your example ends here!

     

    Log into Teiid using JDBC driver and authenticate using Kerberos to Teiid server

     

    In this step we will enable the Kerberos login on Teiid itself and login into Teiid using Kerberos authentication. For this we need to make following changes to the standalone.xml file. Edit and add the following to the "security" subsystem

    <security-domain name="host">
        <authentication>
            <login-module code="Kerberos" flag="required" module="org.jboss.security.negotiation">
                <module-option name="storeKey" value="true"/>
                <module-option name="useKeyTab" value="true"/>
                <module-option name="keyTab" value="${jboss.home.dir}/HTTP_localhost"/>
                <module-option name="principal" value="HTTP/localhost@EXAMPLE.COM"/>
                <module-option name="doNotPrompt" value="true"/>
                <module-option name="useTicketCache" value="true"/>
                <module-option name="debug" value="true"/>
                <module-option name="refreshKrb5Config" value="false"/>
                <module-option name="isInitiator" value="true"/>
                <module-option name="addGSSCredential" value="true"/>
                <module-option name="delegationCredential" value="USE"/>
                <module-option name="ticketCache" value="/tmp/krb5cc_100e0"/>
            </login-module>
        </authentication>
    </security-domain>
    <security-domain name="EXAMPLE.COM">
        <authentication>
            <login-module code="SPNEGO" flag="requisite" module="org.jboss.security.negotiation">
                <module-option name="password-stacking" value="useFirstPass"/>
                <module-option name="serverSecurityDomain" value="host"/>
            </login-module>
        </authentication>
        <mapping>
            <mapping-module code="SimpleRoles" type="role">
                <module-option name="KRBUSR05@MW.LAB.ENG.BOS.REDHAT.COM" value="user,odata"/>
            </mapping-module>
        </mapping>
    </security-domain>
    

     

    Make sure you copy the key tab for "HTTP/localhost@EXAMPLE.COM" in the Teiid/WildFly home directory or path defined. Since the JDBC uses the GSS-API additional SPENGO based module also needed for negotiations. We also need to configure the VDB to use this kerberos security domain. For that undeploy the previous VDB and deploy a new VDB looks like

     

    oracle-vdb.xml

     

    <vdb name="oracle" version="1">
      <property name="security-domain" value="MW.LAB.ENG.BOS.REDHAT.COM"/>
      <property name="authentication-type" value="GSS"/>
      <model name="test">
        <source name="local" translator-name="oracle" connection-jndi-name="java:/Oracle12_krb"/>
           <metadata type="DDL"><![CDATA[
             CREATE FOREIGN TABLE dual (
                "user" string PRIMARY KEY
             );
           ]]> </metadata>
      </model>
    </vdb
    

     

    Note the extra two lines at the beginning of the VDB defining the security domain to be used with the VDB. Now it's time to verify this authentication.

     

    For verifying this, I am going to write a custom Java client, with following configuration.

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    
    public class JDBCClient {
        
        public static void main(String[] args) throws Exception {
            if (args.length < 4) {
                System.out.println("usage: JDBCClient <host> <vdb> <sql-command>");
                System.exit(-1);
            }
            execute(getDriverConnection(args[0], Integer.parseInt(args[1]), args[2]), args[3]);
        }
        
        static Connection getDriverConnection(String host, int port, String vdb) throws Exception {
            String url = "jdbc:teiid:" + vdb + "@mm://" + host + ":" + port+";jaasName=Client;kerberosServicePrincipleName=HTTP/localhost@EXAMPLE.COM";
            Class.forName("org.teiid.jdbc.TeiidDriver");    
            return DriverManager.getConnection(url);        
        }
        
        public static boolean execute(Connection connection, String sql) throws Exception {
            boolean hasRs = true;
            try {
                Statement statement = connection.createStatement();
                hasRs = statement.execute(sql);
                if (!hasRs) {
                    int cnt = statement.getUpdateCount();
                    System.out.println("----------------\r");
                    System.out.println("Updated #rows: " + cnt);
                    System.out.println("----------------\r");
                } else {
                    ResultSet results = statement.getResultSet();
                    ResultSetMetaData metadata = results.getMetaData();
                    int columns = metadata.getColumnCount();
                    System.out.println("Results");
                    for (int row = 1; results.next(); row++) {
                        System.out.print(row + ": ");
                        for (int i = 0; i < columns; i++) {
                            if (i > 0) {
                                System.out.print(",");
                            }
                            System.out.print(results.getObject(i+1));
                        }
                        System.out.println();
                    }
                    results.close();
                }
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    connection.close();
                }
            }
            return hasRs;
        }
    }
    

     

    Execute this java file with following system options

     

    -Djava.security.krb5.conf=/home/rareddy/testing/EAP-6.4.0/krb5oracle.conf
    -Djava.security.auth.login.config=/home/rareddy/development/workspace/teiid/TeiidClient/client.conf 
    -Djavax.security.auth.useSubjectCredsOnly=false  
    -Dsun.security.krb5.debug=true
    

     

    where client.conf is

    Client {  
        com.sun.security.auth.module.Krb5LoginModule required  
        useTicketCache=true  
        storeKey=true  
        useKeyTab=true  
        keyTab="/home/rareddy/testing/EAP-6.4.0/KRBUSR05"  
        doNotPrompt=true  
        debug=true  
        principal="KRBUSR05@EXAMPLE.COM";  
    }; 
    

     

    make sure your keytab paths are correct. Note that the JDBC URL defined is different, it is

    jdbc:teiid:<vdb>@mm://<host>:<port>;jaasName=Client;kerberosServicePrincipleName=HTTP/localhost@EXAMPLE.COM
    

     

    Note the Kerberos Service Principle name in the URL, this needs to match with that of in the Server configuration. Save all the files. And when you execute "select * from test.dual", you will see "KRBUSR05" as your user from Oracle (this did not change), execute "select user() from dual" you will see "KRBUSR05\@EXAMPLE.COM@EXAMPLE.COM" as "user()" is function in Teiid. So, you see the same user!!!

     

    Delegate the JDBC Kerberos user to the Oracle

     

    In the past two scenarios, the two separate Kerberos connection were used from Client -> JDBC -> Teiid and Teiid -> Oracle. Now in this delegation scenario, we want to pass single user in SSO fashion from client to the Oracle, like Client -> JDBC -> Teiid -> Oracle. For that add following configuration to the standalone.xml file

    <security-domain name="passthrough-oracle">
        <authentication>
            <login-module code="Kerberos" flag="required" module="org.jboss.security.negotiation">
                <module-option name="addGSSCredential" value="true"/>
                <module-option name="delegationCredential" value="REQUIRE"/>
                <module-option name="credentialLifetime" value="-1"/>
            </login-module>
        </authentication>
    </security-domain>
    

     

    and also change the <security-domain> element in the data source configuration from <security-domain>direct-krb</security-domain> to <security-domain>passthrough-oracle</security-domain> and use the JDBC client in the above step to test.

     

    Configure the OData access to use the Kerberos authentication

     

    For this follow directions here to configure the OData WAR file to configure to use the Kerberos authentication. Kerberos support through GSSAPI · Teiid Documentation

    The major task is how to test this, I used the following test class (credit to Red Hat QE  folks)

     

    import java.io.IOException;
    import java.security.Principal;
    import java.security.PrivilegedAction;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.security.auth.Subject;
    import javax.security.auth.login.LoginContext;
    
    import org.apache.http.HttpResponse;
    import org.apache.http.HttpStatus;
    import org.apache.http.auth.AuthSchemeProvider;
    import org.apache.http.auth.AuthScope;
    import org.apache.http.auth.Credentials;
    import org.apache.http.client.ClientProtocolException;
    import org.apache.http.client.HttpClient;
    import org.apache.http.client.config.AuthSchemes;
    import org.apache.http.client.methods.HttpGet;
    import org.apache.http.client.methods.HttpUriRequest;
    import org.apache.http.config.RegistryBuilder;
    import org.apache.http.impl.auth.SPNegoSchemeFactory;
    import org.apache.http.impl.client.BasicCredentialsProvider;
    import org.apache.http.impl.client.CloseableHttpClient;
    import org.apache.http.impl.client.HttpClientBuilder;
    import org.apache.http.config.Lookup;
    import com.fasterxml.jackson.databind.ObjectMapper;
    import org.junit.Assert;
    
    public class RestClient {
        public static void main(String[] args) throws Exception {
            HttpResponse response = callRestUrl(
                "http://localhost:8080/odata4/oracle/test/dual?$top=1");
            Assert.assertEquals(response.getStatusLine().getStatusCode(), HttpStatus.SC_OK);
            HashMap<String, List<Map<String, Object>>> result = new ObjectMapper()
                .<HashMap> readValue(response.getEntity().getContent(),
                HashMap.class);
            
            System.out.println(result);
        }
    
        private static HttpClient buildSpnegoHttpClient() {
            HttpClientBuilder builder = HttpClientBuilder.create();
            Lookup<AuthSchemeProvider> authSchemeRegistry = RegistryBuilder
                .<AuthSchemeProvider> create()
                .register(AuthSchemes.SPNEGO, new SPNegoSchemeFactory(true)).build();
            builder.setDefaultAuthSchemeRegistry(authSchemeRegistry);
            BasicCredentialsProvider credentialsProvider = new BasicCredentialsProvider();
    
            credentialsProvider.setCredentials(new AuthScope(null, -1, null), new Credentials() {
                @Override
                public Principal getUserPrincipal() {
                    return null;
                }
    
    
                @Override
                public String getPassword() {
                    return null;
                }
            });
            builder.setDefaultCredentialsProvider(credentialsProvider);
            CloseableHttpClient httpClient = builder.build();
            return httpClient;
        }
    
        public static HttpResponse callRestUrl(final String url) throws ClientProtocolException, IOException {
            try {
                LoginContext lc = new LoginContext("Client");
                lc.login();
                Subject serviceSubject = lc.getSubject();
                return Subject.doAs(serviceSubject, new PrivilegedAction<HttpResponse>() {
                    HttpResponse httpResponse = null;
    
    
                    @Override
                    public HttpResponse run() {
                        try {
                            HttpUriRequest request = new HttpGet(url);
                            HttpClient spnegoHttpClient = buildSpnegoHttpClient();
                            httpResponse = spnegoHttpClient.execute(request);
                            return httpResponse;
                        } catch (IOException ioe) {
                            ioe.printStackTrace();
                        }
                        return httpResponse;
                    }
                });
            } catch (Exception le) {
                le.printStackTrace();
            }
            return null;
        }
    }
    

     

    I used same client.conf from JDBC client example with with same system options as before to execute. Note this class needs apache httpclient, com.fasterxml as dependencies. ( TODO: need to convert this into a maven project)

     

    Hope this showed how to configure and test the kerberos and delegation of credential in Teiid step by step. This article can be used with any data source, just the initial JDBC driver setup and data source configuration changes, all the kerberos specific stuff should remain same.

     

    Ramesh..