Tuesday, June 16, 2009

OBIEE Get all users from RPD

I had to get all the users from a very large repository because they where implementing a new security model. Wrote a small script to make life easy:
'Read_Users.VBS
'John Minkjan
'http:// http://www.obiee101.blogspot.com/
'Get all the users from a repository
'1: Make an UDML export of the PRD using nqgenudml.exe
'2: Change the filename/location in this script
'3: Run the script from the command line cscript Read_Users.VBS > users.txt
Set objFSO = CreateObject("Scripting.FileSystemObject")

'point this to your UDML EXPORTSet
objFile = objFSO.OpenTextFile("E:\names.txt", ForReading)

Const ForReading = 1
Dim arrFileLines()
dim strRLinedim strTemp1dim strTemp2

i = 0

Do Until objFile.AtEndOfStream
strRline = objFile.ReadLine
if left(strRline,12) = "DECLARE USER" then
Redim Preserve arrFileLines(i)
arrFileLines(i) = strRline
i = i + 1
end if
Loop

objFile.Close
'Then you can iterate it like this
For Each strLine in arrFileLines
strTemp1 = MID(strLine ,15 , 50)
IF instr(strline,"}" ) >0 THEN
strTemp2 = MID(strLine , instr(strline,"{")+ 1, (instr(strline,"}") - (instr(strline,"{")+ 1))) ELSE strTemp2 = ""
END IF
WScript.Echo MID(strTemp1 ,1 , instr(strTemp1, """")-1) &";" & strtemp2
Next

Till Next Time

11 comments:

Ajay said...

Hi John,

Im an OBIEE developer, and have been following your blog for quite sometime... Excellent work!!!
Now, I have no idea about the VB scripts you have given. How/where do you execute these scripts? Any information on that will be greatly appreciated.

Thanks,
Ajay

John Minkjan said...

@Ajay,

Just put them in a text file ending .VBS. Then execute them from the command line using cscript. (They work only on a windows box)

regards

John

Anonymous said...

Hi John,

nice post,
we are in the process of defining security model and would like to get a list of users/responsibilities and their access to subject areas.
how can we generate such a report?

Thanks,
Bob

John Minkjan said...

@Bob,

Should be simple, just make a UDML export and loop trough the file searching for the security group membership of the user.

Regards

John

Andriy Yakushyn said...

Excellent post John! You're really thinking out of the box (I haven't thought about using other scripting languages alongside of OBIEE).

I'm going to give it a try - I can see how it can be useful!

When you have a minute -
Can you please take a quick look here - http://forums.oracle.com/forums/thread.jspa?threadID=916898&tstart=0 ?

I was wondering if you might have a solution - I know you like tricky things to solve!

Ajay said...

Thanks John.

Donald said...

I came to your blog just when I was surfing on this topic. I am happy that I found your blog and information I wanted.

Anonymous said...

hi John,

Is there a script to generate a list of server/session variables used within rpd..?

Thanks,
Matt

Annette said...

Hello John,

I work in the OBIEE space as well. Very nice work and thanks for sharing your knowledge with us. Had a question in a related area. How is the OLAP_PASSWORD variable used? Once the static variable is created, how should the connection pool be modified to pick up the variable? Any information will be very helpful.

John Minkjan said...

@Anonymous,

Just extra the RPD and do a CTRL-F on "variable" ?

regards

John

John Minkjan said...

@Annette,

Did you try valueof(:OLAP_PASSWORD) in the connection pool?

regards

John