Pages

Saturday, March 08, 2014

PL/SQL, UTL_HTTP and Digest Authentication

For the first time in what seems like ages, I've actually put together a piece of code worth sharing. It's not that I haven't been working, but just that it has all been very 'in-house' specific.However I had a recent requirement to use a web service that makes use of Digest Authentication. If you have look at the UTL_HTTP SET_AUTHENTICATION subprogram, it only addresses Basic authentication (and, apparently, Amazon S3 which looks intriguing).In Basic authentication, the username and password get sent across as part of the request. Going through SSL, that doesn't seem too bad, as it is encrypted over the transfer and the certificates should ensure you are talking to the legitimate destination. However if that destination has been compromised, you've handed over your username and password. In an ideal world, the server shouldn't need to know your password, which is why database should only have hashed versions of passwords. Outside of SSL, you might as well just print the username and password on the back of a postcard.

In Digest authentication, you get a more complex interaction that keeps the password secret. You ask for a page, the server responds with an "Authentication Required" plus some bits of information including a nonce. You come up with a hashed value based on the server nonce, your own nonce and a hash of your username and password and send it back with the next request. The server has its own record of your username/password hash and can duplicate the calculations. If everyone is happy, the server can fulfill your request and nobody ever actually needs to know the password.Our server used SSL, and thanks to Tim's article on SSL and UTL_HTTP, it was a simple set up. I've done it before, but that was in the days when it seemed a lot hard to get certificates OUT of a browser to put them in your Oracle Wallet.The Interwebs were a lot less forthcoming on a PL/SQL implementation of Digest authentication though. The closest I got was this discussion, which can be summed up as "This may be complex, but I do not see these offhand as being impossible to do in PL/SQL....No Digest configured web server nearby or I would definitely have had a bash at this"A read through the Wikipedia article, and I came up with the code below:Firstly, after the initial request, go through the header to get the 'WWW-Authenticate' item. Take the value associated with that header, and pass it to the "auth_digest" procedure. l_max := UTL_HTTP.GET_HEADER_COUNT(l_http_response); l_ind := 1; l_name := '-'; while l_ind <= l_max AND l_name != 'WWW-Authenticate' LOOP UTL_HTTP.GET_HEADER(l_http_response, l_ind, l_name, l_value); IF l_name = 'WWW-Authenticate' AND l_http_response.status_code = UTL_HTTP.HTTP_UNAUTHORIZED THEN -- -- Unauthorized. Using the Authorization response header, we can come up with the -- required values to allow a re-request with the authentication/authorisation details -- dbms_application_info.set_action('auth:'||$$PLSQL_LINE); UTL_HTTP.END_RESPONSE(l_http_response); -- dbms_application_info.set_action('auth_req:'||$$PLSQL_LINE); l_http_request := UTL_HTTP.BEGIN_REQUEST(l_server||l_method); auth_digest (io_http_request => l_http_request, i_auth_value => l_value, i_username => nvl(i_username,'xxxx'), i_password => nvl(i_password,'xxxx'), i_req_path => l_method, i_client_nonce => null); dbms_output.put_line($$PLSQL_LINE||':Get Response from authenticated request'); dbms_application_info.set_action('auth_resp:'||$$PLSQL_LINE); l_http_response := UTL_HTTP.GET_RESPONSE(l_http_request); dump_resp (l_http_response); dump_hdr (l_http_response); END IF; l_ind := l_ind + 1;

END LOOP;

The auth_digest starts with an extraction of the 'valuables' from that value string. I've used regular expressions here. I spent time working with grep, awk and perl, and regexes are habit forming. procedure extract_auth_items (i_text in varchar2, o_realm out varchar2, o_qop out varchar2, o_nonce out varchar2, o_opaque out varchar2) is begin o_realm := substr(regexp_substr(i_text, 'realm="[^"]+' ),8); o_qop := substr(regexp_substr(i_text, 'qop="[^"]+' ),6); o_nonce := substr(regexp_substr(i_text, 'nonce="[^"]+' ),8); o_opaque := substr(regexp_substr(i_text, 'opaque="[^"]+'),9);

end extract_auth_items;

Next is the 'meat' where the values are combined in the various hashes. Yes, there's a hard-coded default client nonce in there that, by a strange coincidence, matches on in the wikipedia article. That's how this stuff gets developed, by following through a worked example. Just like school.

function digest_auth_md5_calcs

(i_username in varchar2, i_password in varchar2, i_req_path in varchar2,

A package with the code is available from my CodeSpace page, or directly here. There's a lot of debug 'stuff' in there. The code I'm using is still tailored to my single specific need, and I've stripped specific values from this published variant. You'll need to hard-code or parameterize it for any real use. I may be able to do a 'cleaned-up' version in the future, but don't hold your breath.