I can't believe my eyes! that script has a hard-coded password!

Hi folks!

Raise your hands! How many of you have seen or used hard-coded passwords in FDM Classic or FDMEE scripts? When I say passwords, I mean all type of passwords but especially database passwords.

If you are still reading this post, most probably... you have!

Hopefully what you are going to read below can help you to avoid showing the world how unsecured you scripts are :-)

Why do we need to use passwords in scripts?
There are many situations where you would need to use passwords in a script.

For example, you may want to open an Essbase connection to check outline members.

But among all of scenarios, probably the one you are more familiar with is opening database connections.
Do we really need to open a database connection from a script? There is enough material to write a new post about this topic so I will focus in situations where you really need it.

Why don't we want them to be hard-coded?
Code is not pretty if you can read a password...
Administrators can get furious...
Password can be context/environment dependent...
Change the password, change the script(s)...
See the script, see the password...
I don't think it complies with any security policy...
bla bla bla

Our starting point
Maybe you don't know what a hard-coded password is but I'm sure you have used them.

Let's see an example:

As you can see when I need to open a JDBC connection with a database, I must know:

• JDBC connection string
• DB user
• DB password

I'm going to use the FDMEE database as an example. However it's quite important to remark that most of the times you don't need to open a connection with the FDMEE database as it's already opened when the script starts. 

In my case, I need it. Why? it's a long story but let's summarize it...the database has some custom stored procedures returning cursors which can be executed only through callable statements as they are not supported using standard fdmAPI functions like executeDML. Don't ask me why using these stored procedure. Somebody else built it due to specific requirements and we were forced to re-use them. Simply that.

Multiple approaches to avoid hard-coding
This is not new in FDMEE. We already had to open database connections in FDM Classic.
There are many approaches to avoid hard-coding passwords:

• Use a System Environmental Variable to store the password and read it from the script
• Use encrypted configuration files to store passwords
• Encrypt password and decrypt in the script
• Use db links/linked servers from FDMEE database to source databases
• ...

Any of the solutions could easily suffer code injection. What's that? For example, if I have access to the script I can insert some code to write the decrypted password into a file. Even the solution I will show below can be "injected". 

In any case, if your require one security extra level, you can always implement a Java class to perform secured database connection and use it in your FDMEE scripts.

Today I will focus only in a clean solution to avoid hard-coding passwords in a very flexible way.

1st Step - Storing passwords for database connections in ODI

Let's be clear:
FDMEE uses ODI > ODI stores database connections in Topology > Let's use ODI!

As you can see we can store any database connection in Topology, either through ODI Studio or ODI Console (web). You may want to connect to a data warehouse to get data or export your Planning/Essbase data into a reporting system.

Luckily, if we are going to open a connection against FDMEE database, we won't need to create it as it is created with default installation. However any other connection can be created as needed.

ODI uses its own encoding/decoding so why not taking advantage of this? By inserting the password in ODI topology, we are getting them already encrypted and stored somewhere. 

But where is the password actually stored? 

FDMEE database has a set of tables prefixed by SNP for the ODI repository. Among all these tables there is one which stores the physical connections configured in Topology. This table is SNP_CONNECT.

If we run this simple query then we will see the encrypted password:

What do we now with that password? Can we pass it directly to the database connection? No, that's the answer.

2nd Step - Decrypting the Database Password

From my ODI times (I still do a lot with ODI but TBH a lot more with FDMEE) I used to play with the ODI SDK (Software Development Kit). After some investigation I found that the SDK API for ODI 11g (the one FDMEE uses so far although ODI 12c has similar ones) has a class called com.sunopsis.DwgObject which has a static method (can be easily called without creating any instance of the class) called snpDecypher. This method accepts a parameters as a string (encrypted password) and returns the decrypted password.

You can easily see it by opening odi-core.jar file with Eclipse:

Let's give a try!

Testing the solution with a FDMEE Custom Script
I want to keep things simple so I'm just going to show s Jython script which decrypts the password stored in ODI repository. Besides, it gets the FDMEE JDBC connection string and database user from System settings in FDMEE. Another option would be to get JDBC and USER also from the ODI repository :-)

As you already know, one of the strength points of Jython is that we can easily use Java classes to extend FDMEE functionality and decypher our database password:

       decryptedPwd = DwgObject.snpsDecypher(encryptedPwd)

If we run the custom script through Script Execution in FDMEE:

Voilà!

I hope you found this post helpful. It doesn't matter the method you use but remember...

No hard-coded passwords any more!!!