Wednesday, February 24, 2010

OBIEE: Create Repository Init Block

My post earlier today, OBIEE: Text File as Data Source , was the first part of my overall goal.

Here's what I'm trying to do. Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is.

The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments.

In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share.

We've pulled the text file into the RPD already. Now, I need to create 2 repository variables to hold these values. I open up the Administration tool, go to Manage then variables. You should see this:

variable manager

Then follow this picture to create a new Initialization Block

create init block

Name your Initialization block init_block_test

big picture

Leave the scheduler stuff alone for now, click on Edit Data Source

some funny caption goes here

First, browse for the connection pool

kthxbi

Select the get_local_password (yes, the name mysteriously changed from the default Connection Pool to "get_local_password," it makes sense doesn't it?).

teh tubes!

In the Default Initialization String text box, enter:
SELECT username, password 
FROM test.txt
You can test it if you want, but I'm saving it for the final step.

Your Initialization Block Data Source should look like this

bollocks

Now select Edit Data Target

edit variables

Which looks like this

add block variable target

Select New and enter INIT_USERNAME in the Name text box and make the Default Initializer 'USERNAME' (in single quotes).

init username variable

Do the same thing for the password column, INIT_PASSWORD and 'PASSWORD'

Now you are back at the main screen and the Test button (lower left hand corner) should be enabled. Select it.

test results

One final test...let's see if we can access these in the presentation layer.

success!

So it's a success right?

After I was done, I began to think..."You can access the repository variable from the presentation server"

Ummm...that's not good. Double checked the properties of both the Initialization Block and the Variables to see if there's a way to lock it down...and there's not. Because of this "small" little security issue, I'm not completely sold. It is a proof of concept, perhaps it should stay there.

6 comments:

Pete Scott said...

There is a command line utility that allows you to do this using a textfile source that you can keep secret...

My colleague, Venkat, blogged about it see

Pete Scott said...
This comment has been removed by the author.
Pete Scott said...

url got lost

http://oraclebizint.wordpress.com/2008/05/02/oracle-bi-ee-101332-automating-password-updates-of-connection-pools-and-users-command-line-options/

Christian Berg said...

Hi Pete, hi Chet,

Just before people start copying this again and using it at random:

The command line feature is as documented and officially supported as UDML. Meaning: Use at your own risk.

I don't want people nagging about how they killed their rpd. ;-)

Cheers,
C.

rnm1978 said...

hey chet,

nice post.

here's another set of posts about the admintool function that Christian referred to :-) http://rnm1978.wordpress.com/category/obiee/admintool/

have fun.

FWIW I don't think anyone's yet come up with a good solution to the problem of changing passwords across environments. I thought I'd cracked it with XML files but you can't use them for init blocks. There's a thread on OTN forum about it somewhere, Turribeach chimed in on it too.

Bear in mind if you are setting passwords as variables where the value of variables can be read, eg admin tool too.

The whole issue of migration OBIEE deployments through the environments is a bit of a pig's ear with no foolproof method that I've seen. Fingers crossed 11g pulls something out of the bag :)
I think people (me included) get carried away with trying to be too smart with it sometimes; sometimes an idiot-sheet and a quiet room is actually sufficient for ensuring decent environment control....

Christian Berg said...

Don't put too much hope into 11g just yet.

As for the deployment itself...agreed. I haven't seen anything really foolproof either and I prefer to do some manual check rather than relying 100% on command line scripts (especially where PROD is concerned).

Cheers