Select DBMS_CRYPTO password hashes in Oracle 11g

Question

I am currently hashing my password field in my Oracle database by using DBMS_CRYPTO.HASH. At this point, however, I need to allow my Java application to be able to authenticate a password on user input in a way similar to what MySQL does. Something like:

SELECT *
FROM user_login_table
WHERE password = SHA1('MyPassword');

I am currently hashing via the following procedure:

CREATE OR REPLACE PROCEDURE 
  MUNGAI.p_auth_insert_user (
    par_username in varchar2,
    par_password in varchar2,
    par_work in varchar2
  )
IS l_hash raw(2000);
  BEGIN
    l_hash :=
      dbms_crypto.hash(
        utl_i18n.string_to_raw(par_password || par_work || upper(par_username),
        'AL32UTF8'
      ),
      dbms_crypto.hash_sh1
    );

    INSERT INTO user_login_table (user_name, p_word, work_class)
    VALUES (par_username, l_hash, par_work);
  END p_auth_insert_user;
/

I am then executing the procedure as follows, to insert into the table:

EXEC MUNGAI.p_auth_insert_user('MUNGAI', 'gatungo', '999')

Is there a better way to achieve this in my situation? I am using Oracle 11g, if that matters.

Answer 1

I would recommend you do the hashing in the code, outside database. This way you are DB-vendor independent and you have to write the hashing implementation to only one place. The DB column can be a regular varchar.

It would be something like this:

1. When adding a user/changing the password, hash provided password with proper algorithm and salt before the insert/update. I'd recommend at least SHA-256. Save the salt next to the hash too!

2. During authentication, obtain hash and salt for the user, hash the provided password with salt and compare to the hash from database.

Tips for hashing/salting for example in here: http://crackstation.net/hashing-security.htm