NTLM HTTP Authentication and Application Express

I think for my first blog post ever, I should start with a light topic, such as NTLM Authentication in Application Express...

Many customers have expressed interest in using NTLM with Application Express. The argument is that they are already using this authentication in their .NET intranet applications and users of those applications do not have to supply their domain credentials again, the application simply knows who they are. I know many customers have deployed Apache and mod_ntlm, and used a custom authentication scheme described in the following paper:

http://www.greenit.li/website/content/OracleApplicationExpressProofOfConceptNTLM.doc

There have been some problems reported with using mod_ntlm such as configuration and users getting prompted for username and password periodically. I decided to do some investigation to see if there have been any Java or .NET code examples of doing NTLM authentication to see if I could rewrite the code in PL/SQL. I found the following JSP:

http://www.rgagnon.com/javadetails/java-0441.html

There is a problem with the JSP implementation when you are using a browser that won't support NTLM. You get prompted for a username and password and the JSP will just accept whatever is typed in. Luckily though, you can detect that the user was prompted by the size of the token. I kept that in mind when trying to reverse engineer into a PL/SQL solution.

Through some brute force debugging and examination the HTTP traffic, I was able to successfully write some PL/SQL that does essentially the same thing as the JSP. I used the code in the mod_ntlm page sentry function from the white paper referenced above as a starting point. Unlike the JSP, this function will set the username to "nobody" if it detects that the browser prompted the user for their credentials instead of just silently negotiating them. You can then write authorization schemes that deny access to the "nobody" user.

First you need to configure your DAD used for Application Express so that mod_plsql can be aware of a CGI environment variable called "Authorization." To do this:

1. Find the file that contains the DAD description used for Application Express (most likely $OH/Apache/modplsql/conf/dads.conf)
2. Edit the DAD entry for Application Express adding PlsqlCGIEnvironmentList AUTHORIZATION
3. Save the file
4. Stop and start Apache/ Oracle HTTP Server

Now you can access the CGI environment variable "Authorization." Next you compile a function that will be used as a page sentry function for a custom authentication scheme. Compile this function in the same schema as your application.

create or replace function ntlm_page_sentry
return boolean
is
    l_username      varchar2(512);
    l_session_id    number;
    l_raw           raw(1000);
    l_domain        varchar2(128);
    l_user          varchar2(128);
    l_auth          varchar2(512);
    l_decode        varchar2(2000);
    l_off           pls_integer := 0;
    l_length        pls_integer;
    l_offset        pls_integer;
    l_htp_buffer    htp.htbuf_arr;
    l_htp_rows      INTEGER;
    l_url           VARCHAR2(500);
    l_charset       VARCHAR2(128);
