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

Assuming that the hashed password is stored in a RAW column in user_login_table, you could simply call dbms_crypto.hash in your SQL statement. Depending on how you are doing the initial hashing (in particular how you are converting the plain-text password to RAW and what algorithm and options you're using), something like this would work

select * 
  from user_login_table 
 where password = dbms_crypto.hash( utl_i18n.string_to_raw( 'MYPassword', 'AL32UTF8' ),
                                    <<whatever hash algorithm you want to use>> );

Of course, as a general matter of good coding practices you almost certainly want to define your own function that hashes the password so that you can embed the logic for how to convert the string to a RAW and specify the hash algorithm in a single place. You would then call that new function from your SQL statement. That's the function where, presumably, you would add an appropriate salt as well. You would use that function, then, both to do the initial seeding of hashed data in the table and to verify password hashes in the future.

I would also assume that your actual query would have a predicate on username in addition to password

select * 
  from user_login_table 
 where password = new_function_name( 'MYPassword' )
   and username = 'YourUserName'

Otherwise, the query you posted would merely validate that the password matched the password of someone in the database not the specific person that was trying to log in. Plus, it would return multiple rows if two people had the same password hash.

In your specific case, therefore, I would expect that you would want to create a new function hash_password

CREATE OR REPLACE function MUNGAI.hash_password(par_username in varchar2,
                              par_password in varchar2,
                              par_work in varchar2
                             )
  return raw
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
     );
  return l_hash;
end;

You would then call this function from your insert 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 := hash_password( par_username, par_password, par_work );

  insert into user_login_table
    (user_name, p_word, work_class)
   values
    (par_username, l_hash, par_work);
end p_auth_insert_user;
/

Your query would then be

select * 
  from user_login_table 
 where password = new_function_name( username, 
                                     'MYPassword', 
                                     <<whatever `par_work` is supposed to be>> )
   and username = 'YourUserName'

Answer 2

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