ORDS, SODA & JSON in the Database

We have been using APEX for many years with Oracle OHS. To provide Windows integrated authentication so Windows users can automatically login to the APEX app without a login screen, we used the Apache mod_ntlm module. That module is very old and NTLM is a deprecated protocol, using Kerberos is the recommended course of action. Over the last few years, Oracle REST Data Services (ORDS - formerly APEX listener) has become a stable and mature option for APEX (plus REST-ful services, XLS uploads, PDF printing and much more!) and Version 2.0 has re-added support for deploying in a Apache Tomcat container. Tomcat 8 now has built-in support for Windows authentication using the Kerberos/SPNEGO authenticator. But the configuration is not very intuitive. I spent a lot of time configuring all the pieces and finally got it working and thought it might be a good idea to share with the community.

Assumptions - Change server names as needed for your environment

1. Oracle 11.1 on Linux, server name APEX-DEV
2. APEX 4.2.2
3. Windows domain - company.com
4. Kerberos Key Distribution Center (KDC) - kdc.company.com
5. Active Directory (AD) service account for Kerberos pre-authentication - APEX_SSO
6. Tomcat installation directory on APEX-DEV - Path in environment variable $CATALINA_HOME

Step 1 - Install APEX (I used 4.2.2) as per the installation instructions

Step 2 - Download and install ORDS as per the installation instructions. First make sure it works in standalone mode and then follow the instructions to deploy as a Tomcat container. See the reference links below for some useful links on Tomcat architecture. Tomcat itself has a very comprehensive documentation online. Be sure to rename the ords.war file to apex.war so the APEX app URLs will be /apex/f?p=...

At this point, you should have a working APEX installation deployed as a Tomcat container webapp. It should be accessible at http://apex-dev.company.com:8080/apex

If you go the Administration > About page and scroll down to the CGI environment variable section, you will see that the REMOTE_USER is set to APEX_PUBLIC_USER and not your current Windows login id. The goal of the rest of the configuration is to use Kerberos authentication against your Windows AD to transparently authenticate.

Step 3 - Work with Windows administrator to create the service account APEX_SSO in Active Directory. See the user account configuration link below for details on what properties need to be set in AD.

Step 4 - Create a Service Principal Name (SPN) for the APEX server to use for Kerberos pre-authentication.  This is done by the Windows administrator running the following commands.

setspn  -A HTTP/apex-dev APEX_SSO
setspn  -A HTTP/apex-dev.company.com APEX_SSO

Step 5 - Work with the Windows administrator to create a Kerberos keytab file.

ktpass /out c:\tomcat.keytab /mapuser apex_sso@company.com
          /princ HTTP/apex-dev.company.com@company.com
          /pass <password>

The purpose behind these 2 steps is so Kerberos can uniquely identify the apex-dev "service". Think of it as creating a "trusted relationship" between the KDC and your APEX server.

Step 6 -  Copy the tomcat.keytab file over to the $CATALINA_HOME/tomcat.keytab

Step 7 - Checkpoint. Make sure that the pieces are talking to each other.

/usr/kerberos/bin/kinit   -V -k -t $CATALINA_HOME/conf/tomcat.keytab   HTTP/apex-dev.company.com@company.com

The output of this command should be Authenticated to Kerberos v5

Step 8 - Another sanity test

/usr/kerberos/bin/klist -e -k  -t  $CATALINA_HOME/conf/tomcat.keytab

This should read the keytab file and list the SPNs in it, one row per encryption protocol supported.

Step 9 - Open the $CATALINA_HOME/webapps/apex/WEB-INF/web.xml file and add the following snippet at the end, just before the closing web-app tag

