Bookmark

About Me

I am Venkatakrishnan J, a Business Intelligence enthusiast working with Rittman Mead Consulting who likes blogging about acquisitions in the BI space, technical workings of the BI tools in general and Oracle Business Intelligence tools in particular. All the views expressed here are my own and does not reflect the views of Rittman Mead Consulting or Oracle. Going forward, i would be blogging at http://rittmanmead.com/blog.

Subscribe

Meta

Stats

Oracle BI EE 10.1.3.3/2 and OID – USER and GROUP – Phase 2

Posted by Venkatakrishnan J on October 12, 2007

In the last post, we saw how to establish OID authentication. But that was the simplest part in the sense that all one would have to do is establish the connection to OID by giving in the proper credentials. In this post, we would see how to go about getting GROUPs from OID for the logged in user. This is also straightforward once we know how to get the groups from OID using the user names. In order to the user related groups one would have to use the DBMS_LDAP package. This package has some methods that would return the groups of users. In our case, what we would do is to call this package from a pipelined function so that we can directly use this function in the repository. Following is the function that i used

Before creating the above function one would have to create a array type using the below code.

create or replace type array as table of varchar2(32767);

Now lets test out the above function.

As you see above i have 2 users orcladmin and Test in my OID that are associated with certain groups. The above function would return the data that we needed. Once this is done the next step is to create another session initialization block that would basically call this database function. If you do not have a connection pool for connecting to the schema of the database function create one. In my case i used a connection pool called Authentication.

Name the Initialization block as GroupIB and click on Edit Data Source.

Choose database as the data source and enter the above sql. Then go to Edit Data Target and set Row-Wise Initialization. This would convert the above sql in the datasource into a single row statement.

Just to be sure, test the initialization block (after changing :USER to orcladmin in the data source. You should be getting the output similar to the below picture.

Once this is done click on edit preference and click on Edit Execution Precedence and select the first initialization block (OIDIB) that we created in the last post. Also remeber to check the Used for Authentication check box.

Now go to BI Answers login page and login as orcladmin. In order to see whether our groups have been properly assigned lets create a sample report. In the title view of the report, enter the following

@{biServer.variables[‘NQ_SESSION.GROUP’]}

As you see above our groups have got populated properly into the GROUP variable.

oddjobsaid

Build Your Own Residual Income Business
Products to Make You Feel Great, a Strong Support Team, and a Revolutionary New, Lucrative Compensation Plan! Agel is a new company and is uniquely positioned to be the next giant in this area. The company has developed an entirely new category of products. Imagine being part of the next industry-changing innovation.

Sidhusaid

Thanks again for this info. I implemented this using Active Directory and as we didnt have groups in Active Directory and as there was no options to import all users, I had to create a separate table in some database and add user with their group. I followed the rest using your lines and it worked.

Harishsaid

Homerosaid

Hi Venkatakrishnan J,
I’m trying to bind OBIEE with MS Active Directory, I followed all the steps you describe but in the GET GROUP MEMBERSHIP section I get a -3 with the get_group_membership()… do i need to do something special to get the groups from MS AD??

Belizsaid

dpaussaid

I have implemented the above in DEV, however, I need to restrict users that belong only to one group to be authenticated. Do you have a post that shows how to restrict to only one group in the LDAP? I am using OBIEE 10.1.3.4.1 and the LDAP is Novell eDirectory (basically there is an Oracle bug that is being fixed in a future release, but I am implementing now).

Also, I cannot find the previous post to this one about the OIDIB initialization block.

Andréssaid

I also obtain the message “get_group_membership returns: -3″, but this is because I use the uid to get groups, and the function require user_id (I not know if this are the same), when i use the user name the function return groups correctly.