1 2 Previous Next 28 Replies Latest reply: Feb 26, 2014 5:11 AM by _Karthick_ RSS

    Password encryption

      Hi all,

       

      11.2.0.1

       

      I am debugging our app which can not login due to unmatched  passwords.

      I am check the password encrypt/decrypt trigger but I can not understand what it is doing.

      I want to check if the password I entered is really equal to the stored encrypted  password.

      Please help me how to do this. Thanks, pK

       

      create or replace

      TRIGGER "HR"."DEC_PASSWORD_TRIGGER"

      BEFORE

      INSERT ON "HR"."DECRYPTED_PASSWORD" FOR EACH ROW DECLARE

              enc_string VARCHAR2(64);

      BEGIN

              IF INSERTING THEN

                      enc_string := encrypt_data(:NEW.enc_data, 'ABCDEFGH');

                      :NEW.enc_data := enc_string;

              END IF;

      END;

       

      Question for the above: is "ENCRYPT_DATA" a built-in function of Oracle? or programmer created?

       

      Is ENCRYPT_DATA same function to encrypt decrypt data?

        • 1. Re: Password encryption
          _Karthick_

          Oracle 11.2 does not have any function called ENCRYPT_DATA. And why are you encrypting password? You should be hashing it.

           

          In your DB run the below query to know what ENCRYPT_DATA is.

           

          select *

            from all_objects

          where object_name = 'ENCRYPT_DATA';

           

          • 2. Re: Password encryption
            K.S.I.

            Hi.

            function ENCRYPT_DATA  isn't built-in

             

            Look at a source text of function... most likely used calls of dbms_crypto or dbms_obfuscation_toolkit  ... ( if function ENCRYPT_DATA  is not  wrapped  )

            select * from user_source s

            where  s.name = 'ENCRYPT_DATA'

            • 3. Re: Password encryption

              Thanks,

               

              I got this from the function:

               

              dbms_obfuscation_toolkit.desencrypt(

                         input_string => v_text,

                          key_string => p_key,

                          encrypted_string=>v_enc);

                  return v_enc;

               

              Is key mandatory? What is the standard name usage for key? is ''ABCDEGF' valid?

               

              Thanks

              • 4. Re: Password encryption
                K.S.I.

                The key for encoding and decoding shall match.

                You can take the ciphered password for any record and cause the Decrypt function for receiving the initial line of the password with your key  ABCDEF

                 

                simple example of decrypt function:

                 

                function decrypt( p_str in varchar2 ) return varchar2

                   as

                       l_data  varchar2(255);

                   begin

                       dbms_obfuscation_toolkit.DESDecrypt

                           ( input_string => p_str,

                             key_string   => 'ABCDEF',

                             decrypted_string=> l_data );

                 

                       return rtrim( l_data, chr(0) );

                   end;

                   /

                 

                You can show the function text  ENCRYPT_DATA ?

                • 5. Re: Password encryption
                  AnnPricks E

                  You can use DBMS_CRYPTO for encryption and decryption.. See the below code as well as test cases

                  create or replace 

                  PACKAGE  passwd_enc_dec  

                  as                       

                    FUNCTION main_key(p_key VARCHAR2) RETURN RAW DETERMINISTIC;

                    FUNCTION fn_ENCRYPT (p_plainText VARCHAR2,p_key VARCHAR2) RETURN RAW DETERMINISTIC; 

                    FUNCTION fn_decrypt (p_encryptedText RAW,p_key VARCHAR2) RETURN VARCHAR2 DETERMINISTIC; 

                  END; 

                      create or replace 

                      package body  passwd_enc_dec 

                      as 

                        encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

                                                       + DBMS_CRYPTO.CHAIN_CBC

                                                       + DBMS_CRYPTO.PAD_PKCS5;

                      FUNCTION MAIN_KEY(P_KEY IN VARCHAR2) RETURN RAW DETERMINISTIC IS  

                       encryption_key  RAW(32);

                      begin 

                        encryption_key  := UTL_RAW.CAST_TO_RAW(P_KEY); 

                        RETURN encryption_key; 

                        END MAIN_KEY; 

                        

                      FUNCTION fn_ENCRYPT (P_PLAINTEXT VARCHAR2,

                                           P_KEY VARCHAR2) RETURN RAW DETERMINISTIC IS 

                        encrypted_raw RAW (2000); 

                        crypto_key RAW(32); 

                        

                      begin 

                        crypto_key := main_key(P_KEY); 

                        encrypted_raw := dbms_crypto.encrypt ( 

                                           src => utl_raw.cast_to_raw (p_plaintext), 

                                           TYP => encryption_type, 

                                           KEY =>crypto_key); 

                        RETURN ENCRYPTED_RAW;

                        end; 

                      function fn_decrypt (p_encryptedtext RAW,

                                           P_KEY VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS 

                        decrypted_raw      RAW (2000); 

                        dummy VARCHAR2(1000); 

                        decrypto_key RAW(32); 

                      begin 

                        decrypto_key := main_key(P_KEY); 

                        decrypted_raw := dbms_crypto.decrypt ( 

                                           src => p_encryptedtext, 

                                           typ => encryption_type, 

                                           key => decrypto_key ); 

                        return (utl_raw.cast_to_varchar2 (decrypted_raw));

                      end; 

                      end;

                      /


                  Testecases for that:-

                  SQL>select passwd_enc_dec.fn_encrypt('Jhones','Hello World') encrypted from dual;

                   

                  ENCRYPTED

                  --------------------------------------------------------------------------------

                  874CDF02A9F67D69

                   

                  SQL>select passwd_enc_dec.fn_decrypt('874CDF02A9F67D69','Hello World') decrypted from dual;

                   

                  DECRYPTED

                  --------------------------------------------------------------------------------

                  Jhones

                   

                  Or else use key is a common value(fixed) in package

                  create or replace 

                  PACKAGE  passwd_enc_dec  

                  as

                    FUNCTION fn_ENCRYPT (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC; 

                    FUNCTION fn_decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC; 

                  END; 

                      create or replace 

                      package body  passwd_enc_dec 

                      as 

                        encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

                                                       + DBMS_CRYPTO.CHAIN_CBC

                                                       + DBMS_CRYPTO.PAD_PKCS5;

                        encryption_key     RAW (32) := UTL_RAW.cast_to_raw('Hello World');                                    

                         

                      FUNCTION fn_ENCRYPT (P_PLAINTEXT VARCHAR2) RETURN RAW DETERMINISTIC IS 

                        encrypted_raw RAW (2000); 

                         

                      begin

                        encrypted_raw := dbms_crypto.encrypt ( 

                                           src => utl_raw.cast_to_raw (p_plaintext), 

                                           TYP => encryption_type, 

                                           KEY =>encryption_key); 

                        RETURN ENCRYPTED_RAW;

                        end; 

                      function fn_decrypt (p_encryptedtext RAW) RETURN VARCHAR2 DETERMINISTIC IS 

                        decrypted_raw      RAW (2000); 

                        dummy VARCHAR2(1000); 

                        decrypto_key RAW(32); 

                      begin 

                        decrypted_raw := dbms_crypto.decrypt ( 

                                           src => p_encryptedtext, 

                                           typ => encryption_type, 

                                           KEY => encryption_key ); 

                        return (utl_raw.cast_to_varchar2 (decrypted_raw));

                      end; 

                      end;

                      /


                  SQL>select passwd_enc_dec.fn_encrypt('Jhones') encrypted from dual;

                   

                  ENCRYPTED

                  --------------------------------------------------------------------------------

                  874CDF02A9F67D69


                   

                  SQL>select passwd_enc_dec.fn_decrypt('874CDF02A9F67D69') decrypted from dual;

                   

                  DECRYPTED

                  --------------------------------------------------------------------------------

                  Jhones

                  • 6. Re: Password encryption
                    _Karthick_

                    Encryption is not the right choice for securing the password. Use One way hash as oracle does to secure its password.

                    • 7. Re: Password encryption
                      AnnPricks E

                      Yes agree with that. If i encrypt the password using hash then i can't able to decrypt that right? That is what, you are coming to say right?.. Just see the below

                      CREATE OR REPLACE

                      FUNCTION TEST_ENCRYPTUSRPWD(V_USRPASSWORD  VARCHAR2

                                                 )

                      RETURN VARCHAR2

                      AS

                      V_MACKEY            VARCHAR2(6) := 'HELLO';

                      V_RAWENCRYPTDATA    RAW(4000);

                      V_HEXENCRYPTEDDATA  VARCHAR2(4000);

                      BEGIN

                      V_RAWENCRYPTDATA := DBMS_CRYPTO.MAC(UTL_RAW.CAST_TO_RAW(V_USRPASSWORD),DBMS_CRYPTO.HMAC_SH1,UTL_RAW.CAST_TO_RAW(V_MACKEY));

                      V_HEXENCRYPTEDDATA := RAWTOHEX(V_RAWENCRYPTDATA);

                      RETURN V_HEXENCRYPTEDDATA;

                      EXCEPTION

                      END TEST_ENCRYPTUSRPWD;

                      • 8. Re: Password encryption
                        _Karthick_

                        Yes, Basically there is no necessity to know the actual value of a stored password. All we need is to validate it with the password supplied by the user. For that one way hashing is the best approach.

                        • 9. Re: Password encryption

                          I thank you all

                           

                          I can now see the userpass_table clear text password value and was able to change it and the app can now connect okay.

                          I know that there is password encryption because of the trigger attached on the table.

                           

                          I have another userpass_table which has no encryption trigger attached to the table. sample password value is

                          user1LKRKZ2gbeBfBMFDpc05fxQ==
                          user2LKRKZ2gbeBfBMFDpc05fxQ==
                          user3LKRKZ2gbeBfBMFDpc05fxQ==
                          user4LKRKZ2gbeBfBMFDpc05fxQ==
                          user5LKRKZ2gbeBfBMFDpc05fxQ==

                           

                          Since there is no ecryption trigger, so it must be default or generic encrpyted being used?

                          How can I decrypt it using the generic way?

                           

                          Thanks,

                          pK

                          • 10. Re: Password encryption
                            AnnPricks E

                            Yes.. I think OP needs decryption method also that is what i gave that solution.. Fine.. You are right..

                            • 11. Re: Password encryption
                              AnnPricks E

                              Generic means without key value right?

                              • 12. Re: Password encryption
                                AnnPricks E

                                Instead of decryption, use encrypt method using hash.. Once encrypted cant able to decrypt that password. That is more security..

                                • 13. Re: Password encryption

                                  Well maybe,

                                  because I can not find any trigger related to this password table.

                                  Can you give me what possible command to decrypt it?

                                  • 14. Re: Password encryption
                                    AnnPricks E

                                    First you need to see how they are encrypting that password. Because if they are using some hash function to encrypt the password(which i showed above) then we can't decrypt that password. First find out how they are encrypting that password and let me know.. Will check possible solution for that.

                                    1 2 Previous Next