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.

Thursday, September 15, 2011

Using the Java Class component in OWB

I have been meaning to write about this for a long time.

I promised David Allan to write something about the Java Activity and then my personal life caught up with me so i had to put this on the backburner.
But here it is: the beginning of my blog about what i do for a living, dabbling around in Oracle stuff.
In the meantime David posted a hello world example here that covered the basics, so i'll focus more on the example i built instead of explaining all of it.
Using a java component isn't that difficult as you saw there but what can you do with it  ?
I have used it to process a very, very large (8GB) xml file that we needed as a source for a datawarehouse. And i have used to convert a simple xml extract from the owb repository to WordML.

I'll explain the xml processing in another topic and just explain the java stuff here.


We were faced with importing a huge xmlfile into a warehouse built with owb.
After lots of experimenting i ended up with a easy and clean solution, using a java based sax parser to process the xml file and convert it to 4 csv files.
Importing the csv files into your warehouse is easy then.
A sax parser is a java library that can do xml transformations for you.

It takes a xml file and a xslt file and spits out another file.

So we need for this to work the following bits:
  1. a sax parser, this is a java library that does the work
  2. an xslt document, this tells the library what to do
  3. an input file, the actual source
  4. a java vm, owb provides this for us
  5. some glue in owb to tie it together.
  6. a place to store this all
The sax parser is made by Saxonica, full details here. It is not for free but one of the best around with great support for a decent price.

Calling a XML Transformation goes like this under Windows:
--calling a xslt transformation from the commandline
java -cp saxon9ee.jar net.sf.saxon.Transform  -xsl:export.xsl -o:csv_export.txt -it:main filename=file:///c:\data\yourfile.xml

Breakdown of the parameters involved:

-> java : this is the java executable , owb will provide this for us
-> -cp  : os name of the library that does the parsing
-> net.sf.saxon.Transform : name of function inside the library
-> -xsl : parameter of the Transform function that tells where on the os the xml stylesheet resides 
-> -o : parameter of the Transform function that specifies where to put the output 
-> it:main name of the template in the xslt that will be processed first
-> filename=  : parameter used  in the xslt specified above.
All of the Transform parameters can be found here.

Next is getting it in owb. David explained this nice and easy so'll touch this just briefly and zoom in on the details.
Finished process flow