CODEWITHSUNDEEP
oracle-database
Dream Coder
Dream Coder

Reputation: 39

Oracle 12c Hash encryption

I want to store hash data for the password of the user table. So I want to know the what's data type of the UserPassword field. How can I insert data to the UserPassword column & retrieve from the validate user from the User table.

SysUsers Table column: (USERID NUMERIC, USERNAME VARCHAR(50), USERPASSWORD [Data_Type]).

Insert: INSERT INTO SYSUSERS(USERID, USERNAME, USERPASSWORD) VALUES(1, 'John', _____)

Retreive Data: SELECT * FROM SYSUSERS WHERE USERNAME = 'John' AND USERPASSWORD = ____

Upvotes: 2

Views: 4528

Answers (2)

Codo
Codo

Reputation: 78835

To safely store password, several conditions must be met:

  • Store a hash of the password instead of the password itself
  • Use salted password to prevent dictionary attacks
  • Use a slow hash function to prevent brute force attacks

See Secure Salted Password Hashing.

Additionally, I recommend to use a hash function and an encoding that can be implemented on other platforms as well because one day, you'll want to migrate away from Oracle or you might want to hash the password in your application server (instead of in the database).

A proven function for password hashing is PBKDF2. Oracle even uses it to store the user's passwords. However, there is no direct implementation available.

Mike Pargeter has published a PL/SQL implementation in PBKDF2 in Oracle:

CREATE OR REPLACE FUNCTION pbkdf2
( p_password   IN VARCHAR2
, p_salt       IN VARCHAR2
, p_count      IN INTEGER
, p_key_length IN INTEGER
)
  RETURN VARCHAR2
IS
  l_block_count INTEGER;
  l_last        RAW(32767);
  l_xorsum      RAW(32767);
  l_result      RAW(32767);
BEGIN
  l_block_count := ceil(p_key_length / 20);  -- 20 bytes for SHA1.

  FOR i IN 1..l_block_count
  LOOP
    l_last := utl_raw.concat(utl_raw.cast_to_raw(p_salt), utl_raw.cast_from_binary_integer(i, utl_raw.big_endian));

    l_xorsum := NULL;

    FOR j IN 1..p_count
    LOOP
      l_last := dbms_crypto.mac(l_last, dbms_crypto.hmac_sh1, utl_raw.cast_to_raw(p_password));

      IF l_xorsum IS NULL
      THEN
        l_xorsum := l_last;
      ELSE
        l_xorsum := utl_raw.bit_xor(l_xorsum, l_last);
      END IF;

    END LOOP;

    l_result := utl_raw.concat(l_result, l_xorsum);

  END LOOP;

  RETURN rawtohex(utl_raw.substr(l_result, 1, p_key_length));

END pbkdf2;
/

Note that the function returns the result as a hex encoded string. That's how you want it so you can migrate to another system if needed.

To generate the salt, use the cryptographic random function and store it together with the password hash as you need to for password verification:

rawtohex(DBMS_CRYPTO.RANDOMBYTES(32))

Upvotes: 2

Praveen
Praveen

Reputation: 9335

One way to store pass word is by using a A one-way hash function

A one-way hash function is a hash function that works in one direction. It is easy to compute a hash value from the input data, but it is hard to generate data that hashes to a particular value. Consequently, one-way hash functions work well to ensure data integrity

You can raw datatype for the password column
Use DBMS_CRYPTO.Hash to generate the raw value from the password
Use UTL_RAW.COMPARE to check the pass word matches.

Demo

CREATE TABLE UserTable(
    usertId NUMBER(10) PRIMARY KEY ,
    username NVARCHAR2(50) NOT NULL,
    password RAW(500) NOT NULL
);
/
--Create table, executed in 106 ms

INSERT INTO UserTable 
SELECT 1, 'John', DBMS_CRYPTO.hash(utl_raw.cast_to_raw('abcd123'), 3) 
FROM dual;
--Insert - 1 row(s), executed in 37 ms

SELECT 
    usertId, username
FROM UserTable
WHERE UTL_RAW.COMPARE(password, DBMS_CRYPTO.hash(utl_raw.cast_to_raw('abcd123'), 3)) = 0;
--USERTID   USERNAME
--1         John

SELECT 
    usertId, username
FROM UserTable
WHERE UTL_RAW.COMPARE(password, DBMS_CRYPTO.hash(utl_raw.cast_to_raw('ABCD123'), 3)) = 0;
--no row selected

Upvotes: 0

Related Questions