<security-constraint>
  <web-resource-collection>
    <web-resource-name>APEX</web-resource-name>
    <url-pattern>/*</url-pattern>
  </web-resource-collection>
  <auth-constraint>
     <role-name>*</role-name>
  </auth-constraint>
</security-constraint>
<login-config>
   <auth-method>SPNEGO</auth-method>
</login-config>

This is the most critical step. This instructs Tomcat to require authentication when any URL from this webapp is requested. The authentication method is SPNEGO.

One caveat here - We have changed the Oracle-supplied web.xml file. The default Tomcat configuration is setup to monitor all WAR files in webapps for changes so if you modify or "touch" the apex.war file, Tomcat will automatically and silently remove the apex directory and re-create it from the WAR. Something to be aware of especially since Oracle has chosen to use the executable WAR feature to configure ORDS (e.g. java -jar ords.war <command> <option> <arguments>), an operation which modifies the WAR file. I would suggest doing the initial configuration as Oracle suggests and then making further changes by directly modifying the defaults.xml file in the ORDS configdir. I may even remove/rename the $CATALINA_HOME/webapps/apex.war file (be sure to stop Tomcat before doing this otherwise Tomcat will automatically un-deploy (i.e. delete) the apex directory!). This way, there is no danger of inadvertently re-creating the apex directory.

Step 9 - APEX is "auto-deployed" using the supplied WAR file and Tomcat explodes this into a apex directory in $CATALINA_HOME. Auto-deployed web apps don't have a explicit Context element defined. Fortunately, we can override that by creating a file $CATALINA_HOME/conf/Catalina/localhost/apex.xml with the following

<?xml version="1.0" encoding="UTF-8"?>
<Context>
  <Valve className="org.apache.catalina.authenticator.SpnegoAuthenticator"
      loginConfigName="APEX"
  />
  <Realm className="org.apache.catalina.realm.JAASRealm"
         allRolesMode="authOnly"
         appName="APEX"
  />
</Context>

This is the key part of the configuration. It sets the loginConfigName on the SpnegoAuthenticator valve and the appName on the JAASRealm valve to the same value, which will be used in subsequent steps. The allRolesMode is also important because it instructs Tomcat to use this realm just for authentication and not try to do any further role-based authorization (e.g. against a LDAP or JDBC data source).

Step 10 - Create a file called krb5.conf in $CATALINA_HOME with the following

[libdefaults]
     default_tkt_enctypes = aes128-cts rc4-hmac des3-cbc-sha1 des-cbc-md5 des-cbc-crc
     default_tgs_enctypes = aes128-cts rc4-hmac des3-cbc-sha1 des-cbc-md5 des-cbc-crc
     permitted_enctypes   = aes128-cts rc4-hmac des3-cbc-sha1 des-cbc-md5 des-cbc-crc
     default_realm       = company.COM

[realms]
     company.COM = {
        kdc = company.com
        default_domain = company.COM
}

[domain_realm]
     .company.COM  = company.COM

Step 11 - Create a file called jaas.conf in $CATALINA_HOME/conf with the following

APEX {
    com.sun.security.auth.module.Krb5LoginModule required
    doNotPrompt=true
    principal="<the value in the Principal column in the output of the klist command>"
    useKeyTab=true
    keyTab=<full path to tomcat.keytab file>
    storeKey=true;
};

The location of the above 2 files is defaulted. If you want to put them in a different location, you can use the JAVA_OPTS environment variable to set the location before starting Tomcat. e.g.

export JAVA_OPTS="-Djava.security.auth.login.config=/path/to/jaas.conf -Djava.security.krb5.conf=/path/to/krb5.conf -Dsun.security.krb5.debug=true -Dsun.security.jgss.debug=true "

The last 2 flags are to turn on debugging, very helpful when setting everything up, can be removed for Production use.

For additional debugging output in $CATALINA_HOME/logs, you can edit the $CATALINA_HOME/conf/logging.properties file add the following line

org.apache.catalina.realm.JAASRealm.level = FINEST

Or change the logging level of org.apache.catalina.core.ContainerBase.[Catalina].[localhost].level  from INFO to FINEST

Step 11 - Restart Tomcat.

Step 12 - Now when you login to APEX and go to the Administration > About page, if everything is working well, the REMOTE_USER should show your Windows username instead of APEX.

This can now be used in APEX authentication schemes based on the HTTP Header.

Here are some references I found useful to understand the concepts and architecture of all the components involved here

1. Kerberos protocol concepts - Kerberos is a very elegant protocol, very well designed.
2. Kerberos implementation in Windows - part 1 of 4 - Good series explaining step by step how Kerberos is implemented in a Windows environment
3. Part 2 of 4
4. Part 3 of 4
5. Part 4 of 4
6. Active Directory user account configuration
7. A useful thread on what KTPASS does
8. Tomcat architecture
9. Tomcat - URL Patterns
10. JAAS authentication in Tomcat