Tuesday, 27 October 2009

Part II - Working with WLS 10.3.1 SQLAuthenticator password algorithms

In the previous post we looked at how to configure the SQLAuthenticator password encryption options. Among other encryption algorithms we discovered that on creating a user from the WLS console, WLS would create the associated user in a database table with password "password" encrypted to:

{SHA-1}W6ph5Mm5Pz8GgiULbPgzG37mj9g=

...when the SHA-1 option was set.

As was mentioned in the previous post, as the database table with its users and passwords may be shared by non-WLS based applications, it's important that those systems can encrypt passwords and compare them to the WLS result. In other words, in the example above, given that WLS generated a SHA-1 encrypted password, if another system uses the same SHA-1 algorithm will it generate the same encrypted password allowing it to compare the database SHA-1 encrypted password against the SHA-1 encrypted password it has?

In order to check we can get the same encrypted results, we'll investigate generating a SHA-1 password using the Oracle database's encryption facilities (so in this case the database acts as the other subsystem), comparing the database's encrypted SHA-1 password to that of WLS.

The following solution owes thanks to Sean at Oracle Support who very patiently led me in the right direction with my findings.

dbms_crypto

Oracle database fans will be familiar with the dbms_crypto package that provides encryption support.

dbms_crypto allows us to generate an encrypted password that we can compare to the WLS result. From table 34-1 of the dbms_crypto link, we note that dbms_crypto supports the following one-way hash algorithms: SHA-1, MD4 and MD5. As WLS via the JCE extensions (see the previous post) supports SHA-1, MD2 and MD5, it's fortunate we picked SHA-1 for this example.

The following anonymous PL/SQL block shows an example using the dbms_crypto package hash function with SHA-1 to produce an encrypted result:

DECLARE
input_string VARCHAR2(8);
raw_input RAW(128);
encrypted_raw RAW(2048);
BEGIN
input_string := 'password';
raw_input := utl_raw.cast_to_raw(convert(input_string, 'AL32UTF8','US7ASCII'));

encrypted_raw := dbms_crypto.hash(src => raw_input, typ => dbms_crypto.hash_sh1);
dbms_output.put_line('Output: ' || encrypted_raw);
END;
/

Output: 5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
Note the output, a hex value, and doesn't match our WLS output for the same plaintext password "password" encrypted with SHA-1.

The missing bit of information (that I haven't found documented) is that WLS after encrypting the plaintext password, as confirmed by Oracle Support, WLS then converts the output to base 64. In the case of the dbms_crypto hash function, it converts the encrypted result to Hex. In order to get the same result you need to convert the Hex output to base 64.

There's a number of different ways to do this. One is to use a Java routine in the database, converting the dbms_crypto Hex result to a byte array, then byte array to base 64. A suitable algorithm would be:

byte[] bytearray = hexStringToByteArray("5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8");
String base64encoded = new BASE64Encoder().encodeBuffer(bytearray);
...where the hexStringToByteArray function is borrowed from Dave L on StackOverflow.

The end result is: W6ph5Mm5Pz8GgiULbPgzG37mj9g= ... finally matching what WLS wrote to the database (missing the algorithm prefix of course).

Conclusion

Why the WebLogic Server's SQLAuthenticator can make use of different encryption algorithm when writing to the database, it's important to ensure that the results are expected and understood and can be used by other subsystems.

6 comments:

John Stegeman said...

Hi Chris,

Nice post - I would suggest that you correct a single word that you use "encrypt" - technically, SHA-1 is "hashing" the password, not encrypting it. Hashes are not reversible, whereas encryption typically is reversible. Otherwise, the tenor of the article is just fine.

John

Chris Muir said...

Thanks for the comment John. I'll leave the comment to speak for itself.

CM.

JCFlack said...

If you want to encode or decode base64 in the database, you don't need a Java Stored procedure. 10g added a UTL_ENCODE package that has base64_encode and base64_decode functions. And if you need this in earlier versions of the database, I have some PL/SQL code that does it.

Chris Muir said...

Hi John

Good pointer. I did try the utl_encode package but received incorrect results. Probably my fault.

Cheers,

CM.

Erkki said...

I used the following SQL code to check for the correct password:

concat('{SHA-1}',
utl_raw.cast_to_varchar2(
utl_encode.base64_encode(
dbms_crypto.hash(
utl_raw.cast_to_raw(
'password'),3))))

'Eki

Manuel said...

thanks a lot
almost 4 years, this works perfectly for me