Thursday, August 30, 2012

More on encryption

SQLDeveloper stores their connection passwords in an xml file. Although it might seem safe since it is being stored in an encrypted way i found out that these can be decrypted.

I needed a password in readable format so i Googled for an answer :-).

Looking here i saw that others had the same question :-).
Luckily somebody else , mostly Adam Paynter, did all the leg work and made a java class to decode it.
Chris Jones made a Java class , ready to be used in the database, out of it.

So using this as the Java Class in the database:

import javax.crypto.*;
import javax.crypto.spec.*;

 * Decrypt passwords stored in Oracle SQL Developer. This is intended for
 * password recovery.
 * Passwords are stored in
 * ~/.sqldeveloper/system2.
 * .
public class Decrypt {
    public static byte[] decryptPassword(byte[] result)
            throws GeneralSecurityException {
        byte constant = result[0];
        if (constant != (byte) 5) {
            throw new IllegalArgumentException();

        byte[] secretKey = new byte[8];
        System.arraycopy(result, 1, secretKey, 0, 8);

        byte[] encryptedPassword = new byte[result.length - 9];
        System.arraycopy(result, 9, encryptedPassword, 0,

        byte[] iv = new byte[8];
        for (int i = 0; i < iv.length; i++) {
            iv[i] = 0;

        Cipher cipher = Cipher.getInstance("DES/CBC/PKCS5Padding");
        cipher.init(Cipher.DECRYPT_MODE, new SecretKeySpec(secretKey, "DES"),
                new IvParameterSpec(iv));
        return cipher.doFinal(encryptedPassword);

    public static void main(String[] args) {
        if (args.length != 1) {
            System.err.println("Usage:  java Decrypt ");

        if (args[0].length() % 2 != 0) {
                    .println("Password must consist of hex pairs.  Length is odd (not even).");

        byte[] secret = new byte[args[0].length() / 2];
        for (int i = 0; i < args[0].length(); i += 2) {
            String pair = args[0].substring(i, i + 2);
            secret[i / 2] = (byte) (Integer.parseInt(pair, 16));

        try {
            System.out.println(new String(decryptPassword(secret)));
        } catch (GeneralSecurityException e) {
and this as a PL/SQL wrapper :
create or replace
language java                                
 name 'Decrypt.main( java.lang.String[] )';
you now have something that decodes the passwords.
How to actually use this ?
Just use this in SQL Developer
----------Gebruik van de procedure 
set serveroutput on size 1000000;
call dbms_java.set_output (1000000); --reroute println etc to DBMS_OUTPUT
exec get_sql_pw('05F2CFAA600383C3614C41D6BE2A6558FE');  --decode pw; dbms_output shows password
Note the use of  dbms_java.set_output. This reroutes all the System.out lines to the DBMS_OUTPUT console. I did this so that i wouldn't have to rewrite the java stuff to return a value instead of writing it to the console.

There: ready to use to decrypt your SQL developer passwords.

Credits: Adam Paynter, Chris Jones.

Thursday, August 23, 2012

Using MD5 encryption in OWB

Just a quickie to demonstrate how easy it is to use MD5 hashes instead of the normal hash function in your owb mappings:.

 when INGRP1.TO_BE_HASHED is null then null

I used this make personal information in a table non-reversable unreadable. As you might know straightforward hashing is not unique enough in large datasets.
Thats the reason why i used an MD5 hash.

Later on in the project i was faced with the question of reverting the process. The powers that be have a way of changing their needs...

So i thought of  taking the source, with plain data, md5 hash it and compare that to the stored value.
That works but when you have a large dataset you'll need an index, and 
this : 


doesn't index.
You'll need an function-based index for this.
However using a function-based index with this throws a non-deterministic error. This means that the md5 function is compiled with the deterministic flag off, and as such can not be used in fbi's.

I created a wrapper function that just makes the MD5 function deterministic.
See here:

CREATE OR REPLACE FUNCTION md5er (a_hash IN varchar2) RETURN varchar2

 RETURN((DBMS_OBFUSCATION_TOOLKIT.MD5(input_string=> a_hash)));

Then you can create the fbi on the stage table that holds the source:
See here:

create index 

STG_EXPOR_FBIDX1 on STG_EXPORT(RAWTOHEX(md5er(a_hash=> nvl(,'empty') )));

Fbi's need their index updated with this:

exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'stg_export', estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

And now you can use the deterministic MD5 function called md5er in your query and have good performance.

update DWH.dwh_person_h ph

set ph.person_number = nvl((
 select xt.plain_data
  from (
         select xt1.source_field source_field 
         from stg.stg_export xt1
         group by xt1.source_field
        ) xt
    RAWTOHEX(md5er(a_hash=> nvl(xt.source_field,'empty') ))= ph.person_number

  ), ph.person_number)--for persons not in the source anymore

And all of a sudden another page has managed to fill itself.. 
BTW: i promised myself to write more often, once a year is not enough..
I have more in the works, and the XML post needs another follow-up.