Introduction

The way Oracle stores the password in database is using Hash algorithm. We know a technique called enryption for storing the password, but it really not a safe idea to implement encryption for storing the password. Because all that matters in encryption is the “key”, which is used to encrypt the password.
In this short post we will see the difference between encryption and Hashing and how it can be used in Oracle database.
Oracle supports encryption but it doesnt use encryption for storing password. Instead it uses hashing for storing the password.

Oracle Encryption:

In oracle database, we can encrypt any string using DBMS_OBFUSCATION_TOOLKIT package. There is a procedure called DSEEncrypt which can be used to encrypt the string

The basic inputs that this procedure ask is input string which you want to encrypt, the encryption key to be used and gives output as encrypted password.
You can use DESDecrypt procedure of the same package to decrypt the password.
You need to decrypt the password using the same key.

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 varchar2(2000) := 'advait';
 3  l_temp varchar2(100);
 4  opvar varchar2(2000);
 5  begin
 6  l_temp := rpad (var1, (trunc(length(var1)/8)+1)*8, chr(0));
 7  dbms_obfuscation_toolkit.DESEncrypt(input_string => l_temp, key_string => 'oracleencryptionkey', encrypted_string => opvar);
 8  dbms_output.put_line('Enc Output :'||opvar);
 9  dbms_obfuscation_toolkit.DESdEcrypt(input_string => opvar, key_string => 'oracleencryptionkey', decrypted_string => var1 );
 10  dbms_output.put_line('Dec Output :'||var1 );
 11  end;
 12  /
Enc Output :I?8??+)i
Dec Output :advait

PL/SQL procedure successfully completed.

Problem with is method is the encryption key. Everytime we want to veryfy the password we need to know the encryption key. Without encryption key we cannot encrypt or decript the password and hence cannot be verfied. Now if someone comes to know about encryption key, he knows everything.

Oracle Hashing:

Next we will see how the Oracle uses hashing for storing the password in database. Hash is basically a key generated for specific input string. Example, we can define a hashing table which will have several finite entries. With any input string we can have some logic applied on that string and get a hash value for that sting. It could be a simple logic of checking a length of the string multiply that with some number and get a hash value for that from hash table to a very complex algorithm. Also we can hash a string to a hash value but we cannot get back our original string from a hash value. So how does password varification happens.
What Oracle database does it when it receives the password it gets a hash value of that password and stors it in USER$ table. When a user tries to login using the password it will again get the hash value of that password and compare that with hash value stored in USER$ table. If they match then user is in if now he is denied access.

Does this mean that 2 same password (example : welcome) will have same hash value? The answer is yes.
In that case a user sys and user scott has both password as tiger it will hash it to same value and user scott can login as sys using his password. Hence in order to avoid this we should usually hash a biger string instead of just the password. Like for example a ‘username/password’. This combination will be unique and a user with his password can login. If this case even if scott and sys has same password tiger the hash key for scott/tiger and sys/tiger “might” differ.
I said might because even 2 differnt strings can have a same hash value. The best hasing alogithm is the one which tries to give different values for each different input string. Basically we are trying to convert the infinite number of strings into finite number of hash values so there could be same hash values for different input strings.
Below is the simple hashing function we can use in Oracle. Oracle database itself uses some complete function to store the password.

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 number;
 3  begin
 4  var1 := dbms_utility.GET_HASH_VALUE('welcome',1000000000, power(2,30));
 5  dbms_output.put_line(var1);
 6  end;
 7  /
1291289758

PL/SQL procedure successfully completed.

So the hash value that we get here is a number. You can convert the same in hexadecimal as well.
GET_HASH_VALUE function takes 3 inputs and returns number. The 3 inputs that it takes are 1) the input string to be hashed 2) base value to be added to the hash number that we get 3) size of the hash table to be used
You can vary your input and change the hash value that you can get. Example instead of adding a constant value of 1000000000 to hash value I can as well give power(10,length(‘welcome’)). So depending on the input string my base value to be added will change as shown below.

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 number;
 3  begin
 4  var1 := dbms_utility.GET_HASH_VALUE('welcome',power(10,length('welcome')),power(2,30));
 5  dbms_output.put_line(var1);
 6  end;
 7  /
301289758

PL/SQL procedure successfully completed.

Another logic that you can apply for securing your password is encrypt the password and then get a hash key for the same and then store the hash key like below

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 varchar2(2000) := 'welcome';
 3  l_temp varchar2(100);
 4  opvar varchar2(2000);
 5  var2 number;
 6  begin
 7  l_temp := rpad (var1, (trunc(length(var1)/8)+1)*8, chr(0));
 8  dbms_obfuscation_toolkit.DESEncrypt(input_string => l_temp, key_string => 'oracleencryptionkey', encrypted_string => opvar);
 9  var2 := dbms_utility.GET_HASH_VALUE(var1,power(10,length(var1)),power(2,30));
 10  dbms_output.put_line('Enc Output :'||opvar);
 11  dbms_output.put_line('Hash value :'||var2);
 12  end;
 13  /
Enc Output :?B;FAE
Hash value :301289758

PL/SQL procedure successfully completed.

So in this case even if the user is able to find the encryption key, he wont know the hashing algorithm and he wont be able to crack.

Hope this helps !!