begin
    -- check to ensure that we are running as the correct database user.
    if user != 'APEX_PUBLIC_USER' then
        return false;
    end if;
    -- get sessionid.
    l_session_id := wwv_flow_custom_auth_std.get_session_id_from_cookie;
    -- check application session cookie.
    if wwv_flow_custom_auth_std.is_session_valid then
        apex_application.g_instance := l_session_id;
        l_username := wwv_flow_custom_auth_std.get_username;
        wwv_flow_custom_auth.define_user_session(p_user => l_username,
                p_session_id => l_session_id);
        return true;
    else
        -- get username using NTLM
        l_auth := owa_util.get_cgi_env('AUTHORIZATION');
        if l_auth is null then
            owa_util.status_line(nstatus => 401,
                    creason => 'Unauthorized',
                    bclose_header => false);
            htp.p('WWW-Authenticate: NTLM');
            owa_util.mime_header('text/html', false, 'utf-8');
            owa_util.http_header_close;
            wwv_flow.g_unrecoverable_error := TRUE;
            return false;
        end if;
        if substr(l_auth,1,5) = 'NTLM ' then
            l_decode := utl_encode.text_decode(buf => substr(l_auth,6), encoding => UTL_ENCODE.BASE64);
            l_raw := utl_raw.cast_to_raw(l_decode);
            if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,14,1)) != 130 then
                if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,9,1)) = 1 then
                    owa_util.mime_header('text/html', false, 'utf-8');
                    owa_util.status_line(nstatus => 401,
                            creason => 'Unauthorized',
                            bclose_header => false);
                    htp.p('WWW-Authenticate: NTLM TlRMTVNTUAACAAAAAAAAACgAAAABggAAAAICAgAAAAAAAAAAAAAAAA==');
                    owa_util.http_header_close;
                    wwv_flow.g_unrecoverable_error := TRUE;
                    return false;
                end if;
                -- Determine DB charset and convert raw to WE8MSWIN1252, thanks to Andrew Barbaccia
                select value into l_charset from nls_database_parameters where parameter='NLS_CHARACTERSET';
                l_length := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,32,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,31,1));
                l_offset := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,34,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,33,1));
                l_domain := replace(replace(substr(convert(utl_raw.cast_to_varchar2(l_raw),l_charset,'WE8MSWIN1252'),l_offset + 1,l_length),chr(0),null),chr(15),null);
                l_length := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,40,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,39,1));
                l_offset := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,42,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,41,1));
                l_user := replace(substr(convert(utl_raw.cast_to_varchar2(l_raw),l_charset,'WE8MSWIN1252'),l_offset,l_length),chr(0),null);
                l_username := l_domain'\'l_user;
            else
                l_username := 'nobody';
            end if;
        end if;
        -- application session cookie not valid --> define a new apex session.
        wwv_flow_custom_auth.define_user_session(p_user => l_username,
            p_session_id => wwv_flow_custom_auth.get_next_session_id);
        -- tell apex engine to quit.
        apex_application.g_unrecoverable_error := true;
        if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET'  then
            wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'
            wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
        else
        wwv_flow_custom_auth.remember_deep_link(p_url => 'f?p='
          to_char(apex_application.g_flow_id)':'
          to_char(nvl(apex_application.g_flow_step_id, 0))':'
          to_char(apex_application.g_instance));
      end if;
      -- register the session in apex sessions table, set cookie, redirect back.
      wwv_flow_custom_auth_std.post_login(p_uname => l_username,
        p_session_id => nv('APP_SESSION'), p_flow_page => apex_application.g_flow_id
        ':'nvl(apex_application.g_flow_step_id, 0), p_preserve_case => true);
        -- get HTP output wwv_flow_custom_auth_std.post_login has written,
        -- it contains the session cookie we need.
        -- Thanks to Patrick Wolf for the following code
        l_htp_rows := 15; /* where and how to get an actual value for irows???? */
        htp.get_page
          ( thepage => l_htp_buffer
          , irows   => l_htp_rows
          );
        -- reset the HTP buffer so that we can write our own header, ...
        htp.init;
        -- See http://www.nabble.com/Empty-POST-requests-on-IE-td15332680.html
        -- We have to trick IE that he thinks the authentication fails, otherwise
        -- he doesn't send any data when issueing a POST because he wants to
        -- do the NTLM stuff again
        owa_util.status_line
          ( nstatus => 401,
            creason => 'Unauthorized',
            bclose_header => FALSE
          );
        -- write the session cookie into our output
        FOR ii IN 1 .. l_htp_rows
        LOOP
            IF l_htp_buffer(ii) LIKE 'Set-Cookie:%'
            THEN
                htp.p(rtrim(l_htp_buffer(ii), CHR(10)));
            END IF;
        END LOOP;
        --
        l_url := 'f?p='
                 apex_application.g_flow_id':'
                 nvl(apex_application.g_flow_step_id, 0)':'
                 apex_application.g_instance;
        --
        IF WWV_Flow.get_browser_version = 'NSCP'
        THEN
            -- Firefox: redirect can be set with a HTTP header attribute
            htp.p('Location: 'l_url);
            owa_util.http_header_close;
        ELSE
            -- For IE: The javascript is required so that we are redirected to the page as
            -- the wwv_flow_custom_auth_std.post_login would normally do with the
            -- HTTP 302 redirect
            owa_util.http_header_close;
            htp.p('<html><head>');
            htp.p('<script type="text/javascript">');
            htp.p('  location.href="'l_url'";');
            htp.p('</script>');
            htp.p('<noscript>');
            htp.p('<meta http-equiv="Refresh" content="0; URL="'l_url'">');
            htp.p('</noscript>');
            htp.p('</head>');
            htp.p('<body>');
            htp.p('You were logged in successfully. Click <a href="'l_url'">here</a> to continue.');
            htp.p('</body>');
            htp.p('</html>');
        END IF;
      return false;
    end if;
end ntlm_page_sentry;
/

The last step is to create a custom authentication scheme that uses the above function as the page sentry function. To create a custom authentication scheme:

1. Click Shared Components from the Application Builder home page
2. Click Authentication Schemes under Security
3. Click Create >
4. Choose From scratch and click Next >
5. Enter NTLM in the Name field and click Next >
6. Enter return ntlm_page_sentry in the Page Sentry Function text area and click Next >
7. Click Next > until the Confirm step
8. Click Create Scheme
9. Click Change Current
10. Choose NTLM and Click Next >
11. Click Make Current

Run the application and you should see your username in the format of DOMAIN\username provided you are using a browser that is configured to support NTLM negotiation.

Now, a couple of notes about browser support and NTLM. (Of course if you are already using NTLM for authentication with other applications, you are well aware of these notes). In order for Internet Explorer to automatically negotiate NTLM, the security settings of the browser must be set to Medium-low or Low. By default, IE is set to Medium-low for local intranet sites, and this authentication really only makes sense for local intranet sites.

Firefox will work with NTLM, but each browser has to be configured to trust each server where you want to employ NTLM. To configure Firefox to negotiate NTLM with a specific server:

