1 2 Previous Next 17 Replies Latest reply on Apr 13, 2018 10:27 AM by Eric Z-Oracle

    Display EBS User password

    Beauty_and_dBest

      EBS 12.x

      OL6

      11gR2

       

       

      Hi ALL,

       

      We found this script to display or decrypt passwords in EBS, (just in case they forgot?)

       

      SELECT ppf.person_id,

             ppf.first_name || ' ' ||

                               CASE WHEN ppf.middle_names IS NOT NULL

                                         THEN substr(ppf.middle_names,1,1) || '. '

                                         ELSE ' '

                               END ||

             ppf.last_name person_name,

             ppf.effective_end_date,

             fu.end_date,

             fu.user_name,

             IPC_DECRYPT_ORA_USR_PWD(fu.encrypted_user_password) user_pass

                      FROM per_people_f ppf LEFT JOIN fnd_user fu

                                  ON PPF.PERSON_ID = fu.employee_id

                      WHERE 1= 1

                                  and fu.user_name = 'JWAGNER'

                      -- ppf.attribute3 = 'FINANCE AND ACCOUNTING'

                               and ppf.effective_end_date = '31-DEC-4712'

                               and fu.end_date IS NULL

                      ORDER BY ppf.last_name,

                                    ppf.first_name;

       

       

      Is this supported? or anyone have this kind of script?

       

       

      Kind regards,

      jc

        • 1. Re: Display EBS User password
          Eric Z-Oracle

          Hi JC,

           

          We provide an fnd package fnd_web_sec.get_guest_username_pwd to allow you decrypt fnd user password.

          But it is not recommended to run on production for security concerns.

          Anyway, below example will work for you, which is found in this external blog => [Decrypting User Password]How to find password of a User in Oracle Apps R12? | OracleAppsDNA

           

          CREATE OR REPLACE PACKAGE get_pwd

          AS

             FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

                RETURN VARCHAR2;

          END get_pwd;

          /

           

          CREATE OR REPLACE PACKAGE BODY get_pwd

          AS

             FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

                RETURN VARCHAR2

             AS

                LANGUAGE JAVA

                NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

          END get_pwd;

          /

           

          SELECT usr.user_name,

                 get_pwd.decrypt

                    ((SELECT (SELECT get_pwd.decrypt

                                        (fnd_web_sec.get_guest_username_pwd,

                                         usertable.encrypted_foundation_password

                                        )

                                FROM DUAL) AS apps_password

                        FROM fnd_user usertable

                       WHERE usertable.user_name =

                                (SELECT SUBSTR

                                            (fnd_web_sec.get_guest_username_pwd,

                                             1,

                                               INSTR

                                                    (fnd_web_sec.get_guest_username_pwd,

                                                     '/'

                                                    )

                                             - 1

                                            )

                                   FROM DUAL)),

                     usr.encrypted_user_password

                    ) PASSWORD

            FROM fnd_user usr

          WHERE usr.user_name = '&USER_NAME';

           

          As to the IPC package, I bet it's a custom package.

           

          Cheers!

          Eric

          • 2. Re: Display EBS User password
            John_K

            It does look like the IPC package is custom - neither ETRM or Google throws up any results.

             

            Also (out of interest) I compiled and ran the process Eric provided however it returns null for my user when run as apps.

            • 3. Re: Display EBS User password
              Eric Z-Oracle

              Hi John,

               

              It is used for decrypting FND users, I don't think database schemas are included.

              I had tested this with sysadmin user, and it worked fine.

               

              Cheers!

              Eric

              • 5. Re: Display EBS User password
                John_K

                Yes, it was for an FND_USER. I ran it as the APPS database user, passing in an FND_USER name.

                • 6. Re: Display EBS User password
                  Eric Z-Oracle

                  Hi John,

                   

                  How did you test it? With which user?

                  I wonder how it goes for JC though, as for me it worked well, and I'm really curious about why it didn't work for you.

                  Keep me updated.

                   

                  Cheers!

                  Eric

                  • 7. Re: Display EBS User password
                    John_K

                    Tried various users, just returns null for each.

                     

                    SQL> set echo on
                    SQL> select user from dual;
                    USER
                    ------------------------------
                    APPS
                    1 row selected.
                    SQL> @a.sql
                    SQL> create or replace package get_pwd as
                      2    function decrypt(key in varchar2, value in varchar2)
                      3      return varchar2;
                      4  end get_pwd;
                      5  /
                    Package created.
                    SQL>
                    SQL>
                    SQL>
                    SQL> create or replace package body get_pwd as
                      2    function decrypt(key in varchar2, value in varchar2)
                      3      return varchar2 as
                      4      language java
                      5      name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String' ;
                      6  end get_pwd;
                      7  /
                    Package body created.
                    SQL>
                    SQL>
                    SQL>
                    SQL> select usr.user_name,
                      2         get_pwd.decrypt(
                      3           (select (select get_pwd.decrypt(fnd_web_sec.get_guest_username_pwd,
                      4                                           usertable.encrypted_foundation_password)
                      5                      from dual)
                      6                     as apps_password
                      7              from fnd_user usertable
                      8             where usertable.user_name =
                      9                   (select substr(fnd_web_sec.get_guest_username_pwd,
                     10                                  1,
                     11                                  instr(fnd_web_sec.get_guest_username_pwd, '/') - 1)
                     12                      from dual)),
                     13           usr.encrypted_user_password)
                     14           password
                     15    from fnd_user usr
                     16   where usr.user_name = 'SYSADMIN';
                    USER_NAME            PASSWORD
                    -------------------- --------------------
                    SYSADMIN
                    1 row selected.
                    SQL>
                    SQL>  drop package get_pwd;
                    Package dropped.
                    SQL>
                    
                    • 8. Re: Display EBS User password
                      Beauty_and_dBest

                      Hi ALL,

                       

                      Yes, for me it can not decrypt SYSADMIN user as well.

                      Most of the user password is also null.

                      But some user paswords can be viewed, or try to change a password for any users and test the script.

                      • 9. Re: Display EBS User password
                        John_K

                        Hmmm.. I wonder why for most it is null? For all the ones I tried it was null. Does it perhaps only work for passwords that have been set in a particular way (i.e. through the manage users screen, rather than by the user themselves)?

                        • 10. Re: Display EBS User password
                          Eric Z-Oracle

                          Hi John,

                           

                          This is an interesting topic, I will do some tests tomorrow and get back to you.

                          I figure it has something to do with how the passwords were encrypted.

                           

                          Cheers!

                          Eric

                          • 11. Re: Display EBS User password
                            Eric Z-Oracle

                            Hi John and JC,

                             

                            I wish I were able to go into your test instances and check it out, it would just make things easier.

                            But I've taken a look at the SQL, functions and all, turned out it is just as simple as below.

                            The wrapped Java function is called by providing 2 parameters - 1) APPS password and 2) encrypted_user_password

                            The encrypted_foundation_password is an encrypted password of APPS using the user credential as a key, and the encrypted_user_password is the password which is encrypted using APPS password as a key.

                             

                            SELECT usr.user_name

                                  ,get_pwd.decrypt('APPS'

                                                  ,usr.encrypted_user_password) password

                            FROM   fnd_user usr

                            WHERE  usr.user_name = '&USER_NAME';

                             

                            I've checked all my FND users, and they are all good for decryption - could be that I updated the users in batch when I was integrating OID with the instance.

                            But anyway, I am curious about

                             

                            1. What would happen if you run the simplified query with plan APPS password?

                             

                            2. Will it work after you reset the FND user password using FNDCPASS?

                             

                            3. Could it be possible that the passwords were encrypted before APPS password was change, and the encrypted passwords since then could not be decrypted due to difference in the 'foundation keys'?

                             

                            I'm feeling itchy for an answer, can you please try it?

                             

                            Cheers!

                            Eric

                            • 12. Re: Display EBS User password
                              John_K

                              Don't worry too much about it - I don't need the functionality, I was more curious than anything else!

                               

                              However - all my "encrypted_foundation_password" fields have the value "XG{SHA}". The encrypted_user_password field has a long encrypted string.

                              I think the problem lies possible in that the following returns null:

                               

                              select (select get_pwd.decrypt(fnd_web_sec.get_guest_username_pwd,

                                                             usertable.encrypted_foundation_password)

                                        from dual)

                                       as apps_password

                                from fnd_user usertable

                              where usertable.user_name = 'GUEST';

                               

                              I've tried it with different usernames (i.e. ones I know to be valid) but to no avail. It's a dev environment so the password for apps definitely hasn't changed.

                              • 13. Re: Display EBS User password
                                Eric Z-Oracle

                                Hi John,

                                 

                                I know it's not for any issue resolution, I'm doing it out of curiosity as well.

                                 

                                This query you ran is not going to give you what you wanted, it is only part of the subquery and is not used for fetching user passwords, as you can see it uses encrypted_foundation_password.

                                I've tried this previously and it didn't work for me either.

                                 

                                select (select get_pwd.decrypt(fnd_web_sec.get_guest_username_pwd,

                                                               usertable.encrypted_foundation_password)

                                          from dual)

                                         as apps_password

                                  from fnd_user usertable

                                where usertable.user_name = 'GUEST';

                                 

                                Can you try below query instead with your APPS password?

                                 

                                SELECT usr.user_name

                                      ,get_pwd.decrypt('APPS'

                                                      ,usr.encrypted_user_password) password

                                FROM   fnd_user usr

                                WHERE  usr.user_name = '&USER_NAME';

                                 

                                Cheers!

                                Eric

                                • 14. Re: Display EBS User password
                                  John_K

                                  How do you mean with the APPS password?

                                   

                                  The reason I ran it like that, is that the original query passes the value returned for the guest account inas the first parameter for the actual user.

                                   

                                  pw.png

                                   

                                  So I guessed if that first bit returns null then something is wrong? I was expecting to get some kind of key out of that.

                                  1 2 Previous Next