18 May, 2012

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

16 comments:

  1. Note that If you you change key value
    GS$KEY VARCHAR2 (8) := 'MAHMOUD';
    then every old encrypted values will get wrong value with new key.
    To get the correct value in decryption you should use the key that was used in encryption.

    ReplyDelete
  2. You should warp package body code in production environment to hide encryption key.

    ReplyDelete
  3. My personal preference is encrypt the password using a technique that results in encoding that can't be decrypted. If a user needs to reset the password then steps can be built into the application layer that makes them answer questions and confirm using a code sent to their email address.

    If you need to check the password then encrypt the submitted string using the same technique and see if the result matches what is stored.

    ReplyDelete
    Replies
    1. I encrypt passwords to be unreadable in database tables

      Delete
    2. Always do your business logic in database and GUI is for showing data only.

      Delete
  4. You can also create your custom encryption algorithm, In this post I used built ins package supported by oracle.

    ReplyDelete
  5. You should NOT encrypt passwords, you should hash them with a keyed hash. Look at dbms_crypto.mac. This is a one-way function. Don't even think about using md5 (lookup rainbow tables)

    ReplyDelete
  6. Oh, and never invent your own encryption algorithm. People much smarter than us do that for a living and have a peer review process to validate them.

    ReplyDelete
    Replies
    1. Why you don't encourage an innovation

      Delete
    2. Because you need a lot of experience if you want to create a secure encryption algorithm. Just knowing how to write applications isn't enough by far! Without the proper mathematical knowledge and experience, you're certainly going to create an encryption which is easy to crack!
      If you build database applications, you should focus on database applications and not reinvent the wheel where it isn't necessary and even risky. Just look at the process which was used when AES was standardized, it's pretty insane..

      Delete
  7. Oh, and any compiled code that references the password table or your function should do so through dynamic SQL so it's harder to find the code trail via dba_depenancies...

    ReplyDelete
    Replies
    1. If you make it dynamic code after execute the dynamic code it will displayed in dba_depenancies view also, so It is better to wrap your package body.
      I mentioned this point in previous comment

      Delete
    2. But be aware that unwrapping an Oracle package isn't a big deal! If it has to be secure, you should probably save your password as a hash in combination with a salt by using something like bcrypt. In this case you cannot get the password unless the password was too simple or the attacker has a super computer underneath his desk..

      Delete
  8. Problems with this

    a) you cannot use varchar2, you will have character set issues. You have code that says "I'm sending you a varchar2, but it is really raw"

    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);


    that means that whoever is calling this - has raw bits and bytes in a string and is sending it to you.

    If your database character set is not identical to the client character set - there will be character set translation taking place. *The bits and bytes sent by the client will be converted using rules from once character set to another*. In short, it will not work.

    You have to use RAW and only RAW - OR, you have to convert the raw to hex using rawtohex -- and convert back using hextoraw.

    b) storing the key in the package is a horrifically bad idea, I don't care if you wrap this (there are unwrappers). Furthermore, if I gain access to your database (I steal a backup), I have unlimited access to everything (I'll restore and be in as sysdba, I won't even need to "see" your code, I'll just access it.

    c) storing the key outside of the database is unmanageable, key management is *hard*, really really really *hard*, really hard. And you'd have to store it encrypted (to protect against theft) and the client would have to have a way to access it securely, and you'd have to have a secure way to get the client the key to decrypt it and so on and so on and so on.

    d) you wrote:

    If you make it dynamic code after execute the dynamic code it will displayed in dba_depenancies view also, so It is better to wrap your package body.
    I mentioned this point in previous comment

    that is false, if you make it dynamic, it will never appear in dba_dependencies, not that it matters - that would just be security via obscurity. And wrapping doesn't secure it. It just makes it one degree harder to snoop the code.



    tylermuth above gave the only correct answer here - do not store encrypted passwords, and use a one way hashing function using dbms_crypto.mac.


    *never store passwords*.

    You never need to
    It exposes you horribly if you do


    Thomas Kyte
    http://asktom.oracle.com/

    ReplyDelete
    Replies
    1. Hi Tom,
      I am happy because of your passing at my blog.
      As your habit, you always provide us with good solutions and knowledge.

      I am good follower of you fantastic asktom site.
      Again I am thankful and I hope you continue in guiding us.

      Thanks & Kind Regards

      Delete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...