Using Hash SHA-256

Question

I know this is kind of an open ended question, but I need an example on how to use DBMS_CRYPTO to do a 1 way hash on a password column, using SHA-256. I'm not quite getting how to use the package and options to create this, and was hoping someone can provide some PL/SQL that will be able to do this task.

Many thanks!

score 0 · Answer 1 · answered Jun 08 '21 at 19:45

To get you started, this is a procedure similar to the one I use. See if it helps.

Table that contains my users and their usernames/passwords:

SQL> create table user_authentication
  2    (id       number,
  3     username varchar2(30),
  4     password varchar2(40));

Table created.

Procedure that hashes the password: as my database is 11gXE, I used hash_sh1 in line #11. You'd use hash_sh256 (11gXE doesn't support it).

SQL> create or replace
  2     procedure p_auth_insert_user (par_id           in number,
  3                                   par_username     in varchar2,
  4                                   par_password     in varchar2)
  5     is
  6        l_hash  raw (2000);
  7     begin
  8        l_hash :=
  9           dbms_crypto.hash (
 10              utl_i18n.string_to_raw (par_password, 'AL32UTF8'),
 11              dbms_crypto.hash_sh1);
 12
 13        insert into user_authentication (id, username, password)
 14             values (par_id, par_username, l_hash);
 15     end p_auth_insert_user;
 16  /

Procedure created.

Testing:

SQL> exec p_auth_insert_user(1, 'Littlefoot', 'stackoverflow');

PL/SQL procedure successfully completed.

SQL> select * from user_authentication;

        ID USERNAME                       PASSWORD
---------- ------------------------------ ----------------------------------------
         1 Littlefoot                     FDFEB16F096983ADA02DB49D46A8154475D700AE

SQL>

Using Hash SHA-256

1 Answers1