Application Express Nuggets

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:

Find the file that contains the DAD description used for Application Express (most likely $OH/Apache/modplsql/conf/dads.conf)
Edit the DAD entry for Application Express adding PlsqlCGIEnvironmentList AUTHORIZATION
Save the file
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);
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.http_header_close;
            wwv_flow.g_unrecoverable_error := TRUE;
            return false;
        end if;


        if substr(l_auth,1,5) = 'NTLM ' and length(l_auth) > 79 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,9,1)) = 1 then

                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;

            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(utl_raw.cast_to_varchar2(l_raw),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(utl_raw.cast_to_varchar2(l_raw),l_offset,l_length),chr(0),null);

            l_username := l_domain||'\'||l_user;

        else

            l_username := 'nobody';

        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(l_htp_buffer(ii));
            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:

Click Shared Components from the Application Builder home page
Click Authentication Schemes under Security
Click Create >
Choose From scratch and click Next >
Enter NTLM in the Name field and click Next >
Enter return ntlm_page_sentry in the Page Sentry Function text area and click Next >
Click Next > until the Confirm step
Click Create Scheme
Click Change Current
Choose NTLM and Click Next >
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:

Type about:config in the address bar
Type ntlm in the filter text box
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:

Find out who these people are and fire them or get them fired
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

Application Express Nuggets

Tuesday, April 1, 2008

Application Express Web Services Integration Page on OTN

Monday, March 17, 2008

NTLM HTTP Authentication and Application Express

Blog Archive

About Me