1 2 Previous Next 29 Replies Latest reply: Mar 5, 2014 10:16 PM by petra-K Go to original post RSS
      • 15. Re: How to change password using password table?
        Partha Sarathy S

        That is what i told. You can encrypt and store in table and then decrypt while giving alter user

        • 16. Re: How to change password using password table?
          onkar.nath

          you can have a encrypt and decrypt function using DBMS_CRYPTO  package. If the password pattern is consistent then rather than storing, just generate the password  on fly and change it. Like if the password should have current month number and some other fixed character or pattern then no need to store in any of the table.

           

          Onkar

          • 17. Re: How to change password using password table?

            Please give sample procedure.

             

            Thanks

            • 18. Re: How to change password using password table?
              Partha Sarathy S

              Check if this helps

               

              ORACLENERD: DBMS_CRYPTO: Example

               

              This is the sample code.

               

              Specification

              CREATE OR REPLACE
              PACKAGE p_encrypt
              AS
                FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW;
                FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2;
              END p_encrypt;
              /
              show errors

              Body

              CREATE OR REPLACE
              PACKAGE BODY p_encrypt
              AS
              --DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
              --IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
              --THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE)
                G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
                G_STRING VARCHAR2(32) := '12345678901234567890123456789012';
                G_KEY RAW(250) := utl_i18n.string_to_raw
                ( data => G_STRING,
                dst_charset => G_CHARACTER_SET );
                G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
                + dbms_crypto.chain_cbc
                + dbms_crypto.pad_pkcs5;
               
                FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW
                IS
                l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, G_CHARACTER_SET );
                l_encrypted RAW(32);
                BEGIN
                l_ssn := utl_i18n.string_to_raw
                ( data => p_ssn,
                dst_charset => G_CHARACTER_SET );

                l_encrypted := dbms_crypto.encrypt
                ( src => l_ssn,
                typ => G_ENCRYPTION_TYPE,
                key => G_KEY );
                                  
                RETURN l_encrypted;
                END encrypt_ssn;
               
                FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2
                IS
                l_decrypted RAW(32);
                l_decrypted_string VARCHAR2(32);
                BEGIN
                l_decrypted := dbms_crypto.decrypt
                ( src => p_ssn,
                typ => G_ENCRYPTION_TYPE,
                key => G_KEY );

                l_decrypted_string := utl_i18n.raw_to_char
                ( data => l_decrypted,
                src_charset => G_CHARACTER_SET );
                RETURN l_decrypted_string;
                END decrypt_ssn;
               
              END p_encrypt;
              /
              show errors

               

              Message was edited by: Partha Sarathy S

              • 19. Re: How to change password using password table?
                KarK

                I found this code in one of the thread:

                It may helps you

                 

                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

                • 21. Re: How to change password using password table?
                  onkar.nath

                  CREATE OR REPLACE PACKAGE enc_dec

                  AS

                     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;

                     FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;

                  END;

                  /

                   

                  CREATE OR REPLACE PACKAGE BODY enc_dec

                  AS

                       encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

                                                       + DBMS_CRYPTO.CHAIN_CBC

                                                       + DBMS_CRYPTO.PAD_PKCS5;

                       /*

                         ENCRYPT_DES is the encryption algorithem. Data Encryption Standard. Block cipher.

                         Uses key length of 56 bits.

                         CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext

                         block before it is encrypted.

                         PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based

                         Cryptography Standard

                       */

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

                       -- The encryption key for DES algorithem, should be 8 bytes or more.

                   

                       FUNCTION 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 encrypt;

                       FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC

                       IS

                          decrypted_raw      RAW (2000);

                       BEGIN

                          decrypted_raw := DBMS_CRYPTO.DECRYPT

                          (

                              src => p_encryptedText,

                              typ => encryption_type,

                              key => encryption_key

                          );

                          RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));

                       END decrypt;

                  END;

                  /

                  to save this proc code from someone to get the algorithm, wrap this procedure as

                   

                  wrap iname=enc_dec.sql oname=enc_dec.wrp

                  and run this .wrp in the DB.

                  Wrapping PL/SQL Source Code

                   

                  Onkar

                  • 22. Re: How to change password using password table?
                    hitgon

                    We are using below configurations


                    MD5 Usingè DBMS_OBFUSCATION_TOOLKIT

                     

                    SQL>     CREATE OR REPLACE FUNCTION get_hash_val (p_in VARCHAR2)

                    2         RETURN VARCHAR2

                    3      IS

                    4         l_hash   VARCHAR2 (2000);

                    5      BEGIN

                    6         l_hash :=

                    7            RAWTOHEX

                    8 (UTL_RAW.cast_to_raw

                    9 (DBMS_OBFUSCATION_TOOLKIT.md5 (input_string      => p_in)

                     

                    10                     )

                    11               );

                    12 RETURN l_hash;

                    13 END;

                    14  /

                     

                    Function created.

                     

                    SQL> SELECT  get_hash_val('Account Balance is 12345.67') FROM DUAL;

                     

                    GET_HASH_VAL('ACCOUNTBALANCEIS12345.67')

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

                     

                    A09308E539C35C97CD612E918BA58B4C

                     

                     

                    SQL> CREATE OR REPLACE

                    2  FUNCTION md5hash (str IN VARCHAR2)

                    3     RETURN VARCHAR2

                    4     IS v_checksum VARCHAR2(32);

                    5

                    6     BEGIN

                    7             v_checksum := LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW( sys.dbms_obf

                    uscation_toolkit.md5(input_string => str) ) ) );

                    8             RETURN v_checksum;

                    9             EXCEPTION

                    10                     WHEN NO_DATA_FOUND THEN

                    11                     NULL;

                    12 WHEN OTHERS THEN

                    13                     -- Consider logging the error and then re-raise

                    14                     RAISE;

                    15 END md5hash;

                    16  /

                     

                    Function created.

                     

                    SQL> SELECT md5hash('Account Balance is 12345.67') FROM DUAL;

                    MD5HASH('ACCOUNTBALANCEIS12345.67')

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

                    a09308e539c35c97cd612e918ba58b4c

                    DESCRIPTION

                    What this basically does is use the sys.dbms_obfuscation_toolkit.md5 function’s raw byte array, convert it to a hexadecimal string and convert that string to all lowercase characters. This way you can easily convert strings to MD5.

                    The MD5 result we were expecting that perfect match with EliteAAA Java Application

                    CAST_TO_RAW Function

                    Converts a VARCHAR2 represented using n data bytes into a RAW with n data bytes

                    RAWTOHEX

                    RAWTOHEX converts raw to a character value containing its hexadecimal representation

                     

                     

                    1. MD5 Using èDBMS_CRYPTO

                    HASH_MD5 (128 bit hash)

                    2 is the constant for MD5 hashing.

                    https://forums.oracle.com/forums/thread.jspa?threadID=996879

                    SQL> select dbms_crypto.hash(to_clob('hitesh'), 2) from dual;

                    DBMS_CRYPTO.HASH(TO_CLOB('HITESH'),2)

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

                    80E2235FD9A018996178A07A6A3F4FFF

                     

                    SQL> select dbms_crypto.hash(RAWTOHEX('hitesh'), 2) from dual;

                    DBMS_CRYPTO.HASH(RAWTOHEX('HITESH'),2)

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

                    80E2235FD9A018996178A07A6A3F4FFF

                     

                    SQL> select dbms_crypto.hash(utl_raw.cast_to_raw('hitesh'), 2) from dual;

                    DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('HITESH'),2)

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

                    80E2235FD9A018996178A07A6A3F4FFF

                     

                    DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT Feature Comparison

                    http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_crypto.htm#BJFFAJCC

                    • 23. Re: How to change password using password table?

                      Thanks all,

                       

                      I still have to analyze all your inputs

                       

                      I can not it relate it on how to attached it to my password table, and then fire the change password trigger using this encrypted value.

                      • 24. Re: How to change password using password table?
                        Partha Sarathy S

                        Its simple. Use DBMS_CRYPTO to create encrypted password and insert it into passwd_table. Then in the procedure, decrypt it while altering user.

                        • 25. Re: How to change password using password table?
                          KarK

                          See the example given:

                           

                           

                           

                          create table t_login (t_username varchar2(20),t_password RAW(2000)); 

                          While inserting call the passwd_enc_dec.fn_ENCRYPT to encrypt the password and insert it into the table.

                          INSERT INTO t_login VALUES('SCOTT',SYS.passwd_enc_dec.fn_ENCRYPT('SCOTT'));

                          SQL>select * from t_login;

                          T_USERNAME           T_PASSWORD
                          -------------------- ------------------------------
                          SCOTT                73DC2A2EC13228B0


                          While altering the user,call the passwd_enc_dec.fn_decrypt inside the procedure to decrypt the password.

                           

                           

                          CREATE OR REPLACE PROCEDURE SP_PASS(P_USERNAME VARCHAR2)

                          AS

                          L_PASSWORD VARCHAR2(20);

                          BEGIN

                          SELECT SYS.passwd_enc_dec.fn_decrypt(T_PASSWORD) INTO L_PASSWORD FROM t_login where t_username=P_USERNAME;

                          EXECUTE IMMEDIATE 'ALTER USER '||P_USERNAME||' IDENTIFIED BY '||L_PASSWORD;

                          END;

                          /

                           

                          SCOTT@orcl> exec SP_PASS('SCOTT')

                          PL/SQL procedure successfully completed.

                          • 26. Re: How to change password using password table?
                            BPeaslandDBA

                            Our database end-users like scott/tiger needs to change database password every month without loging in to sqlplus.

                            So they will enter the userid/password on a data-entry form like in apex where it stored in a table


                            I've worked on systems where it was too cumbersome to have the user sign in with SQL*Plus just to change their password. However, storing it in a database table and then changing the password later on is too cumbersome. Just create a stored procedure which accepts four parameters. 1) username, 2) old password, 3) new password, 4) new password again. This is pretty standard fare to ask for  these four things in password change forms. Even those coded in Apex. Then have the Apex form call the stored procedure and have the password changed immediately.

                             

                            Just my 2 cents,
                            Brian

                            • 27. Re: How to change password using password table?

                              Thanks all,

                               

                              I still can not get what you are proposing.

                               

                              Supposing I created the procedure. How can end-user scott access the procedure?  You mean I will be attached to the data-entry form?

                              • 28. Re: How to change password using password table?
                                sb92075

                                f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

                                 

                                Thanks all,

                                 

                                I still can not get what you are proposing.

                                 

                                Supposing I created the procedure. How can end-user scott access the procedure?  You mean I will be attached to the data-entry form?

                                GOOGLE APEX

                                • 29. Re: How to change password using password table?

                                  Yeah I know apex But want to clear if I need it, because my impression is that, there is a way to change password by using procedure only and not using a table???

                                   

                                  Thanks

                                  1 2 Previous Next