8 Replies Latest reply: Apr 4, 2014 7:22 PM by rp0428 RSS

    4.0 connections.xml encryption decryption

    darkmatter

      The encryption method has changed from 3.x to 4.0.  We distribute a connections.xml file for all our developers to use that has access to the many schemas under multiple environments.  We had a script that decrypts the 3.x passwords but now 4.0 has changed that.  Does anyone have any idea how to decrypt 4.0 passwords stored in the connections.xml file.  I have tried the extension showmepassword which actually performs the decryption but I can't seem to make a simple function that actually does the decrypt.  Thanks.

        • 1. Re: 4.0 connections.xml encryption decryption
          rp0428
          The encryption method has changed from 3.x to 4.0.  We distribute a connections.xml file for all our developers to use that has access to the many schemas under multiple environments.  We had a script that decrypts the 3.x passwords but now 4.0 has changed that.  Does anyone have any idea how to decrypt 4.0 passwords stored in the connections.xml file.  I have tried the extension showmepassword which actually performs the decryption but I can't seem to make a simple function that actually does the decrypt.

          Why do you need to be able to decrypt the passwords? That has NOTHING to do with distributing the xml file from one machine to another.

           

          Have a user that KNOWS the passwords create the proper connections and then distribute that 'template' XML file. You should NOT need to decrypt the passwords.

          • 2. Re: 4.0 connections.xml encryption decryption
            darkmatter

            You can't import into SQL Developer without having the key used to export.  The problem is we have the passwords for all our schemas stored in a table and in order for my process to build out the connections.xml file, I need to know how they encrypt passwords so I can create a function to do this.  Version 3.x used the DES with PKCS5 padding, so we was able to write our own subroutine to build out the encrypted password then we just generated the rest of the data needed for our connections.xml for everyone to import providing them the key. 

            • 3. Re: 4.0 connections.xml encryption decryption
              Gary Graham-Oracle

              Actually this sounds like an opportunity for you to make an enhancement request, either through Oracle Support or the SQL Developer Exchange, to our command line client.  As of the 4.0 release it offers the following features...

              C:\sqldev401\sqldeveloper\sqldeveloper\bin>sdcli
              Available features:
              cart: Database Cart Batch Tasks
              dba: Basic Batch DBA Tasks
              format: SQL Format Task
              migration: Database Migration Tasks
              reports: Basic Batch Reporting Tasks
              unittest: Unit Testing Batch Tasks

              Having "connections: Update Password Batch Task" as a feature would be ideal for you.  Otherwise, since you have already found an extension that knows what to do...

              I have tried the extension showmepassword which actually performs the decryption

              ...it should be fairly easy to find a decompiler that reveals the method that extension employs. Just generally providing encryption details (even if I knew) seems to be frowned upon as we see in a (very old) discussion on this topic...

              Password encryption in the connections xml file, how?

               

              Regards,
              Gary

              SQL Developer Team

              • 4. Re: 4.0 connections.xml encryption decryption
                rp0428
                You can't import into SQL Developer without having the key used to export.  The problem is we have the passwords for all our schemas stored in a table and in order for my process to build out the connections.xml file, I need to know how they encrypt passwords so I can create a function to do this.  Version 3.x used the DES with PKCS5 padding, so we was able to write our own subroutine to build out the encrypted password then we just generated the rest of the data needed for our connections.xml for everyone to import providing them the key. 

                I'm not sure I understand all of that. Correct me where I am wrong.

                The problem is we have the passwords for all our schemas stored in a table and in order for my process to build out the connections.xml file, I need to know how they encrypt passwords so I can create a function to do this.

                1. The sql developer xml file stores ENCRYPTED passwords

                2. Your store plain-text passwords

                3. You want to know how to ENCRYPT (not decrypt) plain-text passwords so you can produce the xml file with the proper ENCRYPTED passwords

                 

                Is that correct?

                 

                Why don't you just modify your table (passwords for all our schemas stored in a table) to store the sql developer ENCRYPTED password also.

                 

                1. You don't need a function of your own

                2. You don't need to know the encryption method or algorithm

                 

                That's the approach Oracle itself uses in the database. They don't store the unencrypted/unhashed passwords for the user. They store the hashed version. You can connect to the DB using that hashed version and you can create a user on another system using that hashed password.

                 

                One-way hashes provide much better security than storing plain-text passwords because they can NOT be decrypted.

                 

                That should be a far simpler solution to your problem. As I said above just have a user that KNOWS the passwords create the proper connections. Then take those ENCRTYPED values and store them in your table. Then use them when you create your XML files for distribution.

                 

                The way you are doing it now is a HUGE security risk.

                • 5. Re: 4.0 connections.xml encryption decryption
                  darkmatter

                  Thanks for you replies.  That is correct.  I was initially trying to decrypt the values that Oracle stored in the connections.xml becuase when you export, you either have to remove the passwords, or they get encrypted (or hashed) using a key and I was just trying to guess what type of encryption method they used by running various crypto in an anonymous block to try to figure it out.  But what you say is I just basically can use that value that SQL Developer exports for authenticating to that schema without having to manipulate that data any further?  I just assumed that with the 3.x version being an actual encrypted value that they followed suit for 4.0 and just increased the security.  We change our passwords somewhat quite often and the goal was to avoid having someone manually update all their SQL Developer passwords before they export out.  That's why we created this process that basically reads the new passwords from the updated table, encrypts the password using the shared key, and writes that to a connection.xml file, which no longer works for 4.0.  Anyways, I'm going to play with this hash and see if I can figure out a way to automate this without any one user having to 'update' passwords.  Thanks guys.

                  • 6. Re: 4.0 connections.xml encryption decryption
                    rp0428

                    But what you say is I just basically can use that value that SQL Developer exports for authenticating to that schema without having to manipulate that data any further?  I just assumed that with the 3.x version being an actual encrypted value that they followed suit for 4.0 and just increased the security.

                    No - that is NOT what I said. And I don't know what you mean by 'value that SQL Developer exports'. What 'export' are you talking about.

                     

                    Reread my reply. The Oracle database uses a HASH algorithm - not encryption - and stores the hashed password.

                     

                    Sql Developer stores an ENCRYPTED password in the XML file. Sql Developer needs to decrypt the password to submit a clear-text password to Oracle when connecting. Those are two DIFFERENT uses.

                     

                    If you are storing passwords for sql developer to use just store the encrypted version from the xml file.

                     

                    avoid having someone manually update all their SQL Developer passwords before they export out.

                    None of that means ANYTHING to me; I have no idea what you are talking about when you say 'export out'.

                     

                    The connections.xml file already stores the passwords in encrypted form. When you 'export' connections and save to a file you can then either have the passwords removed or have the encrypted passwords encrypted yet again by providing another password. That encrypts ALL of the already-encrypted passwords using the same key.

                     

                    If the list of sql connections is being supplied by you from a central location why do you need to have users export the ones on their machine?

                     

                    And if you need to add new connections to that central location just do what I said above. Have ONE superuser modify a MASTER copy of connections.xml to add/change/delete the connections and then save that new master file for later distribution. Or take the encrypted password and connection info out of it and put it into your DB table so you can reconstruct the connections.xml file later.

                     

                    I still don't see ANY need to actually perform your own encrypt/decrypt of what sql developer is already doing.

                    • 7. Re: 4.0 connections.xml encryption decryption
                      darkmatter

                      I appreciate your reply and I'm sorry I'm having such a hard time trying to define my problem.  My goal here is to avoid any superuser from having to modify a master copy of the connections.xml merely because we have 4 dedicated environments with 50+ schemas in each.  Instead I would like to update my PL/SQL package that go out to where the passwords are stored (in plain-text) on our internal website and read in each schema name and corresponding password.  From there I want my package to write out a physical file.  This file will be the connections.xml.  But I can't store the passwords in plain text and in order to automate this without having to go and forth via SQL Developer to determine what the updated encrypted password looks like, I want to update my encryption function to basically do what SQL Developer does already without having to use SQL Developer as the middle man.  Does that make sense?  That way when my package runs, it will spit out a connections.xml file built out completely with the correct encrypted password already in there.  Then I just let all the end users know to import this file using "xxxx" as the key. 

                      • 8. Re: 4.0 connections.xml encryption decryption
                        rp0428
                        I appreciate your reply and I'm sorry I'm having such a hard time trying to define my problem.  My goal here is to avoid any superuser from having to modify a master copy of the connections.xml merely because we have 4 dedicated environments with 50+ schemas in each.  Instead I would like to update my PL/SQL package that go out to where the passwords are stored (in plain-text) on our internal website and read in each schema name and corresponding password.  From there I want my package to write out a physical file.  This file will be the connections.xml.  But I can't store the passwords in plain text and in order to automate this without having to go and forth via SQL Developer to determine what the updated encrypted password looks like, I want to update my encryption function to basically do what SQL Developer does already without having to use SQL Developer as the middle man.  Does that make sense?  That way when my package runs, it will spit out a connections.xml file built out completely with the correct encrypted password already in there.  Then I just let all the end users know to import this file using "xxxx" as the key.

                        Thanks but I understand what you are wanting to do. And I understand that it may seem like that is a good method to deal with the distribution problem.

                         

                        I am trying to point out WHY I don't think that approach is the way to go and the problems that you will have now, and in the future, if you go that way.

                         

                        It is also probably illegal and could subject your company to legal action and penalties.

                         

                        Oracle's license agreements usually contain a 'no reverse enginerring' clause and they have NOT published the details of the specific methodology that they use for what you are discussing. Using such an approach would NOT be permitted at any of the compainies I have worked at in the last 25+ years. as that solution relies on undocumented and unsupported methods and is quite probably illegal.

                         

                        To the extent that you don't care about that there is no point in reading on. But I want to make sure that others reading this thread fully understand the issues and implications of trying to do what you are doing.

                         

                        Your original post was also pretty misleading in light of what you just said above. The statement above is all about ENCRYPTION but your original post was all about DECRYPTION:

                        We had a script that decrypts the 3.x passwords but now 4.0 has changed that.  Does anyone have any idea how to decrypt 4.0 passwords stored in the connections.xml file.  I have tried the extension showmepassword which actually performs the decryption but I can't seem to make a simple function that actually does the decrypt

                        You 'had a script the decrypts'. Now you can't make a function that 'does the decrypt'. Decryption has NOTHING to do with the statement and goal you just posted in your last reply.

                         

                        Here is a short list of SOME of the facts, as I understand them, that I think are relevant to your problem.

                         

                        1. Both the encryption protocol and the implemention methodology were changed between sql developer 3 and 4.

                         

                        2. The contents of the encrypted password stored in the connections.xml file are different between V3 and V4.

                         

                        3.  In V3 a hex representation of a 'secret key' and the encrypted password were stored. To decrypt it you peeled off the secret key and used it to create SecretKeySpec instance for DES. That was then used to decrypt the password.

                         

                        4. In V4 the contents are MUCH shorter and do NOT include any secret key. That secret key is now a GUID and is stored elsewhere.

                         

                        5. The secret key (GUID) is DIFFERENT for each sql developer installation.

                          a. you can NOT just copy/use a connections.xml file from one installation on a different installation since the secret key will be different.

                          b.you can NOT just install a new sql developer for one of your new users and simply copy an existing connections.xml file - you would also need to copy/replace the secret key GUID that it goes with

                          c. you can NOT copy an exported connections.xml file to ANY sql developer installation (even the original one)

                         

                        6. When you 'export' the connections and provide a password the encrypted passwords in the current connections.xml file are decrypted using the original GUID secret key and then reencrypted using a new secret key.

                         

                        7. Oracle could change the methodology at any time without notice breaking anything you implement that relies on it - just as it broke your old method.

                         

                        8. That extension you tried, showmepassword, will ONLY work for the connections.xml file on the machine it is installed on since it uses the GUID secret key that is on that machine. It is NOT a generic decrypter that will work for just any XML file.

                         

                        9. The extension has NO encryption functionality.

                         

                        If I were implementing what you are wanting to do I would do it the way I suggested before:

                         

                        1. create a master sql developer installation for distribution to all users. That would ensure that ALL users were using the SAME secret key.

                        2. create a master connections.xml file - that will generate the proper encrypted passwords

                        3. save the encrypted passwords from the master XML file to your own database table.

                        4. generate new connections XML files that have the proper subset of connections for your various users (not all of them need EVERY connection).

                        5. add the appropriate 'already encrypted' passwords to the new connections XML file.

                        6. distribute the new connections XML files and replace (not import) the users existing XML file

                         

                        If you want the users to use the 'import' facility you will have to have a super-user manually use the export facility for the proper connection subset. That would also mean that if different users needed different subset you would have to manually export each set in order to use a different password.

                         

                        Personally I would just auto-generate the XML file and replace the existing one rather than use the import facility.

                         

                        There may appear to be a downside to that 'master sql developer' method in that users could then swap/copy/steal another user's connections.xml file and it would work on their machine since all secret key GUIDs would be the same.

                         

                        But the fact even if the secret keys were different the could just swap/copy/steal the matching secret key when they get the XML file.

                         

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

                        *********************************************

                        The above caveats aside if you want to use the published API to encrypt and decrypt here is some sample code.

                         

                        Add the db-ca.jar and the adf-share-ca.jar to the project

                         

                        import oracle.jdevimpl.db.adapter.ReferenceWorker;
                        import javax.naming.StringRefAddr;

                        public class Main {
                            public static void main(String[] args) {
                                String sSecretKey = "MySecretKey"; // the secret sauce specified when exporting
                                String sPassword  = "tiger";
                                ReferenceWorker referenceWorker;
                                referenceWorker = ReferenceWorker.createDefaultWorker(sSecretKey);
                                StringRefAddr stringRefAddr = (StringRefAddr) referenceWorker.encrypt(null, sPassword.toCharArray(), null);

                                String sEncryptedPassword = stringRefAddr.getContent().toString();
                                System.out.println("SecretKey [" + sSecretKey + "].");
                                System.out.println("Password [" + sPassword + "].");
                                System.out.println("Encrypted Password [" + sEncryptedPassword + "].");
                                char[] c = referenceWorker.decrypt(new StringRefAddr(null, sEncryptedPassword), null);
                                String sUnencryptedPassword = new String (c);
                                System.out.println("Unencrypted Password [" + sUnencryptedPassword + "].");
                            }
                        }

                        compile-single:

                        run-single:

                        SecretKey [MySecretKey].

                        Password [tiger].

                        Encrypted Password [8N3coxCIdro=].

                        Unencrypted Password [tiger].

                        BUILD SUCCESSFUL (total time: 0 seconds)

                        Good luck with your project.