java 在 Oracle 11g 中选择 DBMS_CRYPTO 密码哈希

Select DBMS_CRYPTO password hashes in Oracle 11g

提问by ErrorNotFoundException

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:

我目前正在使用DBMS_CRYPTO.HASH. 然而，此时，我需要让我的 Java 应用程序能够以类似于 MySQL 的方式对用户输入的密码进行身份验证。就像是：

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.

在我的情况下，有没有更好的方法来实现这一目标？如果这很重要，我正在使用 Oracle 11g。

回答by blomqvie

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.

我建议您在代码中、数据库外进行哈希处理。这样您就独立于 DB 供应商，您只需将散列实现编写到一个地方。DB 列可以是常规 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.

1. 添加用户/更改密码时，会在插入/更新之前使用适当的算法和盐哈希提供密码。我建议至少使用 SHA-256。把盐放在哈希旁边！

2. 在认证过程中，获取用户的 hash 和 salt，用 salt 对提供的密码进行 hash 并与数据库中的 hash 进行比较。

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

例如，这里的哈希/加盐技巧：http: //crackstation.net/hashing-security.htm

回答by Justin Cave

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

假设散列密码存储在 中的RAW列中user_login_table，您可以简单地调用dbms_crypto.hashSQL 语句。根据您如何进行初始散列（特别是您如何将纯文本密码转换为RAW以及您使用的算法和选项），这样的事情会起作用

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 RAWand 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.

当然，作为良好编码实践的一般问题，您几乎肯定希望定义自己的函数来对密码进行散列，以便您可以嵌入有关如何将字符串转换为 a 的逻辑RAW并在一个地方指定散列算法。然后，您将从 SQL 语句中调用该新函数。这就是您可能还会添加适当的盐的功能。然后，您将使用该函数对表中的散列数据进行初始播种，并在将来验证密码散列。

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

我还假设您的实际查询username除了密码之外还有一个谓词

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

因此，在您的特定情况下，我希望您想要创建一个新功能 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'