1. Type about:config in the address bar
2. Type ntlm in the filter text box
3. Double click the preference network.automatic-ntlm-auth.trusted-uri's and enter a comma separated list of trusted servers on your network

Vista has local security policies that, by default, do not allow browser negotiation of NTLM authentication. (Again, you already know this if you have Vista and NTLM auth employed with applications in your environment). The link that follows contains information on how to change this.

http://www.jimmah.com/vista/Networking/ntlm.aspx

If you are now thinking to yourself, "wow, I can't believe I would have to change this setting on every Vista client in my organization," then you should familiarize yourself with the notion of group policy and the following document:

http://msdn2.microsoft.com/en-us/library/ms814176.aspx

Again, NTLM authentication is really only relevant for clients that are part of an Active Directory domain, and therefore, group policy would apply.

Finally, it is possible (with any application that authenticates with NTLM, not just this example) for someone to sniff traffic on your network, see the NTLM authorization token for a specific user, and then use that token to spoof the identity of someone and use your application. You should:

1. Find out who these people are and fire them or get them fired
2. Use SSL

Update 3/19/2008: I should mention that this solution only works with Apache/ Oracle HTTP Server and is not supported by the XDB HTTP Server with the embedded PL/SQL gateway (EPG), yet...

Update 4/17/2008: For more information on why this solution will not work with the embedded PL/SQL gateway, see the section titled "Configuring Static Authentication with DBMS_EPG" in the following document:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_web.htm#BGBCFIIB

"The database rejects access if the browser user attempts to connect explicitly with the HTTP Authorization header."

Update 5/8/2008: Patrick Wolf discovered an issue described at the following post:

http://forums.oracle.com/forums/thread.jspa?messageID=2511974&#2511974

He also came up with a very elegant solution described in the same post. I guess I should have tested this method with a more complex application (like one that posts a page). ;) Anyway, thanks to Patrick and I have included his fix in an updated version of the function.

Update 5/14/2008: John Scott may have discovered a way to use this authentication mechanism with the EPG, using Apache to proxy requests to EPG and rewriting the Authorization header:

http://forums.oracle.com/forums/thread.jspa?threadID=652805&start=15&tstart=0

Update 8/20/2008: It seems that checking the length of the NTLM token has proven unreliable to detect the case where the browser prompted for a username and password. I have found that when the browser prompts the user, the token "NTLM TlRMTVNTUAABAAAAB4IIAAAAAAAAAAAAAAAAAAAAAAA=" is consistently passed by the client. I have altered the PL/SQL code to test for this token instead of the token length.

Update 11/17/2008: I have updated the function to include two changes. The first is a suggestion from Andrew Barbaccia about character set conversion. See the comments below and the referenced forum discussion.

The second modification is how we detect when the browser prompted for username and password. I noticed that recenlty, the token changed in this case when using IE7, although the token was the same in FF. Ilmar in his comments below has come accross the same issue. I did a little more investigation and have found that the binary integer equivalent of the 14th byte of the NTLM token is equal to 130 when the browser is prompted. I will go with that for now.

Update 07/13/2009: It seems that Microsoft published the following which will make the ntlm_page_sentry function no longer work:

"Cumulative Security Update for Internet Explorer 7 for Windows Vista (KB963027)Security issues have been identified that could allow an attacker to compromise a system that is running Microsoft Internet Explorer and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this item, you may have to restart your computer. This update is provided to you and licensed under the Windows Vista License Terms.

More information: http://go.microsoft.com/fwlink/?LinkId=146659

Help and Support: http://support.microsoft.com/"

One workaround is to de-install this update. I don't recommend that option. Another workaround listed in the comments below is to comment out the following check:

if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,14,1) != 130

I don't recommend that option either. The purpose of the check above is to detect the case where the browser prompted for Username and Password. This will happen if someone visits your site using the ntlm_page_sentry function and your site is not listed as in the local intranet. If the above is commented out, users that visit your application where the browser thinks it is not the local intranet will be able to type in any username they want and be that user.I have spent some time trying to figure out a workaround but I don't have one. If any of you have any ideas, please post a comment.

Update 07/14/2009: A registry hack was provided at the following forum post which can be applied via group policy:

http://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=921524

Update 08/14/2009: I also want to point out some text from the whitepaper based on this article to make it clear what this function does (decodes an NTLM token) and does not do (negotiate anything with any domain controller).

"This paper presents a pure PL/SQL code solution for decoding an NTLM token and using that decoded value as the authenticated user in APEX applications. The function will set the username to "nobody" if it detects that the browser prompted the user for their credentials instead of just silently negotiating them. You can then write authorization schemes that deny access to the "nobody" user. Note that unlike the mod_ntlm Apache module, this solution does not pass along credentials to a domain controller for authentication. This solution requests that the browser present an NTLM authentication token and decodes the username and domain from that token."