Encrypt and Decrypt Passwords in Database

Sometimes we store passwords in database table regarding to business requirement.
If we store password as plain text in table, Everyone who have access to database can read password easily. That's mean big security hole.

So I decided to develop package for encrypting and decrypting password.
I used DBMS_OBFUSCATION_TOOLKIT, DBMS_CRYPTO built-ins package to help me doing encryption and decryption.

I developed MAHMOUD_ENCRYPT_DECRYPT package which contains four functions (ENCRYPT1, ENCRYPT2, DECRYPT1, DECRYPT2) .

ENCRYPT1 and  DECRYPT1 functions use DBMS_OBFUSCATION_TOOLKIT package.
ENCRYPT2 and  DECRYPT2 functions use DBMS_CRYPTO package.

Package Specification  

 CREATE OR REPLACE PACKAGE MAHMOUD_ENCRYPT_DECRYPT  
 AS  
   FUNCTION ENCRYPT1 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2;  
   FUNCTION ENCRYPT2 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2;  
   FUNCTION DECRYPT1 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2;  
   FUNCTION DECRYPT2 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2;  
 END MAHMOUD_ENCRYPT_DECRYPT;  

Package Body

 CREATE OR REPLACE PACKAGE BODY MAHMOUD_ENCRYPT_DECRYPT  
 AS  
   GS$KEY  VARCHAR2 (8) := 'MAHMOUD';  
   FUNCTION ENCRYPT1 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2  
   IS  
    LC$ENCRYPTED_VALUE   VARCHAR2 (50);  
    LC$PASSWORD_MUTIPLES  VARCHAR2 (50);  
    LN$LENGTH       NUMBER;  
   BEGIN  
    GS$KEY := RPAD (GS$KEY, CEIL (LENGTH (GS$KEY) / 8) * 8, CHR (0));  
    LN$LENGTH := LENGTH (GS$KEY);  
    LC$PASSWORD_MUTIPLES :=  
      RPAD (IN_PASSWORD,  
         CEIL (LENGTH (IN_PASSWORD) / LN$LENGTH) * LN$LENGTH,  
         CHR (0));  
    LC$ENCRYPTED_VALUE :=  
      DBMS_OBFUSCATION_TOOLKIT.  
      DESENCRYPT (INPUT_STRING  => LC$PASSWORD_MUTIPLES,  
            KEY_STRING   => GS$KEY);  
    RETURN LC$ENCRYPTED_VALUE;  
   END ENCRYPT1;  
   FUNCTION ENCRYPT2 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2  
   IS  
    LR$PASSWORD     RAW (100);  
    LR$ENCRYPTED_VALUE  RAW (1024);  
   BEGIN  
    LR$PASSWORD := UTL_RAW.CAST_TO_RAW (IN_PASSWORD);  
    LR$ENCRYPTED_VALUE :=  
      DBMS_CRYPTO.  
      ENCRYPT (LR$PASSWORD,  
          DBMS_CRYPTO.DES_CBC_PKCS5,  
          UTL_RAW.CAST_TO_RAW (GS$KEY));  
    RETURN UTL_RAW.CAST_TO_VARCHAR2 (LR$ENCRYPTED_VALUE);  
   END ENCRYPT2;  
   FUNCTION DECRYPT1 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2  
   IS  
    LC$DECRYPTED_VALUE  VARCHAR2 (50);  
   BEGIN  
    LC$DECRYPTED_VALUE :=  
      DBMS_OBFUSCATION_TOOLKIT.  
      DESDECRYPT (INPUT_STRING => IN_PASSWORD, KEY_STRING => GS$KEY);  
    RETURN LC$DECRYPTED_VALUE;  
   END DECRYPT1;  
   FUNCTION DECRYPT2 (IN_PASSWORD VARCHAR2)  
    RETURN VARCHAR2  
   IS  
    lr$password     RAW (1024);  
    LC$DECRYPTED_VALUE  RAW (1024);  
   BEGIN  
    lr$password := UTL_RAW.CAST_TO_RAW (in_password);  
    LC$DECRYPTED_VALUE :=  
      DBMS_CRYPTO.decrypt (lr$password, DBMS_CRYPTO.des_cbc_pkcs5, GS$KEY);  
    RETURN UTL_RAW.CAST_TO_VARCHAR2 (LC$DECRYPTED_VALUE);  
   END DECRYPT2;  
 END MAHMOUD_ENCRYPT_DECRYPT;  

Note : If you face below exception when compiling package body

PLS-00201: identifier 'DBMS_CRYPTO' must be declared

Then ask dba to grant your user execute on DBMS_CRYPTO package

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO <<USER>>;

Testing The Package

 DECLARE  
   lc$string      VARCHAR2 (50);  
   lc$encrypted_value  VARCHAR2 (100);  
   lc$derypted_value  VARCHAR2 (100);  
 BEGIN  
   lc$string := 'Mahmoud A. El-Sayed';  
   DBMS_OUTPUT.put_line ('Begin Encrypt using ENCRYPT1 function');  
   lc$encrypted_value := MAHMOUD_ENCRYPT_DECRYPT.encrypt1 (lc$string);  
   DBMS_OUTPUT.  
   put_line (  
    '"' || lc$string || '" after encrypting ==>> ' || lc$encrypted_value);  
   DBMS_OUTPUT.put_line ('Begin Decrypt using DECRYPT1 function');  
   lc$derypted_value := MAHMOUD_ENCRYPT_DECRYPT.decrypt1 (lc$encrypted_value);  
   DBMS_OUTPUT.  
   put_line (  
      '"'  
    || lc$encrypted_value  
    || '" after decrypting ==>> '  
    || lc$derypted_value);  
   DBMS_OUTPUT.put_line ('Begin Encrypt using ENCRYPT2 function');  
   lc$encrypted_value := MAHMOUD_ENCRYPT_DECRYPT.encrypt2 (lc$string);  
   DBMS_OUTPUT.  
   put_line (  
    '"' || lc$string || '" after encrypting ==>> ' || lc$encrypted_value);  
   DBMS_OUTPUT.put_line ('Begin Decrypt using DECRYPT2 function');  
   lc$derypted_value := MAHMOUD_ENCRYPT_DECRYPT.decrypt1 (lc$encrypted_value);  
   DBMS_OUTPUT.  
   put_line (  
      '"'  
    || lc$encrypted_value  
    || '" after decrypting ==>> '  
    || lc$derypted_value);  
 END;  

Output of Testing

Begin Encrypt using ENCRYPT1 function
"Mahmoud A. El-Sayed" after encrypting ==>> F ب¥ «ئ QدآK-‍ْK‏4—¬k"ç%
Begin Decrypt using DECRYPT1 function
"F ب¥ «ئ QدآK-‍ْK‏4—¬k"ç%" after decrypting ==>> Mahmoud A. El-Sayed

Conclusion
Encrypting and decrypting password can be done by a lot of ways in database.
I use built-ins package in this post to decrypt and decrypt.
You may develop your own algorithm or use Java to do the same task.
Choose the correct way that is suitable for your requirements.

Thanks
Mahmoud A. El-Sayed